Database structure and speed

[heals1ic]

I am running this query against the tables below but I seem to be having performance problems (>30 sec per query). sup_prices has ~2600 records for 7 suppliers and 328 products.


SELECT supplier,product,price_each as price, R.prodid

FROM (products P INNER JOIN sup_prices R
ON P.prodid = R.prodid)

INNER JOIN suppliers S
ON S.supplierid = R.supplierid

WHERE (R.prodid,price_each)
IN (SELECT sup_prices.prodid, min(price_each)
FROM sup_prices GROUP BY sup_prices.prodid)

AND catid = 2

order by 'product'


PRODUCTS table
| prodid | catid | product |
------

SUP_PRICES table
| prodid | supplierid | price_each |
------ ----------

SUPPLIERS table
| supplierid | supplier |
---------

CATEGORIES table
| catid | category |
---------
This is the normalised structured solution I believe

Would it be an advantage to change the table structure to -

PRODUCTS table
| prodid | catid | product | sup_1_price | sup_2_price | sup_3_price | etc...
------

SUPPLIERS table
| supplierid | supplier |
---------

CATEGORIES table
| catid | category |
---------

This would decrease the number of records needed to traverse to get the result for the above query but move away from a normalised database structure.

Is indexing a table a solution?

Query speed is quite important as this query will be run often by many people concurrently.

Any ideas?

[chazzy]

for one, this looks like bad syntax:

WHERE (R.prodid,price_each)
IN (SELECT sup_prices.prodid, min(price_each)
FROM sup_prices GROUP BY sup_prices.prodid)


A where ... in ... over multiple columns is very slow.

you might also want to build a couple of indexes on the tables in question, the columns involved in wheres and joins specifically.