How do I optimize this MYSQL JOIN Query?-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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