IF Condition Perform Query, Else Perform Other Query

Question

It feels pretty straightforward in anything but MySQL.

Basically I need to switch what type of index I am using along with a few other conditions based on how many results a particular term returns.

Something to the effect of:

IF (SELECT COUNT(*) FROM table WHERE term LIKE "term") > 4000
   EXECUTE QUERY A
ELSE
   EXECUTE QUERY B

Is this possible in a MySQL statement?

EDIT:

Query A:

SELECT id 
FROM table_a
FORCE INDEX(id)
JOIN table_b ON table_a.id = table_b.id
WHERE term LIKE "term"
ORDER BY date
LIMIT 100;

Query B:

SELECT id 
FROM table_a
FORCE INDEX(term)
JOIN table_b ON table_a.id = table_b.id
WHERE term LIKE "term"
GROUP BY term    # These lines would be included for a few conditions not mentioned above.. but are necessary
HAVING COUNT = 1 # same...  
ORDER BY date
LIMIT 100;

The reason for the query switch is I get dramatically different result times based on the popularity of the "term".

What are `QUERY A` and `QUERY B` specifically? SQL typically allows this situation to be handled with a creative `WHERE` clause, but we can't say unless we know if A & B are sufficiently related. — Michael Berkowski, Jun 19 '13 at 19:25
...Otherwise, MySQL only permits the use of flow control logic in functions, triggers, & stored procedures -- not in regular queries. — Michael Berkowski, Jun 19 '13 at 19:26
In theory - yes (with a help of `CASE` statement and joins), but it depends on whether number of fields and records in both queries match. You should post those two along with test data, so that we can answer. — David Jashi, Jun 19 '13 at 19:27
you could execute both query A and B and then use a CASE for the count. if that doesn't work, look for stored procedures — juuga, Jun 19 '13 at 19:35
@juuga, the reason for the switch is one query could take either .03 seconds or 17 seconds depending on the index used and whether I add the GROUP BY statement. I feel like performing them both would be hard on the system. — Howard Zoopaloopa, Jun 19 '13 at 19:38

James Green · Accepted Answer · 2013-06-19T21:02:56.283

EDIT: What I said below about requiring a stored procedure is NOT TRUE. Try this:

SELECT CASE WHEN ( (SELECT COUNT(*) FROM table WHERE term LIKE "term") > 4000 )
    THEN <QUERY A>
    ELSE <QUERY B>
END

This is, indeed, a case expression, and it works fine outside a stored proc :-)

For instance:

mysql> SELECT CASE WHEN ( 5 > 4 ) THEN ( SELECT 'foo' ) ELSE ( SELECT 'bar' ) END;
+---------------------------------------------------------------------+
| CASE WHEN ( 5 > 4 ) THEN ( SELECT 'foo' ) ELSE ( SELECT 'bar' ) END |
+---------------------------------------------------------------------+
| foo                                                                 |
+---------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT CASE WHEN ( 3 > 4 ) THEN ( SELECT 'foo' ) ELSE ( SELECT 'bar' ) END;
+---------------------------------------------------------------------+
| CASE WHEN ( 3 > 4 ) THEN ( SELECT 'foo' ) ELSE ( SELECT 'bar' ) END |
+---------------------------------------------------------------------+
| bar                                                                 |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

Old answer below for historical interest, since it already gather upvotes:

You can use the below I think, but only inside a stored procedure:

CASE (SELECT COUNT(*) FROM table WHERE term LIKE "term") > 4000
    WHEN 1 THEN <QUERY A>
    ELSE <QUERY B>
END CASE

This is a CASE statement, as distinct from a CASE expression... https://dev.mysql.com/doc/refman/5.0/en/case.html has more gory details.

Actually, I suspect in general if you want to execute different queries conditionally, you're going to need to look toward stored procedures -- I could be wrong, but that's my gut feeling at this point. If you can do it, it'll probably be with CASE expressions!

One last edit: in any real world example, I'd probably do the conditional bit in my application, and just hand off to SQL (or to an ORM which would generate my SQL) once I'd decided what to search for.

You probably should also mention that this works only when the queries return only 1 row. Otherwise you'll get an error stating that the subquery returns more than 1 row. — fancyPants, Feb 01 '18 at 14:20

score 4 · Answer 2 · answered Jun 19 '13 at 19:49

4

Try:

select coalesce(i.id, t.id) id
from (SELECT COUNT(*) countterm FROM table WHERE term LIKE "term") c
left join
     (SELECT id, date
      FROM table_a
      FORCE INDEX(id)
      JOIN table_b ON table_a.id = table_b.id
      WHERE term LIKE "term") i on countterm > 4000
left join
     (SELECT id, date
      FROM table_a
      FORCE INDEX(term)
      JOIN table_b ON table_a.id = table_b.id
      WHERE term LIKE "term"
      GROUP BY term
      HAVING COUNT = 1) t on countterm <= 4000
ORDER BY coalesce(i.date, t.date)
LIMIT 100;

answered Jun 19 '13 at 19:49

2

I like this in that it gives the correct output, but it executes both queries and I'm sure OP wants to avoid that. – Bohemian Jun 19 '13 at 19:58
@Bohemian: Will it actually execute the sub-query if the `join ... on` (to the main query) condition is false? – Jun 19 '13 at 20:01
1

I would think so, because the ON condition is a mini where clause - a filter - for the joined rows, and the false-ness of the condition can not be optimized out (no way to know beforehand that it will be false). I could be wrong... – Bohemian Jun 19 '13 at 20:05
@MarkBannister, I had some success with the above answer, however, thank you greatly for taking the time to provide a solution. – Howard Zoopaloopa Jun 19 '13 at 21:23

IF Condition Perform Query, Else Perform Other Query

2 Answers2

Linked