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)
