I have a rand query which runs very slow like almost every rand query. I researched all stackoverflow but cannot find any good solution for my query
SELECT u.id
, u.is_instagram_connected
, u.tokens
, u.username
, u.name
, u.photo
, u.bio
, u.voice
, u.mobile_update
, 1584450999 - l.time idleTime
FROM mobile_login_list l
JOIN users u
ON l.username = u.username
JOIN mobile_token_list t
ON t.username = l.username
WHERE l.time > 1584393399
AND l.username NOT IN ('enesdoo')
AND u.username NOT IN (
SELECT blocked_username
FROM hided_mobile_users_from_shuffle
WHERE username = 'enesdoo'
)
AND u.ban_status = 0
AND u.perma_ban = 0
AND u.mobile_online_status = 1
AND u.lock_status = 0
GROUP
BY l.username
ORDER
BY RAND( )
LIMIT 27
If i remove the order by rand line, this runs very very quick like 100 times faster.
How can i speed up this query?
mobile_login_list has > 50k rows
users has > 1m rows
Edit:
Explain:
My table:
CREATE TABLE IF NOT EXISTS `mobile_login_list` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(30) COLLATE utf8_bin NOT NULL,
`key` varchar(32) COLLATE utf8_bin NOT NULL,
`time` int(11) NOT NULL,
`ip` int(11) NOT NULL,
`version` smallint(4) NOT NULL,
`messaged` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `kontrol` (`username`,`key`),
KEY `username` (`username`),
KEY `time` (`time`),
KEY `username_2` (`username`,`time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=3351637 ;

Aucun commentaire:
Enregistrer un commentaire