The first query:

SELECT ProductID 
FROM Products 
WHERE ProductActive = 3 
    OR (ProductChecked = 2 AND ProductActive NOT IN(2, 10))
LIMIT 0, 100

Would not be able to take advantage of index, unless ProductActive_ProductChecked is an composite index in the exact order.

Why?

Because, OR query over an individual index could only filter one of the two clauses. For the second clause, you need to scan the db again and take a "UNION" since its an OR clause. MySQL cannot use two indexes at the same time (in most of the cases).

Now why are the queries performing differently?

Let's term any clause on ProductActive column as "A" and a clause on ProductChecked as "B"

The first query can be represented as => SELECT <> FROM <> WHERE A U (A ∩ B). Note that in this query, there's an intersection with B. Which can be done by finding relevant rows for A in index, then filtering them on the basis of B . Then do a union with A which is again, available from index. Operating term here being there's UNION with same column and INTERSECTION with different column. Hence, one can scan index for A and that would work. [No table scan required.]

Now why the second query does not perform?

The query can be represented by => SELECT <> FROM <> WHERE (A U B) ∩ A . Note that there's a UNION with B. Which has to be done by first figuring out relevant rows for A then scanning the table to figure out rows for B and then merging them. Post that take an intersection with A. Operating term here being there's UNION with DIFFERENT column and INTERSECTION with same column. Hence, one cannot just scan index for A and had to do a table scan.

Soln:

Try doing SQL Performance UNION vs OR optimisation.