I have a big messages database there are 2.4m rows:
Showing rows 0 - 24 (2455455 total, Query took 0.0006 seconds.)
Messages, so I need conversations to load faster, for users that have less conversations it loads (user have 3.2k conversations):
Showing rows 0 - 24 (3266 total, Query took 0.0345 seconds.) [id: 5009666... - 4375619...]
For users that have high number of conversations it loads slower (user have 40k conversations):
Showing rows 0 - 24 (40296 total, Query took 5.1763 seconds.) [id: 5021561... - 5015545...]
I’m using index keys for these columns:
id, to_id, from_id, time, seen
CREATE TABLE `messages` ( `id` int(255) NOT NULL, `to_id` int(20) NOT NULL, `from_id` int(20) NOT NULL, `message` longtext NOT NULL, `time` double NOT NULL, `seen` int(2) NOT NULL, ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `messages` (`id`, `to_id`, `from_id`, `message`, `time`, `seen`) VALUES (2, 6001, 2, 'Hi there', 1587581995.5222, 1); ALTER TABLE `messages` ADD PRIMARY KEY (`id`), ADD KEY `time_idx` (`time`), ADD KEY `from_idx` (`from_id`), ADD KEY `to_idx` (`to_id`), ADD KEY `seenx` (`seen`), ADD KEY `idx` (`id`); ALTER TABLE `messages` MODIFY `id` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5021570; COMMIT;
I’m using this query:
SELECT * FROM messages, ( SELECT MAX(id) as lastid FROM messages WHERE ( messages.to_id = '1' -- ID to compare with (logged in users's ID) OR messages.from_id = '1' -- ID to compare with (logged in users's ID) ) GROUP BY CONCAT( LEAST(messages.to_id, messages.from_id), '.', GREATEST(messages.to_id, messages.from_id) ) ) as conversations WHERE id = conversations.lastid ORDER BY messages.id DESC
I don’t know how to make it faster for users that have a lot of conversations, should i re create the database structure.
- Use UNION instead of OR (see below)
- There are redundant keys. The
PRIMARY KEYis a key, so toss
- Don’t blindly index every column; instead use the queries to determine what indexes, especially composite indexes, would actually be useful.
CONCATis unnecessary and possibly counterproductive in
- Length fields on
INTare ignored. What you have is limited to 2 billion values. (This is overkill for
seen, which assume is 0 or 1?)
- Use the new syntax: JOIN..ON.
seenis just true/false, then toss the index on it. (Or show me the query that you think will benefit from it.)
CONCAT-LEAST-GREATEST — This is to construct a "friends_id"? Perhaps you really wanted a "conversation_id"? Currently, two users can never have more than one "conversation", correct?
Make a new column for
conversation_id if it is really needed. (Currently, the
GROUP BY is inefficient.) The code below obviates the need for such an id.
( SELECT lastid FROM ( ( SELECT from_id, MAX(id) AS lastid FROM messages WHERE to_id = ? GROUP BY from_id ) UNION DISTINCT ( SELECT to_id, MAX(id) AS lastid FROM messages WHERE from_id = ? GROUP BY to_id ) ) AS x ) AS conversations
And have these these ‘covering’ and ‘composite’ indexes:
INDEX(to_id, from_id, id) INDEX(from_id, to_id, id)
and toss KEY(to_id), KEY(from_id) as since my new indexes handle anything else that these were to do.
I think this has the same effect but will run much faster.
Putting it together:
SELECT * FROM ( ( SELECT from_id AS other_id, MAX(id) AS lastid FROM messages WHERE to_id = ? GROUP BY from_id ) UNION ALL ( SELECT to_id AS other_id, MAX(id) AS lastid FROM messages WHERE from_id = ? GROUP BY to_id ) ) AS latest JOIN messages ON messages.id = latest.lastid ORDER BY messages.id DESC
(plus the two indexes)
I was thinking (incorrectly) that
UNION DISTINCT would replace the need for a
conversation_id. But it won’t. Offhand I see some solutions:
- Add a
conversation_idand dedup using it. (Meanwhile, I changed
UNION ALL, making the query a little faster without changing the results.)
- Put the output of my query into a temp table with (from_id, to_id, latestid); then do your
CONCAT-LEAST-GREATESTtrick to dedup conversations; finally do the JOIN back to
messagesto get the rest of the columns.
- That temp table technique makes it easier to write and debug. My 3rd suggestion is simply cramming the pieces together to do it is a single (hard-to-read) query with Selects nested at 3 levels deep.
Answered By – Rick James
Answer Checked By – Robin (BugsFixing Admin)