•  

Announcement

Announcement Module
Collapse
No announcement yet.

how to prevent mysql from filesorting

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

  • how to prevent mysql from filesorting

    #1
    two tables are as follows .

    CREATE TABLE `table_a` ( `id` int(11) NOT NULL, `col1` date NOT NULL, PRIMARY KEY (`id`), KEY `col1` (`col1`))



    CREATE TABLE `table_b` ( `id` int(11) NOT NULL, `col1` varchar(255) NOT NULL, PRIMARY KEY (`id`))


    and then a query is


    select a.id, b.col1, a.col1 from table_a a, table_b b where a.id=b.id order by a.col1 desc;


    this query always does filesort. why?
    col1 column on table_a is arleady indexed.
    does simple 'order by' query on joined tables always filesort?

    how can i prevent mysql from filesorting.
    help me please..
    Tags: None

  • #2
    I see two indexes on table b which are not declared in your sql create statement so first make sure you show it all.
    optimize table might does it.

    Comment


    • #3
      thanks for your reply.

      but, there are no indexes on table b.
      there is a just only primary key on table because other columns are not used in where clause.

      Comment


      • #4
        This might be the same thing I was seeing for grouping, check this post.

        Basically, MySQL is refusing to use in-memory temp tables for the sort because of the presence of your varchar column. You can verify if that's the case by checking SHOW STATUS LIKE 'Created_tmp_disk_tables'. In my grouping case, even when there were only a few rows in my tables, it would still create the temp table on disk instead of in memory.

        Thing you can try:
        - normalizing your data (good for grouping, not so much for sorting)
        - changing the column to CHAR
        - If you can't change the data model easily, you can try putting your server's temp data directory on a tmpfs partition or other memory-based file system. This will speed things up (at the cost of RAM), but not nearly as much avoiding the filesort altogether.

        Comment

        Working...
        X