Announcement

Announcement Module
Collapse
No announcement yet.

Query Advisor Results

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Query Advisor Results

    Per Baron, I'm pasting this here:

    http://tools.percona.com/query/PLUQcFBa/advice

    specifically, I can't seem to find any information on CLA.003 (Paginating a result set with LIMIT and OFFSET is O(n^2) complexity, and will cause performance problems as the data grows larger. Pagination techniques such as bookmarked scans are much more efficient.) and "bookmarked scans".

  • #2
    The reason it's warning about CLA.003 is this:

    LIMIT 20 OFFSET 7620

    This means that the query will actually generate 7620 + 20 rows, and then throw away the first 7620. That is, 99.74% of the work it's doing is wasted.

    A bookmarked scan works like this: select the first 21 results, display 20, and remember the 21st result. Next time, use a greater-than or less-than with the 21st result as the starting point and a LIMIT 21 again. To generate the third page, start from the 41st row, and so on. This way the query is only generating 20 rows at a time.

    However, this won't help in your case, because you have a GROUP BY, and some ORDER BY problems that will prevent early materialization. This query will always generate a temporary table with ALL the rows, sort them, then scan them and retrieve the desired rows.

    This is a very common type of problem in applications that show paginated displays. There are a variety of ways to solve it; I'd begin with the slides for "Efficient Pagination Using MySQL" from our 2009 conference, listed here: http://www.percona.com/live/santa-clara-2009/

    Comment


    • #3
      Can I suggest linking to that slide deck from that warning? Googling for "bookmarked scans" doesn't work very well...

      Comment


      • #4
        Well, hopefully now it will.

        bookmarked scans
        bookmarked scans
        bookmarked scans
        bookmarked scans
        bookmarked scans
        bookmarked scans

        Whee!

        Comment

        Working...
        X