· 10 years ago · Aug 25, 2016, 11:06 AM
1;;
2
3DROP TABLE IF EXISTS t_user_search;
4
5;;
6
7CREATE TABLE t_user_search
8(
9 id INT(10) UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
10 user_id INT(10) NOT NULL DEFAULT 0,
11 search_txt TEXT,
12
13 FULLTEXT (search_txt)
14)
15ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
16
17;;
18
19REPLACE INTO t_user_search
20(
21 user_id,
22 search_txt
23)
24SELECT
25 id,
26 CONCAT_WS(' ',
27 id,
28 state,
29 zip_code,
30 full_name,
31 first_name,
32 last_name,
33 email,
34 type_name,
35 username,
36 street
37 )
38FROM
39 t_user;
40
41;;
42
43EXPLAIN EXTENDED
44SELECT
45 user_id
46FROM
47 t_user_search
48WHERE
49 search_txt LIKE 'garden%'
50
51;;
52
53EXPLAIN EXTENDED
54SELECT
55 user_id
56FROM
57 t_user_search
58WHERE
59 search_txt LIKE '%garden%'
60
61;;
62
63EXPLAIN EXTENDED
64SELECT
65 user_id
66FROM
67 t_user_search
68WHERE
69 MATCH(search_txt) AGAINST('garden')