List:MySQL on Win32« Previous MessageNext Message »
From:Michael Widenius Date:October 18 1999 9:08pm
Subject:The use of Index's and speeding up Query's
View as plain text  
>>>>> "Neil" == Neil Galvin <neil@stripped> writes:

Neil> Hi I have the following two tables in my database:
Neil> CREATE TABLE Disks (
Neil>    DiskID int(11) DEFAULT '0' NOT NULL auto_increment,
Neil>    CatNO varchar(8),
Neil>    Title varchar(255),
Neil>    ReleaseDate varchar(10),
Neil>    Style varchar(50),
Neil>    Format varchar(50),
Neil>    Label varchar(50),
Neil>    Description varchar(255),
Neil>    Price varchar(10),
Neil>    Weight varchar(10),
Neil>    Image varchar(50),
Neil>    Audio varchar(50),
Neil>    PRIMARY KEY (DiskID)
Neil> );

Neil> CREATE TABLE Tracklist (
Neil>    TracklistID int(11) DEFAULT '0' NOT NULL auto_increment,
Neil>    Disk int(11),
Neil>    Number tinyint(3) unsigned,
Neil>    Title varchar(255),
Neil>    Duration varchar(5),
Neil>    Artist varchar(255),
Neil>    Audio varchar(255),
Neil>    PRIMARY KEY (TracklistID)

Neil> and the following code in a php script.
Neil>           "FROM Disks LEFT JOIN Tracklist ON Disks.K = Tracklist.Disk ".
Neil>           "WHERE (Disks.Title Like '%$search%') OR ".
Neil>           "(Disks.Label Like '%$search%') OR ".
Neil>           "(Tracklist.Title Like '%$search%') OR ".
Neil>           "(Tracklist.Artist Like '%$search%')";

Neil> The Disks table has approx. 850 records while Tracklist has approx. 5000.
Neil> The query takes approx. 90 seconds to execute.

Neil> I've tried running the query with explain but it just reports: In the TYPE
Neil> section (all) in all fields and NULL in the rest.

Neil> I've also tried indexing various fields, but the biggest speed increase I've
Neil> seen was only about 15-20%.

Neil> I would therefore be grateful for any pointers on how I can speed up the
Neil> query and/or database


The problem is that MySQL can't yet optimize an OR over different

The only way to get the above fast is to do 4


queries and then query the result table.  In MySQL 3.23 this is pretty 
easy to do with TEMPORARY tables.

The use of Index's and speeding up Query'sNeil Galvin18 Oct
  • The use of Index's and speeding up Query'sMichael Widenius19 Oct
    • RE: The use of Index's and speeding up Query'sNeil Galvin19 Oct