bywWFxvv

1Complex query for recreating fulltext search effect on innodb
2SELECT ... WHERE row LIKE '%some%' OR row LIKE '%search%' OR  row LIKE '%string%'
3	
4create table users (...) engine=innodb;
5
6create table forums (...) engine=innodb;
7
8create table threads
9(
10forum_id smallint unsigned not null,
11thread_id int unsigned not null default 0,
12user_id int unsigned not null,
13subject varchar(255) not null, -- gonna want to search this... !!
14created_date datetime not null,
15next_reply_id int unsigned not null default 0,
16view_count int unsigned not null default 0,
17primary key (forum_id, thread_id) -- composite clustered PK index
18)
19engine=innodb;
20	
21create table threads_ft
22(
23forum_id smallint unsigned not null,
24thread_id int unsigned not null default 0,
25subject varchar(255) not null,
26fulltext (subject), -- fulltext index on subject
27primary key (forum_id, thread_id) -- composite non-clustered index 
28)
29engine=myisam;
30	
31drop procedure if exists ft_search_threads;
32delimiter #
33
34create procedure ft_search_threads
35(
36in p_search varchar(255)
37)
38begin
39
40select
41 t.*,
42 f.title as forum_title,
43 u.username,
44 match(tft.subject) against (p_search in boolean mode) as rank
45from
46 threads_ft tft
47inner join threads t on tft.forum_id = t.forum_id and tft.thread_id = t.thread_id
48inner join forums f on t.forum_id = f.forum_id
49inner join users u on t.user_id = u.user_id
50where
51 match(tft.subject) against (p_search in boolean mode) 
52order by 
53 rank desc
54limit 100;
55
56end;
57
58call ft_search_threads('+innodb +clustered +index');
59	
60$words=dict($userQuery);
61$numwords = sizeof($words);
62$innerquery="";
63for($i=0;$i<$numwords;$i++) {
64    $words[$i] = mysql_real_escape_string($words[$i]);
65    if($i>0) $innerquery .= " AND ";
66    $innerquery .= "
67        (
68            field1 LIKE "%$words[$i]%" OR
69            field2 LIKE "%$words[$i]%" OR
70            field3 LIKE "%$words[$i]%" OR
71            field4 LIKE "%$words[$i]%"
72        )
73    ";
74}
75
76
77SELECT fields FROM table WHERE $innerquery AND whatever;