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.
Enhance performance of mysql query
Moderator: General Moderators
-
fractalvibes
- Forum Contributor
- Posts: 335
- Joined: Thu Sep 26, 2002 6:14 pm
- Location: Waco, Texas
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
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
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
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
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.
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.