Hello, cracking site by the way 
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.