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