Skip navigation links

MySQL Forums :: Optimizer & Parser :: Queries using JOIN and ORDER BY LIMIT


Advanced Search

Re: Queries using JOIN and ORDER BY LIMIT
Posted by: irek kordirko ()
Date: March 12, 2012 06:18PM

You might do a "copy" of created_ts column in the biz_groups table,
and the query could access only one table skipping the join.

alter table biz_groups
add column `created_ts` bigint(20);

update biz join biz_groups bg on biz.id = bg.biz_id
set bg.created_ts = biz.created_ts
;

alter table biz_groups
modify `created_ts` bigint(20) not null;

create unique index id_created on biz( created_ts, id );

alter table biz_groups
drop foreign key biz_groups_bi_fk;

alter table biz_groups
add constraint biz_groups_bi_fk
foreign key ( created_ts, biz_id ) references biz( created_ts, id )
on update cascade;

explain extended
select SQL_NO_CACHE distinct biz_id
from biz_groups 
where group_uuid in ('group-uuid-01','group-uuid-03','group-uuid-05', 'group-uuid-07')
order by created_ts desc
limit 20 offset 20;

+----+-------------+------------+-------+---------------+------------------+---------+------+------+----------+------------------------------+
| id | select_type | table      | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+------------+-------+---------------+------------------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | biz_groups | index | gubi_idx      | biz_groups_bi_fk | 16      | NULL |  197 | 82197.97 | Using where; Using temporary |
+----+-------------+------------+-------+---------------+------------------+---------+------+------+----------+------------------------------+

In this way you are avoiding a filesort of a large resultset (40000 rows) caused by the distinct clause.



Edited 5 time(s). Last edit at 03/12/2012 06:31PM by irek kordirko.

Options: ReplyQuote


Subject Views Written By Posted
Queries using JOIN and ORDER BY LIMIT 2395 Nikita Dubrovsky 03/10/2012 01:11PM
Re: Queries using JOIN and ORDER BY LIMIT 767 irek kordirko 03/12/2012 06:18PM
Re: Queries using JOIN and ORDER BY LIMIT 758 Rick James 03/13/2012 11:07PM
Re: Queries using JOIN and ORDER BY LIMIT 715 Nikita Dubrovsky 03/14/2012 08:45AM
Re: Queries using JOIN and ORDER BY LIMIT 884 Rick James 03/15/2012 10:50AM


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.