#mysql
#mysql
Вопрос:
Мой запрос:
SELECT * FROM privatemessages69
WHERE sender='19' OR recipient='19'
ORDER BY id DESC;
Моя таблица:
CREATE TABLE `privatemessages69` (
`id` int(11) NOT NULL auto_increment,
`recipient` int(11) NOT NULL,
`sender` int(11) NOT NULL,
`time` int(11) NOT NULL,
`readstatus` int(11) NOT NULL,
`message` varchar(255) NOT NULL,
`messagetype` int(11) NOT NULL,
`rdeleted` int(11) NOT NULL,
`sdeleted` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `msgpanel` (`sender`,`recipient`,`id`)
) ENGINE=InnoDB AUTO_INCREMENT=50000 DEFAULT CHARSET=latin1
Объясните:
mysql> explain SELECT * FROM privatemessages69 WHERE sender='19' OR recipient='19' ORDER BY id DESC;
---- ------------- ------------------- ------- --------------- --------- --------- ------ ------- -------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---- ------------- ------------------- ------- --------------- --------- --------- ------ ------- -------------
| 1 | SIMPLE | privatemessages69 | index | msgpanel | PRIMARY | 4 | NULL | 50191 | Using where |
---- ------------- ------------------- ------- --------------- --------- --------- ------ ------- -------------
1 row in set (0.00 sec)
Может кто-нибудь подсказать мне правильный способ индексации этой таблицы для этого запроса? Если я удалю order by, type перейдет ко ВСЕМ.
Спасибо за любую помощь
Ответ №1:
Для такого запроса должно быть достаточно одного включенного sender
индекса и одного включенного recipient
:
CREATE INDEX ind_sender
ON privatemessages69 (sender) ;
CREATE INDEX ind_recipient
ON privatemessages69 (recipient) ;
И я не думаю, что это будет быстрее, если вы напишете это с помощью UNION
.
Вы также можете попробовать добавить эти два индекса (и удалить вышеупомянутые):
CREATE INDEX ind_sender_2
ON privatemessages69 (sender, id) ;
CREATE INDEX ind_recipient_2
ON privatemessages69 (recipient, id) ;
Я не могу протестировать сейчас, но это может избавить от сортировки файлов.
Вы все еще можете отредактировать свой вопрос и добавить в конце:
- Время с ПОРЯДКОМ ПО: (3-4 секунды, как вы говорите)
- Общее количество строк в таблице:
- Количество строк с
recipient=19
: - Количество строк с
sender=19
: - Количество строк, возвращаемых запросом:
Некоторые тесты, которые я провел вчера с общим количеством строк около 300 тысяч, показали, что запрос выполняется менее чем за секунду. Медлительность, возможно, связана с настройками конфигурации MySQL.
Также: занимает ли запрос 3-4 секунды, что бы вы ни указали (вместо 19
)?
Комментарии:
1. Это определенно кажется самым быстрым способом сделать это. Я думаю, единственная реальная проблема, с которой я сталкиваюсь сейчас, — это упорядочивание за счет замедления запроса. Знаете ли вы хорошее решение для ускорения этого? (Должен ли я задать это как новый вопрос? Новое здесь) Спасибо за помощь!
Ответ №2:
Вы не можете проиндексировать таблицу для этого запроса, но вот еще один, который будет выполняться быстро, если вы добавите 2 разных индекса в sender
и recipient
:
SELECT * FROM (
SELECT * FROM privatemessages69 WHERE sender=19
UNION
SELECT * FROM privatemessages69 WHERE recipient=19
) t
ORDER BY id DESC;
Комментарии:
1. Любая ссылка на то, почему это так?
2. @Kevin Peno: Mysql не может использовать индекс, охватывающий 2 двух столбца, если оба столбца объединены с использованием
OR
. Взгляните на документацию mysql, объясняющую использование индекса (последнее поле примера): dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html3. @soulmerge, если вы добавите отдельный индекс для получателя, тогда вы можете использовать
select * from privatemessages69 where sender = 19 or recipient = 19
, и он будет использовать и индексировать для обоих. если вы не измените таблицу и, следовательно, не добавите индекс, ваш код не будет использовать индекс дляrecipient
, потому что это не самая левая часть составного индекса, так чем же ваш код лучше, чем использование простого ИЛИ?4. @soulmerge: Вы (или я должен сказать, что онлайн-документация MySQL) ошибаетесь в этом пункте. Два (или более) индекса для двух (или более) отдельных полей могут использоваться с ИЛИ, точно так же, как запрос Брайана. Версия, которую я проверил (5.0.51), показана
type: index_merge
в ОБЪЯСНЕНИИ.5. Это ссылка на
index-merge
: dev.mysql.com/doc/refman/5.5/en/index-merge-optimization.html
Ответ №3:
Я полагаю, вам нужно было бы написать индекс, в котором оба столбца имеют первую позицию. В вашем случае добавление:
CREATE INDEX `msgpanel_recp` ON `privatemessages69` (`recipient`,`id`)
Комментарии:
1. Это использует type: all, когда ORDER BY удаляется из запроса