List:General Discussion« Previous MessageNext Message »
From:Martin Date:April 17 2003 5:58pm
Subject:Re: How do I optimize SELECT COUNT(*) WHERE queries?
View as plain text  
What happens when you run these commands at MySQL command line?
Do you experience the same behavior?
Martin
----- Original Message ----- 
From: "Mike Hillyer" <mhillyer@stripped>
To: <mysql@stripped>
Sent: Thursday, April 17, 2003 9:35 AM
Subject: How do I optimize SELECT COUNT(*) WHERE queries?


Hi all;

I have had problems with slow queries involving SQL_CALC_FOUND_ROWS and
so I was thinking of a second query with COUNT(*), but the query
involves a WHERE clause. 

Below are the details of my last problem which no-one has been able to
help with, if anyone can help me get some actually speed out of thet
query, or help me speed up a COUNT(*) query with a WHERE clause, I would
greatly appreciate it.

Mike Hillyer
www.vbmysql.com


mysql> SELECT SQL_CALC_FOUND_ROWS * FROM wd WHERE affiliationid = 0
ORDER BY productgroup, itemcode LIMIT 2;

........
2 rows in set (34.41 sec)

mysql> EXPLAIN SELECT SQL_CALC_FOUND_ROWS * FROM wd WHERE affiliationid
= 0 ORDER BY productgroup, itemcode LIMIT 2;
+-------+-------+---------------+-------+---------+------+---------+----
---------+
| table | type  | possible_keys | key   | key_len | ref  | rows    |
Extra
  |
+-------+-------+---------------+-------+---------+------+---------+----
---------+
| wd    | index | test1         | test1 |      61 | NULL | 1129309 |
Using where |
+-------+-------+---------------+-------+---------+------+---------+----
---------+
1 row in set (0.08 sec)

And without the SQL_CALC_FOUND_ROWS:

mysql> SELECT * FROM wd WHERE affiliationid = 0 ORDER BY productgroup,
itemcode LIMIT 2;

............
2 rows in set (0.02 sec)

mysql> EXPLAIN SELECT * FROM wd WHERE affiliationid = 0 ORDER BY
productgroup, itemcode LIMIT 2;
+-------+-------+---------------+-------+---------+------+---------+----
---------+
| table | type  | possible_keys | key   | key_len | ref  | rows    |
Extra
 |
+-------+-------+---------------+-------+---------+------+---------+----
---------+
| wd    | range | test1         | test1 |       5 | NULL | 1129309 |
Using where |
+-------+-------+---------------+-------+---------+------+---------+----
---------+
1 row in set (0.00 sec)

This does result in an index versus range lookup, and I wind up with a
longer key length, but I am stumped as to where to put an index.

In case it helps, here are the current indices (indexes?)

mysql> show index from wd;
+-------+------------+----------------+--------------+---------------+--
---------+-------------+----------+--------+------+------------+--------
-+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name   |
Collation
 | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------------+--------------+---------------+--
---------+-------------+----------+--------+------+------------+--------
-+
| wd    |          1 | test1          |            1 | AffiliationID | A
 |          18 |     NULL | NULL   | YES  | BTREE      |         |
| wd    |          1 | test1          |            2 | ProductGroup  | A
 |        3102 |     NULL | NULL   | YES  | BTREE      |         |
| wd    |          1 | test1          |            3 | ItemCode      | A
 |     1129309 |     NULL | NULL   | YES  | BTREE      |         |
| wd    |          1 | InsertIndex    |            1 | ProductGroup  | A
 |          18 |     NULL | NULL   | YES  | BTREE      |         |
| wd    |          1 | InsertIndex    |            2 | ItemCode      | A
 |     1129309 |     NULL | NULL   | YES  | BTREE      |         |
| wd    |          1 | InsertIndex    |            3 | PriceFileID   | A
 |     1129309 |     NULL | NULL   | YES  | BTREE      |         |
| wd    |          1 | SupplierSearch |            1 | SupplierID    | A
 |        1273 |     NULL | NULL   | YES  | BTREE      |         |
| wd    |          1 | SupplierSearch |            2 | PriceFileID   | A
 |        2110 |     NULL | NULL   | YES  | BTREE      |         |
+-------+------------+----------------+--------------+---------------+--
---------+-------------+----------+--------+------+------------+--------
-+
8 rows in set (0.58 sec)

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1

Thread
How do I optimize SELECT COUNT(*) WHERE queries?Mike Hillyer17 Apr
  • Re: How do I optimize SELECT COUNT(*) WHERE queries?Martin17 Apr
RE: How do I optimize SELECT COUNT(*) WHERE queries?Mike Hillyer17 Apr
  • RE: How do I optimize SELECT COUNT(*) WHERE queries?Daevid Vincent18 Apr