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 );

You do know there is a character limit on GROUP_CONCAT, right. Text past this limit will be truncated... https://stackoverflow.com/questions/2567000/mysql-and-group-concat-maximum-length — ArtisticPhoenix, Jul 15 '17 at 03:53
To a rough approximation, there is no problem in MySQL for which CONCAT or GROUP_CONCAT need form any part of the answer. This is doubly true for result sets which are subsequently parsed in PHP, and, typically, performance is improved by their removal. So I'd start there. — Strawberry, Jul 15 '17 at 06:09
Also, correlated subqueries tend to perform more slowly than uncorrelated ones — Strawberry, Jul 15 '17 at 06:15

ArtisticPhoenix · Accepted Answer · 2017-07-15T04:36:34.470

2

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.'

edited Jul 15 '17 at 04:36

answered Jul 15 '17 at 03:58

ArtisticPhoenix

21,464
2
24
38

It gives me some error, check this fiddle http://rextester.com/HQJRH16867 – Josh Poor Jul 15 '17 at 04:21
LOL, you messed the `S` in `ELECT` as in `SELECT` Fixing that you'll get this `SELECT command denied to user 'rextester_user'@'localhost' for table 'tmdb_id'` – ArtisticPhoenix Jul 15 '17 at 04:22
Oh yes, the same error, i am getting in my PHP file. What's the fix of that new error bro? And thanks a lot – Josh Poor Jul 15 '17 at 04:25
Remove the field from the table name in the `JOIN tmdb_movies.tmdb_id AS tmdb_movies1` Should be `JOIN tmdb_movies AS tmdb_movies1` – ArtisticPhoenix Jul 15 '17 at 04:26
`Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'KC_movies.tmdb_movies.popularity' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by` Oh god, i get this error all the time. – Josh Poor Jul 15 '17 at 04:27
this is what I get `1 NULL NULL NULL NULL NULL` – ArtisticPhoenix Jul 15 '17 at 04:29
You may have to use `genres.genres_name IN( 'Crime','Drama' )` http://rextester.com/DHSBZ93643 – ArtisticPhoenix Jul 15 '17 at 04:32
`genres.genres_name IN( 'Crime','Drama' )` this gives me this error: `Unknown column 'genres.genres_name' in 'where clause'` And it is not supposed to give `NULL` right? Because Logan movie have Crime+Drama genres. – Josh Poor Jul 15 '17 at 04:36
DEPENDS if that should be OR, using IN is like using an OR – ArtisticPhoenix Jul 15 '17 at 04:37
`this is incompatible with sql_mode=only_full_group_by` :/ – Josh Poor Jul 15 '17 at 04:43
That stinks! I don't think there is much I can do about that. – ArtisticPhoenix Jul 15 '17 at 04:47
This post has some info in it, https://stackoverflow.com/questions/23921117/disable-only-full-group-by You could try setting it at run time. `SET sql_mode = ''` Just to see if the query is faster then the other way. – ArtisticPhoenix Jul 15 '17 at 04:49
I've used tricks like this on a table with over 100Million rows. Just thought I mention that ( we just changed to MongoDB for that table ) – ArtisticPhoenix Jul 15 '17 at 04:57
Oh and here is the result from the last fiddle `1|Logan|1|rgret34,rfrf34|Official|Trailer,Trailer|2|Crime,Comedy,Drama` – ArtisticPhoenix Jul 15 '17 at 04:59
Sorry for late reply, Internet stopped working. Last fiddle is giving this result `1 NULL NULL NULL NULL NULL` http://rextester.com/MJIMW41225 – Josh Poor Jul 15 '17 at 05:29
Sorry, I must not have saved it after changing it to in try this one http://rextester.com/CZTBW36219 – ArtisticPhoenix Jul 15 '17 at 06:05
I should mention this `IN ('Crime','Drama' )` is like doing an OR, so you may have to do both exists queries. As AND and one is not working because it does the AND on the same row, which is impossible. – ArtisticPhoenix Jul 15 '17 at 06:07
for that you can use this one http://rextester.com/UDCOR66233 they produce the same result in this case, but they are not the same logic. – ArtisticPhoenix Jul 15 '17 at 06:09
Last thing is this ( fiddle ) was missing the sort part, which is probably what the issue was to begin with, so without that it's difficult to say if this is faster. `ORDER BY $sortby $order LIMIT 10 OFFSET $start` Should go right after `GROUP BY tmdb_movies.movie_title, tmdb_movies.tmdb_id` in the sub-query – ArtisticPhoenix Jul 15 '17 at 06:10
Allowing an end user to sort on `$sortby` ( besides the sql injection possibility ) allows them to sort on non-indexed fields such as `movie_title` which will DESTROY any performance from this query ( your original ). Even my updated one will suffer for it. This is probably 75% chance your issue. – ArtisticPhoenix Jul 15 '17 at 06:14

score 0 · Answer 2 · answered Jul 15 '17 at 04:45

You can try filter while doing join with genres table.

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
    left JOIN genres  ON genres.tmdb_id=tmdb_movies.tmdb_id and genres.genres_name = '$genres1' AND genres.genres_name = '$genres2')
    --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

you can skip records while joining, this will help to improve your query performance.

   left JOIN genres  ON genres.tmdb_id=tmdb_movies.tmdb_id and genres.genres_name = '$genres1' AND genres.genres_name = '$genres2')

Try above code and check.

Improve SQL Query performance in PHP file

2 Answers2