Vryjdfkk

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.

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.)