Page 1 of 1
Enhance performance of mysql query
Posted: Mon Jun 14, 2004 10:47 pm
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.
Posted: Tue Jun 15, 2004 2:16 am
by timvw
Posted: Tue Jun 15, 2004 6:54 am
by Grim...
Blue?
Eeeeeugh
Sorry.
Posted: Tue Jun 15, 2004 11:42 pm
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]
Posted: Wed Jun 16, 2004 12:24 am
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
Posted: Wed Jun 16, 2004 2:25 am
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
Posted: Wed Jun 16, 2004 5:14 am
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.