CODEWITHSUNDEEP
mysql
Howard Zoopaloopa
Howard Zoopaloopa

Reputation: 3822

IF Condition Perform Query, Else Perform Other Query

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".

Upvotes: 27

Views: 39580

Answers (2)

James Green
James Green

Reputation: 1753

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.

Upvotes: 23

user359040
user359040

Reputation:

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;

Upvotes: 4

Related Questions