How can I debug a slow MySQL join?

There's not much to "debug". How long does it take to do just this?

SELECT count(*) FROM `deal_category`;

mysql> SELECT count(*) FROM `deal_category`; 
+----------+
| count(*) |
+----------+
|   157379 |
+----------+
1 row in set (0.03 sec)

mysql> SELECT count(*) FROM `deal`;
+----------+
| count(*) |
+----------+
|   146616 |
+----------+
1 row in set (0.03 sec)

mysql> SELECT count(*) FROM `category`;
+----------+
| count(*) |
+----------+
|       12 |
+----------+
1 row in set (0.00 sec)

Select allOpen in new window

try starting with

SELECT count(*) FROM `deal_category`;

add each join separately. is one slower then the other?

then try both joins. any notable difference from the change of table sequence?

This is joining tables 1-2 and 2-3.  Tables 1 and 3 can't be merged directly since there is no common column between them.  Even merging 2-3 in a subquery is faster for some reason.

mysql> SELECT count(1) FROM `category` JOIN `deal_category` ON deal_category.CategoryId = category.CategoryId;
+----------+
| count(1) |
+----------+
|   157380 |
+----------+
1 row in set (0.04 sec)

mysql> SELECT count(1) FROM `deal_category` JOIN `deal` ON deal.DealId = deal_category.DealId;
+----------+
| count(1) |
+----------+
|   157379 |
+----------+
1 row in set (0.30 sec)

mysql> SELECT count(1) FROM `category` INNER JOIN (SELECT `deal_category`.* FROM `deal_category` JOIN `deal` ON deal.DealId = deal_category.DealId) as `deal_category` ON deal_category.CategoryId = category.CategoryId;
+----------+
| count(1) |
+----------+
|   157379 |
+----------+
1 row in set (0.69 sec)

mysql> explain SELECT count(1) FROM `category` INNER JOIN (SELECT `deal_category`.* FROM `deal_category` JOIN `deal` ON deal.DealId = deal_category.DealId) as `deal_category` ON deal_category.CategoryId = category.CategoryId;             
+----+-------------+---------------+-------+---------------+-----------------+---------+-------------------------------+--------+-------------+
| id | select_type | table         | type  | possible_keys | key             | key_len | ref                           | rows   | Extra       |
+----+-------------+---------------+-------+---------------+-----------------+---------+-------------------------------+--------+-------------+
|  1 | PRIMARY     | category      | index | PRIMARY       | PRIMARY         | 4       | NULL                          |     12 | Using index |
|  1 | PRIMARY     | <derived2>    | ref   | <auto_key0>   | <auto_key0>     | 4       | bunch_www.category.CategoryId |   1085 | NULL        |
|  2 | DERIVED     | deal          | index | PRIMARY       | deal_status_idx | 1       | NULL                          | 108531 | Using index |
|  2 | DERIVED     | deal_category | ref   | dc_dealid_idx | dc_dealid_idx   | 5       | bunch_www.deal.DealId         |      1 | NULL        |
+----+-------------+---------------+-------+---------------+-----------------+---------+-------------------------------+--------+-------------+
4 rows in set (0.00 sec)

Select allOpen in new window

This is why it's so frustrating, there doesn't seem to be a clear reason WHY it's slow.

There is nothing to be gained from using subqueries

Start with
SELECT count(1) FROM `deal_category`
+----------+
| count(*) |
+----------+
|   157379 |
+----------+
1 row in set (0.03 sec)


then add
INNER JOIN `deal` ON  deal_category.DealId = deal.DealId
+----------+
| count(1) |
+----------+
|   157379 |
+----------+
1 row in set (0.30 sec)


then add
INNER JOIN `category` ON deal_category.CategoryId = category.CategoryId

????

That was the original query I started with.

mysql> SELECT count(1) FROM `deal_category` INNER JOIN `deal` ON  deal_category.DealId = deal.DealId INNER JOIN `category` ON deal_category.CategoryId = category.CategoryId;
+----------+
| count(1) |
+----------+
|   157379 |
+----------+
1 row in set (2.24 sec)

Select allOpen in new window

:) not quite,  the table sequence is different (and that can sometimes make a difference).

just in case, are the data types of
       deal_category.CategoryId
   & category.CategoryId
the same?


At this point my MySQL skills start to get seriously strained, but I would be looking at the health of the tables and indexes. I'm afraid I can't suggest much more than that as I know there are MySQL commands to do those things but I have never used them so won't attempt to advise on specifics.

Yep, both are int(10) NOT NULL

mysql> desc deal_category;
+-----------------+------------+------+-----+---------+----------------+
| Field           | Type       | Null | Key | Default | Extra          |
+-----------------+------------+------+-----+---------+----------------+
| DealCategoryId  | int(10)    | NO   | PRI | NULL    | auto_increment |
| DealId          | int(10)    | YES  | MUL | NULL    |                |
| CategoryId      | int(10)    | NO   | MUL | NULL    |                |
| PrimaryCategory | tinyint(1) | NO   |     | NULL    |                |
+-----------------+------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> desc category;
+---------------------------+--------------+------+-----+---------------+----------------+
| Field                     | Type         | Null | Key | Default       | Extra          |
+---------------------------+--------------+------+-----+---------------+----------------+
| CategoryId                | int(10)      | NO   | PRI | NULL          | auto_increment |
| CategoryName              | varchar(100) | YES  |     | NULL          |                |
| CategoryDispName          | varchar(64)  | NO   |     | NULL          |                |
| CategoryDescription       | text         | NO   |     | NULL          |                |
| Category                  | varchar(100) | YES  |     | NULL          |                |
| CategoryRadius            | int(11)      | YES  |     | NULL          |                |
| CategoryEnabled           | tinyint(1)   | NO   |     | 1             |                |
| Icon                      | varchar(255) | NO   |     |               |                |
| CategoryPreExpiryDisplay  | varchar(255) | YES  |     | D_STR_EXPIRES |                |
| CategoryPostExpiryDisplay | varchar(255) | YES  |     | D_STR_EXPIRED |                |
+---------------------------+--------------+------+-----+---------------+----------------+
10 rows in set (0.00 sec)

