Mysql – thesql join 3 table retain order of data order by used in subquery

MySQL

I have a query like this

SELECT a.pmid,a.sendid,a.sendtime,a.recid,a.message,b.unread,r.avator1 ra,s.avator1 sa,s.username sun,r.username run 
FROM pm a 
JOIN 
( 
  SELECT SUM(IF(recid = '347' and hasread=0, 1, 0)) AS undread,
         LEAST(sendid,recid) user1 , 
         GREATEST(sendid,recid) user2 , 
         MAX(pmid) pmid 
  FROM pm 
  WHERE sendid='347' OR recid='347' 
  GROUP BY user1 , user2 
) b 
ON b.pmid = a.pmid 
JOIN
  users s ON s.userid = a.sendid 
JOIN 
  users r ON r.userid=a.recid 
ORDER BY unread DESC, pmid DESC LIMIT 0,10

I have performance issue with this query it's taking some 8-10 seconds to complete execution if I alter by removing order by unread desc from the past of the query

query complete execution with in 1 sec but result is wrong,not what i need.

if i move order by unread desc inside the subquery example

SELECT a.pmid,
       a.sendid,
       a.sendtime,
       a.recid,
       a.message,
       b.unread,
       r.avator1 ra,
       s.avator1 sa,
       s.username sun,
       r.username run
FROM pm a
JOIN
  (SELECT SUM(IF(recid = '347'
                 AND hasread=0, 1, 0)) AS unread,
          LEAST(sendid,recid) user1,
          GREATEST(sendid,recid) user2,
          MAX(pmid) pmid
   FROM pm
   WHERE sendid='347'
     OR recid='347'
   GROUP BY user1,
            user2
   ORDER BY unread DESC,pmid DESC) b ON b.pmid = a.pmid
JOIN users s ON s.userid=a.sendid
JOIN users r ON r.userid=a.recid
LIMIT 0,
      10

then it is still not giving result what i need.

if i run the subquery “

SELECT SUM(IF(recid = '347'
              AND hasread=0, 1, 0)) AS unread,
       LEAST(sendid,recid) user1,
       GREATEST(sendid,recid) user2,
       MAX(pmid) pmid
FROM pm
WHERE sendid='347'
  OR recid='347'
GROUP BY user1,
         user2
ORDER BY unread DESC,
         pmid DESC

separately then its giving correct data but then if i join user table once with on either recid or sendid then result is correct but when i join user table twice with both sendid and recid then the result order is incorrect.

table pm structure here

CREATE TABLE `pm` (
 `pmid` bigint(40) unsigned NOT NULL AUTO_INCREMENT,
 `sendid` bigint(20) unsigned DEFAULT NULL,
 `recid` bigint(20) unsigned DEFAULT NULL,
 `subject` mediumtext DEFAULT NULL,
 `message` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `hasread` int(10) unsigned DEFAULT 0,
 `sendtime` int(10) unsigned DEFAULT NULL,
 `ack` int(10) unsigned NOT NULL DEFAULT 0,
 PRIMARY KEY (`pmid`),
 KEY `sendid_2` (`sendid`,`recid`),
 KEY `recid` (`recid`,`hasread`),
 KEY `recid_2` (`recid`)
) ENGINE=InnoDB AUTO_INCREMENT=1900982 DEFAULT CHARSET=utf8

table users structure here

 CREATE TABLE `users` (
 `userid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
 `username` text CHARACTER SET latin1 DEFAULT NULL,
 `password` text CHARACTER SET latin1 DEFAULT NULL,
 `pass` varchar(30) CHARACTER SET latin1 NOT NULL,
 `groupid` bigint(20) unsigned NOT NULL DEFAULT 2,
 `email` text CHARACTER SET latin1 DEFAULT NULL,
 `rank` varchar(30) CHARACTER SET latin1 NOT NULL DEFAULT '0',
 `validated` int(10) unsigned NOT NULL DEFAULT 0,
 `avator1` varchar(30) CHARACTER SET latin1 NOT NULL DEFAULT '0.gif',
 PRIMARY KEY (`userid`)
) ENGINE=InnoDB AUTO_INCREMENT=33744 DEFAULT CHARSET=utf8mb4

please help me.

Best Answer

This kludge allegedly works: Tack LIMIT 999999999 onto the subquery. That forces the optimizer to use the ORDER BY. Normally (in newer versions of MySQL), the Optimizer simply throws away the ORDER BY in certain subqueries -- because the SQL Standard says that a subquery delivers an unordered set. A reference.

(Not relevant in your case.) Meanwhile, explicitly add ORDER BY in the subquery; don't depend on GROUP BY providing an ordering. That MySQL 'extension' is in flux. (If the GROUP BY and ORDER BY are the same, the Optimizer is smart enough to do them together.)

Because of the OR, the only practical way to do the subquery is with a full table scan of the 2M rows.

Optimization tip: Smaller --> faster, especially if the table is too big to be cached. So...

  • Don't use BIGINT (8 bytes) when something smaller would suffice.
  • Don't use INT (4 bytes) for a 'flag'. TINYINT is 1 byte.
  • Don't use TEXT when VARCHAR(255 or less) would suffice. (There is an extra savings when a tmp table is needed.)
Related Question