Ускорение выполнения запросов MySQL
1) Использование DISTINCT работает быстрее чем group by
Было:
|
1 |
select requestid from request_posts where requestid in (select id from requests where created_on between '2016-09-01 00:00:00' and '2016-09-30 23:59:59' and classid<>13) and author_id in (5) group by requestid; |
Стало:
|
1 |
select DISTINCT(requestid) from request_posts where requestid in (select id from requests where created_on between '2016-09-01 00:00:00' and '2016-09-30 23:59:59' and classid<>13) and author_id in (5) |
2) IN в запросах работает медленно. Очень медленно! Лучше вместо IN, использовать JOIN
Было:
|
1 |
select * from requests where id in (select (requestid) from request_posts where requestid in (select id from requests where created_on between '2016-09-01 00:00:00' and '2016-09-30 23:59:59' and classid<>13) and author_id in (5) ); |
Выполняется 50 секунд
Стало:
|
1 |
select * from requests inner join (select DISTINCT(requestid) from request_posts where requestid in (select id from requests where created_on between '2016-09-01 00:00:00' and '2016-09-30 23:59:59' and classid<>13) and author_id in (5)) as zx on zx.requestid=requests.id; |
Выполняется 0.23 секуны