0

The MySQL query

SELECT `p`.*,
       `p.Author`.*
FROM   `post` AS `p`
       INNER JOIN `user` AS `p.Author` ON `p`.`userid` = `p.Author`.`userid`
WHERE  ( ( `p`.`threadid` = 29780 )
         AND ( `p`.`parentid` <> 0 ) )
       AND `p`.`visible` IN ( 1, 2 )
ORDER  BY `p`.`dateline`,
          `p`.`postid`
LIMIT  10 offset 0 

takes about ~800ms to 1000ms and create a temp table with all rows in user table (about 17k)

explain result

Strangely this doesn't appear on other threads. Lets change the threadid filter to 29781

enter image description here

On threadid = 29781 the PK is used as expected. So this query is much faster and took only about 0.000056ms on my testserver where threadid = 29780 ran for nearly a second. Can't explain why the first case creates a temp table: user.userid is PK.

Tested on MariaDB 10.2 and 10.3 running on Docker.

Edit

Can break it down to this query:

select post.*, user.*
from post
inner join user on user.userid=post.userid
where threadid=29780
order by post.dateline
limit 10

Edit #2

Tried to force using the index

select post.*, user.*
from post use index(PRIMARY)
inner join user use index(PRIMARY) on user.userid=post.userid 
where threadid=29780
order by post.dateline
limit 10

The index on post make it faster, but still slow for sql (~100ms). EXPLAIN say that no key on post table is used, so the whole table (~350k rows) is scanned. Forcing the index on user doesn't seem to have any effect.

The ORDER BY seems to be the problem. When removing it, execution speed is fine. Altough it seems that dateline has an index. Ordering by postid (PK) also takes very long.

Background

I'm using Entity Framework core to query an existing vBulletin database. The query above is part of a EF generated query, which should fetch the replys of a thread. In the tests I found the query to one test thread extremly slow. So I break it down to the query above.

Here the table definitions:

post

