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.
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.
thanks!
