Re: is it possible to make this faster?

On May 25, 2006, at 4:11 PM, Tom Lane wrote:
> Tom Lane  writes:
>> "Merlin Moncure"  writes:
>>> recent versions of mysql do much better, returning same set in <  
>>> 20ms.
>
>> Well, since they don't do MVCC they can answer this query from the
>> index without going to the heap at all.  But that still seems  
>> remarkably
>> fast for something that has to grovel through 300k index entries.
>
> Are you sure you measured that right?  I tried to duplicate this using
> mysql 5.0.21, and I see runtimes of 0.45 sec without an index and
> 0.15 sec with.  This compares to psql times around 0.175 sec.  Doesn't
> look to me like we're hurting all that badly, even without using the
> index.

Well, that would depend greatly on how wide the rows were, and I  
don't believe the OP ever mentioned that. If he's got a nice, fat  
varchar(1024) in that table, then it's not surprising that an index  
would help things.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461