Can anyone explain the following? I encountered the following very strange
behaviour while attempting to optimize a query (more details are provided
later on for those interested):
1) execute query
takes 2 minutes
2) add index
3) execute same query
takes 11 seconds
4) drop index
5) execute same query
takes 0.2 seconds and uses a different method of returning results from the
original query in 1)
6) restart mysql
7) execute query
takes 2 minutes
Here's the SQL I used to produce the behaviour:
mysql> select phantom_products.id FROM phantom_products LEFT OUTER JOIN
phantom_labels ON phantom_labels.id = phantom_products.label_id where
phantom_labels.full_name like '%lame%' order by title LIMIT 75, 25;
[data omitted for clarity]
25 rows in set (1 min 50.23 sec)
mysql> explain SELECT phantom_products.id FROM phantom_products LEFT OUTER
JOIN phantom_labels ON phantom_labels.id = phantom_products.label_id WHERE
(phantom_labels.full_name like '%lame%') ORDER BY title ASC LIMIT 75, 25;
+----+-------------+------------------+--------+---------------+---------+---------+--------------------------------------------+--------+----------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra
|
+----+-------------+------------------+--------+---------------+---------+---------+--------------------------------------------+--------+----------------+
| 1 | SIMPLE | phantom_products | ALL | label_id_idx | NULL |
NULL | NULL | 787738 | Using
filesort |
| 1 | SIMPLE | phantom_labels | eq_ref | PRIMARY | PRIMARY | 4
| krad_development.phantom_products.label_id | 1 | Using where
|
+----+-------------+------------------+--------+---------------+---------+---------+--------------------------------------------+--------+----------------+
2 rows in set (0.00 sec)
mysql> create index title_idx on phantom_products(title);
Query OK, 777262 rows affected (1 min 58.08 sec)
Records: 777262 Duplicates: 0 Warnings: 0
select phantom_products.id FROM phantom_products LEFT OUTER JOIN
phantom_labels ON phantom_labels.id = phantom_products.label_id where
phantom_labels.full_name like '%lame%' order by title LIMIT 75, 25;
25 rows in set (11.03 sec)
mysql> explain select phantom_products.id FROM phantom_products LEFT OUTER
JOIN phantom_labels ON phantom_labels.id = phantom_products.label_id where
phantom_labels.full_name like '%lame%' order by title LIMIT 75, 25;
+----+-------------+------------------+--------+---------------+-----------+---------+--------------------------------------------+--------+-------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra
|
+----+-------------+------------------+--------+---------------+-----------+---------+--------------------------------------------+--------+-------------+
| 1 | SIMPLE | phantom_products | index | label_id_idx | title_idx |
258 | NULL | 785367 |
|
| 1 | SIMPLE | phantom_labels | eq_ref | PRIMARY | PRIMARY |
4 | krad_development.phantom_products.label_id | 1 | Using where
|
+----+-------------+------------------+--------+---------------+-----------+---------+--------------------------------------------+--------+-------------+
mysql> select phantom_products.id FROM phantom_products LEFT OUTER JOIN
phantom_labels ON phantom_labels.id = phantom_products.label_id where
phantom_labels.full_name like '%lame%' LIMIT 75, 25;
25 rows in set (0.01 sec)
mysql> drop index title_idx on phantom_products;
Query OK, 777262 rows affected (53.89 sec)
Records: 777262 Duplicates: 0 Warnings: 0
mysql> SELECT phantom_products.id FROM phantom_products LEFT OUTER JOIN
phantom_labels ON phantom_labels.id = phantom_products.label_id WHERE
(phantom_labels.full_name like '%lame%') ORDER BY title ASC LIMIT 75, 25;
25 rows in set (0.02 sec)
mysql> explain SELECT phantom_products.id FROM phantom_products LEFT OUTER
JOIN phantom_labels ON phantom_labels.id = phantom_products.label_id WHERE
(phantom_labels.full_name like '%lame%') ORDER BY title ASC LIMIT 75, 25;
+----+-------------+------------------+------+---------------+--------------+---------+------------------------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key
| key_len | ref | rows | Extra
|
+----+-------------+------------------+------+---------------+--------------+---------+------------------------------------+-------+----------------------------------------------+
| 1 | SIMPLE | phantom_labels | ALL | PRIMARY | NULL
| NULL | NULL | 17632 | Using where; Using
temporary; Using filesort |
| 1 | SIMPLE | phantom_products | ref | label_id_idx | label_id_idx
| 5 | krad_development.phantom_labels.id | 19 | Using where
|
+----+-------------+------------------+------+---------------+--------------+---------+------------------------------------+-------+----------------------------------------------+
2 rows in set (0.00 sec)
Sorry to include so many statements, but this was the series of steps I took
which would consistently produce these results.
So at first my query takes 2 minutes to compute since I'm ordering by a
non-indexed attribute (title). I then add an index for this attribute and my
query time reduces to 11 seconds
After I remove the index and re-run the query, the execution time is still
very low (0.2 seconds). When I explain the query, it produces different
results from my first query which didn't have the index on the title
attribute.
If I restart the server, and rerun the query,then it goes back to how it
initially was before the addition of the index (takes about 2 minutes to
execute). Can anyone tell me what's causing this behaviour? Is it normal
that adding an index and then removing the index should produce different
results than simply not adding the index in the first place?
Also, right after I dropped the index, the explain statement shows that my
query is now "Using where; Using temporary; Using filesort" and it looks
like it's first searching through the phantom_labels table to narrow down
the number of matches, and then doing a join on the phantom_products table.
Why wasn't it doing this in the first place? I thought that this is how the
query should've been executed (regardless of having an index or not).
Shouldn't mysql narrow down the search criteria first and then execute the
join, rather than joining the tables and then using the search criteria?
In case anyone is interested, phantom_labels has 18,000 records and
phantom_products has 800,000 records.
Thanks for any help
