0

I was under the impression that the more number of tables you join in your query the more its execution time increases, but something really opposite is happening in my case.

Here is the query where I am joining two tables task_group_attempt and task_group -

select count(*) from (
select ga.id, ga.task_group_id from task_group_attempt ga 
where ga.started_on >= '2015-05-30 18:30:00' and ga.started_on < '2015-06-30 18:30:00'
) tab1 
inner join task_group tg on tg.id = tab1.task_group_id and tg.task_group_type_id != 6

The value it is returning is 585856 and the execution time is 17.6 secs.

For the table task_group_attempt id is the primary key and indexing has been done to the column task_group_id.

For the table task_group id is the primary key and indexing has been done to the column task_group_type_id.

Now the second query which is just the extension of the first one and here we are joining one more table named task_attempt -

select count(*) from (
select ga.id, ga.task_group_id from task_group_attempt ga 
where ga.started_on >= '2015-05-30 18:30:00' and ga.started_on < '2015-06-30 18:30:00'
) tab1 
inner join task_group tg on tg.id = tab1.task_group_id and tg.task_group_type_id != 6
inner join task_attempt ta on tab1.id = ta.task_group_attempt_id

The value it is returning is 1109682 and the execution time is 7.88 secs.

For the table task_attempt indexing has been done to the column task_group_attempt_id.

I have checked it multiple times and every time the query execution time is almost or close to the above mentioned time.

ADDITION INFO -

Number of rows in task_group_attempt is 8565011
Number of rows in task_group is 1057
Number of rows in task_attempt is 15156598

It would be really helpful is somebody can help me understand how is this possible?

Rito
  • 3,092
  • 2
  • 27
  • 40
  • 1
    Does it make a difference if you run the queries in the opposite order? In other words, run the 3-join query first, then the 2-join query. Mysql caching can make a significant difference in run times. – Chris Lear Jun 16 '16 at 10:54
  • 2
    You'll have to show the query plans for anyone to be able to say anything. – Sami Kuhmonen Jun 16 '16 at 10:55
  • @Chris Lear that is exactly what I thought, may be because of the MySql caching the 3-join query is running fast. But that is not true, even running it in reverse order I see the same output. – Rito Jun 16 '16 at 11:48

0 Answers0