Improve SQL Query performance in PHP file

Question

This is the query, I use to count the total number of rows (I need this for pagination)

$sql = "SELECT count(tmdb_id),group_concat(genres.genres_name) AS genres_name

 FROM `tmdb_movies` 

JOIN genres USING (tmdb_id)

WHERE 1=1
AND EXISTS( SELECT 1 FROM genres  WHERE tmdb_movies.tmdb_id = genres.tmdb_id AND genres.genres_name = '$genres1' )
    AND EXISTS( SELECT 1 FROM genres  WHERE tmdb_movies.tmdb_id = genres.tmdb_id AND genres.genres_name = '$genres2' )

GROUP BY tmdb_movies.tmdb_id";


$result = $conn->prepare($sql); 
$result->execute(); 
$totalrows = $result->rowCount();

Screenshot of this query with EXPLAIN statement

This is the SQL query, I use to echo results

$stmt = $conn->prepare("SELECT tmdb_movies.movie_title,tmdb_movies.tmdb_id
,GROUP_CONCAT(DISTINCT videos.videos_key) as videos_key
,GROUP_CONCAT(DISTINCT videos.videos_name) as videos_name
,GROUP_CONCAT(DISTINCT genres.genres_name) AS genres_name

FROM tmdb_movies

LEFT JOIN videos ON videos.videos_tmdb_id=tmdb_movies.tmdb_id
JOIN genres USING (tmdb_id)

WHERE 1=1
AND EXISTS( SELECT 1 FROM genres  WHERE tmdb_movies.tmdb_id = genres.tmdb_id AND genres.genres_name = '$genres1' )
AND EXISTS( SELECT 1 FROM genres  WHERE tmdb_movies.tmdb_id = genres.tmdb_id AND genres.genres_name = '$genres2' )

GROUP BY tmdb_movies.movie_title,tmdb_movies.tmdb_id

ORDER BY $sortby $order LIMIT 10 OFFSET $start"); 

     // Then fire it up
     $stmt->execute();

     // Pick up the result as an array
     $result = $stmt->fetchAll();




    // Now you run through this array in many ways, for example
     for($x=0, $n=count($result); $x < $n; $x++){
   echo $result[$x]["movie_title"];

}

Screenshot of EXPLAIN statement with this query

Here, the first query takes around 0.6 seconds to execute, and second one also takes around 0.6 seconds to execute.

Is there any way to merge both the SQL Query's to save execution time?

Any other way to increase SQL Query performance?

Here is the SQL Fiddle of second SQL Query.

My Indexes:

create index idxm on tmdb_movies(tmdb_id);
create index idxv on videos(videos_tmdb_id, videos_name, videos_key);
create index idxv on genres(genres_name, tmdb_id );

ArtisticPhoenix · Accepted Answer

Whats the point of this

I wouldn't say this is an answer, because query optimization, is never that easy, this is more query simplification ...

WHERE 1=1
AND EXISTS( SELECT 1 FROM genres  WHERE tmdb_movies.tmdb_id = genres.tmdb_id AND genres.genres_name = '$genres1' )
AND EXISTS( SELECT 1 FROM genres  WHERE tmdb_movies.tmdb_id = genres.tmdb_id AND genres.genres_name = '$genres2' )

Isn't this equivalent ( it's all ands )

WHERE
EXISTS( SELECT 1 FROM genres  WHERE tmdb_movies.tmdb_id = genres.tmdb_id AND genres.genres_name IN( '$genres1','$genres2'))

My guess is the sorting is gonna kill you... To try run it without the sort.

If that is quick then sometimes sorting in an inner query that pulls just ID's can help, because sort might make a temp table, so pulling the smallest amount of information sorting it, then doing an outer query with a join, can help.

Something like this ( note I cant really test this so it's just a guess, although I have had success using this method in the past )

SELECT
    tmdb_movies1.movie_title
    ,tmdb_movies1.tmdb_id
    ,GROUP_CONCAT(DISTINCT videos.videos_key) as videos_key
    ,GROUP_CONCAT(DISTINCT videos.videos_name) as videos_name
    ,GROUP_CONCAT(DISTINCT genres.genres_name) AS genres_name
FROM (
    SELECT 
        tmdb_movies.tmdb_id
    FROM
        tmdb_movies
    WHERE
        EXISTS( SELECT 1 FROM genres  WHERE tmdb_movies.tmdb_id = genres.tmdb_id AND genres.genres_name IN('$genres1','$genres2' ))
    GROUP BY tmdb_movies.movie_title, tmdb_movies.tmdb_id
    ORDER BY $sortby $order LIMIT 10 OFFSET $start
) AS tmdb_movies0
JOIN
    tmdb_movies AS tmdb_movies1 
ON
    tmdb_movies0.tmdb_id = tmdb_movies1.tmdb_id
LEFT JOIN
    videos
ON
    videos.videos_tmdb_id=tmdb_movies1.tmdb_id
JOIN
    genres ON genres.tmdb_id = tmdb_movies1.tmdb_id

Basically to speed up the sorting, your pulling out the smallest amount of data, just an ID, sorting them and then using that to Join and pull out the rest of the data ( the bulk ) that lets you create the smallest Temp table possible with the sort, which uses less memory etc...

It could work.... ( 15% chance really ... ha ha )

* As a side note * Or's can also be a killer, ( although I don't see any ) you can get some performance from a query with or by doing a similar trick with a sub query that uses UNION, instead of OR, and pulls just the Pk ID's. Then joins back on the tables to pull out the data, 2 Unions queries can be equivalent to an OR.

NOTE I would not be doing you a good service If i didn't mention SQL Injection, So use prepared statements for this stuff '.$var.'

Improve SQL Query performance in PHP file

Answers (2)

Related Questions