For the first query below--if you really run it often enough to mess with indexes, and it really has a limit 1 or a small limit--an index on (VoidStatus, InstNum) ought to avoid having MySQL create a big temporary table and then sort it.
In addition, you could add to the index any of columns in the other AND clauses, if doing so would allow a lot of records to be skipped over during the index scan, rather than read in their entirety. > From: "Wendell Dingus" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Subject: RE: MySQL/INNODB speed on large databases > Date: Wed, 2 Jul 2003 11:51:05 -0400 > 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]
