Filesort/Query performace

Hello, cracking site by the way :slight_smile:

Right to get to it…I have this query:

SELECT
story.stid, story.author, story.longheadline, story.storylead, story.storydescription, story.imageid, story.imagetext,story.defaultimage
FROM story ,story_x_section
WHERE
story.siteid IN (520,95,96,97,98,99,100,101,102,103,104,105,193,130,165,170, 175,209,245,254,333,344,369,425,503,505)
AND story.stid = story_x_section.story_id
AND story.hold=‘no’
AND story.storydescription !=’’
AND story_x_section.section_id=8
ORDER BY livetime DESC
LIMIT 0,7

The “Story” table is about 400 meg in size with about 88,000 rows.

stid is my Primary Key, I have an index on siteid and on livetime.

The “story_x_section” table is about 3 meg in size and has story_id and section_id set as the primary key, also has an index on section_id.

If I do an explain on the above query I get…

±—±------------±----------------±-------±-------------------±--------±--------±----------------------±------±----------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |±—±------------±----------------±-------±-------------------±--------±--------±----------------------±------±----------------------------+| 1 | SIMPLE | story | range | PRIMARY,SITEID | SITEID | 4 | NULL | 11478 | Using where; Using filesort | | 1 | SIMPLE | story_x_section | eq_ref | PRIMARY,section_id | PRIMARY | 8 | snv4.story.stid,const | 1 | Using index | ±—±------------±----------------±-------±-------------------±--------±--------±----------------------±------±----------------------------+

The query takes about 30 seconds, which is just bonkers.

The simple way I suppose is to add a new column to the story table, what the query does is select stories flagged as section id 8 which is “front page news”, so I could flag a story as front page…but this defeats my cross over table and I am sure it should…if i can tweak the server/indexes better.

Hm right a bit of digging on your blog and I found this article:

http://www.mysqlperformanceblog.com/2006/09/01/order-by-limi t-performance-optimization

Used forced index on my livetime index and the query executes in 0.3 seconds…huzza. I will go and have a play now see if I can speed it up even more!

Heh,

Good you’ve found answer yourself. That is good to delay responses sometimes :slight_smile: