Mysql – How to get all non deleted messages from one user

MySQLmysql-5performancequeryquery-performance

I have two tables one for messages called default_messages and the other for recipients called default_recipient. This is how SQL for those table looks like:

CREATE TABLE IF NOT EXISTS `default_messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subject` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
  `message` text COLLATE utf8_unicode_ci NOT NULL,
  `sender_user_id` int(11) NOT NULL,
  `reply_to_message_id` int(11) NOT NULL,
  `thread_root_message_id` int(11) NOT NULL,
  `date` datetime NOT NULL,
  `deleted` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5 ;

And this is the DATA for this table:

INSERT INTO `default_messages` (`id`, `subject`, `message`, `sender_user_id`, `reply_to_message_id`, `thread_root_message_id`, `date`, `deleted`) VALUES
(1, 'asdasdadasdasdasdasd', 'sdasdasdad', 1, 0, 0, '2012-05-11 09:29:00', 1),
(2, 'sadasdasd', 'asdasd', 2, 0, 0, '2012-05-11 09:44:23', 0),
(3, 'Re: asdasdadasdasdasdasd', 'asdadadadadadad', 2, 1, 0, '2012-05-11 09:44:41', 1),
(4, 'Re: sadasdasd', 'asdadad respuesta', 1, 2, 0, '2012-05-11 09:46:22', 1);

Now for the second table this is the SQL:

