MySQL Forums
Forum List  »  Optimizer & Parser

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
Re: Queries using JOIN and ORDER BY LIMIT
2704
March 12, 2012 06:18PM


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.