CODEWITHSUNDEEP
mysqlsql
user2035806
user2035806

Reputation:

Fetching a big number of records using JOIN

I have this query that I use in order to get records from several tables, using JOIN.

SELECT c.id           AS contestant_id,
       c.created_date,
       c.name         AS contestant_name,
       counter.total  AS score,
       c.email
  FROM submission AS s,

       (SELECT ans.id AS ans_id, sub.contestant_id, count(sub.id) AS total
          FROM submission AS sub
          JOIN (SELECT id, is_true FROM answer) AS ans
         WHERE sub.answer_id = ans.id
           AND ans.is_true = 1
         GROUP BY sub.contestant_id) AS counter

  JOIN (SELECT id, name, email, type, created_date
          FROM contestant
         WHERE contest_type = 1
           AND submission_status = 1) AS c

 WHERE counter.contestant_id = c.id
 GROUP BY c.id
 ORDER BY c.created_date DESC

The problem is that each record in table contestant will have 30 record in the submission table. So, when I retrieve 1000 contestant or more, the server hangs.

Upvotes: 0

Views: 76

Answers (2)

user2941651
user2941651

Reputation:

Please try the following restructured query:

SELECT 
    c.id AS contestant_id, 
    c.created_date, 
    c.name AS contestant_name, 
    counter.total AS score, 
    c.email
FROM 
    (
      SELECT  
        sub.contestant_id, count(sub.id) AS total
      FROM 
        submission AS sub 
        JOIN answer AS ans
            ON sub.answer_id = ans.id AND ans.is_true = 1 
      GROUP BY 
        sub.contestant_id
    )
    AS counter
    JOIN contestant c
      ON c.contest_type = 1 AND c.submission_status = 1 AND c.id = counter.contestant_id
WHERE 
    counter.contestant_id = c.id 
GROUP BY 
    c.id 
ORDER BY 
    c.created_date DESC

Upvotes: 1

ashkufaraz
ashkufaraz

Reputation: 5297

It is possible to change the query result row limit, or remove the limit entirely.

Go to "Edit -> Preferences... -> SQL Editor (tab)"

Locate the "Query Results" section and untick the "Limit Rows" checkbox

Click "OK"

Re-run your query

Exporting query results in MySQL Workbench beyond 1000 records

Upvotes: 0

Related Questions