8
\$\begingroup\$

I have a dilemma. I have a messages table containing fields id, sender, receiver, body I need to select all users a given user has had a conversation with. A conversation may be a single message without a response.

Here's a show create table for you to see the indexes (as I may have set them up incorrectly):

CREATE TABLE `messages` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sender` int(10) unsigned NOT NULL,
  `receiver` int(10) unsigned NOT NULL,
  `body` text,
  `datetime` datetime DEFAULT NULL,
  `seen` enum('0','1') NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `message_receiver` (`receiver`),
  KEY `msg_snd` (`sender`),
  CONSTRAINT `msg_rcv` FOREIGN KEY (`receiver`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `msg_snd` FOREIGN KEY (`sender`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

Here is basically the simplest way I could come up with to get the task done

SELECT DISTINCT
    (
        CASE SENDER
        WHEN 15 THEN
            receiver
        ELSE
            CASE receiver
        WHEN 15 THEN
            sender
        END
        END
    ) AS `user`
FROM
    messages
WHERE
    sender = 15
OR receiver = 15

However, I know that MySQL cannot take full advantage of indexes with OR comparisons (may be wrong), so I came up with an alternative of two queries which can both benefit from the indexes:

SELECT DISTINCT
    `user`
FROM
    (
        SELECT
            DISTINCT sender AS `user`
        FROM
            messages
        WHERE
            receiver = 15
        UNION
            SELECT
                DISTINCT receiver AS `user`
            FROM
                messages
            WHERE
                sender = 15
    ) AS conversations

Which one of these approaches is better in terms of performance and complexity?

\$\endgroup\$
5
\$\begingroup\$

Use OR

You should make it a habit to look at your queries' execution plan when in doubt. I recreated the table in SQL Fiddle (albeit, without containing data) and it shows that the query with OR is less complex.

Please note: SQL Fiddle does not support your FOREIGN KEY constraints, so I had to remove them.

First query with OR:

OR

Second query with UNION:

UNION

As you can see, the complexity of the 2nd query is higher, which might make it slower (or faster!), albeit the direction (faster or slower) and how much difference it will have depends on a lot of other things too.

In MySQL (and many other DBMS), it's actually (and counter-intuitively) the UNION ALL is usually more efficient than the OR version. Of course, that's because the UNION ALL does not have to remove duplicate rows. In many cases, including this one, we have use UNION (so UNION DISTINCT).


The edit below has been suggested by @ypercube, from dba.SE, and voluntarily added by OP:

Also note that your second query has a unnecessary level and an extra DISTINCT that is redundant which will almost certainly drag efficiency down. Here are two simplifications:

 -- query 2b:  SELECT DISTINCT - UNION DISTINCT - SELECT DISTINCT
SELECT
    DISTINCT sender AS `user`
FROM
    messages
WHERE
    receiver = 15
UNION
    SELECT
        DISTINCT receiver 
    FROM
        messages
    WHERE
        sender = 15 ;


-- query 2c: SELECT ALL - UNION DISTINCT - SELECT ALL
SELECT
    sender AS `user`
FROM
    messages
WHERE
    receiver = 15
UNION
    SELECT
        receiver 
    FROM
        messages
    WHERE
        sender = 15 ;
\$\endgroup\$
  • \$\begingroup\$ I noticed this myself too but what made me ask this question is that I asked myself how this all works. Yes the union creates a derived table but I know that only one index can be used in a single where clause thus the simple query will only benefit from either of the indexes, and to fulfill the query will have to do an all scan for the other parameter. With a lot of records this will be way slower than the complex query. \$\endgroup\$ – php_nub_qq Jun 14 '15 at 17:22
  • \$\begingroup\$ I found no evidence that it's actually the case "that only one index can be used in a single where clause", do you have a link? I read this page about MySQL optimizations but it did not seem to mention this, unless I read it wrong? \$\endgroup\$ – Phrancis Jun 14 '15 at 17:36
  • 2
    \$\begingroup\$ @php_nub_qq It's a common misconception but no. MySQL can and does use 2 or more indexes (but rarely). Check about "Index Merge Optimization". \$\endgroup\$ – ypercubeᵀᴹ Jun 14 '15 at 22:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.