Announcement

Announcement Module
Collapse
No announcement yet.

Help optimizing a query not to use filesort

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Help optimizing a query not to use filesort

    Hi,

    The query i'm using is:

    SELECT changes.user_id, changes.changename, changes.date, changes.link, changes.changetype
    FROM changes, friends
    WHERE (friends.user_id=SOMEID AND friends.user_id=changes.user_id) OR (friends.user_id2=SOMEID AND friends.user_id2=changes.user_id) AND changes.date>SOMEDATE
    GROUP BY changes.change_id
    ORDER BY changes.date DESC
    LIMIT 0, 10

    This query will list a set of changes made by a user, or one of their listed friends (sort of like a mini-feed in Facebook).

    When I run an explain on the query I get the output shown in the file attached.

    The table 'changes' currently has around 180,000 rows (60,000 additional rows get added a month on average - table is 3 months old). So the table is very large, but this is the nature of the table and what it's meant to do. That's why im using the date to limit how much is fetched.

    The iamge attached also has the table structures

  • #2
    bump!

    Comment


    • #3
      Two questions:
      What version of mysql are you using?
      Why not use the date type for date and use an index on that date?

      Comment


      • #4
        Mysql version 5.0.45
        Date format isn't being used as I use linux timestamps - don't all developers use this nowadays? :-/

        Comment


        • #5
          I always use date as date makes my sql easier to read.
          There is no index on date
          could an combined index on user_id and date help?

          What about:

          SELECT changes.user_id, changes.changename, changes.date, changes.link, changes.changetype
          FROM changes, friends
          WHERE changes.user_id=SOMEID and ((friends.user_id=changes.user_id) OR (friends.user_id2=changes.user_id)) AND changes.date>SOMEDATE
          GROUP BY changes.change_id
          ORDER BY changes.date DESC

          Comment

          Working...
          X