Might instead want to look at where fooid in (xx, xx, xx, xx)
On Sat, 4 Oct 2003, Marc Slemko wrote: > 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] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
