Page 1 of 1

Query Optimization

Posted: Tue Apr 26, 2011 2:54 pm
by Celauran
I've been looking through my slow queries logs and have encountered, among others, the following:

Code: Select all

# Query_time: 2.030830  Lock_time: 0.001278 Rows_sent: 208  Rows_examined: 16250
SELECT ct.cityid, c.countryid, COUNT(*) as adcnt
  FROM clf_ads a
  INNER JOIN clf_cities ct ON ct.cityid = a.cityid AND (a.enabled = '1' AND a.verified = '1' AND a.expireson >= NOW())
  INNER JOIN clf_countries c ON ct.countryid = c.countryid
  WHERE ct.enabled = '1' AND c.enabled = '1'
  GROUP BY ct.cityid
Rows examined seems awfully high. I'm not an SQL expert by any stretch of the imagination, so I'm hoping someone here can provide some tips on how to optimize this query, or point me in the direction of a particularly good source for such information. I've been reading hackmysql.com, but either I'm missing some crucial point(s) or the queries used in their examples don't translate well to the ones I need to fix.

Here is some more info that may be pertinent/useful:

Code: Select all

EXPLAIN <above query>
+----+-------------+-------+------+-------------------------+-----------+---------+-----------------+------+----------------------------------+
| id | select_type | table | type | possible_keys           | key       | key_len | ref             | rows | Extra                            |
+----+-------------+-------+------+-------------------------+-----------+---------+-----------------+------+----------------------------------+
|  1 | SIMPLE      | c     | ref  | PRIMARY,enabled         | enabled   | 1       | const           |    2 | Using where; temporary; filesort |
|  1 | SIMPLE      | ct    | ref  | PRIMARY,countryid       | countryid | 2       | ads.c.countryid |   89 | Using where                      |
|  1 | SIMPLE      | a     | ref  | cityid,enabled_verified | cityid    | 2       | ads.ct.cityid   |   70 | Using where                      |
+----+-------------+-------+------+-------------------------+-----------+---------+-----------------+------+----------------------------------+

mysql> SHOW INDEX FROM clf_ads;
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
| Table   | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type |
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
| clf_ads |          0 | PRIMARY          |            1 | adid        | A         |       15756 |     NULL | NULL   |      | BTREE      |
| clf_ads |          1 | subcatid         |            1 | subcatid    | A         |         137 |     NULL | NULL   |      | BTREE      |
| clf_ads |          1 | cityid           |            1 | cityid      | A         |         225 |     NULL | NULL   |      | BTREE      |
| clf_ads |          1 | origin           |            1 | origin      | A         |          67 |     NULL | NULL   |      | BTREE      |
| clf_ads |          1 | ip               |            1 | ip          | A         |       15756 |     NULL | NULL   |      | BTREE      |
| clf_ads |          1 | enabled_verified |            1 | enabled     | A         |           2 |     NULL | NULL   |      | BTREE      |
| clf_ads |          1 | enabled_verified |            2 | verified    | A         |           4 |     NULL | NULL   |      | BTREE      |
| clf_ads |          1 | subcatid_cityid  |            1 | subcatid    | A         |         137 |     NULL | NULL   |      | BTREE      |
| clf_ads |          1 | subcatid_cityid  |            2 | cityid      | A         |        2626 |     NULL | NULL   |      | BTREE      |
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+

mysql> SHOW INDEX FROM clf_cities;
+------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
| Table      | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type |
+------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
| clf_cities |          0 | PRIMARY   |            1 | cityid      | A         |         267 |     NULL | NULL   |      | BTREE      |
| clf_cities |          1 | countryid |            1 | countryid   | A         |           3 |     NULL | NULL   |      | BTREE      |
+------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+

mysql> SHOW INDEX FROM clf_countries;
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
| Table         | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
| clf_countries |          0 | PRIMARY  |            1 | countryid   | A         |           3 |     NULL | NULL   |      | BTREE      |
| clf_countries |          1 | enabled  |            1 | enabled     | A         |           1 |     NULL | NULL   |      | BTREE      |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+

Re: Query Optimization

Posted: Thu Apr 28, 2011 5:44 am
by Darhazer
You can use only 1 index at a time, so having so many indexes does not make sense, except if you have different queries that use different columns
First try runnign this:

Code: Select all

SELECT ct.cityid, c.countryid, COUNT(*) as adcnt
  FROM clf_ads a
  INNER JOIN clf_cities ct ON ct.cityid = a.cityid AND (a.enabled = '1' AND a.verified = '1' AND a.expireson >= NOW())
  INNER JOIN clf_countries c ON ct.countryid = c.countryid AND ct.enablded = 1 AND c.enabled = 1
  GROUP BY ct.cityid
