|Large table still very slow|
Tried everything to optimize
A week of work on and my sloq query log is still full.
Here's my table structure:
id int(10) UNSIGNED
Current size: 588,000 Rows
Here's my indexes:
id A 588925 NULLNULLBTREE
start_date 1 start_date A 215 NULLNULLBTREE
duration 1 duration A 4 NULLNULLBTREE
unit_code 1 siteCode A 489 NULLNULLBTREE
num_people 1 sleeps A 9 NULLNULLBTREE
prod_name 1 siteName A 489 NULLNULLBTREE
has_extras 1 hasHotTub A 1 NULLNULLBTREE
This is a query that takes 40 seconds:
SELECT start_date,old_price,new_price,spec_code,num_people,duration,spec_code FROM hoseasons_cache
WHERE product_id = 'LP5454'
AND start_date BETWEEN '2012-05-01' AND '2012-05-31' AND duration IN ('3','4','7');
What should I do? Would smaller tables with JOIN be faster?
Any help is very much appreciated.
I don't see an index on `product_id` and that is the main field you seem to be searching on.
Major issue: you have no index on product_id
Minor issue: duration is a tinyint, but you're using single quotes around the values- change to "duration IN (3,4,7)"
Possible issue: if you aren't using those fields for searching in other queries, drop the indexes on unit_code, num_people, prod_name, and has_extras (Unneeded indexes probably won't make the SELECT query slower, but can drastically increase the DB size and slow INSERTs and UPDATEs.)
How did I miss that one? Thank you - that's fixed it.