CREATE TABLE `post` (
    `postid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `threadid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
    `parentid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
    `username` VARCHAR(100) NOT NULL DEFAULT '',
    `userid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
    `title` VARCHAR(250) NOT NULL DEFAULT '',
    `dateline` INT(10) UNSIGNED NOT NULL DEFAULT '0',
    `pagetext` MEDIUMTEXT NULL DEFAULT NULL,
    `allowsmilie` SMALLINT(6) NOT NULL DEFAULT '0',
    `showsignature` SMALLINT(6) NOT NULL DEFAULT '0',
    `ipaddress` VARCHAR(45) NOT NULL DEFAULT '',
    `iconid` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
    `visible` SMALLINT(6) NOT NULL DEFAULT '0',
    `attach` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
    `infraction` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
    `reportthreadid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
    `post_thanks_amount` INT(10) UNSIGNED NOT NULL DEFAULT '0',
    `htmlstate` ENUM('off','on','on_nl2br') NOT NULL DEFAULT 'on_nl2br',
    `isForeign` INT(1) NULL DEFAULT '0',
    PRIMARY KEY (`postid`),
    INDEX `threadid` (`threadid`, `userid`),
    INDEX `threadid_visible_dateline` (`threadid`, `visible`, `dateline`, `userid`, `postid`),
    INDEX `ipaddress` (`ipaddress`),
    INDEX `dateline` (`dateline`),
    INDEX `userid` (`userid`, `parentid`),
    INDEX `user_date` (`userid`, `dateline`)
)
COLLATE='latin1_swedish_ci'
ENGINE=MyISAM
;

user

CREATE TABLE `user` (
    `userid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `usergroupid` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
    `membergroupids` CHAR(250) NOT NULL DEFAULT '',
    `displaygroupid` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
    `username` VARCHAR(100) NOT NULL DEFAULT '',
    `password` CHAR(32) NOT NULL DEFAULT '',
    `passworddate` DATE NOT NULL DEFAULT '1000-01-01',
    `email` CHAR(100) NOT NULL DEFAULT '',
    `styleid` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
    `parentemail` CHAR(50) NOT NULL DEFAULT '',
    `homepage` CHAR(100) NOT NULL DEFAULT '',
    `icq` CHAR(20) NOT NULL DEFAULT '',
    `aim` CHAR(20) NOT NULL DEFAULT '',
    `yahoo` CHAR(32) NOT NULL DEFAULT '',
    `msn` CHAR(100) NOT NULL DEFAULT '',
    `skype` CHAR(32) NOT NULL DEFAULT '',
    `showvbcode` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
    `showbirthday` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '2',
    `usertitle` CHAR(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',
    `lastpostid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
    `posts` INT(10) UNSIGNED NOT NULL DEFAULT '0',
    `reputation` INT(11) NOT NULL DEFAULT '10',
    `reputationlevelid` INT(10) UNSIGNED NOT NULL DEFAULT '1',
    `timezoneoffset` CHAR(4) NOT NULL DEFAULT '',
    `pmpopup` SMALLINT(6) NOT NULL DEFAULT '0',
    `avatarid` SMALLINT(6) NOT NULL DEFAULT '0',
    `avatarrevision` INT(10) UNSIGNED NOT NULL DEFAULT '0',
    `profilepicrevision` INT(10) UNSIGNED NOT NULL DEFAULT '0',
    `sigpicrevision` INT(10) UNSIGNED NOT NULL DEFAULT '0',
    `options` INT(10) UNSIGNED NOT NULL DEFAULT '33570831',
    `birthday` CHAR(10) NOT NULL DEFAULT '',
    `birthday_search` DATE NOT NULL DEFAULT '1000-01-01',
    `maxposts` SMALLINT(6) NOT NULL DEFAULT '-1',
    `startofweek` SMALLINT(6) NOT NULL DEFAULT '1',
    `ipaddress` VARCHAR(45) NOT NULL DEFAULT '',
    `referrerid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
    `languageid` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
    `emailstamp` INT(10) UNSIGNED NOT NULL DEFAULT '0',
    `threadedmode` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
    `autosubscribe` SMALLINT(6) NOT NULL DEFAULT '-1',
    `pmtotal` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
    `pmunread` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
    `salt` CHAR(30) NOT NULL DEFAULT '',
    `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',
    `adminoptions` INT(10) UNSIGNED 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',
    `post_thanks_user_amount` INT(10) UNSIGNED NOT NULL DEFAULT '0',
    `post_thanks_thanked_posts` INT(10) UNSIGNED NOT NULL DEFAULT '0',
    `post_thanks_thanked_times` INT(10) UNSIGNED NOT NULL DEFAULT '0',
    `xperience` BIGINT(20) NULL DEFAULT '1',
    `xperience_done` SMALLINT(6) NULL DEFAULT '0',
    `xperience_level` SMALLINT(6) NULL DEFAULT '1',
    `xperience_levelp` SMALLINT(6) NULL DEFAULT '1',
    `xperience_next_level` BIGINT(20) NULL DEFAULT '0',
    `xperience_next_level_points` BIGINT(20) NULL DEFAULT '1',
    `xperience_ppd` FLOAT NULL DEFAULT '0',
    `xperience_awards` MEDIUMTEXT NULL DEFAULT NULL,
    `xperience_shopitems` MEDIUMTEXT NULL DEFAULT NULL,
    `xperience_achievements` MEDIUMTEXT NULL DEFAULT NULL,
    `xperience_lastupdate` INT(10) NULL DEFAULT '0',
    `xperience_awardcount` SMALLINT(6) NULL DEFAULT '0',
    `xperience_promotioncount` SMALLINT(6) NULL DEFAULT '0',
    `xperience_achievementcount` SMALLINT(6) NULL DEFAULT '0',
    `ncode_imageresizer_mode` ENUM('none','enlarge','samewindow','newwindow') NULL DEFAULT NULL,
    `ncode_imageresizer_maxwidth` SMALLINT(5) UNSIGNED NULL DEFAULT NULL,
    `ncode_imageresizer_maxheight` SMALLINT(5) UNSIGNED NULL DEFAULT NULL,
    `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',
    `itrader_total` INT(10) NOT NULL DEFAULT '0',
    `itrader_pcnt` INT(10) NOT NULL DEFAULT '0',
    `itrader_buy` VARCHAR(255) NOT NULL DEFAULT '',
    `itrader_sell` VARCHAR(255) NOT NULL DEFAULT '',
    `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,
    `newrepcount` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
    `bloggroupreqcount` INT(10) UNSIGNED NOT NULL DEFAULT '0',
    `showblogcss` INT(11) NOT NULL DEFAULT '1',
    `vsafrules_date` INT(10) NOT NULL DEFAULT '0',
    `vsafrules_sets` VARCHAR(40) NOT NULL DEFAULT '0',
    `showvsastats` TINYINT(1) NOT NULL DEFAULT '1',
    `ars_count` INT(10) UNSIGNED NOT NULL DEFAULT '0',
    `indexpage` INT(11) NULL DEFAULT NULL,
    `isForeign` INT(1) NULL DEFAULT '0',
    `stylechoosen` INT(1) UNSIGNED NULL DEFAULT '0',
    `recent_thankcnt` INT(3) NOT NULL DEFAULT '0',
    `recent_thankact` TINYINT(1) NOT NULL DEFAULT '1',
    `uloShowDesktopNotifications` INT(1) UNSIGNED NOT NULL DEFAULT '1',
    `source` VARCHAR(50) NULL DEFAULT NULL,
    PRIMARY KEY (`userid`),
    INDEX `usergroupid` (`usergroupid`),
    INDEX `username` (`username`),
    INDEX `birthday` (`birthday`, `showbirthday`),
    INDEX `birthday_search` (`birthday_search`),
    INDEX `referrerid` (`referrerid`),
    INDEX `post_thanks_thanked_times` (`post_thanks_thanked_times`),
    INDEX `fbuserid` (`fbuserid`),
    INDEX `email` (`email`),
    INDEX `lastactivity` (`lastactivity`),
    INDEX `post_thanks_thanked_times_2` (`post_thanks_thanked_times`),
    INDEX `itrader_total` (`itrader_total`)
)
COLLATE='latin1_swedish_ci'
ENGINE=MyISAM
;
0

user table should have both the multicolumn indexes by the (id, userid) and (userid, id) columns while post table should have the index (postid, threadid, parentid, userid, visible, dateline) The order columns appear in the latter index also can be changed but I estimate an affordable result with the proposed one.

Also my advice is to migrate to the InnoDB from the MyISAM engine that will become obsolete soon.

  • What column do you mean by id? The user table has only a single id column called userid which is the PK. It seems that it has something to do with ORDER BY. When removing it, the small test query is pretty fast. Ordering by post.postid is also slow, where user.userid is fast... – Lion Jan 1 at 21:21
0

For this

    WHERE  `p`.`threadid` = 29780
      AND  `p`.`parentid` <> 0
      AND  `p`.`visible` IN ( 1, 2 )

The following index is optimal:

INDEX(threadid, visible)

Tacking more columns onto it (which you have done) probably does not help.

No index can finish off the WHERE clause and get into the ORDER BY because of the <>. So you are stuck with a sort and the temp table that goes with it. Furthermore, since you cannot make a single INDEX to handle both the WHERE and the ORDER BY, you can't get to the LIMIT in time to prevent fetching possibly lots of rows.

Even if you got rid of p.parentid <> 0, the IN would prevent having a single index to do the task.

STRAIGHT_JOIN might be a solution. This should force it to reference p first.

This is terribly confusing: p.Author means database p, table Author. Yet you seem to have a table named p.Author?!

Don't use MyISAM, use InnoDB.

Don't use CHAR except for truely constant-sized columns; use VARCHAR.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.