Processing huge mysql query: BY group sensitivity

Question

I need to run this Mysql query on my website to get a huge amount of data: (3 tables, each of which contains 100,000 + records)

SELECT on_resume.*, on_users.subscribed, on_users.user_avatar, on_resume_page.* FROM on_resume LEFT JOIN on_users ON (on_resume.resume_userid = on_users.user_id ) LEFT JOIN on_resume_page ON ( on_resume.resume_userid = on_resume_page.resume_userid) WHERE on_resume.active= '1' GROUP BY on_resume.rid ORDER BY on_resume.rid DESC LIMIT 0,18 

The time I started in the sqlpmadmin section has completely shut down and needs to be restarted.

Now I tested this request, and it turned out that if I do not use the Group by and Order by conditions, the request will be fine.

 SELECT on_resume.*, on_users.subscribed, on_users.user_avatar, on_resume_page.* FROM on_resume LEFT JOIN on_users ON (on_resume.resume_userid = on_users.user_id ) LEFT JOIN on_resume_page ON ( on_resume.resume_userid = on_resume_page.resume_userid) WHERE on_resume.active= '1' LIMIT 0,18 

Display lines 0 - 17 (total of 18 queries, 0.4248 sec)

Why is this and how can I fix it? ...

NOTE. . I tested the SQL query with a group or one order in any case, even if one of them still did not fulfill the query and the server freezes.

EDIT . This problem is solved by indexing the on_resume_page.resume_userid column.

Answer 1

This is what I was told it took a while to figure it out: Look at @jer's comment in Chicago

Remember that when there is a GROUP BY clause, certain rules apply to group columns. One of these rules is the “Unambiguous Rule” - each column named in the SELECT list must also be a grouping column if it is not an argument for one of the specified functions. MySQL extends standard SQL to allow columns or calculations in the SELECT list that are not displayed in the GROUP BY clause. However, we do not recommend using this function unless the columns that you omit from the GROUP BY clause are unique in the group because you will get unpredictable results.