CODEWITHSUNDEEP
mysqloptimization
Kibbee
Kibbee

Reputation: 66142

Will MySQL Optimize the lower half of a union query

Is there any way (or is it automatic) for MySQL to optimize the bottom half of a UNION ALL query if I impose a LIMIT statement on the number of results which should be returned. For instance if I have a query as such:

SELECT ID FROM MyTable WHERE Match (NameColumn) Against ('seachterm')
UNION ALL
SELECT ID FROM MyTable WHERE (DescriptionColumn) Against ('seachterm')
LIMIT 10;

If I run this query and the first query for an match in the name returns 15 results, there should be no reason to even run the second query, as I already have enough results. Does MySQL Optimize away the second query, or is there a way to tell it to do so? Based on performing the query with EXPLAIN EXTENDED in front, it doesn't appear as though the second part is optimized away.

Upvotes: 1

Views: 209

Answers (2)

Kibbee
Kibbee

Reputation: 66142

After a bit of testing, it seems that even though it does show the second query in the "EXPLAIN" output, it doesn't run the second query if necessary. I performed the following query:

SELECT ID FROM MyTable WHERE Match (NameColumn) Against ('seachterm')
UNION ALL
SELECT ID FROM MyTable WHERE (DescriptionColumn) Against ('seachterm')
UNION ALL
SELECT ID FROM MyTable WHERE DescriptionColumn LIKE '%seachterm%'
LIMIT 10;

Now, in a table with 3 million rows, the last query should take a long time (and I tested it alone, and it does take a long time), but when added as part of a union, it doesn't slow down the query at all, because it isn't even necessary to run it, because we get enough rows from the first two statements. If I increase the Limit to a larger number such that I don't get enough results from the first two queries, the third query comes into play and starts slowing down the query significantly.

Upvotes: 1

Ronnis
Ronnis

Reputation: 12843

With your real query, is it possible to do something like this? (untested)

select id
      ,case when nameColumn        like '%Product X%' then 1
            when DescriptionColumn like '%Product X%' then 2
        end as priority
  from MyTable 
 where nameColumn        like '%Product X%'
    or DescriptionColumn like '%Product X%'
 order by priority
 limit 10;

Edit
I read your question again and realized i missunderstood the question. I thought you where asking how to prioritize name matches over description matches. I'm leaving the code in, in case it comes out on top performance wise anyway.

Upvotes: 0

Related Questions