On May 25, 2006, at 4:11 PM, Tom Lane wrote:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> "Merlin Moncure" <mmoncure@gmail.com> 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