•  

Announcement

Announcement Module
Collapse
No announcement yet.

Avoid "using filesort"...

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

  • Avoid "using filesort"...

    #1
    I have this query :

    SELECT id, playerid1, playerid2, playerid3, score, value, scoretowinthemax, maxchange from table WHERE type = 123 and value >= 89.2 and value <= 129.2 and score > 12 order by score desc limit 20;

    I have an index type_value_score(type,value,score) but when I run the query and do explain, I get that it uses "using filesort" ( although it uses my index ) and it becomes slow because it needs to retrieve the rows twice for sorting the results. Usually if I have an index like the one above, it works... Any ideas how to overcome "using filesort" ? ( My table has 4,2 millions rows )

    here is my explain output :


    [MYSQL]+--------+-------+-------------------------------------------------------------------------------------+------------------+---------+------+--------+----------------------------+| table | type | possible_keys | key | key_len | ref | rows | Extra |+--------+-------+-------------------------------------------------------------------------------------+------------------+---------+------+--------+----------------------------+| ztrade | range | type_value_score,type_value_scoretowinthemax,type_ score,type_scoretowinthemax,score | type_value_score | 14 | NULL | 348625 | where used; Using filesort |+--------+-------+-------------------------------------------------------------------------------------+------------------+---------+------+--------+----------------------------+1 row in set (0.00 sec)[/MYSQL]
    Tags: None
  • #2
    you could try having individual keys for the type,value,score columns.

    I think that as score is the last part of your composite key, it cannot be used.

    Comment

    • #3
      fmpwizard wrote on Wed, 31 October 2007 04:17

      you could try having individual keys for the type,value,score columns.

      I think that as score is the last part of your composite key, it cannot be used.

      Actually jorje29 has done everything right.

      A query like that where all columns in the WHERE part is AND'ed together and then sorted by the last column will return the result without any filesort.

      And individual indexes will not be faster than a combined index on a query like this.

      But what throws MySQL of in this case is the "value >=89.2 AND value <= 129.2".
      Try it with an absolute value like "value = 90" for example and you can see that it gives you the execution plan you are after.

      As for a solution for this lack of optimization that MySQL doesn't handle I'm not really sure how to solve it.

      The only thing I can give you as advice directly is that you increase the sort_buffer_size so that the sorting is performed in RAM (unless you have already done so).

      BTW newer versions of MySQL is using the modified filesort algorithm as default to avoid reading the data twice read more about it here.

      Comment

      • #4
        fmpwizard and sterin thank you for your replies...I solved the problem with a unique way, but not perfect...I want also to ask you if you know anything further about this example below :

        As I wrote

        SELECT id, playerid1, playerid2, playerid3, score, value, scoretowinthemax, maxchange from table WHERE type = 123 and value >= 89.2 and value <= 129.2 and score > 12 order by score desc limit 20;

        with index (type,value,score)

        using filesort

        BUT

        ( Order is asc now, not desc )

        SELECT id, playerid1, playerid2, playerid3, score, value, scoretowinthemax, maxchange from table WHERE type = 123 and value >= 89.2 and value <= 129.2 and score > 12 order by score asc limit 20;

        doesn't !!!!

        my index is (type,value,score)

        I tried to create index (type,value,score desc) but it didn't work and also I didn't know how to verify that the index is in desc order...

        I decided to put "score" field into my table, in reverse order so I can sort in ascending order, using the index, avoiding filesort and output the "score" field after mutliplying it with(-1)...

        The surprise is here : (type,value,score) didnt work !!! but (type,score) did !!!

        So, I changed

        WHERE type = 123 and value >= 89.2 and value <= 129.2 and score > 12

        to

        WHERE type = 123 and score > 12 and value >= 89.2 and value <= 129.2

        I have my solution but still I don't know how to sort indexes according to my needs ( order by column )...Do you know how ?

        thanx again

        Comment

        • #5
          Try index on (type, score, value) - it should help.

          Comment

          • #6
            scoundrel,

            Did you read my post or you just replied ???

            Comment

            • #7
              jorje29 wrote on Thu, 01 November 2007 00:53


              I have my solution but still I don't know how to sort indexes according to my needs ( order by column )...Do you know how ?


              Unfortunately not possible.
              MySQL doesn't support sorting indexes in DESC order.
              It allows the SQL syntax for future expansion but doesn't care about it when creating the index.
              Indexes are always created in ascending order.

              Comment

              • #8
                Hi,

                any tips for avoiding filesort in a simple query like this?

                EXPLAIN SELECT id, name
                FROM table_name
                WHERE id IN ( 222839, 299872, 301535 )
                ORDER BY FIELD( id, 222839, 299872, 301535 )

                It's mysql 4.1, innodb, according to EXPLAIN primary (id) index is used, but it still uses filesort.

                Suggestions anyone?

                Comment

                • #9
                  myshpa,

                  Use UNION...for more details read here ( in the middle of the page there's an example )

                  http://www.mysqlperformanceblog.com/2006/08/14/mysql-followu p-on-union-for-query-optimization-query-profiling/

                  I hope, it's the solution for what you want to do...

                  Tell me if it works, I'm interested too to know, because I haven't used this method...

                  Comment

                  • #10
                    yes, that's it, it works ... maybe a little bit slower (i've just verified it works, no performance tests yet), but it really seems ok

                    thank you,

                    m.

                    Comment

                    • #11
                      myshpa wrote on Thu, 01 November 2007 19:59

                      Hi,

                      any tips for avoiding filesort in a simple query like this?

                      EXPLAIN SELECT id, name
                      FROM table_name
                      WHERE id IN ( 222839, 299872, 301535 )
                      ORDER BY FIELD( id, 222839, 299872, 301535 )

                      It's mysql 4.1, innodb, according to EXPLAIN primary (id) index is used, but it still uses filesort.


                      The thing is that on a query like this you don't have to care about the filesort.

                      Sorting is one of the last bits of processing that is performed before the server returns the result.
                      And in a query like this you have already limited the result to only 3 records with the where clause.

                      Which means that MySQL needs to "filesort" 3 rows and that takes about a nanosecond.

                      The query in the beginning of this thread is a totally different matter since that query's WHERE probably returned a _lot_ of rows and then you only want the top 20 of them since it uses ORDER BY ... DESC together with LIMIT 20.
                      That is why he wants to avoid a filesort.

                      So filesort on thousands of rows is bad, while filesort on just a few rows doesn't matter.

                      Comment

                      • #12
                        I agree with Sterin but I want also to note that the query returns 3 rows just because it uses (id) in the WHERE clause, otherwise this type of queries can produce thousands/millions of results and the solution with UNION helps having "filesort" on just a few rows...

                        Sterin, do you know, in my example, why index (type,value,score) didn't work but (type,score) did ??? I really don't have an idea...

                        Comment

                        • #13
                          jorje29 wrote on Thu, 01 November 2007 19:07


                          Sterin, do you know, in my example, why index (type,value,score) didn't work but (type,score) did ??? I really don't have an idea...


                          This is how indexes actually work. You have strict condition for "type" field, which allows mysql to choose some subset of the index rows, then it uses second part od the index to retrieve rows with "score > 12" and check them against "value >= 89.2 and value <= 129.2" condition. That is why I suggested to create an index on these three columns - to let mysql perform all WHERE checks and then only retrieve rows by primary key.

                          Comment

                          • #14
                            Scoundrel,

                            My question is :

                            Now I use (type,score,value) index and it doesn't use "filesort"

                            but

                            with (type,value,score) it uses "filesort"

                            Why ?

                            I changed my query to :

                            WHERE type = 123 and score > 12 and value >= 89.2 and value <= 129.2

                            Comment

                            • #15
                              jorje29 wrote on Thu, 01 November 2007 19:44


                              Scoundrel,

                              My question is :

                              Now I use (type,score,value) index and it doesn't use "filesort"

                              but

                              with (type,value,score) it uses "filesort"

                              Why ?

                              I changed my query to :

                              WHERE type = 123 and score > 12 and value >= 89.2 and value <= 129.2




                              For all following text T=type, V=value, s=score.

                              When you use (T,V,S) index on your table, you can look at it as on a copy of your original table, ordered by T, then by V, then by S. Now Try to put yourself on mysql's place: when you need to execute mentioned query you will do the following:

                              1) find a subset of rows where T=123 - it is easy because your data is ordered by T

                              2) get all records where T=123 and use only those, which have V between 89.2 and 129.2 - it is easy again because your table is ordered by V inside every distinct subset where T = const.

                              3) choose records where S > 12 - it is as easy as scan all records in step2 results and choose all needed rows

                              4) SORT resuts by S because all of them are ordererd by V (T is const).


                              If you'd use (T,S,V) key, then you'll:

                              1) find a subset of rows where T=123 - it is easy because your data is ordered by T

                              2) choose records where T=123 and use only those, which have S > 12 - it is easy again because your table is ordered by S inside every distinct subset where T = const.

                              3) take step2 results and get all records where V is between 89.2 and 129.2 - index scan

                              4) RETURN records to your user w/o sorting because all of your records are already ordered by S (while T is const).

                              Comment

                              Working...
                              X