Mysql exact word search string

Question

I have a system that is looking for a company. I want that when a user searches for "Demo", all records that have "Demo" will be returned, for example, "Demo", "Demo Inc." etc. I don’t want records like Democratic, Demolition, etc. I think you understand my point.

At the moment, my working request looks something like this:

select * from table where company LIKE "Demo%" 

But it really does not meet my requirement. I also tried this:

 select * from table where company RLIKE "[[:<:]]demo[[:>:]]" 

The only problem is that it rules out the possibility of indexing in my company. Thus, the search is very slow. Now I have over a million records. Any ideas how to do this? If this cannot be done in mysql, any idea if this is possible in PHP? Thank you

Answer 1

Create a full text index, and then you can search more easily.

 ALTER TABLE table ADD FULLTEXT INDEX fulltext_index; SELECT * FROM table WHERE MATCH (company) AGAINST ('+Demo' IN BOOLEAN MODE); 

dev.mysql.com/doc/refman/5.6/en/fulltext-search.html

Answer 2

A shot in the dark according to my comment. If you will always receive accurate eligibility criteria. wouldn't it be best to execute a standard select query?

 SELECT * FROM table WHERE company='The Demo' 

Or for practical:

  $Search = $_GET['company']; SELECT * FROM table WHERE company='$Search' 

Obviously, use best practices when dealing with user inputs and queries.

The results drawn will either be in the lines found as Demo, the demo will be returned, or nothing.

---

If you do not always have an exact match. You can use $ _GET again with the added value, i.e. $ _GET ['Exact'] and have two different functions:

 function ExactMatch ($DB,$Company){ /* Query to get exact match as exampled */ } function NotExact($DB,$Company){ /* Query using LIKE syntax */ } 

and confirm:

 if (isset($_GET['Exact'])){ if ($_GET['Exact'] === 1){ ExactMatch($DB,$_GET['Company']); }else{ NotExact($DB,$_GET['Company']) } } 

Alternatively, read in DBA.stackexchange:

https://dba.stackexchange.com/questions/39693/how-to-speed-up-queries-on-a-large-220-million-rows-table-9-gig-data

Answer 3

 SELECT * FROM table_name WHERE company LIKE "% Demo %" OR company LIKE "Demo %" OR company="Demo"; 

Answer 4

I don’t understand why 1M is the problem that I just tested on my MySQL MyISAM laptop, which also has a company, but it is 250K lines and it took 3.3 ms and the field is not indexed. you can try the following

 $search='Demo'; $regex="/\b$search\b/i"; $sql = "select * from table where company like '%$search%'; //... get the results foreach($results as $companyName){ if(preg_match($regex,$companyName,$match)){ //here you got a match } } 

Answer 5

The best solution is to create a full-text index:

 create fulltext index `i_company` on `table`(`company`); 

Then you can search like:

 select * from `table` where match(company) against ('Demo'); 

Read more about mysql full text search .

Depending on your version of MySQL, the full text index is available for MyISAM in version 5.5 or less and is available for InnoDB with 5.6.

Answer 6

You can use REGEXP and [[:<:]] and [[:>:]] word boundary labels:

 SELECT * FROM `table` WHERE company REGEXP '[[:<:]]Demo[[:>:]]'; 

Another solution

 SELECT * FROM `table` WHERE company REGEXP '(^|[[:space:]])Demo([[:space:]]|$)'; 

SQL Fiddle Demo

Answer 7

Try it, it will help you ..

  SELECT * FROM table_name WHERE company LIKE "%Demo%"; 

Answer 8

Try space testing on both sides:

 select * from table where company LIKE "Demo %" OR company LIKE "% Demo" 

However, as you said, you need to use your indexes, and everything related to the leading % pattern will not use indexes.

So, I think you need to implement some preprocessing in the search columns, something like strings:

Pre-process the names of your entries:

• Use the stemming algorithm for all record names in your database
• Save words in one table ( stemmed_words )
• Write the number of occurrences of a word with a stack at the end of the record identifier ( record_index )

Then, when the user searches:

• Use the generation algorithm in search words
• Query your tables to find the result with the most frequently used phrase

Example stemmed_words Column Columns:

 id, stemmed_word // Eg. 1 (auto generated), "Demo" 

Example record_index Table Columns:

 record_id, stemmed_word_id, occurrence_count // Eg. 1 (auto generated), 1 (ID of "Demo" in stemmed_words table), 2 (2 occurrences) 

Here's a basic tutorial where you can start by reducing and counting words