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 <ave...@yahoo-inc.com> 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=vegiv...@tuxera.be > > -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
