MySQL query optimisation

[ndebarnot]

Hello,

There's a MySQL query in particular that seems to be executed quite often on my boards and I'm trying to optimize it as my server is quite loaded.

The query is this one:

SELECT user.*,usertextfield.*,userfield.*, user.userid, options,
                        IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid, infractiongroupid



                 , IF((options & 512 AND user.userid <> 0), 0, lastactivity) AS lastvisittime



                FROM user AS user
                LEFT JOIN usertextfield AS usertextfield ON(usertextfield.userid=user.userid)
                LEFT JOIN userfield AS userfield ON(userfield.userid=user.userid)





                WHERE 1=1
                        AND (user.usergroupid IN (-1,6,29,27,2,5))

                ORDER BY user.username asc
                LIMIT 191600, 100;

I have to Vbulletin 4 boards and on one it scans 173838 rows and on the other one 514771 rows. Is there a way to optimize this query?

Here's the current CREATE syntax for the user table in case you need to see my current indexes:

CREATE TABLE `user` (
  `userid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `usergroupid` smallint(5) unsigned NOT NULL DEFAULT '0',
  `username` varchar(100) NOT NULL DEFAULT '',
  `password` varchar(32) NOT NULL DEFAULT '',
  `email` varchar(100) NOT NULL DEFAULT '',
  `styleid` smallint(5) unsigned NOT NULL DEFAULT '0',
  `parentemail` varchar(50) NOT NULL DEFAULT '',
  `homepage` varchar(100) NOT NULL DEFAULT '',
  `icq` varchar(20) NOT NULL DEFAULT '',
  `aim` varchar(20) NOT NULL DEFAULT '',
  `yahoo` varchar(32) NOT NULL DEFAULT '',
  `usertitle` varchar(250) NOT NULL DEFAULT '',
  `customtitle` smallint(6) NOT NULL DEFAULT '0',
  `joindate` int(10) unsigned NOT NULL DEFAULT '0',
  `daysprune` smallint(6) NOT NULL DEFAULT '0',
  `lastvisit` int(10) unsigned NOT NULL DEFAULT '0',
  `lastactivity` int(10) unsigned NOT NULL DEFAULT '0',
  `lastpost` int(10) unsigned NOT NULL DEFAULT '0',
  `posts` int(10) unsigned NOT NULL DEFAULT '0',
  `timezoneoffset` varchar(4) NOT NULL DEFAULT '',
  `avatarid` smallint(6) NOT NULL DEFAULT '0',
  `options` int(10) unsigned NOT NULL DEFAULT '33570831',
  `birthday` varchar(10) NOT NULL DEFAULT '0000-00-00',
  `maxposts` smallint(6) NOT NULL DEFAULT '-1',
  `startofweek` smallint(6) NOT NULL DEFAULT '1',
  `ipaddress` varchar(15) NOT NULL DEFAULT '',
  `referrerid` int(10) unsigned NOT NULL DEFAULT '0',
  `pmpopup` smallint(6) NOT NULL DEFAULT '0',
  `pmtotal` smallint(5) unsigned NOT NULL DEFAULT '0',
  `pmunread` smallint(5) unsigned NOT NULL DEFAULT '0',
  `passworddate` date NOT NULL DEFAULT '0000-00-00',
  `salt` char(30) NOT NULL DEFAULT '',
  `showvbcode` smallint(5) unsigned NOT NULL DEFAULT '0',
  `msn` varchar(100) NOT NULL DEFAULT '',
  `avatarrevision` int(10) unsigned NOT NULL DEFAULT '0',
  `membergroupids` varchar(250) NOT NULL DEFAULT '',
  `displaygroupid` smallint(5) unsigned NOT NULL DEFAULT '0',
  `reputation` int(11) NOT NULL DEFAULT '10',
  `reputationlevelid` int(10) unsigned NOT NULL DEFAULT '1',
  `languageid` smallint(5) unsigned NOT NULL DEFAULT '0',
  `threadedmode` smallint(5) unsigned NOT NULL DEFAULT '0',
  `emailstamp` int(10) unsigned NOT NULL DEFAULT '0',
  `autosubscribe` smallint(6) NOT NULL DEFAULT '-1',
  `birthday_search` date NOT NULL DEFAULT '0000-00-00',
  `profilepicrevision` int(10) unsigned NOT NULL DEFAULT '0',
  `showbirthday` smallint(5) unsigned NOT NULL DEFAULT '2',
  `skype` varchar(32) NOT NULL DEFAULT '',
  `adminoptions` int(10) unsigned NOT NULL DEFAULT '0',
  `lastpostid` int(10) unsigned NOT NULL DEFAULT '0',
  `sigpicrevision` int(10) unsigned NOT NULL DEFAULT '0',
  `ipoints` int(10) unsigned NOT NULL DEFAULT '0',
  `infractions` int(10) unsigned NOT NULL DEFAULT '0',
  `warnings` int(10) unsigned NOT NULL DEFAULT '0',
  `infractiongroupids` varchar(255) NOT NULL DEFAULT '',
  `infractiongroupid` smallint(5) unsigned NOT NULL DEFAULT '0',
  `cfrules_read_status` tinyint(1) NOT NULL DEFAULT '0',
  `profilevisits` int(10) unsigned NOT NULL DEFAULT '0',
  `friendcount` int(10) unsigned NOT NULL DEFAULT '0',
  `friendreqcount` int(10) unsigned NOT NULL DEFAULT '0',
  `vmunreadcount` int(10) unsigned NOT NULL DEFAULT '0',
  `vmmoderatedcount` int(10) unsigned NOT NULL DEFAULT '0',
  `socgroupinvitecount` int(10) unsigned NOT NULL DEFAULT '0',
  `socgroupreqcount` int(10) unsigned NOT NULL DEFAULT '0',
  `pcunreadcount` int(10) unsigned NOT NULL DEFAULT '0',
  `pcmoderatedcount` int(10) unsigned NOT NULL DEFAULT '0',
  `gmmoderatedcount` int(10) unsigned NOT NULL DEFAULT '0',
  `assetposthash` varchar(32) NOT NULL DEFAULT '',
  `fbuserid` varchar(255) NOT NULL,
  `fbjoindate` int(10) unsigned NOT NULL DEFAULT '0',
  `fbname` varchar(255) NOT NULL,
  `logintype` enum('vb','fb') NOT NULL DEFAULT 'vb',
  `fbaccesstoken` varchar(255) NOT NULL,
  `bloggroupreqcount` int(10) unsigned NOT NULL DEFAULT '0',
  `showblogcss` int(10) unsigned NOT NULL DEFAULT '1',
  `vbseo_likes_in` int(10) unsigned NOT NULL DEFAULT '0',
  `vbseo_likes_out` int(10) unsigned NOT NULL DEFAULT '0',
  `vbseo_likes_unread` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`userid`),
  KEY `username` (`username`),
  KEY `birthday_search` (`birthday_search`),
  KEY `birthday` (`birthday`,`showbirthday`),
  KEY `referrerid` (`referrerid`),
  KEY `posts` (`posts`),
  KEY `fbuserid` (`fbuserid`),
  KEY `email` (`email`)
) ENGINE=MyISAM AUTO_INCREMENT=174348 DEFAULT CHARSET=latin1 PACK_KEYS=1;