Hi,
Don't know if this already came up earlier but I have an idea for improvement.
If I have a query like:
SELECT * FROM (
SELECT
CASE
WHEN field='value1' THEN 1
WHEN field='value2' THEN 2
END AS category
FROM table1
) AS foo
WHERE category=1
doesn't use the index on field, while technically it could do that.
Is it hard to implement drilling down the constant in the WHERE to within the CASE?
This is especially convenient with views (inner SELECT) where the category is some complex list of possibilities and you want to filter (outer SELECT) on specific categories
I know a different solution could be creating an index on that CASE but (especially if you're experimenting a bit) can be quite cumbersome to synchronize that with the actual query.
Is this something that could be put on some wishlist? If so where are the most looked at ones?
Paul
P.S. In replies please use reply to all...