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