Erik Edgren Erik Edgren - 4 months ago 8
MySQL Question

Get the latest data - optimize the SQL query

I am using the following SQL query in order to get the latest data from the database.

SELECT d.id AS id_d,
d.id_user AS id_du,
d.id_channel AS id_dc,
d.datetime_logged AS logged

FROM discord AS d
JOIN discord_users AS du
ON d.id_user = du.id
WHERE datetime_logged IN (SELECT MAX(datetime_logged) FROM discord)
ORDER BY du.data_name ASC


And here's how the database looks like:

CREATE TABLE IF NOT EXISTS `discord` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`id_user` int(11) NOT NULL,
`id_channel` varchar(50) NOT NULL,
`id_game` int(11) NOT NULL,
`data_muted_server` tinyint(4) NOT NULL,
`data_muted_self` tinyint(4) NOT NULL,
`data_deafen_server` tinyint(4) NOT NULL,
`data_deafen_self` tinyint(4) NOT NULL,
`data_suppressed` tinyint(4) NOT NULL,
`data_status` varchar(10) NOT NULL,
`data_game` text,
`datetime_logged` datetime NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
)

CREATE TABLE IF NOT EXISTS `discord_users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`data_id` text NOT NULL,
`data_name` varchar(50) NOT NULL,
`data_avatar` text,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
)


That query takes "ages" to load. But if I remove
WHERE datetime_logged IN (SELECT MAX(datetime_logged) FROM discord)
and add
LIMIT 10
to the query, the page loads super fast!




The purpose of the SQL query is to get only the latest data from the table
discord
. Since the data will only update every 15 minute, the query will easily pick up the latest data.

The relationship with
discord
and
discord_users
is that
discord
needs the ID from
discord_users
in order to get the information from an user.

To answer symcbean's questions futhermore:

Q) Why, when you are aapparently restricting the ouput to a single record based on the content of the "many" side of the relationship, are you ordering by the "one" side?

A) What do you mean?

Q) Why are you opening 2 cursors on the discord_users table in the absence of differential indexes on the table?

A) Once again, what do you mean? :)




So here's my question. How can I optimize my SQL query so it dosen't takes almost 1 minute to load the page? Here's a link to the page: https://erik-edgren.nu/discord

Answer

If you still have the issue try this option. Create an INDEX say e.g index_datelog on the column datetime_logged of discord table. And then try below query

SELECT 
    d.id AS id_d, d.id_user AS id_du, d.id_channel AS id_dc, d.datetime_logged AS logged 
FROM 
    discord d 
JOIN 
    discord_users du ON d.id_user = du.id USE INDEX(index_datelog))
WHERE 
    d. datetime_logged = (SELECT MAX(datetime_logged) FROM discord)
ORDER BY d.datetime_logged ASC