MySQL Forums
Forum List  »  Optimizer & Parser

Re: Optimize Query with multiple joins
Posted by: Angel Lacustre
Date: March 28, 2011 04:28AM

Hi Rick, first of all, thanks for your ellaborated answer. I'll try to answer to your requirements

1) What are the indexes?

Dest: is the smallest of the tables. At this moment it has an index on the primary key, id. I tried creating an index on (min,max) and several other combinations, but MySQL never used them so I decided to remove them.

Dest_c: is the biggest of the tables. It has several indexes, but none of them are used. The index that I think is more relevant is (destId_A, value, points). There are two range conditions on this table, thus with the current query no index can be applied.

Act_r: has ~24K rows. The existing index is (actId, value, destId).

Act_t: has ~6K rows. The existing index is (actId, value_average, destId). This is the index used by MySQL in the query.

2) Which Engine are you using?

I'm using MyIsam. As I said, insertions on these tables are rare.

3) Is this an EAV schema?

I don't understand what you asking, sorry. There is an Entity Model we created before the schema, of course it has been modified over time. I don't think this answers your question.

We do have some big columns with text in them.

4) Show create table

sorry, but I'd rather not show this information. As I said, all the tables use MyISAM and utf8. The destId columns are smallint, which fulfills our needs.

5) Show table status
SHOW TABLE STATUS LIKE 'dest'\G;
*************************** 1. row ***************************
           Name: dest
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 1066
 Avg_row_length: 1446
    Data_length: 1541892
Max_data_length: 281474976710655
   Index_length: 54272
      Data_free: 0
 Auto_increment: 1130
    Create_time: 2011-03-26 01:47:36
    Update_time: 2011-03-28 12:15:45
     Check_time: 2011-03-26 01:47:37
      Collation: utf8_unicode_ci
       Checksum: NULL

SHOW TABLE STATUS LIKE 'dest_c'\G;
*************************** 1. row ***************************
           Name: dest_c
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 1141618
 Avg_row_length: 8
    Data_length: 9132944
Max_data_length: 2251799813685247
   Index_length: 120415232
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2011-02-14 17:20:47
    Update_time: 2011-03-24 13:30:51
     Check_time: 2011-03-11 11:08:34
      Collation: utf8_general_ci
       Checksum: NULL

SHOW TABLE STATUS LIKE 'act_r'\G;
*************************** 1. row ***************************
           Name: act_r
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 24574
 Avg_row_length: 22
    Data_length: 540628
Max_data_length: 6192449487634431
   Index_length: 721920
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2011-03-02 17:52:37
    Update_time: 2011-03-28 12:15:47
     Check_time: 2011-03-11 11:08:00
      Collation: utf8_unicode_ci
       Checksum: NULL

SHOW TABLE STATUS LIKE 'act_t'\G;
*************************** 1. row ***************************
           Name: act_t
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 6432
 Avg_row_length: 35
    Data_length: 225120
Max_data_length: 9851624184872959
   Index_length: 225280
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2011-03-02 17:52:35
    Update_time: 2011-03-25 16:55:23
     Check_time: 2011-03-11 11:08:00
      Collation: utf8_unicode_ci
       Checksum: NULL
5) EXPLAIN
+----+-------------+--------------+--------+-----------------------------------------------------------------------------+---------------+---------+--------------------------------------------------+------+----------+-------------------------------------------+ 
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | 
+----+-------------+--------------+--------+-----------------------------------------------------------------------------+---------------+---------+--------------------------------------------------+------+----------+-------------------------------------------+ 
| 1 | SIMPLE | t_1 | range | PRIMARY,id_act_type | id_act_type | 5 | NULL | 11 | 81.82 | Using where; Using index | 
| 1 | SIMPLE | t_2 | eq_ref | PRIMARY,other....... | PRIMARY | 5 | const,db.t_1.destId | 1 | 100.00 | Using where | 
| 1 | SIMPLE | a_0 | eq_ref | PRIMARY,other....... | PRIMARY | 3 | db.t_scores_2.destId,const | 1 | 100.00 | Using where | 
| 1 | SIMPLE | a_1 | eq_ref | PRIMARY,other....... | PRIMARY | 3 | db.t_scores_2.destId,const | 1 | 100.00 | Using where | 
| 1 | SIMPLE | dest | eq_ref | PRIMARY,other....... | PRIMARY | 2 | db.a_1.destId | 1 | 100.00 | Using where | 
| 1 | SIMPLE | t_0 | eq_ref | PRIMARY,other....... | PRIMARY | 5 | const,db.dest.id | 1 | 100.00 | Using where | 
| 1 | SIMPLE | dc | eq_ref | PRIMARY,other....... | PRIMARY | 4 | const,db.dest.id | 1 | 100.00 | Using where | 
| 1 | SIMPLE | act_r | eq_ref | PRIMARY,other....... | PRIMARY | 3 | db.t_0.destId,const | 1 | 100.00 | Using where | 
+----+-------------+--------------+--------+-----------------------------------------------------------------------------+---------------+---------+--------------------------------------------------+------+----------+-------------------------------------------+
6) SHOW VARIABLES LIKE '%buffer%';
+-------------------------+-----------+
| 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         | 134217728 |
| myisam_sort_buffer_size | 8388608   |
| net_buffer_length       | 16384     |
| preload_buffer_size     | 32768     |
| read_buffer_size        | 2097152   |
| read_rnd_buffer_size    | 4194304   |
| sort_buffer_size        | 33554432  |
| sql_buffer_result       | OFF       |
+-------------------------+-----------+

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Optimize Query with multiple joins
1838
March 28, 2011 04:28AM


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.