MySQL InnoDB indexes slowing down sorts
I am using MySQL 5.6 on FreeBSD and have just recently switched from using
MyISAM tables to InnoDB to gain advances of foreign key constraints and
transactions.
After the switch, I discovered that a query on a table with 100,000 rows
that was previously taking .003 seconds, was now taking 3.6 seconds. The
query looked like this:
SELECT
ot.COL1,
ot.COL2
FROM
OPTIMIZED_SEARCH_TABLE ot
JOIN USERS u ON ot.USER_ID = u.USER_ID
WHERE
u.STATUS = 'ACTIVE'
AND u.ACCESS_ID <= 10
ORDER BY
ot.ORDER_CONDITION
LIMIT 0,100
I noticed that if I removed the ORDER BY clause, the execution time
dropped back down to .003 seconds, so the problem is obviously in the
sorting.
I then discovered that if I added back the ORDER BY but removed indexes on
the columns referred to in the query (STATUS and ACCESS_ID), the query
execution time would take the normal .003 seconds.
Then I discovered that if I added back the indexes on the STATUS and
ACCESS_ID columns, but used IGNORE INDEX (STATUS,ACCESS_ID), the query
would still execute in the normal .003 seconds.
I found this article describing a similar issue:
http://bugs.mysql.com/bug.php?id=21174
The article is from April of 2012. So, was this a bug in MySQL and if so,
why is it still occurring in the latest version of MySQL 5.6?
Or is there something about InnoDB and sorting results when referencing an
indexed column in a WHERE clause that I don't understand?
Am I doing something wrong or is this just a bug?
Any help would be greatly appreciated.
No comments:
Post a Comment