From: Johan De Meersman Date: April 22 2010 11:24am Subject: Re: Auto Increment in InnoDB List-Archive: http://lists.mysql.com/mysql/221379 Message-Id: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary=001636ef0ceabd23690484d18d5a --001636ef0ceabd23690484d18d5a Content-Type: text/plain; charset=ISO-8859-1 The count happens after the where on an index - it should just count the appropriate index rows without looking at the values. Worth benchmarking on your dataset, though. On Thu, Apr 22, 2010 at 1:22 PM, Aveek Misra wrote: > How can count(*) in an InnoDB table be faster than MAX() considering that > the former needs to do a table scan and the latter can use an index if > correctly used? My code starts the sequence from 1. > > > Thanks > Aveek > > Johan De Meersman wrote: > >> Kudos for managing to drag up such an obscure piece of functionality :-) I >> can see where it would be useful, though. >> >> As to your question, though: given that that page indicates that it will >> reuse deleted sequence numbers, I think your best bet would be select @id := >> count(*)+1 from table where cluster='clusterA' AND file='fileA' ; - should >> be slightly faster than a max(), I think. That in a trigger on your table >> should emulate the behaviour pretty closely. >> >> Am I mistaken, or does your code try to start from sequence 0 ? >> >> -- >> Bier met grenadyn >> Is als mosterd by den wyn >> Sy die't drinkt, is eene kwezel >> Hy die't drinkt, is ras een ezel >> > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=vegivamp@stripped > > -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel --001636ef0ceabd23690484d18d5a--