Full-Text search results ordering

Hi,

I have a big database (around 45.000 items in it). I need to perform a full search, when a user want to search for a specific keyphrase, but to order it after some fields.

Here is the table design:

CREATE TABLE `sw_prod` (
  `p_name` varchar(60) NOT NULL default '',
  `p_short_en` varchar(80) NOT NULL default '',
  `p_short_fr` varchar(80) NOT NULL default '',
  `p_short_de` varchar(80) NOT NULL default '',
  `p_descr_en` blob NOT NULL,
  `p_descr_fr` blob NOT NULL,
  `p_descr_de` blob NOT NULL,
  `p_platform` varchar(50) NOT NULL default '',
  `p_price` varchar(8) NOT NULL default '',
  `p_pricemon` varchar(10) NOT NULL default '',
  `id` int(11) NOT NULL auto_increment,
  `p_sizek` int(8) NOT NULL default '0',
  `p_released` date NOT NULL default '0000-00-00',
  `p_release_type` varchar(50) NOT NULL default '',
  `p_added` date NOT NULL default '0000-00-00',
  `p_licence` varchar(25) NOT NULL default '',
  `p_support` varchar(100) NOT NULL default '',
  `p_scr` varchar(100) NOT NULL default '',
  `p_author` varchar(25) NOT NULL default '',
  `p_xml` varchar(150) NOT NULL default '',
  `p_dld` varchar(150) NOT NULL default '',
  `p_buy` varchar(150) NOT NULL default '',
  `p_regnowid` varchar(15) NOT NULL default '',
  `p_shareitid` varchar(15) NOT NULL default '',
  `p_category` int(11) NOT NULL default '0',
  `p_featured` varchar(5) NOT NULL default '',
  `p_nrdld` int(11) NOT NULL default '0',
  `p_vote_nr` int(11) NOT NULL default '0',
  `p_vote_avg` int(11) NOT NULL default '0',
  `p_rating` char(1) NOT NULL default '',
  `p_icon` varchar(150) NOT NULL default '',
  `p_version` varchar(15) NOT NULL default '',
  `p_keyw_en` varchar(150) NOT NULL default '',
  `p_keyw_fr` varchar(150) NOT NULL default '',
  `p_keyw_de` varchar(150) NOT NULL default '',
  `p_active` int(11) NOT NULL default '0',
  `p_affiliated` int(11) NOT NULL default '0',
  `p_deleted` int(11) NOT NULL default '0',
  `p_big_en` blob NOT NULL,
  `p_big_fr` blob NOT NULL,
  `p_big_de` blob NOT NULL,
  `p_xmlsize` int(11) NOT NULL default '0',
  `p_iconexists` int(11) NOT NULL default '0',
  `p_coupon` varchar(30) NOT NULL default '',
  `p_cumparabil` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`,`p_cumparabil`),
  KEY `p_name` (`p_name`),
  KEY `p_author` (`p_author`),
  KEY `p_added` (`p_added`),
  KEY `p_nrdld` (`p_nrdld`),
  KEY `p_rating` (`p_rating`),
  KEY `p_featured` (`p_featured`),
  KEY `p_coupon` (`p_coupon`(15)),
  KEY `search1` (`p_cumparabil`,`p_coupon`,`p_featured`,`p_nrdld`),
  FULLTEXT KEY `blabla` (`p_name`,`p_author`,`p_keyw_en`,`p_short_en`)
) ENGINE=MyISAM AUTO_INCREMENT=45798;

Code (markup):

Here is a query (user searches for "email backup") that takes a lifetime

SELECT p_name,id,p_short_en,p_licence,p_sizek,p_nrdld,p_rating,p_regnowid,p_shareitid,p_buy,p_icon,p_iconexists,p_category,p_version,p_added,p_price,p_pricemon,p_coupon,p_featured, MATCH (p_name,p_author,p_keyw_en,p_short_en) AGAINST ('>("email backup") +email +backup' IN BOOLEAN MODE) AS value FROM `sw_prod` WHERE MATCH (p_name,p_author,p_keyw_en,p_short_en) AGAINST ('>("email backup") +email +backup' IN BOOLEAN MODE) AND p_deleted!='1' ORDER BY p_cumparabil DESC, value DESC , p_coupon DESC, p_featured DESC , p_nrdld DESC LIMIT 0 , 20

Code (markup):

Please help

 

fulltext searches can take a long time... I would try to write a script or app that parses the important data, and puts that in another field - this way you don't have to do FullTextSearches.

 

ok, put the important data in another field... and how to SELECT only those items that fit my query ? using `fieldname` LIKE "%keyword%" ??? isn't this slower ?

 

I am offering $50 (or even more, if the solution is great), for anybody who will help me solve this issue, and get a time of maximum 5 seconds for displaying the results.

 

Solved the problem myself... consider the topic closed.

 

Full tect searches and LIKE searches are always going to be slower as they do not use an index.

How did you solve the issue?

 

well, in fact it was not the full search of 45000 intems the problem. i analyzed it, and it took 0.1 to 0.2 seconds. The problem was that, before the main query, a search for related keyphrases was done.....this search (not indexed ) in 200.000 items, took 20 to 50 seconds i tried to make this second query better, i put a full text index... but the time of 10-20 seconds is still unacceptable. So, for the moment, I eliminated the "related keyphrases" part