List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:March 27 2004 7:59pm
Subject:Re: 6.5 seconds seems slow for the following search?
View as plain text  
In the last episode (Mar 26), Kyle Renfro said:
> I am testing MySQL as a possible replacement for our proprietary db
> server for several large databases.  I really want MySQL but I am
> getting what seems like slow search times.
> Does 6.5 seconds seem slow/typical/fast for the following search?
> SELECT main.ownername FROM main, rolledplate WHERE rolledplate.platenum
> LIKE '3^6SP%' AND main.recid = rolledplate.recid;
> The 'main' table has 21+ million records.  The 'rolledplate' table has
> 144+ million records.
> The EXPLAIN gives pretty optimal results and I have tried the select
> syntax several different ways. The recid field is the PK in the main
> table.  In both tables RECID is an unsigned int with a 1:M
> relationship between main and rolledplate.  The tables are MyISAM
> with a fixed row format.

What's the EXPLAIN look like, and how many records do you get from the
above query: total, and average per rollplate.recid (i.e what's M)?  6
seconds could be high if you are returning only a couple of records,
but if you're returning over 600 records, than it's reasonable
(assuming slow disks at 100 seeks/sec).  Since you're only fetching one
field, creating multicolumn indexes on rolledplate (platenum, recid)
and main (recid, ownername) may let you avoid table lookups altogether.

	Dan Nelson
6.5 seconds seems slow for the following search?Kyle Renfro27 Mar
  • RE: 6.5 seconds seems slow for the following search?Peter Lovatt27 Mar
  • Re: 6.5 seconds seems slow for the following search?Dan Nelson27 Mar
    • Re: 6.5 seconds seems slow for the following search?Kyle Renfro28 Mar