Mysql – INNER JOIN is extremly slow on some rows and create temp table

entity-frameworkjoin;mariadbMySQLtemporary-tables

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)

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

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
;

Best Answer

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.

Background

post

user

Best Answer

Related Solutions

Mysql – Are two indexes needed

Mysql – How to optimize indexes on MySQL query with various sorts

Related Question