On Nov 12, 2007 9:22 AM, Afan Pasalic <[EMAIL PROTECTED]> wrote: > If you have to deal with it again consider using a bunch of unions > instead of the 'IN'. Not prettiest thing, but it should fix your > performance issue. > Could you please give me more details about your statement that mysql deals > not so well with IN, and it's better to use UNIONS? > I just tested two queries, using IN and UNIONS (using MySQL Browser) and > I'm getting almost identical download/fetched time? Someone call me on it if any of the following is incorrect: Prior to Mysql 5.0, queries using OR or IN would not use an index unless the left most portion of the index was the portion using the OR/IN. Mysql 5.0 and later can use a merge index to replicate a UNION (among other) optimization.
Mysql treats each query in a union as a completely separate query. If the reason the query was not using an index was because of the IN or OR a unions might allow mysql to use the index. Look at the explain of the union query. Is it using an index on whichever column you are trying to optimize? I am going to guess it is not, or even if it is the query is not very efficient otherwise. What version of mysql are you on? What % of the entries would fulfill by the IN? Mysql used to choose to do a table scan if an index would not eliminate a significant (like 70%+) of the rows. The algorithm has since changed, but that would be a good rule of thumb. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
