Thanks to everyone who has helped and/or made suggestions so far. I'll try to provide some answers to your further queries and report back on some testing I've done.
Jeremy asked for explains of some of the problem queries: Here is a particularly troublesome one that gets ran quite a lot: mysql> SELECT InstNum FROM TBL_Transactions WHERE ((IndexStatus < '2' OR Scanned<>'Y') OR (MoneyStatus <> '1')) AND ((VoidStatus = 'N') AND (IndexType <> 'CP') AND (Year > '2001')) ORDER BY InstNum ASC LIMIT 1; +----------+ | InstNum | +----------+ | 03128665 | +----------+ 1 row in set (6.59 sec) mysql> explain SELECT InstNum FROM TBL_Transactions WHERE ((IndexStatus < '2' OR Scanned<>'Y') OR (MoneyStatus <> '1')) AND ((VoidStatus = 'N') AND (IndexType <> 'CP') AND (Year > '2001')) ORDER BY InstNum ASC LIMIT 1; +------------------+------+------------------------------------+-------- ----+---------+-------+--------+----------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +------------------+------+------------------------------------+-------- ----+---------+-------+--------+----------------------------+ | TBL_Transactions | ref | Year,VoidStatus,IndexStatus,Year_2 | VoidStatus | 2 | const | 150804 | where used; Using filesort | +------------------+------+------------------------------------+-------- ----+---------+-------+--------+----------------------------+ 1 row in set (0.00 sec) Thanks to Joseph Bueno for suggesting the 4.x query cache: I took the above query and on a test server running 4.0.13 I setup a 1MB query cache and tried it out. It took >6 seconds first time and 0.00 seconds on subsequent times. I'm assuming this cache is smart enough to re-perform the query if any data pertaining to it changes, yeah surely... So on often-executed queries where the data is very cachable this will help. After a few minutes of monitoring this one floats to the top of a mytop output screen as taking the longest to run: mysql> explain SELECT DISTINCT LastName, FirstName, PAName FROM TBL_AllNames WHERE PAName LIKE 'WHITE%' AND NameType<'2' ORDER BY LastName, FirstName; +--------------+-------+-----------------+--------+---------+------+---- ---+-----------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +--------------+-------+-----------------+--------+---------+------+---- ---+-----------------------------+ | TBL_AllNames | range | PAName,NameType | PAName | 81 | NULL | 41830 | where used; Using temporary | +--------------+-------+-----------------+--------+---------+------+---- ---+-----------------------------+ 1 row in set (0.00 sec) mysql> Running the actual query returned >4000 rows and took (58.20 sec) Here's some details of that table: mysql> describe TBL_AllNames; +---------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+-------------+------+-----+---------+-------+ | InstNum | varchar(8) | | PRI | | | | Year | varchar(4) | | PRI | | | | NameType | char(2) | | PRI | | | | NameClass | char(1) | YES | MUL | NULL | | | NameAP | char(1) | YES | | NULL | | | Ncount | int(11) | | PRI | 0 | | | LastName | varchar(80) | YES | MUL | NULL | | | FirstName | varchar(60) | YES | MUL | NULL | | | TypeofName | varchar(20) | YES | | NULL | | | PAName | varchar(80) | YES | MUL | NULL | | | SoundKeyFirst | varchar(12) | YES | MUL | NULL | | | SoundKeyLast | varchar(12) | YES | MUL | NULL | | | RecDate | varchar(8) | | MUL | | | | InstCode | varchar(10) | | MUL | | | | IndexType | varchar(4) | | | | | | XrefGroup | varchar(8) | | | | | +---------------+-------------+------+-----+---------+-------+ 16 rows in set (0.00 sec) mysql> select count(*) from TBL_AllNames; +----------+ | count(*) | +----------+ | 6164129 | +----------+ 1 row in set (50.17 sec) Thanks in advance! PS. I'm still very interested in *paying* MySQL to help analyze and suggest ways we can make the queries faster. Again though, I just want to point *soon* hardware upgrade purchases in the right direction and get that all settled down first. Opterons look nice but with a database size topping 29GB today I think enough ram to cache a sizable portion of it will be cost prohibitive. Could still be a possibility though... I'm still leaning towards a load-balanced setup with backend/real servers having either 15K SCSI drives RAID-0'ed or possibly SATA 10K drives for cost reasons. Again, thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
