Which SQL query is better, MATCH AGAINST or LIKE?

by

Update

As of MySQL 5.6 and later, InnoDB tables supports Match... Against.

The first is much better. On MyISAM tables it will use a full text index against those columns. The other will do a full table scan doing a concat on every row and then a comparison.

LIKE is only efficient if you’re doing it against:

  • a column (not a result of a function unless your particular database vendor supports functional indexes–Oracle, for example–and you’re using them);
  • the start of the column (ie LIKE 'blah%' as opposed to LIKE '%blah%'); and
  • a column that’s indexed.

If any one of those conditions are not true the only way for the SQL engine to execute the query is by doing a full table scan. This can be usable under about 10-20 thousand rows. Beyond that it quickly becomes unusable however.

Note: One problem with MATCH on MySQL is that it seems to only match against whole words so a search for ‘bla’ won’t match a column with a value of ‘blah’, but a search for ‘bla*’ will.

More Related Contents:

  1. VARCHAR as foreign key/primary key in database good or bad?
  2. MySQL query finding values in a comma separated string
  3. In MySQL queries, why use join instead of where?
  4. SQLite INSERT – ON DUPLICATE KEY UPDATE (UPSERT)
  5. Can table columns with a Foreign Key be NULL?
  6. Fulltext Search with InnoDB
  7. MySQL not using indexes with WHERE IN clause?
  8. MySQL dump by query
  9. single fixed table with multiple columns vs flexible abstract tables
  10. How many rows in a database are TOO MANY?
  11. SQLite – ORDER BY RAND()
  12. SQL – How to transpose?
  13. SQL Between clause with strings columns
  14. MySQL equivalent of DECODE function in Oracle
  15. How to delete Duplicates in MySQL table
  16. Best database field type for a URL
  17. Query that ignore the spaces
  18. How to Optimize Queries in a Database – The Basics
  19. MySQL – how to front pad zip code with “0”?
  20. Create date from day, month, year fields in MySQL
  21. Find rows that have the same value on a column in MySQL
  22. what is the difference between GROUP BY and ORDER BY in sql
  23. How do I list all the columns in a table?
  24. Two single-column indexes vs one two-column index in MySQL?
  25. How to find all IDs of children recursively?
  26. How to implement a Keyword Search in MySQL?
  27. Entity Attribute Value Database vs. strict Relational Model Ecommerce
  28. Find a string by searching all tables in SQL Server
  29. Mysql: how to select groups having certain values?
  30. SQL ON DELETE CASCADE, Which Way Does the Deletion Occur?

Leave a Comment