Next, I would try cover the indexes used for join with enabled as well ( index on (a.cityid, a.enabled), (c.countryid, c.enabled), (ct.countryid, c.enabled)

Check explain after each operation and show us the results

Re: Query Optimization

Posted: Thu Apr 28, 2011 9:01 am
by mikosiko
In addition to Darhazer suggestions run the EXPLAIN plan for this modified query and see if you get some improvements:

Code: Select all

SELECT ct.cityid, ct.countryid, COUNT(a.adid) as adcnt
  FROM clf_cities ct
       INNER JOIN clf_countries c ON ct.countryid = c.countryid  AND c.enabled = '1'
       INNER JOIN clf_ads a ON ct.cityid = a.cityid AND (a.enabled = '1' AND a.verified = '1' AND a.expireson >= NOW())
  WHERE ct.enabled = '1'
  GROUP BY ct.cityid, ct.countryid
and as Darhazer suggest check your indexes specially those with low cardinality, those are really unlikely be used; a covered index (as was suggested) should have better results, depending on your queries of course.

Re: Query Optimization

Posted: Thu Apr 28, 2011 9:37 am
by Celauran
Thanks for the replies, guys. I had worked out some of the proposed changes after having posted here and saw some increase in performance. The query is still pretty slow and, unfortunately, runs quite regularly.

Code: Select all

EXPLAIN
SELECT ct.cityid, c.countryid, COUNT(*) as adcnt
  FROM clf_ads a
  INNER JOIN clf_cities ct ON ct.cityid = a.cityid AND (a.enabled = '1' AND a.verified = '1' AND a.expireson >= NOW())
  INNER JOIN clf_countries c ON ct.countryid = c.countryid AND ct.enabled = '1' AND c.enabled = '1'
  GROUP BY ct.cityid
+----+-------------+-------+------+----------------------------------------+-------------+---------+---------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys                          | key         | key_len | ref                       | rows | Extra                                        |
+----+-------------+-------+------+----------------------------------------+-------------+---------+---------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | c     | ref  | PRIMARY,enabled,id_en                  | enabled     | 1       | const                     |    2 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | ct    | ref  | PRIMARY,countryid,cid_ctid,cid_enabled | cid_enabled | 3       | ads.c.countryid,const     |   67 | Using where                                  |
|  1 | SIMPLE      | a     | ref  | cityid,ctid_en_ver                     | ctid_en_ver | 4       | ads.ct.cityid,const,const |   57 | Using where                                  |
+----+-------------+-------+------+----------------------------------------+-------------+---------+---------------------------+------+----------------------------------------------+
(Running EXPLAIN on mikosiko's query yields the very same results)

mysql> SHOW INDEX FROM clf_ads;
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
| Table   | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type |
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
| clf_ads |          0 | PRIMARY     |            1 | adid        | A         |       15756 |     NULL | NULL   |      | BTREE      |
| clf_ads |          1 | subcatid    |            1 | subcatid    | A         |         137 |     NULL | NULL   |      | BTREE      |
| clf_ads |          1 | cityid      |            1 | cityid      | A         |         225 |     NULL | NULL   |      | BTREE      |
| clf_ads |          1 | origin      |            1 | origin      | A         |          67 |     NULL | NULL   |      | BTREE      |
| clf_ads |          1 | ip          |            1 | ip          | A         |       15756 |     NULL | NULL   |      | BTREE      |
| clf_ads |          1 | ctid_en_ver |            1 | cityid      | A         |         225 |     NULL | NULL   |      | BTREE      |
| clf_ads |          1 | ctid_en_ver |            2 | enabled     | A         |         262 |     NULL | NULL   |      | BTREE      |
| clf_ads |          1 | ctid_en_ver |            3 | verified    | A         |         276 |     NULL | NULL   |      | BTREE      |
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+

mysql> SHOW INDEX FROM clf_countries;
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
| Table         | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
| clf_countries |          0 | PRIMARY  |            1 | countryid   | A         |           3 |     NULL | NULL   |      | BTREE      |
| clf_countries |          1 | enabled  |            1 | enabled     | A         |        NULL |     NULL | NULL   |      | BTREE      |
| clf_countries |          1 | id_en    |            1 | countryid   | A         |        NULL |     NULL | NULL   |      | BTREE      |
| clf_countries |          1 | id_en    |            2 | enabled     | A         |        NULL |     NULL | NULL   |      | BTREE      |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+

Then again, there's probably no need for indexing clf_countries
mysql> SELECT DISTINCT(enabled) FROM clf_countries;
+---------+
| enabled |
+---------+
| 1       |
+---------+


mysql> SHOW INDEX FROM clf_cities;
+------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
| Table      | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type |
+------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
| clf_cities |          0 | PRIMARY     |            1 | cityid      | A         |         267 |     NULL | NULL   |      | BTREE      |
| clf_cities |          1 | countryid   |            1 | countryid   | A         |           3 |     NULL | NULL   |      | BTREE      |
| clf_cities |          1 | cid_ctid    |            1 | countryid   | A         |           3 |     NULL | NULL   |      | BTREE      |
| clf_cities |          1 | cid_ctid    |            2 | cityid      | A         |         267 |     NULL | NULL   |      | BTREE      |
| clf_cities |          1 | cid_enabled |            1 | countryid   | A         |           3 |     NULL | NULL   |      | BTREE      |
| clf_cities |          1 | cid_enabled |            2 | enabled     | A         |           3 |     NULL | NULL   |      | BTREE      |
+------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
I realize there are a fair number of indices on clf_ads, though I've removed some from yesterday, but they are being used elsewhere.

I may end up removing the code that uses this and similar queries altogether as I'm beginning to suspect the cost outweighs the benefit.

Re: Query Optimization

Posted: Thu Apr 28, 2011 3:13 pm
by mikosiko
just for curiosity... did you test the code that I did provide?... results?

Re: Query Optimization

Posted: Thu Apr 28, 2011 3:29 pm
by Celauran
I did. The EXPLAIN was identical to Darhazer's query, as well as to the original query with some better indexing.

Re: Query Optimization

Posted: Thu Apr 28, 2011 3:58 pm
by pickle
I'm no SQL expert either, but since you mention this query is run often, I wonder if using temporary tables or Views might help?

Re: Query Optimization

Posted: Thu Apr 28, 2011 4:30 pm
by mikosiko
@Celauran... I just saw the comment answering my question in your explain plan

maybe this could give you some ideas:
http://dev.mysql.com/doc/refman/5.1/en/ ... ation.html

specially the section 7.3.1.12.2. Tight Index Scan

maybe if you post your clf_ads table structure will help to others to offer other ideas.