Menu
is there anyway to speed up mysql like operator performance if wild card is involved? eg. like '%test%'
592
1 Answer. Using '=' operator is faster than the LIKE operator in comparing strings because '=' operator compares the entire string but the LIKE keyword compares by each character of the string.
Does Ilike work in MySQL? It is in PostgreSQL the keyword ILIKE can be used instead of LIKE to make the match case-insensitive according to the active locale. This is not in the SQL standard but is a PostgreSQL extension. In MySQL you do not have ILIKE.
Optimizing Database Schema Limiting the number of columns: MySQL has a limit of 4096 columns per table. Use fewer columns for better performance. If possible, do not use more than a hundred columns unless your business logic requires that. Tables with more columns require more CPU time to process.
MySQL can use an index if your query looks like foo LIKE 'abc%' or foo LIKE 'abc%def%'. It can use the index for any portion or the string before the first wildcard. If you need to match a word anywhere within a string, you might want to consider using FULLTEXT indexes instead.
More detail on indexes: http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html
Full text search: http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
133
Well, the simplest optimization would be to have a constant prefix (e.g. "test%" instead of "%test" or "%test%") since that would allow the engine to narrow down the search space using indices. However, that is not possible in all situations.
Sometimes a preprocessing step can turn a wildcard search into an ordinary equality or a fulltext search. You'll gain more by finding a way to structure your data so that it does not require a wildcard search than trying to optimize the latter.
44
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
