Re: Avoiding "Using temporary; Using filesort"

[Danny Ó Cuív]

Thanks for replying, Rick. Sorry about the incomplete information. I was trying to strike a balance and didn't want to just paste in truckloads. But I'm happy to comply with whatever format is typically used here.

I have 4GB on the machine. I played around with upping the sort_buffer_size but I'm primarily interested in if the filesort can be avoided. (Note: When I previewed this, the EXPLAIN output went off the right of the page and no horizontal scrollbar appeared. However, it was possible to select the text and paste it into something else.)

Thanks in advance,

Danny.

mysql> SHOW CREATE TABLE publications\G -- engine, indexes 
*************************** 1. row ***************************
       Table: publications
Create Table: CREATE TABLE `publications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `publication_type_id` int(11) DEFAULT NULL,
  `date` date DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `is_valid` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `index_publications_on_date` (`date`)
) ENGINE=InnoDB AUTO_INCREMENT=2511 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

mysql> SHOW CREATE TABLE publication_credits\G -- engine, indexes 
*************************** 1. row ***************************
       Table: publication_credits
Create Table: CREATE TABLE `publication_credits` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `publication_id` int(11) NOT NULL,
  `internal` tinyint(1) DEFAULT '1',
  `author_id` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_publication_credits_on_publication_id` (`publication_id`),
  KEY `index_publication_credits_on_author_id` (`author_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6565 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

mysql> SHOW TABLE STATUS LIKE 'publications'\G -- sizes
*************************** 1. row ***************************
           Name: publications
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 3004
 Avg_row_length: 878
    Data_length: 2637824
Max_data_length: 0
   Index_length: 65536
      Data_free: 12582912
 Auto_increment: 2511
    Create_time: 2011-07-12 18:36:58
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_unicode_ci
       Checksum: NULL
 Create_options: 
        Comment:

mysql> SHOW TABLE STATUS LIKE 'publication_credits'\G -- sizes
*************************** 1. row ***************************
           Name: publication_credits
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 6243
 Avg_row_length: 83
    Data_length: 524288
Max_data_length: 0
   Index_length: 262144
      Data_free: 12582912
 Auto_increment: 6565
    Create_time: 2011-07-12 18:35:16
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_unicode_ci
       Checksum: NULL
 Create_options: 
        Comment:

mysql> explain select publications.* from publications inner join publication_credits on publications.id = publication_credits.publication_id where (publication_credits.author_id = 111) order by date desc;
+----+-------------+---------------------+--------+------------------------------------------------------------------------------------+----------------------------------------+---------+----------------------------------------+------+----------------------------------------------+
| id | select_type | table               | type   | possible_keys                                                                      | key                                    | key_len | ref                                    | rows | Extra                                        |
+----+-------------+---------------------+--------+------------------------------------------------------------------------------------+----------------------------------------+---------+----------------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | publication_credits | ref    | index_publication_credits_on_publication_id,index_publication_credits_on_author_id | index_publication_credits_on_author_id | 5       | const                                  |    1 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | publications        | eq_ref | PRIMARY                                                                            | PRIMARY                                | 4       |   publication_credits.publication_id   |    1 |                                              |
+----+-------------+---------------------+--------+------------------------------------------------------------------------------------+----------------------------------------+---------+----------------------------------------+------+----------------------------------------------+

mysql> SHOW VARIABLES LIKE '%buffer%'; -- cache size 
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| bulk_insert_buffer_size | 8388608  |
| innodb_buffer_pool_size | 8388608  |
| innodb_log_buffer_size  | 1048576  |
| join_buffer_size        | 131072   |
| key_buffer_size         | 16777216 |
| myisam_sort_buffer_size | 8388608  |
| net_buffer_length       | 16384    |
| preload_buffer_size     | 32768    |
| read_buffer_size        | 131072   |
| read_rnd_buffer_size    | 262144   |
| sort_buffer_size        | 2097144  |
| sql_buffer_result       | OFF      |
+-------------------------+----------+