MYSQL query crashes when used GROUP BY clause

Question

MYSQL query works perfectly fine on local server with no errors.. but when used on production server, it crashes and i have to restart the server

Here is my table structure for order_table

item_id | order_id | status_id | date
-------------------------------------------
    1   |     1    |     1     | 2014-01-20
    2   |     1    |     2     | 2014-01-20
    3   |     1    |     3     | 2014-01-20
    4   |     2    |     1     | 2014-01-21
    5   |     2    |     2     | 2014-01-21
    6   |     3    |     1     | 2014-01-21
    7   |     3    |     2     | 2014-01-21
    8   |     3    |     3     | 2014-01-22
    9   |     3    |     4     | 2014-01-22
    10  |     3    |     5     | 2014-01-23
-------------------------------------------

i can get the latest status of order_id by using below query

SELECT item_id, order_id, status_id
FROM order_table
WHERE item_id IN (
    SELECT MAX(item_id)
    FROM order_table
    GROUP BY order_id
) 
and status_id = '5' 
order by order_id

the above query works fine on local server but crashes on production server.. however when removed GROUP BY, the query is successfully executed on production server as well.. it looks like GROUP BY is creating problems

can anybody help me with an alternative way of getting same result with different approach

i remember this question from yesterday arn't you using two different database vendors? like MariaDB and MySQL? — Raymond Nijland, Apr 24 '19 at 12:39
@RaymondNijland i fixed fixed the database.. now i am using same database version MYSQL on local as well as production server.. but it works on local server and not on production server — user3292533, Apr 24 '19 at 12:42
Whats the size of data you are processing on production. If its less on DEV and more on PRO environments then you must optimize the query to solve crashesh — Mangesh Sathe, Apr 24 '19 at 12:43
@SougataBose we can ignore order by but Group By is needed so that we get one result of order id's with same value — user3292533, Apr 24 '19 at 12:44
*"i fixed fixed the database.. now i am using same database version MYSQL on local as well as production server."* Fair enough you should have mentioned that in this question.. Like i comment before questions about performance should also include a `EXPLAIN query` output and a `SHOW CREATE TABLE table` for every table involved in the question.. Without that the question is unclear — Raymond Nijland, Apr 24 '19 at 12:45
Try @Strawberry 's answer `IN(SELECT ...)` syntax is known to optimize badly in MySQL., the JOIN syntax which he used in his answer would most likely optimize much better. — Raymond Nijland, Apr 24 '19 at 12:57

score 1 · Answer 1 · answered Apr 24 '19 at 12:43

1

The standard (pre-8) approach is as follows - although it's possible (probable even) that the WHERE clause should be in the subquery...

SELECT x.item_id
     , x.order_id
     , x.status_id
  FROM order_table x
  JOIN 
     ( SELECT order_id
            , MAX(item_id) item_id
         FROM order_table
        GROUP 
           BY order_id
     ) y
    ON y.order_id = x.order_id
   AND y.item_d = x.item_id
 WHERE x.status_id = 5 
 order 
    by x.order_id

answered Apr 24 '19 at 12:43

Strawberry

33,750
13
40
57

*"although it's possible (probable even) that the WHERE clause should be in the subquery..."* How i don't see a (easy) way of doing that there isnt a limitting filter for the y alias only they are all relation depending ? MySQL does not support `LATERAL` joins, unless a new MySQL 8 update supports them? – Raymond Nijland Apr 24 '19 at 12:50
*"unless a new MySQL 8 update supports them"* O it seams MySQL 8.0.14+ now supports `LATERAL` see [manual](https://dev.mysql.com/doc/refman/8.0/en/lateral-derived-tables.html) – Raymond Nijland Apr 24 '19 at 12:53
@Strawberry thank you !!!!... you are a life saver – user3292533 Apr 24 '19 at 13:14

MYSQL query crashes when used GROUP BY clause

1 Answers1