Improving MySQL Query-Performance

Question

Query in PHP File (SQL Fiddle)

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)

GROUP BY tmdb_movies.movie_title,tmdb_movies.tmdb_id

HAVING find_in_set('$category1', genres_name) $andor find_in_set('$category2', genres_name) 

ORDER BY $sortby $order LIMIT 10 OFFSET $start

In this query: $category1 and $category2 are variables like (Action, Crime, Horror, Comedy etc)

$andor is an variable. It's value is AND, if both variables $category1 and $category2 have values.

If only $category1 have value, then $andor variable value is OR

Here this 10 querys take around 4-6 seconds to display. My expected speed is less than 0.1 seconds. I have around 80k rows and 35 columns in MySQL tables.

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(tmdb_id, genres_name);

tmdb_movies table:

tmdb_id      movie_title
1            Logan
2            Iron Man
3            Superman

genres table

tmdb_id                 genres_name
1                         Crime
1                         Comedy
1                         Drama
2                         Action
2                         Horror
2                         Documentary
3                         Music

videos table

videos_tmdb_id          videos_name
1                       Official Trailer
1                       Trailer 2 
2                       Trailer 1
2                       Trailer 2 HD
3                       Superman Trailer 1
3                       Superman Trailer 2

Let me know, if you need more information

Edit: Explain Query Screeshot

I don't think MySQL can use an index to speed up the GROUP BY, but those calls to FIND_IN_SET() could be costly. You might want to normalize your data and avoid making them. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jul 10, 2017 at 6:54
Edited question, and added Explain query Screenshot. I am using one to many relationship structure. Normalization structure is different? I can change my structure without emptying MySQL tables? — Josh Poor
– Josh Poor, Commented Jul 10, 2017 at 7:01
Then, anyway to replace GROUP BY or Find_IN_SET() ? Any other query sir? — Josh Poor
– Josh Poor, Commented Jul 10, 2017 at 7:06

fancyPants · Accepted Answer · 2017-07-10 07:56:29Z

2

Please have a try with the following query:

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 g WHERE g.tmdb_id = genres.tmdb_id AND g.genres_name = '$category1' )

GROUP BY tmdb_movies.movie_title,tmdb_movies.tmdb_id

ORDER BY $sortby $order LIMIT 10 OFFSET $start

Note, that I included the condition 1=1 in the WHERE clause. This is, so you can easily append AND whatever. This way you can more easily build an if condition in your code, to append $category2 or not. Checking for an empty string like you do is pointless and should be avoided.

EDIT:

To avoid the DISTINCT in the GROUP_CONCAT you'd have to write those as subqueries to avoid multiplication by the join. I didn't do it for the genres, though, cause it's covered pretty well with your index, I think.
Anyway, I also have to say, that I doubt that you will gain any improvement with this.

SELECT tmdb_movies.movie_title,tmdb_movies.tmdb_id
, (SELECT GROUP_CONCAT(videos_key) FROM videos v WHERE v.videos_tmdb_id = tmdb_movies.tmdb_id)
, (SELECT GROUP_CONCAT(videos_name) FROM videos v WHERE v.videos_tmdb_id = tmdb_movies.tmdb_id)
,GROUP_CONCAT(DISTINCT genres.genres_name) AS genres_name

FROM tmdb_movies
JOIN genres USING (tmdb_id)

WHERE 1=1
AND EXISTS( SELECT 1 FROM genres g WHERE g.tmdb_id = genres.tmdb_id AND g.genres_name = '$category1' )

GROUP BY tmdb_movies.movie_title,tmdb_movies.tmdb_id

ORDER BY $sortby $order LIMIT 10 OFFSET $start

edited Jul 10, 2017 at 7:56

answered Jul 10, 2017 at 7:17

fancyPants

52.1k34 gold badges94 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Josh Poor Over a year ago

Great, now the page opens in 1.1 seconds. Anyway to further optimize it. And this is how, I put $category2 also right? prntscr.com/ftrdb5

fancyPants Over a year ago

Yes, you can do it that way, but like I said, I would prefer to only append the condition for category2 with and when there's actually a value in $category2. To further improve the query, I think there's not much more you can do query-wise. What does the EXPLAIN output say?

Josh Poor Over a year ago

I heard DISTINCT can also damage speed, How can I remove it without any bug, like sql_mode=only_full_group_by or displaying the same thing multiple times

Josh Poor Over a year ago

Sorry, I forgot to paste the link: Here is explain query Screenshot prntscr.com/ftrkj7

Josh Poor Over a year ago

Oh last thing, What you mean by append it? I never heard of it before

|

etsa · Accepted Answer · 2017-07-10 07:51:45Z

1

I would try to change (if possible) TEXT datatype with VARCHAR, using effective max length for VARCHAR. TEXT could have bad effect on index performance. It could have little meaning to analyse execution plan with so few records as the ones you put on rextester, so please try on your database and let me know.

Eg.:

CREATE TABLE genres (
  tmdb_id INTEGER NOT NULL,
  genres_name VARCHAR(100) NOT NULL
);

CREATE TABLE videos (
  videos_tmdb_id INTEGER NOT NULL,
  videos_name VARCHAR(800) NOT NULL,
  videos_key VARCHAR(100) NOT NULL
);

And then create the index you suggested, but I would give a try changing index on genres to:

create index idxv on genres(genres_name, tmdb_id);

answered Jul 10, 2017 at 7:51

etsa

5,0501 gold badge9 silver badges18 bronze badges

3 Comments

Josh Poor Over a year ago

What is the difference between create index idxv on genres(genres_name, tmdb_id); create index idxv on genres(tmdb_id,genres_name,);

etsa Over a year ago

Try this link stackoverflow.com/questions/44774927/…

etsa Over a year ago

Basically, it can go directly to records wih genres_name (as you would do looking for a last name in a phone book). You are filtering records with genres_name, so I can suppose the index in that way could give some benefits.

verhie · Accepted Answer · 2017-07-10 07:24:15Z

0

I would try to get the genres first, and then join the rest to that result:

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
(
SELECT
distinct(tmdb_id) as tmdb_id
FROM
genres
WHERE 
genres_name = '$category1' $andor genres_name = '$category2'
) x
LEFT JOIN tmdb_movies ON tmdb_movies.tmdb_id=x.tmdb_id
LEFT JOIN videos ON videos.videos_tmdb_id=x.tmdb_id
LEFT JOIN genres ON genres.tmdb_id=x.tmdb_id
GROUP BY tmdb_movies.movie_title,x.tmdb_id
ORDER BY $sortby $order LIMIT 10 OFFSET $start

answered Jul 10, 2017 at 7:24

verhie

1,3181 gold badge7 silver badges7 bronze badges

1 Comment

Josh Poor Over a year ago

It display nothing mate. Test your code here: rextester.com/XVDFQ59723 I added SQL fiddle now

Collectives™ on Stack Overflow

Improving MySQL Query-Performance

3 Answers 3

6 Comments

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related