Как уменьшить время выполнения MySQL запроса?

Question

Как уменьшить время выполнения MySQL запроса?

Рейтинг: 2Ответов: 1Опубликовано: 02.05.2015

Уважаемые форумчане!

У меня есть sql-запрос к базе, в которой в таблице "users" 100 000 строк, а в таблице "friends" 20 000 000 записей. Я пытаюсь выполнить следующий запрос в phpMyAdmin:

SELECT `friends`.`friend_age` 
FROM `friends` 
WHERE `id_user` 
IN(SELECT `users`.`uid` FROM `users` WHERE `age`=13 AND `number_of_friends` <> 0) 
AND `friend_age` <> 0 ORDER BY `friend_age`

При попытке выполнения запроса, всё зависает.... Потом через какое-то время phpMyAdmin выдаёт вот такую ошибку:

Fatal error: Maximum execution time of 300 seconds exceeded in D:\XAMPP\phpMyAdmin\libraries\dbi\DBIMysqli.class.php on line 290

Я уже пробовала создавать индексы полей, по которым осуществляется выборка так, как написано здесь: MySQL: использование индексов. Однако, это никак не помогло, запрос по-прежнему не выполняется. Помогите пожалуйста решить эту проблему.

Попробовала посмотреть с помощью EXPLAIN, что не так и поняла, что первый индекс не очень хороший: Результат Работы операции EXPLAIN

Однако, как прооптимизировать пока не ясно. Заходила сюда: 5.2.1. Синтаксис оператора EXPLAIN (получение информации о SELECT)

Пока что тоже не особо помогло((( Хоть кто-нибудь, отзовитесь пожалуйста! Оч нужна помощь!!!

P.S.: добавила индекс по столбцу id_user в таблице friends и первый индекс стал лучше:

Улучшение индексов

mysql база-данных select phpmyadmin settimeout

Источник: Stack Overflow на русском

Answer 1

▲ 5Принят

Попробуйте так:

select distinct f.friend_age
from fiends f
  join users u on f.user_id = u.uid
where 
  u.age = 13
  and f.friend_age<>0
order by f.friend_age

Индекс для users по age (он у вас есть судя по всему).
Индекс для friends по user_id.

Если будет виснуть, explain этого запроса покажите, возможно придется хинтами пользоваться.

Ограничение на количество друзей не нужно, т.к. выберутся только те, у кого они есть.

UPD

К сожалению, проблему, описанную в вопросе, решить не удастся. Вопрос тут не в индексах (хотя без них будет все еще хуже), а в объеме данных.

Для получения медианы достаточно результата такого запроса:

select f.friend_age, count(*) cnt
from fiends f
  join users u on f.user_id = u.uid
where 
  u.age = 13
  and f.friend_age<>0
group by f.friend_age

Таким образом получаете агрегированный список возрастов (мы выяснили, что аналогичный запрос с distinct'ом выполняется за более-менее разумное время).

Дальше рассчитать медиану уже будет не так сложно. Если сложности все-таки возникнут, лучше оформить другой вопрос.

Как уменьшить время выполнения MySQL запроса?

Ответы