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.