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 I mentioned > 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 > -- Power to people, Linux is here.
