Why im getting error 1114 the table is full?

Question

This query works fine

SELECT MAX(customers.created_at) lst_trx_date, business_infos.id, business_infos.name as business_name, users.name as owner_name  FROM users
    INNER JOIN business_infos ON users.id = business_infos.user_id
    INNER JOIN customers on business_infos.id = customers.businessId
    GROUP BY customers.businessId
    ORDER BY customers.created_at DESC

but this query getting an eerror

SELECT MAX(orders.date) lst_trx_date, business_infos.id, business_infos.name as business_name, users.name as owner_name  FROM users
    INNER JOIN business_infos ON users.id = business_infos.user_id
    INNER JOIN orders on business_infos.id = orders.businessId
    GROUP BY orders.businessId
    ORDER BY orders.created_at DESC

1st query have small data and 2nd query have a large data what should i need to consider ?

Answer 1

It's more likely that tmpdir points to a too-small disk partition.

These indexes may help:

customers:  INDEX(businessId,  created_at)
orders:  INDEX(businessId,  date, created_at)
business_infos:  INDEX(user_id,  id, name)

Even better might be to rearrange the query to something like this:

SELECT ...
    FROM ( SELECT ... GROUP BY ... ) AS x
    JOIN the other tables
    ORDER BY ...

What you have suffered from "explode-implode" because all the JOINing ('explode') is done before the GROUP BY (implode). What I am proposing starts with 'implode'.

This will also shrink the tmp table requirements.