MySQL Forums
Forum List  »  Optimizer & Parser

Re: Avoiding "Using temporary; Using filesort"
Posted by: Danny Ó Cuív
Date: July 18, 2011 12:35PM

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      |
+-------------------------+----------+

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Avoiding "Using temporary; Using filesort"
1708
July 18, 2011 12:35PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.