How do I optimize this MYSQL JOIN Query?

Question

I am having a hard time dealing with query optimization and I believe the one am currently using can be improved a lot.

I have 4 tables;

Artist (14,930 rows)

artist_id	artist_name	artist_slug	artist_country
1	Elle	elle	5

Country (85 rows)

country_id	country_name	country_slug
1	USA	usa

Song (61,119 rows)

song_id	song_title	song_artist_id	song_plays
1	Kitty kat	1	38291
2	Donkey	1	8291

Lyrics (61,119 rows)

lyrics_id	lyrics_song_id	lyrics_body
1	1	Kitty Kat lyrics
2	2	Donkey lyrics

Am joining the tables using this query fetching a single artist's data plus, country, the total number of songs, the sum of the plays on all songs by the artist, and the total number of lyrics.

SELECT /*+ MAX_EXECUTION_TIME(2000) */ 
artist.*,
country.*,
COUNT(song.song_id) AS TotalSongs,
COUNT(lyrics.lyrics_id) AS TotalLyrics,
SUM(song.song_plays) AS TotalPlays
FROM artist
LEFT JOIN country ON artist.artist_country_id = country.country_id
LEFT JOIN song ON artist.artist_id = song.song_artist_id
LEFT JOIN lyrics ON song.song_id = lyrics.lyrics_song_id 
WHERE artist_status = 'enabled' AND artist.artist_slug = '$slug'
GROUP BY artist.artist_id LIMIT 0, 1

How do I improve it to execute faster and use less resources?

Thanks in advance.

I don't know you can change the SQL much. I do worry the multiple joins are multiplying your COUNT() results, where you might find you need correlated subqueries in the SELECT clause instead for some of those counts. That's likely to make things slower, but it could also be necessary if you want accurate results. For performance here, I'd start with your indexing. — Joel Coehoorn
– Joel Coehoorn, Commented May 18, 2022 at 14:22
To help you with your query-optimization question, we need to see your table definitions, your index definitions, and the output of EXPLAIN. Please read this, then edit your question. — O. Jones
– O. Jones, Commented May 18, 2022 at 14:30
Without an ORDER BY, the LIMIT 1 returns an unpredictable row. — Rick James
– Rick James, Commented May 20, 2022 at 18:40
Please provide SHOW CREATE TABLE so we can determine what indexes are missing. — Rick James
– Rick James, Commented May 20, 2022 at 18:43

Salman Arshad · Accepted Answer · 2022-05-18 14:51:55Z

1

I would simply use a subquery in select clause for aggregation:

SELECT artist.*, country.*, (
    SELECT COUNT(*)
    FROM song
    WHERE song.song_artist_id = artist.artist_id
) AS total_songs, (
    SELECT SUM(song_plays)
    FROM song
    WHERE song.song_artist_id = artist.artist_id
) AS total_plays, (
    SELECT COUNT(*)
    FROM song
    JOIN lyrics ON song.song_id = lyrics.lyrics_song_id
    WHERE song.song_artist_id = artist.artist_id
) AS total_lyrics
FROM artist
LEFT JOIN country ON artist.artist_country_id = country.country_id
WHERE artist_status = 'enabled'
AND artist.artist_slug = :slug

In MySQL 8 or later you can use a lateral join:

SELECT artist.*, country.*, lj.*
FROM (artist LEFT JOIN country ON artist.artist_country_id = country.country_id), LATERAL (
    SELECT COUNT(song.song_id) total_songs, SUM(song_plays) total_plays, COUNT(lyrics.lyrics_id) total_lyrics
    FROM song
    LEFT JOIN lyrics ON song.song_id = lyrics.lyrics_song_id
    WHERE song.song_artist_id = artist.artist_id
) AS lj
WHERE artist_status = 'enabled'
AND artist.artist_slug = :slug

edited May 18, 2022 at 14:51

answered May 18, 2022 at 14:34

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rick James Over a year ago

@Busker -- How much faster is this formulation?

Nikhil · Accepted Answer · 2022-05-18 15:06:35Z

Create all the indexes on the tables with columns as :

CREATE INDEX index_name1 ON artist (artist_country_id ); 
CREATE INDEX index_name2 ON country (country_id); 
CREATE INDEX index_name3 ON artist (artist_id ); 
CREATE INDEX index_name4 ON song (song_artist_id); 
CREATE INDEX index_name5 ON artist (artist_status );

and create a view as such:

CREATE VIEW customerPayments
AS 
SELECT /*+ MAX_EXECUTION_TIME(2000) */ 
artist.*,
country.*,
COUNT(song.song_id) AS TotalSongs,
COUNT(lyrics.lyrics_id) AS TotalLyrics,
SUM(song.song_plays) AS TotalPlays
FROM artist
LEFT JOIN country ON artist.artist_country_id = country.country_id
LEFT JOIN song ON artist.artist_id = song.song_artist_id
LEFT JOIN lyrics ON song.song_id = lyrics.lyrics_song_id 
WHERE artist_status = 'enabled' AND artist.artist_slug = '$slug'
GROUP BY artist.artist_id LIMIT 0, 1;

A view is a named query stored in the database catalog

View does not physically store the data. When you issue the SELECT statement against the view, MySQL executes the underlying query specified in the view’s definition and returns the result set. For this reason, sometimes, a view is referred to as a virtual table.

A Materialized View (MV) is the pre-calculated (materialized) result of a query. Unlike a simple VIEW the result of a Materialized View is stored somewhere, generally in a table. Materialized Views are used when immediate response is needed and the query where the Materialized View bases on would take to long to produce a result. Materialized Views have to be refreshed once in a while. It depends on the requirements how often a Materialized View is refreshed and how actual its content is. Basically a Materialized View can be refreshed immediately or deferred, it can be refreshed fully or to a certain point in time. MySQL does not provide Materialized Views by itself. But it is easy to build Materialized Views yourself.

As materialzed views gets data on the basis of cache they are faster than views. There is no concept of materialized views in Mysql.But you can implement your own materialized views by https://fromdual.com/mysql-materialized-views

Since it is tagged [mysql], please note that "Materialized Views" do not exist in MySQL. More to the point MySQL's Views are just syntactic sugar, and sometimes even run slower than the equivalent Select.

Collectives™ on Stack Overflow

How do I optimize this MYSQL JOIN Query?

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related