MySQL Forums
Forum List  »  Optimizer & Parser

Re: Queries using WHERE IN and ORDER BY LIMIT
Posted by: Rick James
Date: February 12, 2011 06:42PM

"want to support databases other than x" -- Good luck!

The usual trick for dealing with OR and LIMIT is:
( SELECT ... WHERE one_of_the_ORs ORDER BY .. LIMIT ..)
UNION
( SELECT ... WHERE the_other_OR ORDER BY .. LIMIT ..)
ORDER BY ... LIMIT ...
The goal is for each SELECT to use an index for both the WHERE and the ORDER BY.
Then each SELECT is efficient because it only looks at only 20 rows.
The UNION has only 40 rows to sort (assuming 2 SELECTs).

Your last query,
select  e.id
    from  entry e
    where  (e.author = 'my.usr.selective'
              or  e.group_uuid is null
              or  e.group_uuid in ('no-entries-with-this-uuid-1',
                                   'no-entries-with-this-uuid-2'))
      and  (e.type = 2)
    order by  e.created_ts desc, e.id desc
    limit  20;
would need both
INDEX(type, author, created_ts, id)
INDEX(type, group_uuid, created_ts, id)
even after turning it into a UNION of 3 SELECTs.

These would do equally well:
INDEX(author, type, created_ts, id)
INDEX(group_uuid, type, created_ts, id)

(Since you are using InnoDB, the "id" on the end is optional -- the PK is included for you.)

They would also achieve "Using index". But your real query are probably fetching something other than the id?

MySQL is behind the curve on optimizing OR. Note that sort_union did not exist until 5.5, which was released only two months ago.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Queries using WHERE IN and ORDER BY LIMIT
1622
February 12, 2011 06:42PM


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.