MySQL Forums
Forum List  »  General

Re: trouble optimizing query - taking much longer than expected
Posted by: Rick James
Date: November 08, 2014 10:57AM

> using limit for paging
> LIMIT 200000, 100

That has to read 200100 rows in order to deliver the last 100 rows.

Why start at 200000??? I can't imagine that someone paged through 2000 pages of 100 each to get to here. I want to pursue this question because it may lead to a more efficient way of getting to the desired 100 rows.

You have no index on capture_timestamp. However, it is not yet clear whether an index would help.

> the gist of the db setup is a hierarchy

I don't see that yet??

> I tried separating indexes on user_id and capture_timestamp rather than having one index with both as previously.

INDEX(a,b) is _not_ the same as INDEX(b,a) is _not_ the same as INDEX(a) + INDEX(b)

Think about a long list of people, sorted by last name, then first name. But all you have is the person's first name and last initial. More discussion:
http://mysql.rjweb.org/doc.php/index1

> INNER JOIN file_logs
> CREATE TABLE `spc_file_logs` (

What is the real name?

> How can 'index condition' on 1770 rows when using the combined index be slower than 'using where' on 159794 rows with separate indexes?

Possibility 1: Everything was cached in the fast case, so it was not really faster. Run each query twice, with SQL_NO_CACHE, to avoid mistaken timing.
Possibility 2: The optimizer does not always "get it right". And your change in the indexes tricked the optimizer into "not doing the wrong thing".
Possibility 3: I don't fully grok what is going on in your queries.

SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; and how much RAM do you have?

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.