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> SELECT DISTINCT Disks.K ".
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

Hi!

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

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

INSERT INTO tmp_table SELECT ..

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

Regards,
Monty
Thread
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