0

Here is my table:

// tags
+----+-----------+-----------------------------------------+
| id |   name    |               description               |
+----+-----------+-----------------------------------------+
| 1  | function  | some explanations about function ..     |
| 2  | array     | some explanations about arrqay ..       |
| 3  | HTML      | some explanations about HTML ..         |
+----+-----------+-----------------------------------------+

Now I'm trying to make a auto-complete box which suggests some tags. Here is my current code:

// current value of the input which has been sent by an ajax request
$input_value = $_POST['q'];

// DB connection is here
$stmt = $db->prepare("SELECT name, description
                      FROM tags
                      WHERE name LIKE CONCAT('%', ?, '%') OR  description LIKE CONCAT('%', ?, '%')
                      LIMIT 6");
$stmt->execute(array($input_value,input_value));

But my query is getting slow. For example sometimes it takes 1 sec to return results. Because my table is getting bigger gradually, So I need to improve my query. Is there any idea?

Noted that I have two indexes on name and description columns separately.

stack
  • 10,280
  • 19
  • 65
  • 117
  • 1
    Where does it hang? If you execute the query on the DB is it +1 second also? – chris85 Dec 22 '16 at 20:18
  • @chris85 Yes .. my php codes are fine .. the problem is the query. Noted that if I remove those `%`s, the speed will be really fast – stack Dec 22 '16 at 20:19
  • 1
    @stack Sorry, missed that last line. Are you rate limiting the queries? (ie. if the user types three letters within a time box, do you make 3 queries? or 1? – AJ X. Dec 22 '16 at 20:19
  • 1
    You are using `=` or `like`? You syntax is kinda mixed up there.. – chris85 Dec 22 '16 at 20:20
  • @axlj Yes, I have a waiting mechanism which waits .5 sec and if there wasn't any keydown, then ajax request will be sent. – stack Dec 22 '16 at 20:22
  • @chris85 Edited .. that was a typo – stack Dec 22 '16 at 20:22
  • 1
    How about a full text search index? – AJ X. Dec 22 '16 at 20:24
  • http://viralpatel.net/blogs/full-text-search-using-mysql-full-text-search-capabilities/ – Alon Eitan Dec 22 '16 at 20:24
  • @axlj I thought about it .. it has a problem, which won't work for less than 3 letters – stack Dec 22 '16 at 20:24
  • I am impressed that your code works at all. Never seen `LIKE %somestring%` before but I *am* familiar with `LIKE '%somestring%'`... – MonkeyZeus Dec 22 '16 at 20:27
  • 3
    @stack I'm not sure how much searching for a 2 letters is helping someone. Would you search for "is" (for example)? It will probably return 95% of the rows – Alon Eitan Dec 22 '16 at 20:28
  • @MonkeyZeus Yes in reality I'm using `'` .. I will edit my question – stack Dec 22 '16 at 20:28
  • With the `like` update I'd say use full text index. You could drop the index to 2 letters. Would you want 1 letter indexing? A vowel search would return every word.. – chris85 Dec 22 '16 at 20:28
  • @AlonEitan SO does that for even 1 letter https://i.stack.imgur.com/aHo5p.png – stack Dec 22 '16 at 20:31
  • @chris85 How can I drop the index to 2 letters ? – stack Dec 22 '16 at 20:32
  • See http://dev.mysql.com/doc/refman/5.7/en/fulltext-fine-tuning.html See `After changing any of these options, rebuild your FULLTEXT indexes for the change to take effect. For example, to make two-character words searchable, you could put the following lines in an option file` – chris85 Dec 22 '16 at 20:33
  • @stack http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_ft_min_word_len (You can set it for 1 and it will solve you problems) – Alon Eitan Dec 22 '16 at 20:33
  • Placeholders don't work inside quotes. You need to do `LIKE CONCAT('%', ?, '%')` – Barmar Dec 22 '16 at 20:34
  • @chris85 You know, If I make any change in the mysql configuration, it will be applied for all my full-text queries .. I don't want that .. I want to just do that for this query. – stack Dec 22 '16 at 20:34
  • I recommend some mysql performance tuning as well, [mysqltuner.pl](http://mysqltuner.com/) or [tuning-primer.sh](http://www.day32.com/MySQL/) – jaggedsoft Dec 22 '16 at 20:34

0 Answers0