In MySQL, does SELECT performance degrade significantly after a certain table size? I have a table with about 107 million rows and some queries seem stuck in "Sending data" for a long time. Of course, "sending data" is a catchall for all sorts of query processing according to the MySQL manual. This is puzzling because EXPLAIN says that the query is "using where; using index" and only returns about 1700 rows. It's the only query running, so I know it's not a locking issue.
Would it help to split this table into a few hundred smaller tables, if I'm splitting on something that's already part of the key? It seems like the key itself should be better than that solution, but I'm at a loss as to why this query should take so much time.
The table engine in question is TokuDB, but I see the same results with InnoDB. I haven't tried MyISAM or any other engine with this table.
The query is (apologies for obfuscation due to internal data):
SELECT id FROM my_table WHERE my_end_time>=1234567890 AND my_start_time<=2345678901 AND a='XXX' AND b='YYY' ORDER BY my_start_time ASC;
The key it's using is a UNIQUE index on
a, b, my_start_time, my_end_time