If I do a query such as: SELECT * from foo where fooid = 10 or fooid = 20 or fooid = 03 ...
with a total of around 1900 "or fooid =" parts on a given table with 500k rows, it takes about four times longer than doing 1900 separate queries in the form: SELECT * from foo where fooid = 10 fooid is the primary key. My real goal is to do updates in a similar way, they have the same issue. And my real goal is actually doing the same thing where instead of "fooid=10" I have a multicolumn primary key and use "(keycol1=10 and keycol2=2 and ...)" My examples here are the simplest case I can find that exhibit the issue. Explain plan on the select shows: table type possible_keys key key_len ref rows Extra adsummary range PRIMARY PRIMARY 31 NULL 1915 Using where so it is doing a range scan in some manner. Given that the rows I'm updating will normally be cached in memory, is there anything I can do to force it to just do repeated index lookups like the individual queries are doing? The reason I don't want to use individual queries is because my performance is then bottlenecked by latency, and when trying to update thousands of rows a second that doesn't work out. Just opening multiple connections and doing them in parallel is a problem because of where the updates are coming from and the fact that they have to be done inside a transaction so other connections would just end up blocked anyway. Currently running 4.0.15a. I'm trying to avoid going to 4.1 at this point, but it does look like the INSERT ... ON DUPLICATE KEY UPDATE ... syntax may do individual index lookups the way I want... I haven't verified that yet though. Any suggestions are greatly appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
