Re: Which Engine to use...

Created QAID & QEID indices, now select time went down to .2787 seconds. Sorry about top-posting, it's a nice feature in gmail where it hides the text that was previously posted. Here is the updated explain result. mysql> EXPLAIN -> SELECT * -> FROM QA -> LEFT JOIN Batch -> ON Batch.QAID=QA.I

Re: Which Engine to use...

(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 spe

Re: Which Engine to use...

mysql> EXPLAIN -> 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 ; ++-+--+---+---+-+-+---

Re: Which Engine to use...

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`) > ) E

Re: Which Engine to use...

'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` ( `QAI

Re: Which Engine to use...

OOPS!!! Wrong tables On 7/27/05, Scott Hamm <[EMAIL PROTECTED]> wrote: > > 'Bands', 'CREATE TABLE `bands` ( > `BandID` int(11) NOT NULL auto_increment, > `Band_Name` varchar(255) default NULL, > PRIMARY KEY (`BandID`) > ) ENGINE=MyISAM DEFAULT CHARSET=latin1' > > 'cd_type', 'CREATE TABLE `cd_typ

Re: Which Engine to use...

'Bands', 'CREATE TABLE `bands` ( `BandID` int(11) NOT NULL auto_increment, `Band_Name` varchar(255) default NULL, PRIMARY KEY (`BandID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1' 'cd_type', 'CREATE TABLE `cd_type` ( `CD_ID` int(11) NOT NULL auto_increment, `Type` varchar(255) NOT NULL, PRIMARY KEY (

Re: Which Engine to use...

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

Re: Which Engine to use...

it is because of the dollar sign that it works so much quicker. Scott Hamm wrote: 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 QAErr

Re: Which Engine to use...

At 09:39 AM 7/21/05, Scott Hamm wrote: 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 Did you create an index? -- MySQL General Mailing List For list archives: http

Which Engine to use...

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 BETWEEN '106805' AN