0

To begin with, here is my query:

SELECT 
  pd.meterclass, pd.meterscore, pd.rt_url, pd.imdbId, pd.title, pd.adult,
  pd.backdrop,pd.budget, pd.homepage, pd.plot, pd.poster, pd.release_date,
  pd.revenue, pd.runtime, pd.language, pd.tagline, pd.status, 
  pd.imdbRatings, pd.imdbVotes, mg.genre_id, cst.cast_id,  
  cst.character_name, cst.name, 
  cst.profile_path, crw.job, crw.name as crew_name, 
  crw`enter code here`.profile_path as crew_profile_path, crw.id as crew_id, 
  crw.credit_id as crew_credit_id 
FROM (
       SELECT * 
       from movie_data_primary  
       WHERE cinestopId="8QRJTD"
     ) pd 
INNER JOIN movie_genre mg ON pd.cinestopId = mg.cinestopId 
INNER JOIN movie_cast cst ON cst.cinestopId  = pd.cinestopId 
INNER JOIN movie_crew crw ON crw.cinestopId = cst.cinestopId;

Before I upgrade my MySQL version to 5.7, this query used to take around 3 seconds (not the most optimal time, but tolerable anyway). After the upgrade, it takes more than half an hour. Here's the profile:

+----------------------+------------+
| Status               | Duration   |
+----------------------+------------+
| starting             |   0.000221 |
| checking permissions |   0.000013 |
| checking permissions |   0.000006 |
| checking permissions |   0.000004 |
| checking permissions |   0.000008 |
| Opening tables       |   0.000039 |
| init                 |   0.000158 |
| System lock          |   0.000018 |
| optimizing           |   0.000025 |
| statistics           |   0.002507 |
| preparing            |   0.000063 |
| executing            |   0.000007 |
| Sending data         | 999.999999 |
| end                  |   0.000013 |
| query end            |   0.000012 |
| closing tables       |   0.000012 |
| freeing items        |   0.002281 |
| cleaning up          |   0.000023 |
+----------------------+------------+

The field 'cinestopId' is indexed. Can someone tell me what's going berserk?

Thanks!

  • What if you break the query and create table from create temporary table t1 as select * from movie_data_primary WHERE cinestopId="8QRJTD"; and then use this table in join instead.. – Nawaz Sohail Apr 9 '18 at 16:39
  • Actually, I have done that in the query. – Prashant Pandey Apr 9 '18 at 16:44
  • Did you read the notes on how the optimizer may have changed? Was the service restarted? I'm thinking the plan used to be in cache but now it has to be read from disk back into memory? What did the query plan before and after look like? Are the wait types the same before and after? – Shaulinator Apr 9 '18 at 17:14
  • @Shaulinator can you please explain a bit more? I am very new to SQL. Yes indeed, the service was restarted after the upgrade. – Prashant Pandey Apr 10 '18 at 6:06
  • Do you have a backup of the database before you upgraded? What was the version you upgraded from? Can you post the query plan? This site will tell you about the query plan. dev.mysql.com/doc/refman/5.5/en/execution-plan-information.html When you restart the service, anything that was in memory gets dumped. As you run queries, the service caches them in memory so it can be re-used again and be much faster. MySQL also optimizes how best to execute your query, it stashes these ideas on how to best do this in memory. If it leaves memory, it must go back to disk for that information. – Shaulinator Apr 10 '18 at 13:20
2

Have you tried the query without joining against a subselect?

SELECT 
  pd.meterclass, pd.meterscore, pd.rt_url, pd.imdbId, pd.title, pd.adult,
  pd.backdrop,pd.budget, pd.homepage, pd.plot, pd.poster, pd.release_date,
  pd.revenue, pd.runtime, pd.language, pd.tagline, pd.status, 
  pd.imdbRatings, pd.imdbVotes, mg.genre_id, cst.cast_id,  
  cst.character_name, cst.name, 
  cst.profile_path, crw.job, crw.name as crew_name, 
  crw`enter code here`.profile_path as crew_profile_path, crw.id as crew_id, 
  crw.credit_id as crew_credit_id 
FROM movie_data_primary pd
INNER JOIN movie_genre mg ON pd.cinestopId = mg.cinestopId 
INNER JOIN movie_cast cst ON cst.cinestopId  = pd.cinestopId 
INNER JOIN movie_crew crw ON crw.cinestopId = cst.cinestopId;
WHERE pd.cinestopId="8QRJTD"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.