List:General Discussion« Previous MessageNext Message »
From:Kyle Renfro Date:March 27 2004 12:48am
Subject:6.5 seconds seems slow for the following search?
View as plain text  
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!

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