From: Michael Widenius
Date: April 10 1999 7:22pm
Subject: Re: speed prob with solaris 2.5.1 and MySQL 3.22.19
List-Archive: http://lists.mysql.com/mysql/1638
Message-Id: <14095.42140.428492.900187@monty.pp.sci.fi>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

>>>>> "Philippe" == Philippe PETIT <annonceur@stripped> writes:

Philippe> I have created this table :
Philippe> CREATE TABLE ZDArticle ( IdArticle INT NOT NULL, TitreArticle CHAR(255),
Philippe> RubriqueDocument INT NOT NULL, DocumentArticle INT NOT NULL, IdTypeInfo INT
Philippe> NOT NULL, ModificationArticle REAL NOT NULL DEFAULT 0, ResumeArticle
Philippe> LONGBLOB, IdMotPrinc INT NOT NULL DEFAULT 0, IdMot LONGBLOB, RubriqueArticle
Philippe> LONGBLOB, SurTitreArticle LONGBLOB, ChampArticle LONGBLOB, RefArticle
Philippe> LONGBLOB, PictArticle LONGBLOB, KEY (RubriqueDocument), KEY (IdMotPrinc),
Philippe> KEY (ModificationArticle), KEY (IdTypeInfo), KEY (DocumentArticle), PRIMARY
Philippe> KEY (IdArticle))

Philippe> and my SQL requests :

Philippe> SELECT * FROM ZDArticle WHERE IdTypeInfo=1 ORDER BY ModificationArticle DESC
Philippe> SELECT * FROM ZDArticle WHERE ChampArticle LIKE '%c1:1:%' AND IdTypeInfo=1
Philippe> ORDER BY ModificationArticle DESC
Philippe> SELECT * FROM ZDArticle WHERE IdTypeInfo=10 AND RubriqueArticle LIKE '%b2:%'
Philippe> ORDER BY ModificationArticle DESC

Hi!

If you are using LIKE with '%keyword%', MySQL must scan the whole
table to solve the query;  This will always be slow (until we have
true text searching).

Fix:

Remove the common usage of LIKE '%keyword%';  You can find a lot of
suggestions in the MySQL mail archive how to do this.

Note that if you have updated your tables a lot, 'OPTIMIZE TABLE' may
help a little...

Regards,
Monty