Query Optimization

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Query Optimization

Post 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      |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
User avatar
Darhazer
DevNet Resident
Posts: 1011
Joined: Thu May 14, 2009 3:00 pm
Location: HellCity, Bulgaria

Re: Query Optimization

Post 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
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Query Optimization

Post 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.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Query Optimization

Post 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.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Query Optimization

Post by mikosiko »

just for curiosity... did you test the code that I did provide?... results?
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Query Optimization

Post by Celauran »

I did. The EXPLAIN was identical to Darhazer's query, as well as to the original query with some better indexing.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Query Optimization

Post 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?
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Query Optimization

Post 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.
Post Reply