How can I debug a slow MySQL join?

I am trying to debug the following query.

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

Open in new window


So far, I have tried an explain extended, but it doesn't seem to give me any useful information to work from.

mysql> explain extended SELECT count(1) FROM `category` INNER JOIN `deal_category` ON deal_category.CategoryId = category.CategoryId 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 | CategoryId | 4       | bunch_www.category.CategoryId  | 7153 |   100.00 | Using where |
|  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)

Open in new window


Any ideas how to make this faster, or to get more information?
auscomAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

SELECT count(*) FROM `deal_category`;
auscomAuthor Commented:
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)

Open in new window

PortletPaulEE Topic AdvisorCommented:
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?
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

auscomAuthor Commented:
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)

Open in new window


This is why it's so frustrating, there doesn't seem to be a clear reason WHY it's slow.
PortletPaulEE Topic AdvisorCommented:
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

????
auscomAuthor Commented:
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)

Open in new window

PortletPaulEE Topic AdvisorCommented:
:) 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.
auscomAuthor Commented:
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)

Open in new window

Tomas Helgi JohannssonCommented:
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
auscomAuthor Commented:
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.
Tomas Helgi JohannssonCommented:
Hi!

Can you post the new explain of your query ?

Regards,
     Tomas Helgi
auscomAuthor Commented:
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)

Open in new window

Tomas Helgi JohannssonCommented:
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
auscomAuthor Commented:
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)

Open in new window

Tomas Helgi JohannssonCommented:
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
auscomAuthor Commented:
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)

Open in new window

Tomas Helgi JohannssonCommented:
Hi!

Sometimes you will need to be smarter than the optmizer. ;)
Using parenthesis () around a query / subquery forces the database to execute the innermost query first and then do the next query and so on until all the query is done.
This has sometimes good or bad affects on performance and execution times in terms of data distribution and statistics, table size (amount of records), table/index structure and complexity of the query.
In your case it is faster as you force the database to join deal_category and deal before joining it to the category table.

Regards,
      Tomas Helgi

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tomas Helgi JohannssonCommented:
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
auscomAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.