CREATE TABLE IF NOT EXISTS `default_recipient` (
  `message_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `read` int(11) NOT NULL DEFAULT '0',
  `recipient_read_date` datetime DEFAULT NULL,
  `deleted` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

And the DATA is this one:

INSERT INTO `default_recipient` (`message_id`, `user_id`, `read`, `recipient_read_date`, `deleted`) VALUES
 (1, 2, 1, NULL, 0),
 (2, 1, 1, NULL, 1),
 (3, 1, 1, NULL, 1),
 (4, 2, 1, NULL, 1);

Now I have this query for get all messages for user_id=x where deleted columns in both tables default_recipient and default_messages is not equal to 1 but it's not working because I get two results instead of one when user_id at table recipient or sender_user_id at table messages takes value equal 2:

SELECT p.*, rcp.*, msg.*, msg.id as message_id FROM default_messages msg 
        LEFT JOIN default_recipient rcp ON (msg.id = rcp.message_id)
        LEFT JOIN default_profiles p ON (p.user_id = msg.sender_user_id)
        WHERE msg.sender_user_id = 2 OR rcp.user_id = 2
        AND msg.deleted = 0 AND rcp.deleted = 0
        ORDER BY msg.date DESC

What I'm doing wrong?

EDIT: The right question here was: how to get all messages for one user "X" let says where rcp.deleted = 0 OR msg.deleted = 0

Best Answer

Two suggestions

SUGGESTION #1 : Refactor the Query

SELECT
    p.*, rcp.*, msg.*, msg.id as message_id 
FROM
    (
        SELECT id FROM default_messages
        WHERE sender_user_id = 2 AND deleted = 0
        ORDER BY date DESC
    ) msgkey
    LEFT JOIN default_messages msg ON (msgkey.id = msg.id)
    LEFT JOIN 
    (
        SELECT * FROM default_recipient
        WHERE user_id = 2 AND deleted = 0
    ) rcp ON (msgkey.id = rcp.message_id) 
    LEFT JOIN default_profiles p ON (msg.sender_user_id = p.user_id) 
;

SUGGESTION #2 : Index the tables to support the subqueries

ALTER TABLE default_messages ADD INDEX (sender_user_id,deleted,date,id);
ALTER TABLE default_recipient ADD INDEX (user_id,deleted);

As an example, I loaded the data from your question and indexed the two tables

mysql> use reynierpm
Database changed
mysql> drop table if exists default_messages;
Query OK, 0 rows affected (0.05 sec)

mysql> drop table if exists default_recipient;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE IF NOT EXISTS `default_messages` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `subject` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
    ->   `message` text COLLATE utf8_unicode_ci NOT NULL,
    ->   `sender_user_id` int(11) NOT NULL,
    ->   `reply_to_message_id` int(11) NOT NULL,
    ->   `thread_root_message_id` int(11) NOT NULL,
    ->   `date` datetime NOT NULL,
    ->   `deleted` int(11) NOT NULL DEFAULT '0',
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5 ;
Query OK, 0 rows affected (0.13 sec)

mysql> CREATE TABLE IF NOT EXISTS `default_recipient` (
    ->   `message_id` int(11) NOT NULL,
    ->   `user_id` int(11) NOT NULL,
    ->   `read` int(11) NOT NULL DEFAULT '0',
    ->   `recipient_read_date` datetime DEFAULT NULL,
    ->   `deleted` int(11) NOT NULL DEFAULT '0'
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.08 sec)

mysql> ALTER TABLE default_messages ADD INDEX (sender_user_id,deleted,date,id);
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE default_recipient ADD INDEX (user_id,deleted);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT INTO `default_messages` (`id`, `subject`, `message`, `sender_user_id`, `reply_to_message_id`, `thread_root_message_id`, `date`, `deleted`) VALUES

    -> (1, 'asdasdadasdasdasdasd', 'sdasdasdad', 1, 0, 0, '2012-05-11 09:29:00', 1),
    -> (2, 'sadasdasd', 'asdasd', 2, 0, 0, '2012-05-11 09:44:23', 0),
    -> (3, 'Re: asdasdadasdasdasdasd', 'asdadadadadadad', 2, 1, 0, '2012-05-11 09:44:41', 1),
    -> (4, 'Re: sadasdasd', 'asdadad respuesta', 1, 2, 0, '2012-05-11 09:46:22', 1);
Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO `default_recipient` (`message_id`, `user_id`, `read`, `recipient_read_date`, `deleted`) VALUES
    ->  (1, 2, 1, NULL, 0),
    ->  (2, 1, 1, NULL, 1),
    ->  (3, 1, 1, NULL, 1),
    ->  (4, 2, 1, NULL, 1);
Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT
    ->     rcp.*, msg.*, msg.id as message_id
    -> FROM
    -> (
    ->     SELECT id FROM default_messages
    ->     WHERE sender_user_id = 2 AND deleted = 0
    ->     ORDER BY date DESC
    -> ) msgkey
    -> LEFT JOIN default_messages msg ON (msgkey.id = msg.id)
    -> LEFT JOIN
    -> (
    ->     SELECT * FROM default_recipient
    ->     WHERE user_id = 2 AND deleted = 0
    -> ) rcp ON (msgkey.id = rcp.message_id)
    -> \G
*************************** 1. row ***************************
            message_id: NULL
               user_id: NULL
                  read: NULL
   recipient_read_date: NULL
               deleted: NULL
                    id: 2
               subject: sadasdasd
               message: asdasd
        sender_user_id: 2
   reply_to_message_id: 0
thread_root_message_id: 0
                  date: 2012-05-11 09:44:23
               deleted: 0
            message_id: 2
1 row in set (0.01 sec)

mysql>

I altered the query to test the join of the two tables:

mysql> SELECT
    ->     rcp.*, msg.*, msg.id as message_id
    -> FROM
    -> (
    ->     SELECT id FROM default_messages
    ->     WHERE sender_user_id = 2 AND deleted = 0
    ->     ORDER BY date DESC
    -> ) msgkey
    -> LEFT JOIN default_messages msg ON (msgkey.id = msg.id)
    -> LEFT JOIN
    -> (
    ->     SELECT * FROM default_recipient
    ->     WHERE user_id = 2 AND deleted = 0
    -> ) rcp ON (msgkey.id = rcp.message_id)
    -> \G
*************************** 1. row ***************************
            message_id: NULL
               user_id: NULL
                  read: NULL
   recipient_read_date: NULL
               deleted: NULL
                    id: 2
               subject: sadasdasd
               message: asdasd
        sender_user_id: 2
   reply_to_message_id: 0
thread_root_message_id: 0
                  date: 2012-05-11 09:44:23
               deleted: 0
            message_id: 2
1 row in set (0.01 sec)

mysql>

I get one row. As the two tables grow, the indexes will help the refactored query run fast.

Give it a Try !!!

Best Answer

SUGGESTION #1 : Refactor the Query

SUGGESTION #2 : Index the tables to support the subqueries

Related Solutions

Mysql – Unexplained InnoDB timeouts

Mysql – How to get non-deleted messages for one user_id if the same user_id appears in two tables at same time

Related Question