Hi, ----- Original Message ----- From: "Marc Slemko" Sent: Sunday, October 05, 2003 2:27 PM Subject: Re: slow performance with large "or" list in where
> On Sun, 5 Oct 2003, Santino wrote: > > > Have You test in operator? > > > > select * from table where id in (10,20,30,50,60,90, ....) > > Yes, IN does perform at the levels I want and works for the simplified > example I gave Yeah, I was gonna suggest IN too. The reason it's faster with so many values I think is because the query is much shorter bytes-wise and therefore there's less for MySQL to parse. And BTW, 3.23's parser seems to be REALLY slow compared to 4's. :-) > but doesn't work for the generalized case I need, > which is matching individual rows in a table with a multicolumn > primary key which is why I can't use it. Ah, I see. So you're gonna have it like this?: WHERE (col1=123 AND col2='foo') OR (col1=456 AND col2='bar') OR ... > Well, I could use it but > it would require creating an extra column that is a string with > all the component columns of the primary key combined or a binary > field that I pack myself then have a unique index on that... but > I'd really like to avoid that since this table will have hundreds > of thousands of rows added a day and has half a dozen columns that > form the primary key. That might actually be something to look at. You have 6 columns to form the PRIMARY KEY?! Remember, the PRI KEY should really be as short as possible (bytes-wise). Maybe you could make the PRIMARY KEY be a value derived from your 6 columns. And then you could make just a unique index on those columns if needed. > Interestingly, the "explain" output is exactly the same for the in and > the fooid=10 or fooid=20 or ... case. Right. They're optimized exactly the same way. The speed difference comes from the fact that IN is shorter and easier to parse than so many ORs. Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
