Mix of Match() Against(), & Match() Against(in boolean mode) returns Non-Sorted results
I have a bit of a problem here...
The following code returns matches that the users searched for - nicely sorted by relevance, & super fast:
SELECT
*
FROM
search_table
WHERE
MATCH (Title_Column) AGAINST (
'Whatever the user searched for'
)
LIMIT 25;
The problem shows up when i do the following to narrow the results by category:
SELECT
*
FROM
search_table
WHERE
MATCH (Title_Column) AGAINST (
'Whatever the user searched for'
)
AND (
MATCH (Category_Column) AGAINST ('Music' IN BOOLEAN MODE)
)
LIMIT 25;
The results are by no means sorted, & they are not related to what the user searched for even though they put quite a few different words in the query.
I have tried the following, but it completely defeates the benefits of having an index... it's speed is really undesirable:
SELECT
*, MATCH (Title_Column) AGAINST (
'Michael Jackson' IN boolean MODE
) AS Relevance
FROM
search_table
WHERE
(
MATCH (Title_Column) AGAINST (
'Michael Jackson' IN boolean MODE
)
AND MATCH (Category_Column) AGAINST ('Music' IN boolean MODE)
)
ORDER BY
Relevance DESC
LIMIT 25;
I was 3 seconds away from deciding to re-learn some other SQL language because this is as far as i can get - speed being the requirement, & it's not usable.
Here are the requirements:
1)... SPEED :D
2)... user selects 1 of 23 categories (category 1 is "ALL CATEGORIES", & some others are a mix of categories, for example: Music = Music-MP3, Music-FLACK, Music-WMA, Music-Video... all seporate categories in the Category_Column column)
3)... Search Title_Column for user's search tearm, & filer it by the categories selected.
4)... Results MUST be sorted by relevance, AND return results according the the user's search tearm.
5)... Did I mention Speed?
Ok... here is the setup:
delimiter $$
CREATE TABLE `search_table` (
`Category_Column` tinytext NOT NULL,
`s_Size` varchar(12) NOT NULL,
`Title_Column` tinytext NOT NULL,
`User_TITLE_Column` tinytext NOT NULL,
`s_PostDate` varchar(40) NOT NULL,
`s_Poster` tinytext NOT NULL,
`s_ItemHASH` varchar(40) NOT NULL,
`s_ItemName` tinytext NOT NULL,
`s_ItemNumber` bigint(9) NOT NULL,
`s_DownloadsCount` bigint(9) NOT NULL,
`s_URL` tinytext NOT NULL,
`tid` bigint(9) NOT NULL auto_increment,
`s_CatImage` varchar(30) NOT NULL,
`s_UnixTimeStamp` bigint(10) NOT NULL,
`s_ID` varchar(40) NOT NULL,
PRIMARY KEY (`tid`),
FULLTEXT KEY `iCategory_Column` (`Category_Column`), <-------FULLTEXT B-Tree
FULLTEXT KEY `iTitle_Column` (`Title_Column`), <----------------------------------FULLTEXT B-Tree
FULLTEXT KEY `iUserTITLE_Column` (`User_TITLE_Column`), <-----------------------FULLTEXT B-Tree
FULLTEXT KEY `i_Poster` (`s_Poster`) <----------------------------------FULLTEXT B-Tree
) ENGINE=MyISAM AUTO_INCREMENT=737319 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC$$
I would like to see examples of the proper syntax that allows the user to search, & return relevant results just as the fisrt (FAST) example I gave above does, but with the category filter.
I would like it to be as fast as the first search example, or even the second (that returns non-relevant results), but it MUST continue to sort the relevant results just as it does without the filter.
Any examples would be awesomely appreciated.
Thanks
Edited 2 time(s). Last edit at 06/27/2012 03:17PM by DTeCH Zone.