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
	dnelson@stripped
Thread
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