CODEWITHSUNDEEP
mysql
noroot
noroot

Reputation: 13

Query slow when using order by in combination with a join

Given following tables:

CREATE TABLE `webs_shoutbox` (
  `shoutID` int(11) NOT NULL AUTO_INCREMENT,
  `date` int(14) NOT NULL DEFAULT '0',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `message` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `ip` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`shoutID`),
  KEY `shoutID` (`shoutID`),
  KEY `name` (`name`(191))
) ENGINE=InnoDB AUTO_INCREMENT=62982 DEFAULT CHARSET=utf8 COLLATE=utf8_roman_ci |

CREATE TABLE `webs_user` (
  `userID` int(11) NOT NULL AUTO_INCREMENT,
  `registerdate` int(14) NOT NULL DEFAULT '0',
  `lastlogin` int(14) NOT NULL DEFAULT '0',
  `username` varchar(255) COLLATE utf8_roman_ci NOT NULL DEFAULT '',
  `password` varchar(255) COLLATE utf8_roman_ci NOT NULL DEFAULT '',
  `nickname` varchar(255) COLLATE utf8_roman_ci NOT NULL DEFAULT '',
   ....,
   ....,
  PRIMARY KEY (`userID`),
  KEY `nickname` (`nickname`),
  KEY `userID` (`userID`)
) ENGINE=MyISAM AUTO_INCREMENT=3366 DEFAULT CHARSET=utf8 COLLATE=utf8_roman_ci

The webs_user table contains about 4k records, the webs_shoutbox contains about 100k records.

I want to query the database and get data from the two tables, using a left join: 

select shoutID, date, name, message, webs_user.userID
from webs_shoutbox 
left join webs_user on webs_shoutbox.name = webs_user.username 
limit 10;

This behaves as expected, and will return the result very quickly (in the ms range). But for my query I need it to order by either date or the shoutID, in order to get the latest ones. So I added an order by statement:

select shoutID, date, name, message, webs_user.userID  
from webs_shoutbox 
left join webs_user on webs_shoutbox.name = webs_user.username  
order by shoutID desc 
limit 10;

Suddenly this query takes 13-14 seconds!? I'm really baffled by this HUGE performance issue. After lot's of reading and googling I am unable to find why it's doing such a horrible job on sorting on the primary key field. When doing a select on the webs_shoutbox table with an order by statement, it works fine. Its only in combination with the join that the performance drops.

What could be causing this? Why would it be taking this long to do seemingly quick operation (sorting on an PK/index)?

Upvotes: 1

Views: 60

Answers (3)

Alisa
Alisa

Reputation: 3072

Try this one (I hope that works because limits the "order by" to one table):

select t1.shoutID, t1.date, t1.name, t1.message, webs_user.userID
    from (
        select * 
        from webs_shoutbox 
        order by order by shoutID desc
        limit 10
        ) as t1 
        left join webs_user on t1.name = webs_user.username 
    order by shoutID desc 
    limit 10;

And in general, you'd better not use text/char/varchar fields as means of relationship (i.e., foreign key). Working with integer fields and indexing them is faster.

Upvotes: 0

Stan
Stan

Reputation: 1430

Try following

SELECT ws.shoutID, ws.date, ws.name, ws.message, webs_user.userID FROM
    (select shoutID, date, name, message 
    from webs_shoutbox 
    order by shoutID
    limit 10) as ws
left join webs_user on ws.name = webs_user.username  
order by shoutID desc limit 10

Upvotes: 1

BeerRush
BeerRush

Reputation: 21

Make sure, the PK has a hash index for joining and a btree index for sorting.

Upvotes: 0

Related Questions