Professional Web Applications Themes

why filesort? - MySQL

Hi, I have a simple query like this: SELECT a FROM data ORDER BY a limit 10; Now when I let mysql 5.0.24a "explain" this query, it gives: mysql> explain select abs_pressure from data order by abs_pressure limit 10; +----+-------------+-------+------+---------------+------+---------+------+-------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+-------+----------------+ | 1 | SIMPLE | data | ALL | NULL | NULL | NULL | NULL | 53912 | Using filesort | +----+-------------+-------+------+---------------+------+---------+------+-------+----------------+ 1 row in set (0.00 sec) So it seems it always does a filesort, even ...

  1. #1

    Default why filesort?

    Hi,

    I have a simple query like this:
    SELECT a FROM data ORDER BY a limit 10;
    Now when I let mysql 5.0.24a "explain" this query, it gives:

    mysql> explain select abs_pressure from data order by abs_pressure limit 10;
    +----+-------------+-------+------+---------------+------+---------+------+-------+----------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref |
    rows | Extra |
    +----+-------------+-------+------+---------------+------+---------+------+-------+----------------+
    | 1 | SIMPLE | data | ALL | NULL | NULL | NULL | NULL |
    53912 | Using filesort |
    +----+-------------+-------+------+---------------+------+---------+------+-------+----------------+
    1 row in set (0.00 sec)

    So it seems it always does a filesort, even for simple queries like this.

    More details:
    - the system has 2GB of ram which 1.1GB free(!)
    - the table has 54k rows
    - the table uses 6.5MB diskspace
    - the table is fairly simple:
    mysql> describe data;
    +----------------+--------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +----------------+--------------+------+-----+---------+-------+
    | timestamp | datetime | NO | PRI | NULL | |
    | temp_in | decimal(4,1) | NO | | 0.0 | |
    | temp_out | decimal(4,1) | NO | | 0.0 | |
    | dewpoint | decimal(4,1) | NO | | 0.0 | |
    | rel_hum_in | tinyint(3) | NO | | 0 | |
    | rel_hum_out | tinyint(3) | NO | | 0 | |
    | windspeed | decimal(4,1) | NO | | 0.0 | |
    | wind_direction | char(3) | NO | | NULL | |
    | wind_angle | decimal(4,1) | NO | | 0.0 | |
    | wind_chill | decimal(4,1) | NO | | 0.0 | |
    | rain_1h | decimal(6,1) | NO | | 0.0 | |
    | rain_24h | decimal(6,1) | NO | | 0.0 | |
    | rain_1w | decimal(7,1) | NO | | 0.0 | |
    | rain_1m | decimal(7,1) | NO | | 0.0 | |
    | rain_total | decimal(8,1) | NO | | 0.0 | |
    | rel_pressure | decimal(8,1) | NO | | 0.0 | |
    | abs_pressure | decimal(8,1) | NO | | 0.0 | |
    | tendency | varchar(10) | NO | | NULL | |
    | forecast | varchar(7) | NO | | NULL | |
    +----------------+--------------+------+-----+---------+-------+
    19 rows in set (0.01 sec)

    - sort buffer of 8MB:
    mysql> show variables like '%sort%';
    +---------------------------+------------+
    | Variable_name | Value |
    +---------------------------+------------+
    | max_length_for_sort_data | 1024 |
    | max_sort_length | 1024 |
    | myisam_max_sort_file_size | 2147483647 |
    | myisam_sort_buffer_size | 8388608 |
    | sort_buffer_size | 2097144 |
    +---------------------------+------------+
    5 rows in set (0.00 sec)

    - database is MyISAM:
    ) ENGINE=MyISAM DEFAULT CHT=latin1;

    --
    --------------------------------------------------------------------
    Phone: +31-6-41278122, PGP-key: 1F28D8AE, www.vanheusden.com


    Folkert Guest

  2. #2

    Default Re: why filesort?

    On 22 Feb, 13:34, "Folkert van Heusden" <com>
    wrote: 
    Since `a` isn't a valid row in the table, I think MySQL is doing
    pretty good to give you any output at all!

    Captain Guest

  3. #3

    Default Re: why filesort?

    Right.
    You're kidding right?
    If not: that query was an example.
    To please you:
    SELECT temp_out FROM data ORDER BY temp_out limit 10;

    "Captain Paralytic" <com> wrote in message
    news:googlegroups.com...
    On 22 Feb, 13:34, "Folkert van Heusden" <com>
    wrote: 
    Since `a` isn't a valid row in the table, I think MySQL is doing
    pretty good to give you any output at all!


    Folkert Guest

  4. #4

    Default Re: why filesort?

    Folkert van Heusden wrote: 
    > Since `a` isn't a valid row in the table, I think MySQL is doing
    > pretty good to give you any output at all!
    >
    >[/ref]

    No, he's not kidding. The actual column name makes a BIG difference.

    First question - do you have an index on temp_out? It looks like you don't.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  5. #5

    Default Re: why filesort?

    I understood it and found it too obvious that's why I asked.

    Ok no index on temp_out because a) the table gets updated way more then read
    (at least 250 times more often updated) and furthermore I would have to have
    an index for each column.
    Also I was wondering why it goes to file as there's not so much data in the
    table, it should easily fit in memory when sorting.
     
    "Jerry Stuckle" <net> wrote in message
    news:com... 
    >> Since `a` isn't a valid row in the table, I think MySQL is doing
    >> pretty good to give you any output at all!
    >>
    >>[/ref]
    >
    >
    > --
    > ==================
    > Remove the "x" from my email address
    > Jerry Stuckle
    > JDS Computer Training Corp.
    > net
    > ==================[/ref]


    Folkert Guest

  6. #6

    Default Re: why filesort?

    Folkert van Heusden wrote: 
    >>
    >> --
    >> ==================
    >> Remove the "x" from my email address
    >> Jerry Stuckle
    >> JDS Computer Training Corp.
    >> net
    >> ==================[/ref]
    >
    >
    > I understood it and found it too obvious that's why I asked.
    >
    > Ok no index on temp_out because a) the table gets updated way more[/ref]
    then read 
    to have 
    in the 

    (Top posting fixed)

    MySQL may or may not actually write to a temp file. The EXPLAIN
    wouldn't know for sure how much data will be sorted (it doesn't actually
    retrieve the data).

    The temp file(s) will be written if the amount of data exceeds
    sort_buffer_size in your mysql configuration.

    See http://dev.mysql.com/doc/internals/en/filesort.html for a
    description on how MySQL does a filesort.

    P.S. Please don't top post. Thanks.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139