Big tables, a join and a LIKE on middleweight hardware - looks about right
> -----Original Message-----
> From: Kyle Renfro [mailto:krenfro@stripped]
> Sent: 27 March 2004 00:48
> To: mysql@stripped
> Subject: 6.5 seconds seems slow for the following search?
> 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
> System Specs:
> P4 2.8 Ghz
> 1 Gb RAM
> Serial ATA disks (data/indexes split on 2 disks)
> MySQL 4.1.1
> The my.cnf is a modified my-huge.cnf with info gleened from web/list.
> Our in-house db beats the pants off of this (but on better hardware).
> I'm not sure if there is some optimization I have missed, but I have
> tried everything I can think of. Any suggestions would be greatly
> appreciated. Server settings or anything else available on request.
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1