List:General Discussion« Previous MessageNext Message »
From:Kyle Renfro Date:March 28 2004 3:48pm
Subject:Re: 6.5 seconds seems slow for the following search?
View as plain text  
On Sat, 2004-03-27 at 13:59, Dan Nelson wrote:
> 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

Here is EXPLAIN:

+----+-------------+-------------+--------+-----------------------+-------------+---------+-----------------------+------+-------------+
| id | select_type | table       | type   | possible_keys         |
key         | key_len | ref                   | rows | Extra       |
+----+-------------+-------------+--------+-----------------------+-------------+---------+-----------------------+------+-------------+
|  1 | SIMPLE      | rolledplate | range  | IDX_RECID,IDX_PLATENO |
IDX_PLATENO |       8 | NULL                  |  131 | Using where |
|  1 | SIMPLE      | main        | eq_ref | PRIMARY,IDX_RECID     |
PRIMARY     |       4 | mvr.rolledplate.RECID |    1 |             |
+----+-------------+-------------+--------+-----------------------+-------------+---------+-----------------------+------+-------------+
2 rows in set (0.00 sec)


or more readable version:
type    table        type   key         klen  ref   rows  Extra
----------------------------------------------------------------------
simple  rolledplate  range  IDX_PLATENO 8     NULL  131   using where   
simple  main         eq_ref PRIMARY     4     RECID 1


The query is returning 164 records. M is about 7. The multi-column index
is a good idea but in general I will need several more fields.  I just
used one for this example.



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