mardi 17 mars 2020

Optimizing rand query with join

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:

enter image description here

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