CODEWITHSUNDEEP
mysqlquery-optimization
thejh
thejh

Reputation: 45568

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 normally is relatively slow, right?

Upvotes: 3

Views: 165

Answers (5)

Linus Kleen
Linus Kleen

Reputation: 34612

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.

Upvotes: 0

Lukas Eder
Lukas Eder

Reputation: 220762

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.

Upvotes: 4

Novikov
Novikov

Reputation: 4489

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.

Upvotes: -1

Abe Miessler
Abe Miessler

Reputation: 85036

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.

Upvotes: 0

ConsultUtah
ConsultUtah

Reputation: 6809

Yes. LIKE's are very expensive. But as with most things, it depends on the size of your DB.

Upvotes: 0

Related Questions