开发者

Does an additional "AND `columnname` LIKE '%'" hurt performance?

simple question: Does adding something like this to a query hurt performance in mysql or does it quickly get optimized away?

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