Query Optimization
Posted: Tue Apr 26, 2011 2:54 pm
I've been looking through my slow queries logs and have encountered, among others, the following:
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
# 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.cityidHere 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 |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+