MySQL Forums
Forum List  »  Quality Assurance

Mix of Match() Against(), & Match() Against(in boolean mode) returns Non-Sorted results
Posted by: DTeCH Zone
Date: June 27, 2012 08:16AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Mix of Match() Against(), & Match() Against(in boolean mode) returns Non-Sorted results
3166
June 27, 2012 08:16AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.