CODEWITHSUNDEEP
sql
Rainer Zufall
Rainer Zufall

Reputation: 41

SQL optimization tips

I am a beginner to sql and have some performance problems with this query. Any tips? The query is running on a huge database...

SELECT
                z_screenshots.guid,
                z_screenshots.player_name,
                z_screenshots.server,
                z_screenshots.map,
                z_screenshots.created,
                z_screenshots.uploaded,
                z_screenshots.uploader_id,
                z_screenshots.filesize
            FROM z_screenshots 
            INNER JOIN (
                SELECT clients.guid
                FROM clients WHERE clients.id NOT IN (
                    SELECT clients.id
                    FROM clients
                    INNER JOIN (
                        SELECT client_id
                        FROM penalties
                        WHERE penalties.inactive = 0 AND(penalties.type = 'Ban' OR (penalties.type = 'TempBan' AND FROM_UNIXTIME(penalties.time_expire) > NOW()) )
                        GROUP BY penalties.client_id
                    ) penalties
                    ON clients.id = penalties.client_id
                )
            ) clients
            ON z_screenshots.guid = clients.guid

            ORDER BY z_screenshots.uploaded DESC
            LIMIT ?, ?;

Upvotes: 1

Views: 82

Answers (1)

fancyPants
fancyPants

Reputation: 51868

I would advise to rewrite your query.

SELECT
    s.guid,
    s.player_name,
    s.server,
    s.map,
    s.created,
    s.uploaded,
    s.uploader_id,
    s.filesize
FROM z_screenshots s 
INNER JOIN clients c ON s.guid = c.guid
LEFT JOIN (
    SELECT DISTINCT client_id
    FROM penalties
    WHERE penalties.inactive = 0 
    AND (penalties.type = 'Ban' OR (penalties.type = 'TempBan' AND FROM_UNIXTIME(penalties.time_expire) > NOW()) )
) p ON c.id = p.client_id
WHERE p.client_id IS NULL;
ORDER BY s.uploaded DESC
LIMIT ?, ?;

When you don't have very much client_id, this query would also be okay:

SELECT
    s.guid,
    s.player_name,
    s.server,
    s.map,
    s.created,
    s.uploaded,
    s.uploader_id,
    s.filesize
FROM z_screenshots s 
INNER JOIN clients c ON s.guid = c.guid
WHERE c.id NOT IN 
(
    SELECT DISTINCT client_id
    FROM penalties
    WHERE penalties.inactive = 0 
    AND (penalties.type = 'Ban' OR (penalties.type = 'TempBan' AND FROM_UNIXTIME(penalties.time_expire) > NOW()) )
)
ORDER BY s.uploaded DESC
LIMIT ?, ?;

Regarding indexes, it's hard to tell. clients, this doesn't sound like you have very much distinct values. An index is the better, the closer the result of this formula gets to 1.

COUNT(DISTINCT <column>) / COUNT(<column>)

Meaning, when you have 5 distinct values and 5000000 rows, MySQL (assuming because of your LIMIT clause) will think that it's cheaper to just read the entire table. If this is the case here, you might consider having an index on z_screenshots.uploaded instead, so that MySQL at least doesn't have to sort. Read about EXPLAIN to know when an index is chosen and which index it is.

Upvotes: 2

Related Questions