Re: Which Engine to use...

[Scott Hamm]

'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 (`CD_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1'

'title', 'CREATE TABLE `title` (
`ID` int(11) NOT NULL auto_increment,
`B_ID` int(11) NOT NULL,
`Title` varchar(255) NOT NULL,
`C_ID` int(11) NOT NULL,
`Track` varchar(255) NOT NULL,
PRIMARY KEY (`ID`),
KEY `FK_Band` (`B_ID`),
KEY `FK_CDType` (`C_ID`)
) ENGINE=MyISAM 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.