Re: Queries using JOIN and ORDER BY LIMIT

[irek kordirko]

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.