homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Gold Sponsor 2015!
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

Large table still very slow
Tried everything to optimize

5+ Year Member

Msg#: 4441376 posted 10:00 am on Apr 16, 2012 (gmt 0)


A week of work on and my sloq query log is still full.

Here's my table structure:

id int(10) UNSIGNED
product_id varchar(6)
num_people tinyint(2)
start_date date
duration tinyint(2)
old_price float(6,2)
new_price float(6,2)
unit_code varchar(4)
discount float(6,2)
area varchar(30)
zone varchar(25)
prod_desc text
spec_code varchar(8)
name varchar(50)
features text
main_description text
dateAdded date
has_extras tinyint(1)

Current size: 588,000 Rows

Here's my indexes:

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.



WebmasterWorld Senior Member whoisgregg us a WebmasterWorld Top Contributor of All Time 10+ Year Member

Msg#: 4441376 posted 2:34 pm on Apr 16, 2012 (gmt 0)

I don't see an index on `product_id` and that is the main field you seem to be searching on.


WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month

Msg#: 4441376 posted 2:40 pm on Apr 16, 2012 (gmt 0)

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.)


5+ Year Member

Msg#: 4441376 posted 8:38 am on Apr 17, 2012 (gmt 0)

How did I miss that one? Thank you - that's fixed it.

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved