Skip navigation links

MySQL Forums :: Performance :: Long execution time despite LIMIT 3


Advanced Search

Re: Long execution time despite LIMIT 3
Posted by: Rick James ()
Date: June 21, 2011 10:08PM

SELECTs on InnoDB rarely cause replication to lag. The following can cause lags:
* Any long-running write query (UPDATE, ALTER, etc)
* A burst of quick write queries from multiple connections. (Replication is single-threaded, so they get queued up.)
* Slave hardware that is slower than the Master.
* SELECT(s) on the Slave that do enough locking of rows, together with write queries that are hitting the same rows or the "gaps" next to the rows.
* Long running transactions. (Long time between BEGIN and COMMIT.)

The first two items sound somewhat like your comment:
> Other queries on other tables that take a long time never cause the slaves to lag.

Meanwhile...
Let's carry the optimization another step:

SELECT  m.*, m.flags + 0 flags
    FROM messages_test m
    JOIN
        (
        (
        SELECT id
            FROM  messages_test
            WHERE  (thread = 817549.001266066)
              AND  (id_user_s = 817549)
              AND  (del_s = 0)
            ORDER BY id DESC LIMIT 3
        )
        UNION DISTINCT
        ( SELECT  id
            FROM  messages_test
            WHERE  (thread = 817549.001266066)
              AND  (id_user_r = 817549)
              AND  (del_r = 0)
           order by  id desc  LIMIT  3
        )
        ) x  ON x.id = m.id
    order by  id desc  LIMIT  3;
Note how the the inner SELECTs and the UNION only haul around `id`. This is much less bulky. Then, the outer query does the heavy lifting of reaching into messages_test (again) to get the rest of the fields.

Also, note that the inner SELECTs will be performed entirely in INDEXes. See EXPLAIN SELECT ... -- it will say "Using index".

Options: ReplyQuote


Subject Views Written By Posted
Long execution time despite LIMIT 3 1169 Joachim Berger 06/19/2011 12:47PM
Re: Long execution time despite LIMIT 3 489 Joachim Berger 06/19/2011 12:54PM
Re: Long execution time despite LIMIT 3 471 Rick James 06/20/2011 06:33PM
Re: Long execution time despite LIMIT 3 459 Joachim Berger 06/20/2011 11:44PM
Re: Long execution time despite LIMIT 3 479 Joachim Berger 06/21/2011 02:06AM
Re: Long execution time despite LIMIT 3 485 Rick James 06/21/2011 07:23AM
Re: Long execution time despite LIMIT 3 421 Joachim Berger 06/21/2011 07:28AM
Re: Long execution time despite LIMIT 3 507 Joachim Berger 06/21/2011 10:33AM
Re: Long execution time despite LIMIT 3 560 Rick James 06/21/2011 10:08PM
Re: Long execution time despite LIMIT 3 664 Joachim Berger 06/22/2011 12:33AM
Re: Long execution time despite LIMIT 3 512 Rick James 06/22/2011 07:26PM
Re: Long execution time despite LIMIT 3 461 Joachim Berger 06/22/2011 11:45PM
Re: Long execution time despite LIMIT 3 425 Rick James 06/23/2011 08:53AM
Re: Long execution time despite LIMIT 3 533 Joachim Berger 06/24/2011 02:51AM


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.