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> > BETWEEN > '106805' > AND > '107179' > ORDER BY > 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
