Filesort/Query performace

  • Filter
  • Time
  • Show
Clear All
new posts

  • Filesort/Query performace

    Hello, cracking site by the way

    Right to get to it...I have this query:

    story.stid, story.author, story.longheadline, story.storylead, story.storydescription, story.imageid, story.imagetext,story.defaultimage
    FROM story ,story_x_section
    story.siteid IN (520,95,96,97,98,99,100,101,102,103,104,105,193,13 0,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.

  • #2
    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!


    • #3

      Good you've found answer yourself. That is good to delay responses sometimes


      • #4
        Hi Peter,

        I must say your site's blummin brilliant. The major head ache of my working life is MySQL and stopping it from crashing..and that query above or ones like it are a major cause of these crashes.

        Your added to my bookmarks and my rss reader.



        • #5
          Thanks a lot hope it will continue to be helpful for you