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
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
