'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.
