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>
> > > BETWEEN
> > > '106805'
> > > AND
> > > '107179'
> > > ORDER BY
> > > 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 BETWEEN '106805' AND '107179'
ORDER BY QA.ID;
What I was looking for were indexes on 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 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 BETWEEN 106805 AND 107179
This statement asks the engine to compare numeric values (the data in
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