Select allOpen in new window

Hi!

Do you have and index on the table deal_category that has these columns

(CategoryId , DealId )   ?

If not then you should put this index on

create index deal_cat_ix on deal_category (CategoryId  asc , DealId asc) using btree;

It should speed up your query a little bit. :)

Regards,
    Tomas Helgi

Added that index, and doesn't really affect the overall time much.  It is being used instead of the individual indexes in the explain though.

Hi!

Can you post the new explain of your query ?

Regards,
     Tomas Helgi

Right, sorry

mysql> explain extended SELECT count(1) FROM `category` INNER JOIN `deal_category` ON deal_category.CategoryId = category.CategoryId INNER JOIN `deal` ON deal.DealId = deal_category.DealId;
+----+-------------+---------------+--------+--------------------------------------+-------------+---------+--------------------------------+------+----------+--------------------------+
| id | select_type | table         | type   | possible_keys                        | key         | key_len | ref                            | rows | filtered | Extra                    |
+----+-------------+---------------+--------+--------------------------------------+-------------+---------+--------------------------------+------+----------+--------------------------+
|  1 | SIMPLE      | category      | index  | PRIMARY                              | PRIMARY     | 4       | NULL                           |   12 |   100.00 | Using index              |
|  1 | SIMPLE      | deal_category | ref    | dc_dealid_idx,CategoryId,deal_cat_ix | deal_cat_ix | 4       | bunch_www.category.CategoryId  | 7154 |   100.00 | Using where; Using index |
|  1 | SIMPLE      | deal          | eq_ref | PRIMARY                              | PRIMARY     | 4       | bunch_www.deal_category.DealId |    1 |   100.00 | Using index              |
+----+-------------+---------------+--------+--------------------------------------+-------------+---------+--------------------------------+------+----------+--------------------------+
3 rows in set, 1 warning (0.00 sec)

Select allOpen in new window

Hi!

How does this query
SELECT count(1) FROM category c,deal_category dc,deal d where d.CategoryId = c.CategoryId and d.DealId = dc.DealId;

show up in your explain ?

This should return the same count.

Regards,
    Tomas Helgi

Similar explain and run time.

mysql> explain extended SELECT count(1) FROM category c, deal_category dc, deal d where dc.CategoryId = c.CategoryId and d.DealId = dc.DealId;
+----+-------------+-------+--------+--------------------------------------+-------------+---------+------------------------+------+----------+--------------------------+
| id | select_type | table | type   | possible_keys                        | key         | key_len | ref                    | rows | filtered | Extra                    |
+----+-------------+-------+--------+--------------------------------------+-------------+---------+------------------------+------+----------+--------------------------+
|  1 | SIMPLE      | c     | index  | PRIMARY                              | PRIMARY     | 4       | NULL                   |   12 |   100.00 | Using index              |
|  1 | SIMPLE      | dc    | ref    | dc_dealid_idx,CategoryId,deal_cat_ix | deal_cat_ix | 4       | bunch_www.c.CategoryId | 7154 |   100.00 | Using where; Using index |
|  1 | SIMPLE      | d     | eq_ref | PRIMARY                              | PRIMARY     | 4       | bunch_www.dc.DealId    |    1 |   100.00 | Using index              |
+----+-------------+-------+--------+--------------------------------------+-------------+---------+------------------------+------+----------+--------------------------+
3 rows in set, 1 warning (0.00 sec)

mysql> SELECT count(1) FROM category c, deal_category dc, deal d where dc.CategoryId = c.CategoryId and d.DealId = dc.DealId;
+----------+
| count(1) |
+----------+
|   157379 |
+----------+
1 row in set (2.46 sec)

Select allOpen in new window

Hi!

This is as optimal as it gets with regards to your query.
The execution time is this long due to your data and it's distribution.
Execute analyze table and optimize table on those tables and execute both explain and the query
to see if that helps.

Regards,
     Tomas Helgi

Analyze table and optimize table on the 3 tables joined didn't help the query time at all.

So the fastest way to do this is using a subquery?

mysql> SELECT count(1) FROM `category` INNER JOIN (SELECT `deal_category`.* FROM `deal_category` INNER JOIN `deal` ON deal.DealId = deal_category.DealId) as `deal_category` ON deal_category.CategoryId = category.CategoryId;
+----------+
| count(1) |
+----------+
|   157563 |
+----------+
1 row in set (0.77 sec)

mysql> SELECT count(1) FROM `category` INNER JOIN `deal_category` ON deal_category.CategoryId = category.CategoryId INNER JOIN `deal` ON deal.DealId = deal_category.DealId;                                             
+----------+
| count(1) |
+----------+
|   157563 |
+----------+
1 row in set (2.24 sec)

Select allOpen in new window

Hi!
 
Please see the grading guidelines here:
http://support.experts-exchange.com/customer/portal/articles/481419
A should be the default grade awarded unless the answer is deficient.
When closing the question, the asker should explain why a B grade was awarded.

Regards,
    Tomas Helgi

Very well. "A B grade means the solution given lacked some information or required you to do a good amount of extra work to resolve the problem".

No one had suggested to use subqueries or any other way to optimize the query to reduce the overall time, so I think this applies.  Since this original question was how to debug and / or make the query faster, I don't feel any answer sufficiently met the brief, although there was helpful information in your answer.