(I don't like top-posting but we are already in that pattern...) This looks good, except for the "Using temporary; Using filesort" (http://dev.mysql.com/doc/mysql/en/explain.html)
>From http://dev.mysql.com/doc/mysql/en/order-by-optimization.html >>>>>>>>>>> If you want to increase ORDER BY speed, first see whether you can get MySQL to use indexes rather than an extra sorting phase. If this is not possible, you can try the following strategies: * Increase the size of the sort_buffer_size variable. * Increase the size of the read_rnd_buffer_size variable. * Change tmpdir to point to a dedicated filesystem with lots of empty space. If you use MySQL 4.1 or later, this option accepts several paths that are used in round-robin fashion. Paths should be separated by colon characters (':') on Unix and semicolon characters (';') on Windows, NetWare, and OS/2. You can use this feature to spread the load across several directories. Note: The paths should be for directories in filesystems that are located on different physical disks, not different partitions of the same disk. <<<<<<<<<<<<<< I would try altering Batch.Index_1 to be (QAID,QEID) and try another explain. I suggest this to try to eliminate the TYPE: ALL for the QAErrors table in your explain output. Even with what I see here the EXPLAIN says you have an estimated 692 * 2 * 9871 = 13661464 rows to examine. That's not much for MySQL to deal with so I don't think the volume of your data is not going to be much of an issue How powerful is your server? Important factors are CPU type, RAM capacity, memory bus speed, operating system, and the HDD specs like rotational speed, RAID configuration, and connection type (IDE, SCSI, etc.). Shawn Green Database Administrator Unimin Corporation - Spruce Pine Scott Hamm <[EMAIL PROTECTED]> wrote on 07/27/2005 11:03:34 AM: > mysql> EXPLAIN > -> SELECT * > -> FROM QA > -> LEFT JOIN Batch > -> ON Batch.QAID=QA.ID > -> LEFT JOIN QAErrors > -> ON QAErrors.ID=Batch.QEID > -> WHERE QA.ID <http://QA.ID> BETWEEN 106805 AND 107179 > -> ORDER BY QA.ID <http://QA.ID>; > +----+-------------+----------+-------+---------------+--------- > +---------+------------------+------ > +----------------------------------------------+ > | id | select_type | table | type | possible_keys | key | key_len | ref | > rows | Extra | > +----+-------------+----------+-------+---------------+--------- > +---------+------------------+------ > +----------------------------------------------+ > | 1 | SIMPLE | QA | range | PRIMARY | PRIMARY | 4 | NULL | 692 | Using > where; Using temporary; Using filesort | > | 1 | SIMPLE | Batch | ref | Index_1 | Index_1 | 5 | qcdata_dbo.QA.ID | 2 | > | > | 1 | SIMPLE | QAErrors | ALL | NULL | NULL | NULL | NULL | 9871 | | > +----+-------------+----------+-------+---------------+--------- > +---------+------------------+------ > +----------------------------------------------+ > 3 rows in set (0.00 sec) > > On 7/27/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > > > > > > > Scott Hamm <[EMAIL PROTECTED]> wrote on 07/27/2005 07:48:56 AM: > > > > > 'Batch', 'CREATE TABLE `batch` ( > > > `QAID` int(10) default NULL, > > > `Order` varchar(9) default NULL, > > > `Errors` tinyint(1) NOT NULL, > > > `Comments` varchar(255) default NULL, > > > `QEID` int(10) default NULL, > > > KEY `Index_1` (`QAID`) > > > ) ENGINE=InnoDB DEFAULT CHARSET=latin1' > > > > > > 'QAErrors', 'CREATE TABLE `qaerrors` ( > > > `QAID` int(10) NOT NULL, > > > `ErrorTypeID` int(10) NOT NULL, > > > `Order` varchar(9) NOT NULL, > > > `ID` int(10) NOT NULL, > > > PRIMARY KEY (`QAID`,`ErrorTypeID`,`Order`) > > > ) ENGINE=InnoDB DEFAULT CHARSET=latin1' > > > > > > 'QA', 'CREATE TABLE `qa` ( > > > `ID` int(10) NOT NULL auto_increment, > > > `LocationID` int(10) default NULL, > > > `OperatorID` int(10) default NULL, > > > `QAID` int(10) default NULL, > > > `NTID` varchar(10) default NULL, > > > `BrandID` int(10) default NULL, > > > `OrdersReviewed` smallint(5) default NULL, > > > `CorrectOrders` smallint(5) default NULL, > > > `Batch` varchar(10) default NULL, > > > `KeyDate` datetime default NULL, > > > `ReceiveDate` datetime default NULL, > > > `Training` tinyint(1) NOT NULL, > > > PRIMARY KEY (`ID`) > > > ) ENGINE=InnoDB DEFAULT CHARSET=latin1' > > > > > > On 7/25/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > > > > > > > > > > > > > > > Scott Hamm <[EMAIL PROTECTED]> wrote on 07/21/2005 09:39:48 AM: > > > > > > > > > I'm now trying to learn engines in MySQL. When I migrated from M$ > > SQL to > > > > > > > > > MySQL to learn the migration process and executed the following: > > > > > > > > > > SELECT > > > > > * > > > > > FROM > > > > > QA > > > > > LEFT JOIN > > > > > Batch > > > > > ON > > > > > Batch.QAID=QA.ID > > > > > LEFT JOIN > > > > > QAErrors > > > > > ON > > > > > QAErrors.ID=Batch.QEID > > > > > WHERE > > > > > QA.ID <http://QA.ID> <http://QA.ID> <http://QA.ID> > > > > > BETWEEN > > > > > '106805' > > > > > AND > > > > > '107179' > > > > > ORDER BY > > > > > QA.ID <http://QA.ID> <http://QA.ID> <http://QA.ID>; > > > > > > > > > > M$ SQL executed and brought up result in 2 seconds > > > > > where MySQL took 801 seconds and where > > > > > Batch datalength is around 18.5 MB, > > > > > QAErrors is around 464KB and > > > > > QA is around 3.5MB > > > > > > > > > > Which engine should I use and should I apply to all these tables or? > > > > > > > > > > Batch/QAErrors/QA is most frequent used in database. > > > > > -- > > > > > Power to people, Linux is here. > > > > > > > > Engine choices will only help you deal with concurrency issues. MyISAM > > > > > > uses table locking while InnoDB uses row-level locking and supports > > > > transactions. What it sounds like is an INDEXING issue. If you used > > the MS > > > > SQL technique of creating several single-column indexes (to duplicate > > an > > > > existing table) you will not get optimal performance from MySQL. > > > You need to > > > > determine the best indexes to cover the majority of your query cases. > > > > > > > > If you could, please post the results of SHOW CREATE TABLE for these > > > > tables: Batch, QAErrors, and QA so that we can review your indexes. > > > > > > > > Shawn Green > > > > Database Administrator > > > > Unimin Corporation - Spruce Pine > > > > > > > > > > > > > > > > > -- > > > Power to people, Linux is here. > > > > Here is your original query (slightly reformatted): > > > > SELECT * FROM QA > > LEFT JOIN Batch > > ON Batch.QAID=QA.ID > > LEFT JOIN QAErrors > > ON QAErrors.ID=Batch.QEID > > WHERE QA.ID <http://QA.ID> BETWEEN '106805' AND '107179' > > ORDER BY QA.ID <http://QA.ID>; > > > > What I was looking for were indexes on QA.ID <http://QA.ID>, Batch.QAID, > > Batch.QEID, and QAErrors.ID. Almost all of them were what I would want > > them to be for a query like this. > > > > The Batch table is joined with twice, once with QA the second with > > QAErrors. There is only one index on the Batch table so the optimizer must > > choose between using or not using the index on QAID. Since that is not a > > PRIMARY KEY or UNIQUE KEY, you may try adding QEID to Batch.Index_1 as a > > secondary column to see if that helps. It may also help to create a second > > index on either QEID (alone) or the combination of QEID,QAID. As Imentioned > > above, all of the other useful index combinations were present. Liberally > > use EXPLAIN to test any differences in index usage (after adding, deleting, > > or changing an index) without the need to wait for actual results. > > > > Now, with that bit of operational theory out of the way. I believe the > > speed killer in your statement is here: > > > > WHERE QA.ID <http://QA.ID> BETWEEN '106805' AND '107179' > > > > (I can't find a reference in the manual to explain why I think this but I > > do remember reading this somewhere) What I think you asked the engine to do > > was to convert all of the numeric values of the ID column of the > QA table to > > string values and compare them to the two string values you gave it. That > > one transformation would preclude the engine from using the PRIMARY KEY on > > the QA table, forcing a full table scan. What you probably should have said > > was: > > > > WHERE QA.ID <http://QA.ID> BETWEEN 106805 AND 107179 > > > > This statement asks the engine to compare numeric values (the data in > > QA.ID <http://QA.ID>) to numbers (the two values in the BETWEEN clause). > > Since you have an index on that column, this will be a ranged index lookup > > and will be quite fast.http://dev.mysql.com/doc/mysql/en/number- > > syntax.html) > > > > Try it again without the quotes. If you are still having problems, post > > the EXPLAIN of the query so we can see what the optimizer thinks > it's doing. > > I agree with you, this should not take any longer than 1-2 seconds to > > compute (even on a weak machine). > > > > Shawn Green > > Database Administrator > > Unimin Corporation - Spruce Pine > > >
