[TriLUG] OT: DBMS performance

[Cristóbal Palmer]

For a database systems class we had to get a text match in < 1sec for
a ~1 million record MySQL database with MyISAM tables. It wasn't
terribly hard, but it did require carefully looking at the schema and
building the right index. The default indices definitely didn't cut
it. I started here:

http://dev.mysql.com/doc/refman/5.1/en/optimization.html

So yeah... basically you can do it with the right index, but it
depends on schema, table engine, etc. There are other questions to
answer, after all, eg. how often do I have to rebuild the index?

Cheers,
CMP

On 4/4/07, Steve Litt <slitt at troubleshooters.com> 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
>
> Steve Litt
> Author: Universal Troubleshooting Process books and courseware
> http://www.troubleshooters.com/
> --
> TriLUG mailing list        : http://www.trilug.org/mailman/listinfo/trilug
> TriLUG Organizational FAQ  : http://trilug.org/faq/
> TriLUG Member Services FAQ : http://members.trilug.org/services_faq/
>


-- 
Cristóbal M. Palmer
http://claimid.com/cristobalpalmer
TriLUG Vice Chair