Enhance performance of mysql query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
seevali
Forum Newbie
Posts: 10
Joined: Wed May 12, 2004 3:27 am

Enhance performance of mysql query

Post by seevali »

Hi all,
I have a Mysql table which has around 125,000 records in it. I search this table and produce a search result to the browser using following query in php. My search query takes around 10 to 15 seconds when i run the query with Mysql client software. How can i enhance the performance of this search? I added an index for some column but no significant impact on this. May be i have added indexes blindly. Does indexing make sense ?

This is the query i am using.

SELECT DISTINCT ArtistID,Artist,Genre FROM Track WHERE Artist LIKE '%blue%' GROUP BY ArtistID ORDER BY Artist.

I had to use DISTINCT since i need to display only one artist.

Thanks in advance.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Grim...
DevNet Resident
Posts: 1445
Joined: Tue May 18, 2004 5:32 am
Location: London, UK

Post by Grim... »

Blue?
Eeeeeugh :(



Sorry.
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post by fractalvibes »

Why don't you just search by Artist ID? The distinct is going to slow things as well as the LIKE %blue%. Under the best of circumstances you could still get multiple rows with that like clause, so the distinct does not buy you anything. What is the context of what you are trying to do?

fv[/quote]
seevali
Forum Newbie
Posts: 10
Joined: Wed May 12, 2004 3:27 am

Post by seevali »

Thanks for replying me.
I had to use DISTINCT coz no use of showing same artist twice or more in the case of album search or track search.
Searching by ArtistID alone wouldn't do my objective. My search application gives user to search either by Artist or Album or Track name or one or more combination of these three. So i have to create the select query dynamically according to user selection and search for these three columns. Typical query will look like the following.

search keywords are 'mer de noms' and search criteria is Artist or Album or Track

SELECT ArtistID,AlbumID,Album,Name,TrackID,Artist,Genre,SoundClip FROM Track WHERE Name LIKE '%mer%' || Artist LIKE '%mer%' || Album LIKE '%mer%' || Name LIKE '%de%' || Artist LIKE '%de%' || Album LIKE '%de%' || Name LIKE '%mer de %' || Artist LIKE '%mer de %' || Name LIKE '%noms%' || Artist LIKE '%noms%' || Album LIKE '%noms%' || Name LIKE '%mer de noms %' || Artist LIKE '%mer de noms %' || Track LIKE '%mer de noms %' GROUP BY ArtistID ORDER BY Artist,Album,Name
Rémy
Forum Newbie
Posts: 1
Joined: Wed Jun 16, 2004 2:25 am
Location: Amsterdam

Post by Rémy »

First, normalize your table into an Artist-, Album-, Track- and GenreTable and then put the right indexes.

Second, search only in the Artisttable, then search in the AlbumTable, then search the TrackTable and then combine the results (or show three resultsets to the user).

Third, have you done a research how users search? Or do you only think that users want to search this way? Does a user really want results with 'de'? I think searching on '%mer%de%noms%' is powerfull enough....

Fourth, if your really need the searchpower, then enable FullTextSearch (does MySQL have this? I thought so, but you can look this up in their manual.)

-Rémy
seevali
Forum Newbie
Posts: 10
Joined: Wed May 12, 2004 3:27 am

Post by seevali »

I use only Track table for searching. There i have Artist, Album and Track columns. When somebdoy search i look for only these three columns.

No i haven't done any research but thought of giving the user to maximum result out the keywors he entered.

I can't do the fulltext search because it doesn't support for InnoDB table type. I use InnoDB tables.
Post Reply