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 !!!
A user is a user, which is different than a person.
A user is a role played by a person. A recruiter is also a role played by a person.
create table party(
id serial primary key,
name text not null
);
create table role_type(
id char(1) primary key,
name text not null unique
);
insert into role_type values
('u', 'user'),
('r', 'recruiter');
/* use Table Inheritance to add role-specific details. */
create table party_role(
party_id integer not null references party(id),
role_type char(1) not null references role_type(id),
...
primary key (party_id, role_type)
);
/* create a user: */
insert into party values
(1, 'Neil');
insert into party_role values
(1, 'u');
/* create someone that is both a recruiter and user: */
insert into party values
(2, 'Tristan');
insert into party_role values
(2, 'u'),
(2, 'r');
Best Answer
All modern chatting interfaces, without exception, implement a hierarchical and a not-chronological schema for chat. That means you have to use (the soon to be released) MySQL 8, as prior versions do not support recursive CTEs which are required to do this. Any workaround can't permit infinite depth, which is usually required or at the very least nice to have.
Rather than drawing up a schema here, you can see what one looks like in PostgreSQL here, where I answered a similar question. Essentially, each message has
id|parent_id
theparent_id
points to theid
on the same table. This creates a hierarchy. We call this implementation of hierarchy "self-referential", or "single-table hierarchy."Moreover, you may wish to implement an array of tagged users or the like for better indexing.
Ultimately, if you're going to go down this route and you have no prior work, you should be using PostgreSQL -- which supports Recursive CTE's now, and sql-array's for easy tagging.
I'm not saying your schema has to look like those. You may have additional demands, but I wouldn't want to start off with an inferior feature set and the wrong tool.
For clarity this critique is specific to your
chat_messages
table. The chat sessioning would be done by just creating a seperate table for it, and linking all messages to an entry in that table. For instance,Other tables..
Or the like.