Geek Answers Handbook

How to optimize this simple JOIN + ORDER BY query?

I have two mysql tables:

/* Table users */
CREATE TABLE IF NOT EXISTS `users` (
  `Id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `DateRegistered` datetime NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/* Table statistics_user */
CREATE TABLE IF NOT EXISTS `statistics_user` (
  `UserId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Sent_Views` int(10) unsigned NOT NULL DEFAULT '0',
  `Sent_Winks` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`UserId`),
  CONSTRAINT `statistics_user_ibfk_1` FOREIGN KEY (`UserId`) REFERENCES `users` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Both tables are populated with 10,000 random rows for testing using the following procedure:

DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `FillUsersStatistics`(IN `cnt` INT)
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE dt DATE;
DECLARE Winks INT DEFAULT 1;
DECLARE Views INT DEFAULT 1;

WHILE (i<=cnt) DO
        SET dt = str_to_date(concat(floor(1 + rand() * (9-1)),'-',floor(1 + rand() * (28 -1)),'-','2011'),'%m-%d-%Y');

        INSERT INTO users (Id, DateRegistered) VALUES(i, dt);

        SET Winks = floor(1 + rand() * (30-1));
        SET Views = floor(1 + rand() * (30-1));
        INSERT INTO statistics_user (UserId, Sent_Winks, Sent_Views) VALUES (i, Winks, Views);

     SET i=i+1;
END WHILE;

END//
DELIMITER ;
CALL `FillUsersStatistics`(10000);

Problem:

When I run EXPLAIN for this request:

SELECT
t1.Id, (Sent_Views + Sent_Winks) / DATEDIFF(NOW(), t1.DateRegistered) as Score
FROM users t1
JOIN  statistics_user t2 ON t2.UserId = t1.Id
ORDER BY Score DESC

.. I will explain this:

Id  select_type table   type    possible_keys   key     key_len     ref             rows    extra
1   SIMPLE      t1      ALL     PRIMARY         (NULL)  (NULL)      (NULL)          10037   Using temporary; Using filesort
1   SIMPLE      t2      eq_ref  PRIMARY         PRIMARY 4           test2.t2.UserId 1

The above query becomes very slow when both tables have more than 500k rows. I think this is due to the use of temporary; Using filesort 'in the request explanation.

How to optimize the request described above so that it works faster?

+5
source share
4 answers

I am sure ORDER BY is killing you, because it cannot be indexed correctly. Here is an effective, if not particularly beautiful solution.

-, , Score . , Sent_Views Sent_Winks , Score, . , ( ) , Sent_Views Sent_Winks. DATEDIFF, Sent_Views + Sent_Winks .

Score ( ). script, cron.

Score !

: , .

+1

, , , , - _.

0

:

, , , , 5000 . . ( rocedure atrigger ( mysql ?)).

score statistics_user, .

SQL :

SELECT
   UserId, score  
FROM
  statistics_user 
ORDER BY score DESC

, , , mysql.

0
source

Shouldn't IndexRegistered be indexed in Users?

0
source

More articles:


All Articles