phpmyadmin Question posted in
The official documentation can be found here.

SQL Database freezes on more than 7 JOIN in SELECT [Query taking forever to load] – Phpmyadmin

I am not sure why this query is taking forever and freezing the entire server. When I remove one coulmn value and Join form below its working. May I know if SQL has something like maximum number of join can support in a single query.

Query 1 [Freezing database and taking forever]

SELECT
u.username AS 'Username',
u.firstname AS 'Firstname',
u.lastname AS 'Lastname',
u.username AS 'Username',
u.id AS 'Userid',
u.email AS 'Email',
u.city AS 'City',
d1.data AS 'Resume',
d2.data AS 'LinkedIn',
d3.data AS 'Searchable', 
d4.data AS 'Score',
d5.data AS 'Current job title',
d6.data AS 'Current Employer',
d7.data AS 'Willing To Relocate',
d8.data AS 'Years of HVAC Experience'

FROM prefix_user u

JOIN prefix_user_info_data d1 ON d1.userid = u.id
JOIN prefix_user_info_field f1 ON d1.fieldid = f1.id AND f1.shortname = 'Resume'

JOIN prefix_user_info_data d2 ON d2.userid = u.id
JOIN prefix_user_info_field f2 ON d2.fieldid = f2.id AND f2.shortname = 'Linkedin'

JOIN prefix_user_info_data d3 ON d3.userid = u.id
JOIN prefix_user_info_field f3 ON d3.fieldid = f3.id AND f3.shortname = 'Searchable'

JOIN prefix_user_info_data d4 ON d4.userid = u.id
JOIN prefix_user_info_field f4 ON d4.fieldid = f4.id AND f4.shortname = 'Scores'

JOIN prefix_user_info_data d5 ON d5.userid = u.id
JOIN prefix_user_info_field f5 ON d5.fieldid = f5.id AND f5.shortname = 'JobTitle'

JOIN prefix_user_info_data d6 ON d6.userid = u.id
JOIN prefix_user_info_field f6 ON d6.fieldid = f6.id AND f6.shortname = 'Employer'

JOIN prefix_user_info_data d7 ON d7.userid = u.id
JOIN prefix_user_info_field f7 ON d7.fieldid = f7.id AND f7.shortname = 'Relocate'

JOIN prefix_user_info_data d8 ON d8.userid = u.id
JOIN prefix_user_info_field f8 ON d8.fieldid = f8.id AND f8.shortname = 'Years'

WHERE
CAST(d8.data AS float) > 5

Query 2 [After removing one value its working]

SELECT
u.username AS 'Username',
u.firstname AS 'Firstname',
u.lastname AS 'Lastname',
u.username AS 'Username',
u.id AS 'Userid',
u.email AS 'Email',
u.city AS 'City',
d1.data AS 'Resume',
d2.data AS 'LinkedIn',
d3.data AS 'Searchable', 
d4.data AS 'Score',
d5.data AS 'Current job title',
d6.data AS 'Current Employer',
d8.data AS 'Years of HVAC Experience'

FROM prefix_user u

JOIN prefix_user_info_data d1 ON d1.userid = u.id
JOIN prefix_user_info_field f1 ON d1.fieldid = f1.id AND f1.shortname = 'Resume'

JOIN prefix_user_info_data d2 ON d2.userid = u.id
JOIN prefix_user_info_field f2 ON d2.fieldid = f2.id AND f2.shortname = 'Linkedin'

JOIN prefix_user_info_data d3 ON d3.userid = u.id
JOIN prefix_user_info_field f3 ON d3.fieldid = f3.id AND f3.shortname = 'Searchable'

JOIN prefix_user_info_data d4 ON d4.userid = u.id
JOIN prefix_user_info_field f4 ON d4.fieldid = f4.id AND f4.shortname = 'Scores'

JOIN prefix_user_info_data d5 ON d5.userid = u.id
JOIN prefix_user_info_field f5 ON d5.fieldid = f5.id AND f5.shortname = 'JobTitle'

JOIN prefix_user_info_data d6 ON d6.userid = u.id
JOIN prefix_user_info_field f6 ON d6.fieldid = f6.id AND f6.shortname = 'Employer'

JOIN prefix_user_info_data d8 ON d8.userid = u.id
JOIN prefix_user_info_field f8 ON d8.fieldid = f8.id AND f8.shortname = 'Years'

WHERE
CAST(d8.data AS float) > 5
Tags:

2

Answers


  1. 0

    Since you took out the table d7, I would suggest checking out what’s the table in d7 and its join condition which is causing query to run for a long time.

    Login or Signup to reply.
  2. 0

    Incidentally, while it won’t do anything for performance, you may find this easier to read and maintain:

    SELECT u.username 
     , u.firstname 
     , u.lastname 
     , u.username 
     , u.id Userid
     , u.email 
     , u.city 
     , MAX(CASE WHEN f.shortname = 'resume' THEN d.data END) resume
     , MAX(CASE WHEN f.shortname = 'linkedin' THEN d.data END) linkedin
     , MAX(CASE WHEN f.shortname = 'searchable'  THEN d.data END) searchable
     , MAX(CASE WHEN f.shortname = 'scores'  THEN d.data END) score
     , MAX(CASE WHEN f.shortname = 'jobtitle'  THEN d.data END) 'current job title'
     , MAX(CASE WHEN f.shortname = 'employer'  THEN d.data END) 'current employer'
     , MAX(CASE WHEN f.shortname = 'relocate'  THEN d.data END) 'relocate'
     , MAX(CASE WHEN f.shortname = 'years'  THEN d.data END) 'Years of HVAC Experience'
  FROM prefix_user u
  JOIN prefix_user_info_data d
    ON d.userid = u.id
  JOIN prefix_user_info_field f
    ON f.id = d.fieldid 
 GROUP
    BY u.id
     , f.shortname
HAVING `Years of HVAC Experience` > 5;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search