Make MySQL evaluate subquery first

Question

SELECT    tt.trans_type_name AS transaction_type,
          trans.transaction_time,
          a.trans_action_name AS transaction_action_name,
          trans.transaction_notes
FROM(SELECT Cast(added_on AS CHAR) AS transaction_time,
            notes                  AS transaction_notes,
            trans_type_id          AS trans_type,
            trans_action_id        AS action_id
     FROM   transactions
     WHERE  added_by = 'service_app'
     AND    notes LIKE %(domain)s
     AND    added_on BETWEEN %(transactionstartdate)s 
                         AND %(transactionenddate)s) trans
LEFT JOIN transaction_types tt
ON        trans.trans_type = tt.trans_type_id
LEFT JOIN transaction_actions a
ON        trans.action_id = a.trans_action_id
ORDER BY  trans.transaction_time DESC

This is a query I rewritten because the older query had the error:

The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay

The new query(written above) is more faster but I am still getting the MAX_JOIN error. The thing is, I cannot set SET SQL_BIG_SELECTS=1. My wish is evaluating the subquery first because for the values I plug, there are only 3 rows. The join needs to only happen for the 3 rows since it is just replacing the ID with it's corresponding value.

Is there a way? Thanks in advance.

Edit: Some more clarity - 3 tables transactions, transaction_types and transaction_actions. The base table is the transactions which has more than million rows. It has two IDs which references the other two tables. I want to select the names instead of IDs so joining it.

The admin won't allow it ??? Both SET SESSION SQL_BIG_SELECTS=1; and SET SESSION MAX_JOIN_SIZE = 18446744073709551615; does not need in any special privilege. How to make MySQL evaluate subquery first Try to define it in CTE. As an extreme option add LIMIT 0, 18446744073709551615 to it. And, if added_on column datatype is DATETIME/TIMESTAMP then you do not need in explicit CAST. — Akina
– Akina, Commented Nov 19 at 7:29
The subquery is already evaluated first, since it forms the temporary table on which the query is performed. An EXPLAIN output would indeed be useful, and could you also add SHOW CREATE TABLE, and the number of rows in each table? JOINS will work a lot better when indexes can be used. — KIKO Software
– KIKO Software, Commented Nov 19 at 8:13
You want the DBMS to notice that the inner query will only get few rows. For this to happen you'll need an index on (added_by, added_on) or (added_on, added_by) depending on selectivty. With up-to-date statistics, the DBMS should detect that it pays to get the transactions rows first. — Thorsten Kettner
– Thorsten Kettner, Commented Nov 19 at 8:27
I've noticed two things in your query: (1) What is added_on? From its name, it sounds like a date, but you cast it into a string and then call it time, however, you use BETWEEN, so I'd guess on a date again. Or is that even some number or string? (2) Then, if you are interested in the domain, why is it part of a notes string and not a separate column? Storing the domain separately from unformatted notes might speed up such queries a lot. — Thorsten Kettner
– Thorsten Kettner, Commented Nov 19 at 8:30
Also what is the current value of max_join_size on your MySQL server? The default is very large (max BIGINT value), so the admin must have reduced it deliberately. Perhaps they reduced it too much. You should talk to them about your problem. — Bill Karwin
– Bill Karwin, Commented 2 days ago

ValNik · Accepted Answer · 2025-11-19 13:14:51Z

If main subquery returns only few rows, try substitute JOIN's by subqueries. Then maybe the query plan won't include JOIN's with subordinate tables.

I assume that trans_type_id is the key in the transaction_types table.
And trans_action_id is the key in the transaction_actions table.


SELECT (select tt.trans_type_name AS transaction_type from  transaction_types tt
         where trans.trans_type_id = tt.trans_type_id
       )AS transaction_type ,
       Cast(added_on AS CHAR) AS transaction_time,
       (select a.trans_action_name from  transaction_actions a
        where trans.trans_action_id = a.trans_action_id
       )AS transaction_action_name,
       trans.notes AS transaction_notes
FROM transactions trans
     WHERE  added_by = 'service_app'
     AND    notes LIKE concat('%',@domain)
     AND    added_on BETWEEN @transactionstartdate 
                         AND @transactionenddate
ORDER BY  trans.added_on DESC

However, "more than million rows" should not lead to such an error with MAX_JOIN_SIZE (with default value 18446744073709551615).

I've changed the WHERE clause somewhat. It doesn't matter.

Collectives™ on Stack Overflow

Make MySQL evaluate subquery first

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related