How to Force MySQL to Perform Subquery First

force subquery resolution first

I suggest you tu use the subquery factoring. The first subquery will be executed only once and then used through the rest of he query.

WITH function_result AS
  (SELECT package.function1('I take a long time') column1 
   ,      package.function2('I take even longer') column2
   FROM   dual)
SELECT function_result.column1
,      function_result.column2
,      function_result.column1 - function_result.column2
,      bigtable.normal_column
FROM   bigtable

Is there a way to force MySQL execution order?

Perhaps you need to use STRAIGHT_JOIN.

http://dev.mysql.com/doc/refman/5.0/en/join.html

STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order.

mysql force index for subquery

Your "subquery" b is a derived table. A derived table is (basically) a completely new table. This is what you intended, you wanted to create a small temporary table b. But this new table has no access to the original indexes, as it is a new table. This is why you get the error message.

Imagine how MySQL would need to use the category index. That index itself didn't change, so it will still contain entries for all rows of the original table. So you would use this index to find records with that category_id in the whole table (as that is what this index does), and then somehow need to check if that entry is in your derived table. Since a derived table can consist of virtually anything, even without any reference to a base table, these is no generic way to do this "somehow".

In your case, this could be done by reevaluating the condition where books.name='some pets 1', but this would mean you lose any advantage you might get from using the idx_books_name index and your derived table in the first place, so it would be the same as

LEFT JOIN books b ON b.category_id = cate.id AND b.name='some pets 1');

which would, with an an index books(category_id, name), be using indexes as you intended.

Using the derived table the way you did is basically already an optimization that wants to prevent MySQL from doing the join in the usual way (although MySQL would be allowed to merge your condition back into the outer query). You'll do this if you have special circumstances, e.g. if there is only a very small percentage of rows that match this condition or if you don't have a useful index on category_id.

MySQL might decide to generate a new index for your derived table on category_id. You (currently) cannot enforce generating those indexes by optimizer hints, and it is worth noting that indexes on small tables may not be as useful as you might think. Indexes scale logarithmically, so while the effect on large tables is huge, for small tables the overhead of generating such an index might be larger than the benefit.

Keep the result of a correlated sub-query

How can I force mysql to keep the value in sb1 and sb2 and not to run the query each time?

Convert your correlated queries to JOIN. Formally (ignoring ambiguities) it will be

Select 
  table_10.a AS sb1,
  table_11.a AS sb2,
  table_12.a AS sb3
FROM my_table 
CROSS JOIN table_10
INNER JOIN table_11 ON a=sb1
INNER JOIN table_12 ON a=sb2
WHERE 1
LIMIT 1

PS. LIMIT without ORDER BY makes no sense. Both in original code and provided one.

PPS. Specify table alias for EACH column name.

Solution to subquery returns more than 1 row error

= can be used when the subquery returns only 1 value.

When subquery returns more than 1 value, you will have to use IN:

select * 
from table
where id IN (multiple row query);

For example:

SELECT *
FROM Students
WHERE Marks = (SELECT MAX(Marks) FROM Students)   --Subquery returns only 1 value

SELECT *
FROM Students
WHERE Marks IN 
      (SELECT Marks 
       FROM Students 
       ORDER BY Marks DESC
       LIMIT 10)                       --Subquery returns 10 values

---

Related Topics