Hi all , I have a question about how to best approach indexing a table with many variations of fields searched by, but one common ORDER BY field. Take for example this table (with only relevant searched fields shown): CREAT TABLE Offers ( bid` mediumtext NOT NULL, `company_name` varchar(50) default NULL, `country` varchar(25) NOT NULL default '', `email` varchar(100) NOT NULL default '', `keywords` varchar(100) default NULL, `deletedate` date NOT NULL default '0000-00-00', `subcatID` int(10) unsigned NOT NULL default '0', `ID` int(10) unsigned NOT NULL auto_increment, `userID` int(10) unsigned NOT NULL default '0', FULLTEXT KEY `keywords` (`keywords`), FULLTEXT KEY `bid` (`bid`) ) So , the question about indexes comes up. There are many variations of searches that will happen such as: bid contains certain words keywords contain certain words subcatID IN (1,23,3,4,5,6,7,8,9,) etc.. subcatID IN (1,23,3,4,5,6,7,8,9,) and country='XX' subcatID IN (1,23,3,4,5,6,7,8,9,) and country='XX' AND userID = '12345678' and so on. and all mixed together at points. subcatID is *usually* in the search criteria. everything is ordered by deletedate to get the most recent results first. What should the thinking be when deciding how to best index this table for speed of searches? All advice welcome and appreciated! Thanks, Aaron
