Geek Answers Handbook

Mysql select query perf really bad

I am not sure why this request takes 4 minutes:

SELECT 
    su.sid,u.uid,u.display_name,u.locale 
FROM user u 
LEFT JOIN subscription_user su ON su.uid = u.uid 
ORDER BY u.display_name DESC 
LIMIT 0,25;

Well, I know this because of the order, delete it and very quickly. If I switch to using INNER JOIN instead, this is fast, but the problem is not that all users can be in the subscription_user table.

CREATE TABLE `user` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `password` varchar(100) DEFAULT NULL,
  `user_type` varchar(10) NOT NULL DEFAULT 'user',
  `display_name` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `locale` varchar(8) DEFAULT 'en',
  `last_login` datetime DEFAULT NULL,
  `auth_type` varchar(10) DEFAULT NULL,
  `auth_data` varchar(500) DEFAULT NULL,
  `inactive` tinyint(4) NOT NULL DEFAULT '0',
  `receive_email` tinyint(4) NOT NULL DEFAULT '1',
  `stateid` int(10) DEFAULT NULL,
  `owner_group_id` int(11) DEFAULT NULL,
  `signature` varchar(500) DEFAULT NULL,
  `raw_signature` varchar(500) DEFAULT NULL,
  `round_robin` smallint(5) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`uid`),
  UNIQUE KEY `email` (`email`),
  KEY `stateid` (`stateid`) USING BTREE,
  KEY `user_type` (`user_type`) USING BTREE,
  KEY `name` (`display_name`)
) ENGINE=InnoDB AUTO_INCREMENT=28343 DEFAULT CHARSET=latin1;

CREATE TABLE `subscription_user` (
  `sid` varchar(50) NOT NULL,
  `uid` int(11) NOT NULL,
  `deleted` tinyint(4) NOT NULL DEFAULT '0',
  `forum_user` varchar(50) NOT NULL,
  PRIMARY KEY (`sid`,`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+4
source share
1 answer

When you have an SQL query, the index can really help you if the first column in the index is part of the query.

Your request is combined su.uid = u.uidand the optimizer will not be able to use it to refer to the first column in the subscription primary key index.

, , , uid

+3

More articles:


All Articles