Bug #79951 Large table queries are very slow compared to 5.6
Submitted: 13 Jan 2016 12:58 Modified: 4 Jun 2017 11:35
Reporter: Lee Butler Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.7.10 OS:Windows
Assigned to: CPU Architecture:Any
Tags: innodb, large table, Latest, queries, slow

[13 Jan 2016 12:58] Lee Butler
Description:
(Marked as S2 as regular jobs cannot complete in the allotted timeframe, and there is no workaround)

I've noticed that queries on large tables of data (in the InnoDB engine at least) are incredibly slow compared to the same queries on the same data in MySQL Server 5.6.24ish.

For example, I have an applicaiton which has a list of just over 10k (10257 currently) items to run 3 queries on each. The target table used to hold just short or 300k rows, but after the slowdown has been reduced to justs 60k for remotely acceptable speed in other uses of the database.

The 3 queries used to take just short of a second for each item, but now the average time for the 3 queries to run is about 18 seconds. (This adds up to around 49 hours total runtime, which for a nightly job is unacceptable, it used to take about 3 hours max)

Tldr: I have a lot of data I run some select queries on, and it takes wayyy longer than it used to. Is there any workarounds or solutions to this?

I can provide a sample dataset if required. 

It may be worth mentioning that there is no problem with most other tables (I don't have many large tables), and the database has been upgraded from the 5.6 data, but the table with the problem has been dropped and recreated, and that hasn't fixed it, so I don't think it has anything to do with data migration.

How to repeat:
1. Create a large table with InnoDB as the engine (mine has about 65 columns)
2. Fill it with data (Mine has about 60k records weighing in at about 40MB of data)
3. Run some queries on the data.
4. Experience slow response. 

Suggested fix:
I woulnd't have a clue where to fix this, other than maybe refer to the 5.6 code and revert some changes.
[4 Jun 2017 11:35] Lee Butler
Hasn't been touched and there's a good chance it was just dodgy SQL on my part