0

Let say I have a MySQL server with one table that has over 1 million records. For the sake of illustration let say those are products. To make it simple the table has only 3 columns:

product_id - name - price

Let say I have one table displaying the information. Through a TextBox, with each key press, I wan't to select the products that contains the string in that TextBox, additionally the TextBox can have multiple search strings (up to 3) divided by a character '^' so if the TextBox had a string like: "st ^ b ^ ry" it would select products that contains 'st', 'b' and 'ry'.

I made the query like this: 

SELECT 
    product_id, 
    name,   
    price
FROM 
    products
WHERE 
    name LIKE %{Param1}%
    AND name LIKE %{Param2}%
    AND name LIKE %{Param3}%
ORDER BY 
    name ASC

Locally this is fine I guess but when the program is being used somewhere else the query and table takes forever to load after each key press. So...

  • What is the best way to do this?
  • Is there a better way to get what i want instead of 3 "LIKE" or is it right how I did it?

UPDATE

I've checked the questions posted by Loathing and according to what I read the best solution is:

SELECT name FROM table
WHERE MATCH (name) AGAINST ('keyterm');

Having a FULLTEXT index (with MyISAM engine). But the problem with that is that it won't show partial entries. If I had a product called "1/2 Blue Strawberry" and in keyterm I have 'a' (as an example), nothing will show up. I don't want it to just look for words but every record containing whatever I want even if that is a single letter.

Hikaros
  • 101
  • 1
  • 12
  • Have you looked into querying asynchronously? – arao6 Sep 03 '14 at 05:21
  • 1
    http://stackoverflow.com/questions/3213998/performance-of-like-query-vs-full-text-search-contains-query http://stackoverflow.com/questions/9589813/most-efficient-way-to-search-in-sql?rq=1 http://stackoverflow.com/questions/411724/optimal-like-search-in-sql – Loathing Sep 03 '14 at 05:37
  • @arao6 Tbh i don't even know how to use those but i will look into it. Loathing, I updated the question. At least now i have an idea :) – Hikaros Sep 03 '14 at 06:59
  • I haven't used FTS before, but this looks promising: http://devzone.zend.com/26/using-mysql-full-text-searching/#Heading14 – Loathing Sep 03 '14 at 08:04
  • This will be useful sometime but for my case (after researching some more stuff) the best was to load every record i'm going to use and use filters in the table. Thanks anyways :) – Hikaros Sep 04 '14 at 06:01

0 Answers0