can i do this query without scanning each row?

Forum Moderators: open

Message Too Old, No Replies

can i do this query without scanning each row?

lethal0r

6:07 pm on Apr 2, 2007 (gmt 0)

SELECT *, MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT(*) AS numItems FROM products WHERE shop='shopname' GROUP BY name LIMIT 0,10

this checks over 300,000 records and takes ~10 seconds to query.

I tried indexing the price field but it doesnt make any difference, is there anything at all I can do so it doesnt have to check all those 300,000+ rows?

LifeinAsia

6:17 pm on Apr 2, 2007 (gmt 0)

How is it supposed to give you accurate results unless it searches all the records?
You can try indexing the shop field as well, but CHAR/VARCHAR searches are almost always slower than numeric searches.

Alternatively, you can build a separate Prices table, where the fields are shop, MinPrice, and MaxPrice. Periodically repopulate that table then run your other queries off that table.

Or if your prices are updated frequently, set a trigger to update the row for that shop whever there is a price change.

justageek

6:20 pm on Apr 2, 2007 (gmt 0)

Are you sure you are not missing one for for 'shop' and 'name'?

JAG

lethal0r

6:51 pm on Apr 2, 2007 (gmt 0)

yeah it has an index on the shop & name fields.

the extra table is something I could look at.

i tried increasing some values such as key_buffer and sort_buffer_size in the my.cnf file but it made little difference. is this file largely for just making little 1 or 2% performance tweaks?

my server only has 512MB RAM, would an upgrade help? or would that again be just a small performance increase?

thank you for the replies.

souFrag

7:11 pm on Apr 2, 2007 (gmt 0)

what if you change "shop" and "name" to int instead of varchar?

so it would be sth like this:

shop(int shop_id, varchar shopname)
product_name(int productname_id, varchar name)
products(int some_unique_key, int productname_id, int price, etc..)

(i assume "name" is the product name)