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.
