How to search for rows containing a substring?

by

Well, you can always try WHERE textcolumn LIKE "%SUBSTRING%" – but this is guaranteed to be pretty slow, as your query can’t do an index match because you are looking for characters on the left side.

It depends on the field type – a textarea usually won’t be saved as VARCHAR, but rather as (a kind of) TEXT field, so you can use the MATCH AGAINST operator.

To get the columns that don’t match, simply put a NOT in front of the like: WHERE textcolumn NOT LIKE "%SUBSTRING%".

Whether the search is case-sensitive or not depends on how you stock the data, especially what COLLATION you use. By default, the search will be case-insensitive.

Updated answer to reflect question update:

I say that doing a WHERE field LIKE "%value%" is slower than WHERE field LIKE "value%" if the column field has an index, but this is still considerably faster than getting all values and having your application filter. Both scenario’s:

1/ If you do SELECT field FROM table WHERE field LIKE "%value%", MySQL will scan the entire table, and only send the fields containing “value”.

2/ If you do SELECT field FROM table and then have your application (in your case PHP) filter only the rows with “value” in it, MySQL will also scan the entire table, but send all the fields to PHP, which then has to do additional work. This is much slower than case #1.

Solution: Please do use the WHERE clause, and use EXPLAIN to see the performance.

More Related Contents:

  1. Fetching UTF-8 text from MySQL in R returns “????”
  2. NOT LIKE not working with SHOW [duplicate]
  3. get first occurrence of last changed value of a column [closed]
  4. MySQL – Rows to Columns
  5. ROW_NUMBER() in MySQL
  6. How to define a custom ORDER BY order in mySQL
  7. How can I temporarily disable a foreign key constraint in MySQL?
  8. How to get the number of days of difference between two dates on MySQL?
  9. conditional join in mysql
  10. MYSQL Select MAX Date inside a join statement
  11. Choosing a stand-alone full-text search server: Sphinx or SOLR? [closed]
  12. Determine which MySQL configuration file is being used
  13. In SQL, how to select the top 2 rows for each group
  14. TRIGGERs that cause INSERTs to fail? Possible?
  15. Mysql query to dynamically convert rows to columns on the basis of two columns
  16. MySQL: #126 – Incorrect key file for table
  17. MySQL: How do I join same table multiple times?
  18. MYSQL Left Join COUNTS from multiple tables
  19. Amazon EC2, mysql aborting start because InnoDB: mmap (x bytes) failed; errno 12
  20. When to use MyISAM and InnoDB? [duplicate]
  21. MySQL my.ini location
  22. Access mysql remote database from command line
  23. Comma separated string of selected values in MySQL
  24. How to turn on/off MySQL strict mode in localhost (xampp)?
  25. Import CSV to Update only one column in table
  26. Error in MySQL library for Node.js
  27. Should I always prefer MySQL InnoDB over MyISAM?
  28. MySQL add 12 hours to a time field
  29. OSX ld: library not found for -lssl
  30. MySQL: Find Missing Dates Between a Date Range

Leave a Comment