List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:April 10 1999 7:22pm
Subject:Re: speed prob with solaris 2.5.1 and MySQL 3.22.19
View as plain text  
>>>>> "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

Thread
speed prob with solaris 2.5.1 and MySQL 3.22.19Philippe PETIT8 Apr
Re: speed prob with solaris 2.5.1 and MySQL 3.22.19Philippe PETIT8 Apr
  • Re: speed prob with solaris 2.5.1 and MySQL 3.22.19Michael Widenius11 Apr