Does an additional "AND `columnname` LIKE '%'" hurt performance?
AND `name` LIKE '%'
I've got no big database here that I could test it on.
By the way, I want this because I want users to be able to send some parameters to my server-side script that then grabs the prepared statement specified by the user and inserts the parameters. I want users to be able to omit parameters when searching without hurting the performance, and LIKE
nor开发者_高级运维mally is relatively slow, right?
LIKE's can be expensive indeed, depending on the indexes you have on the name
field. Have you tried to measure your query?
EXPLAIN [EXTENDED] SELECT [...] AND `name` LIKE '%'
See also http://dev.mysql.com/doc/refman/5.0/en/explain.html
Then MySQL will give you indications whether it has optimised the unnecessary LIKE clause away or not.
Yes. LIKE's are very expensive. But as with most things, it depends on the size of your DB.
LIKE
can definately hurt performance. The most important thing to check is that you have the proper indexes. What indexes do you have on the tables being queried?
I suspect that since your LIKE
isn't actually doing anything it would just be optimized out but you will need to test to confirm this.
I agree with the previous answers: LIKE
is pretty expensive. If your table is properly indexed, a small result-set can be additionally queried using LIKE
.
In conjunction with OR
the query is going to be brutally expensive again, though.
Also, solely using LIKE
to query an indexed VARCHAR
field should present minimal performance gain at best.
Just tested with MySQL 5 using MyISAM tables.
SELECT * FROM tablename
17596 rows in set (0.28 sec)
SELECT * FROM tablename WHERE columnname LIKE '%'
17596 rows in set (0.20 sec)
Seems that MySQL query engine optimizes things like '0'='0' and LIKE '%' away.
精彩评论