[TriLUG] Re: [ORUG] OT: DBMS performance

[Steve Litt]

On Wednesday 04 April 2007 14:30, Steve Litt wrote:
> Hi all,
>
> From a MySQL or Postgres DBMS on a commodity machine (Athlon XP2600+ with
> 1.5MB RAM) that's not being used for anything else, running (Mandriva 2007)
> Linux, how fast should one be able to retrieve 5000 matching records out of
> 3.5 million total records? The matches match the field exactly (including
> capitalization).
>
> How long if the field being searched is indexed?
>
> How long if the field being searched is not indexed nor a primary key?
>
>
> Obviously the answers I'm looking for are very approximate. Would 5 seconds
> be good if it's indexed? If it's not indexed?
>
> How bout 1 second?
>
> Thanks
>
> SteveT

Hi all,

I just built a MySQL table using the data at hand, and here are the results:

Unindexed, or with the default indices, the query took between 2.2 and 2.3 
seconds to get 5200 matching rows out of 3.5 million. I was looking for a 
certain first and last name. When I indexed on last->first, the time dropped 
to between 0.01 and 0.02 seconds.

Thanks

SteveT