Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
Can you provide the query? –  ypercube Mar 5 at 17:32
1  
Can we also see the table structures for the tables in the query ??? –  RolandoMySQLDBA Mar 5 at 17:35
    
I added the query and index info. Do you need more details on the table structure? –  user939259 Mar 5 at 17:49
    
Do you only have SELECT id and is (id) the primary key? Ah, can you also try changing the order by to: ORDER BY a,b,my_start_time; –  ypercube Mar 5 at 17:52
    
id is an auto increment primary key. I don't understand your question "Do you only have SELECT id"? It's the only column being selected in the query, if that's what you mean. I haven't tried changing the order - will that act different for big tables? The question is really more about how table size impacts query performance rather than just optimizing the query –  user939259 Mar 5 at 17:55
show 3 more comments

1 Answer 1

Not sure what version of TokuDB you are running, or what MySQL/MariaDB distribution, but there is an index condition pushdown bug that occurs on queries similar to the one you've provided. We are still waiting for a fix, the only current workaround is to disable ICP when running the problematic query.

Try running the query with ICP turned off. To disable it, issue the following command in the session just before running the query:

SET optimizer_switch='index_condition_pushdown=off';

And please report back success/failure, and the particular versions of TokuDB and MySQL/MariaDB.

(full disclosure, I'm an employee of Tokutek, makers of TokuDB)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.