CODEWITHSUNDEEP
phpsqldatabasesearchsearch-engine
Qqwy
Qqwy

Reputation: 5649

How to sort and filter searches on multiple fields in SQL

I have a SQL database with music songs. Each song of course has an artist, an album and a genre. They also have a general 'popularity' counter, which was obtained from an external source. However, I want to give users the opportunity to vote on the songs as well. In the end, the search results should be ordered on this popularity, as well as the accuracy of the results with the original query.

The current query I use is as follows:

SELECT *
FROM p2pm_tracks
WHERE
 `artist` LIKE '%$searchquestion%' OR
 `genres` LIKE '%$searchquestion%' OR
 `trackname` LIKE '%$searchquestion%' OR
 `album_name` LIKE '%$searchquestion%'
ORDER BY `popularity` DESC
LIMIT $startingpoint, $resultsperpage

I struggle with the following:

  1. Users search for something. I look in all fields: song title, artist, album and genre. However, usually a certain search query contains (parts of) multiple of these tracks.

For instance, a user might search for Opening Philip Glass.

In this case, the first word is the name of the song, and the second and third words are the artist name.

Another example:

If I split the query on spaces, the correct tracks are found. However, if another track that matches only one of these words has a higher popularity, it will be returned before the one that actually accurately matches the search query.

I still want to sort the results in a way that things that match bigger parts of the query at once are at the top. How can I do that using SQL?

  1. I have the static popularity and want to create a new one. Therefore, I want to use the average of all votes on a certain track (these votes are stored in another table), except in the cases where there are no votes yet. How can I construct a SQL query that does this?

My application is built in PHP, but I would like to do as much as possible of this in SQL, preferably in as few queries as possible to reduce latency.

Any help would be appreciated.

Upvotes: 2

Views: 3643

Answers (4)

Wissam El-Kik
Wissam El-Kik

Reputation: 2525

You can add a weight for every column in your search results.

Here's the code:

SELECT *,
    CASE WHEN `artist` LIKE '%$searchquestion%' THEN 1 ELSE 0 END AS artist_match,
    CASE WHEN `genres` LIKE '%$searchquestion%' THEN 1 ELSE 0 END AS genres_match,
    CASE WHEN `trackname` LIKE '%$searchquestion%' THEN 1 ELSE 0 END AS trackname_match,
    CASE WHEN `album_name` LIKE '%$searchquestion%' THEN 1 ELSE 0 END AS album_name_match,
FROM p2pm_tracks
WHERE
 `artist` LIKE '%$searchquestion%' OR
 `genres` LIKE '%$searchquestion%' OR
 `trackname` LIKE '%$searchquestion%' OR
 `album_name` LIKE '%$searchquestion%'
ORDER BY 
 `artist_match` DESC,
 `genres_match` DESC,
 `trackname_match` DESC,
 `album_name_match` DESC,
 `popularity` DESC,
LIMIT $startingpoint, $resultsperpage

This query will gather the results related to:

  • the artist FIRST,
  • THEN the genre,
  • THEN the track's title,
  • THEN the album's name,
  • THEN the popularity of the song

To optimize this query, you should avoid using "LIKE" and use "FULLTEXT SEARCH" instead.

The optimized code will be:

SELECT *,
    CASE WHEN MATCH (artist) AGAINST ('$searchquestion') THEN 1 ELSE 0 END AS artist_match,
    CASE WHEN MATCH (genres) AGAINST ('$searchquestion') THEN 1 ELSE 0 END AS genres_match,
    CASE WHEN MATCH (trackname) AGAINST ('$searchquestion') THEN 1 ELSE 0 END AS trackname_match,
    CASE WHEN MATCH (album_name) AGAINST ('$searchquestion') THEN 1 ELSE 0 END AS album_name_match,
FROM p2pm_tracks
WHERE
 MATCH (artist) AGAINST ('$searchquestion') OR
 MATCH (genres) AGAINST ('$searchquestion') OR
 MATCH (trackname) AGAINST ('$searchquestion') OR
 MATCH (album_name) AGAINST ('$searchquestion')
ORDER BY 
 `artist_match` DESC,
 `genres_match` DESC,
 `trackname_match` DESC,
 `album_name_match` DESC,
 `popularity` DESC,
LIMIT $startingpoint, $resultsperpage

And make sure that you're using the MyISAM engine for the MySQL table and that you created indexes for the columns you want to search. The code for your MySQL table should look like:

CREATE TABLE p2pm_tracks (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    artist VARCHAR(255) NOT NULL,
    trackname VARCHAR(255) NOT NULL,
    ...
    ...
    FULLTEXT (artist,trackname)
) ENGINE=MyISAM;

For more info, check the following: - http://dev.mysql.com/doc/refman/5.0/en/fulltext-natural-language.html - http://dev.mysql.com/doc/refman/5.5/en/fulltext-boolean.html

If you're looking for something more advanced, then look into Solr (based on Lucene), Sphinx, ElasticSearch (based on Lucene) etc.

Upvotes: 4

Paweł Malisak
Paweł Malisak

Reputation: 130

Please don't use LIKE. It's very slow. You can use full text search in mysql but you can not determinate which column is more important.

Better solution is mysql with sphinx.

Upvotes: 1

Hans Dubois
Hans Dubois

Reputation: 269

MySQL is not that good in searching for text :(

  1. What you could try to do is take a look at full text search functionality (http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html)

  2. With the match against function you can get a relevance where you can order on.

    SELECT p2pm_tracks.*, MATCH (artist, genres) AGAINST ('some words') AS relevance, MATCH (artist) AGAINST ('some words') AS artist_relevance

Upvotes: 1

Radon8472
Radon8472

Reputation: 4981

Hmm, to match your 1. example is difficult in SQL, I´m not sure if there is a function. what you need is something like this funktion in php

http://php.net/manual/function.similar-text.php

Or you select in your sql query only per average vote and calculate how "good" the results match via php and the similar-text function.

Upvotes: 0

Related Questions