Short-circuit Logic Evaluation Operators

Are there any short-circuit logic operators (specifically short-circuit AND and short-circuit OR) that I can use in a WHERE clause in MySQL 5.5? If there isn't, what are the altern

Solution 1:

Keep in mind that a query does not execute imperatively. The query you wrote may run on multiple threads, and therefore a short-circuit operator in the where clause would not result in only one result.

Instead, use the LIMIT clause to only return the first row.

SELECT*FROM quantitycache
WHERE bookstore_id =1OR city_id =1OR country_id =1ORDERBY bookstore_id ISNULLASC,
         city_id ISNULLASC,
         country_id ISNULLASC
LIMIT 1;

---

To get the best match for all books in a result set, save the results to a temp table, find the best result, then return interesting fields.

CREATE TEMPORARY TABLE results (id int, book_id int, match_rank int);

INSERTINTO results (id, book_id, match_rank)
SELECT id, book_id, 
    -- this assumes that lower numbers are betterCASEWHEN Bookstore_ID isnotnullthen1WHEN City_ID isnotnullthen2ELSE3ENDas match_rank
FROM quantitycache
WHERE bookstore_id =1OR city_id =1OR country_id =1;

Select*from (
    select book_id, MIN(match_rank) as best_rank 
    from results 
    groupby book_id
) as r
innerjoin results as rid 
    on r.book_id = rid.book_id 
    and rid.match_rank = r.best_rank
innerjoin quantitycache as q on q.id = rid.id;

DROPTABLE results;