seHajLwi

1CREATE TABLE IF NOT EXISTS `pharma` (
2  `ID` int(4) NOT NULL,
3  `drug` varchar(90) DEFAULT NULL, //index
4  `form` varchar(20) DEFAULT NULL,
5  `price` decimal(7,2) DEFAULT NULL,
6  `Company` varchar(54) DEFAULT NULL,
7  `Pharmacology` varchar(96) DEFAULT NULL
8) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=92064 ;
9
10SELECT *
11FROM `pharma`
12WHERE `drug` LIKE '%PANADOL%' ESCAPE '!'
13AND  `drug` LIKE '%500%' ESCAPE '!'
14AND  `drug` LIKE '%mg%' ESCAPE '!'
15AND  `drug` LIKE '%fc%' ESCAPE '!'
16AND  `drug` LIKE '%PANADOL%' ESCAPE '!'
17AND  `drug` LIKE '%500%' ESCAPE '!'
18AND  `drug` LIKE '%mg%' ESCAPE '!'
19ORDER BY (CASE
20            when drug LIKE 'PANADOL %' then 1
21            when drug LIKE 'PANADOL%' then 2
22            when drug LIKE '% PANADOL%'  then 3
23            when drug LIKE '%PANADOL%'  then 4
24            else 4
25            end)
26 LIMIT 15; //average excution time 1.7sec;
27	
28MATCH (drug) AGAINST ('+PANADOL +500 +mg' IN BOOLEAN MODE)
29	
30SELECT ... FROM tbl
31    WHERE MATCH(drug) AGAINST('+PANADOL' IN BOOLEAN MODE)
32      AND ... LIKE ...
33      AND ... LIKE ...
34      ...
35    ORDER BY ...;