[Solved] Query - definition of ( table , table ) in simple mysql inner join

EverSQL Database Performance Knowledge Base

Query - definition of ( table , table ) in simple mysql inner join

Database type:

I'm reconstructing a search query coz it's becoming redundant in "What I see" and I'm wondering what

(albums_artists, artists) ( ) does in join? is it for boosting performance?

A query that uses simple inner joins, using the old (SQL-89) implicit join syntax:

    SELECT 
    ma_users.name, 
    ma_users.username, 
    albums.id AS album_id, 
    albums.upc, 
    albums.name AS album_name,
    albums.status, 
    albuminfos.label, 
    DATE_FORMAT(albums.created, '%Y-%m-%d') AS created_date,
    CONCAT(artists.name) AS artist_name,
    COUNT(tracks.id) AS total_tracks,
    albumstatus.description AS album_status
    FROM albums, albuminfos, ma_users , (albums_artists, artists) , tracks ,(albumstatus, albumtypes)
    WHERE 
    albums.id = albuminfos.id 
    AND ma_users.id = albums.account_id
    AND albums.id = albums_artists.artist_id 
    AND albums_artists.artist_id = artists.id
    AND tracks.album_id = albums.id
    AND albums.status = albumstatus.id 
    AND albumtypes.id = albums.albumtype_id
    AND albuminfos.label LIKE '%$keywords%'
    GROUP BY albums.id
    ORDER BY albuminfos.label

Accepting also for opinions for enhancements and errors that I need to anticipate.

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid LIKE Searches With Leading Wildcard (query line: 31): The database will not use an index when using like searches with a leading wildcard (e.g. '%$keywords%'). Although it's not always a satisfactory solution, please consider using prefix-match LIKE patterns (e.g. 'TERM%').
  2. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `albuminfos` ADD INDEX `albuminfos_idx_id` (`id`);
ALTER TABLE `albums` ADD INDEX `albums_idx_id_account_status_albumty` (`id`,`account_id`,`status`,`albumtype_id`);
ALTER TABLE `albums_artists` ADD INDEX `albums_artists_idx_artist_id` (`artist_id`);
ALTER TABLE `albumstatus` ADD INDEX `albumstatus_idx_id` (`id`);
ALTER TABLE `albumtypes` ADD INDEX `albumtypes_idx_id` (`id`);
ALTER TABLE `artists` ADD INDEX `artists_idx_id` (`id`);
ALTER TABLE `ma_users` ADD INDEX `ma_users_idx_id` (`id`);
ALTER TABLE `tracks` ADD INDEX `tracks_idx_album_id` (`album_id`);
The optimized query:
SELECT
        ma_users.name,
        ma_users.username,
        albums.id AS album_id,
        albums.upc,
        albums.name AS album_name,
        albums.status,
        albuminfos.label,
        DATE_FORMAT(albums.created,
        '%Y-%m-%d') AS created_date,
        CONCAT(artists.name) AS artist_name,
        COUNT(tracks.id) AS total_tracks,
        albumstatus.description AS album_status 
    FROM
        albums,
        albuminfos,
        ma_users,
        (albums_artists,
        artists),
        tracks,
        (albumstatus,
        albumtypes) 
    WHERE
        albums.id = albuminfos.id 
        AND ma_users.id = albums.account_id 
        AND albums.id = albums_artists.artist_id 
        AND albums_artists.artist_id = artists.id 
        AND tracks.album_id = albums.id 
        AND albums.status = albumstatus.id 
        AND albumtypes.id = albums.albumtype_id 
        AND albuminfos.label LIKE '%$keywords%' 
    GROUP BY
        albums.id 
    ORDER BY
        albuminfos.label

Related Articles



* original question posted on StackOverflow here.