?
Solved

efficiency in MySQL. combined indexes on multiple tables?

Posted on 2007-07-29
7
Medium Priority
?
2,108 Views
Last Modified: 2008-01-09
I have two simple tables as follows:
mysql> describe task;
+-----------------------+--------------+------+-----+---------+----------------+
| Field                 | Type         | Null | Key | Default | Extra          |
+-----------------------+--------------+------+-----+---------+----------------+
| id                    | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| taskContext_id        | bigint(20)   | YES  | MUL | NULL    |                |
| state                 | int(11)      | YES  | MUL | NULL    |                |
+-----------------------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> describe task_context;
+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| id               | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| consecutiveFails | int(11)      | YES  | MUL | NULL    |                |
+------------------+--------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

There are 100'000 rows in the table "task" and 5 rows in table "task_context".
Note that all columns are indexed.

The problem is that this simple query takes 0.7 seconds, which it is too much:
SELECT COUNT(*)  FROM Task t LEFT JOIN Task_Context tc ON (t.taskContext_id = tc.id) WHERE t.state=1 AND tc.consecutiveFails<10;

Execution time:
0.03 seconds: SELECT COUNT(*)  FROM Task t WHERE t.state=1;
0.07 seconds: SELECT COUNT(*)  FROM Task t LEFT JOIN Task_Context tc ON (t.taskContext_id = tc.id);
0.05 seconds: SELECT COUNT(*)  FROM Task t LEFT JOIN Task_Context tc ON (t.taskContext_id = tc.id) WHERE tc.consecutiveFails<10;
0.70 seconds: SELECT COUNT(*)  FROM Task t LEFT JOIN Task_Context tc ON (t.taskContext_id = tc.id) WHERE t.state=1 AND tc.consecutiveFails<10;
0.48 seconds: SELECT COUNT(*)  FROM Task t WHERE t.state=1  AND (SELECT consecutiveFails FROM Task_Context tc WHERE t.taskContext_id = tc.id)<10;

Some more info:
mysql> explain SELECT COUNT(*)  FROM Task t LEFT JOIN Task_Context tc ON (t.taskContext_id = tc.id) WHERE t.state=1 AND tc.consecutiveFails<10;
+----+-------------+-------+-------+---------------------------------------+------------------+---------+--------------------+-------+-------------+
| id | select_type | table | type  | possible_keys                         | key              | key_len | ref                | rows  | Extra       |
+----+-------------+-------+-------+---------------------------------------+------------------+---------+--------------------+-------+-------------+
|  1 | SIMPLE      | tc    | range | PRIMARY,consecutiveFails              | consecutiveFails | 5       | NULL               |     1 | Using where |
|  1 | SIMPLE      | t     | ref   | FK272D857416C2F0,combined_index,state | FK272D857416C2F0 | 9       | sourceforge2.tc.id | 26485 | Using where |
+----+-------------+-------+-------+---------------------------------------+------------------+---------+--------------------+-------+-------------+
2 rows in set (0.00 sec)


mysql> explain SELECT COUNT(*)  FROM Task t WHERE t.state=1  AND (SELECT consecutiveFails FROM Task_Context tc WHERE t.taskContext_id = tc.id)<10;
+----+--------------------+-------+--------+----------------------+---------+---------+------------------+-------+-------------+
| id | select_type        | table | type   | possible_keys        | key     | key_len | ref              | rows  | Extra       |
+----+--------------------+-------+--------+----------------------+---------+---------+------------------+-------+-------------+
|  1 | PRIMARY            | t     | ref    | combined_index,state | state   | 5       | const            | 44887 | Using where |
|  2 | DEPENDENT SUBQUERY | tc    | eq_ref | PRIMARY              | PRIMARY | 8       | t.taskContext_id |     1 | Using where |
+----+--------------------+-------+--------+----------------------+---------+---------+------------------+-------+-------------+
2 rows in set (0.00 sec)


How to speed up this query (10 times)?
SELECT COUNT(*)  FROM Task t LEFT JOIN Task_Context tc ON (t.taskContext_id = tc.id) WHERE t.state=1 AND tc.consecutiveFails<10;
Is it possible to have a combined index with columns of different tables?


0
Comment
Question by:dportabella
  • 4
  • 3
7 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19587957
SELECT COUNT(*)  FROM Task t LEFT JOIN Task_Context tc ON (t.taskContext_id = tc.id) WHERE t.state=1 AND tc.consecutiveFails<10;
is the same as:
SELECT COUNT(*)  FROM Task t INNER JOIN Task_Context tc ON (t.taskContext_id = tc.id) WHERE t.state=1 AND tc.consecutiveFails<10;
the where clause with tc.xxxx makes it a inner join.

if you want to really make it a left join:
SELECT COUNT(*)  FROM Task t LEFT JOIN Task_Context tc ON (t.taskContext_id = tc.id AND tc.consecutiveFails<10) WHERE t.state=1 ;
maybe that also helps with the performance...
0
 

Author Comment

by:dportabella
ID: 19643787
angelll,
the thirst query you are giving does not have the expected results.

still, I need to speed up that query.

If both columns were in the same table, I could do a combined index:
INDEX(state, consecutiveFails)

How could I do a combined index, in the case that the two columns are in different tables?
(or any alternative to that)
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19643828
>How could I do a combined index, in the case that the two columns are in different tables?
you cannot.

but you need 2 combined indexes:
Task_Context ( consecutiveFails, id )
Task ( state, taskContext_id )

0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 

Author Comment

by:dportabella
ID: 19643849
how to do those combined indexes?
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1500 total points
ID: 19643879
in short:
create index idx_tc ON  Task_Context ( consecutiveFails, id );
create index idx_t ON Task ( state, taskContext_id );
0
 

Author Comment

by:dportabella
ID: 19643901
angelll,

still, there is not a combined index between state and consecutiveFails.

MySQL can only select one index (from the several available) when performing a query.
Thus, having two indexes does not solve anything.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19643947
>still, there is not a combined index between state and consecutiveFails.
which is not possible, as said already

>MySQL can only select one index (from the several available) when performing a query.
one per table, yes. but, as you join 2 tables, one index per table can be used.

0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating and Managing Databases with phpMyAdmin in cPanel.
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question