CODEWITHSUNDEEP
phpsql-server
Menas Eru
Menas Eru

Reputation: 118

To lower case in PHP or MS SQL

Kind of strange question, I assume PHP is faster but maybe someone knows better.

Question is the following:

A table with approximately 10k rows, varchar(550) field. Field is indexed.

Select that retrieves data has a lot of OR-cases and does a LIKE scan for every condition.

Query is run from PHP script.

Would it be faster to lowercase search substring before running query in script?

e.g.

SELECT * FROM table WHERE field LIKE LOWER('%substring_1%') OR field LIKE LOWER ('%substring_2%') OR ... field LIKE LOWER ('%substring_100%')

Or something similar to:

//$substr_array contains all substrings
$condition = "";
foreach ($substr_array as $substring) {
 $condition .= "field LIKE '%".mb_strtolower($substring, "UTF-8")."%' OR ";
}
$condition = substr($condition, 0, -4);

$query = "SELECT * FROM table WHERE $condition";
$result = query($query);

Upvotes: 0

Views: 232

Answers (1)

Ares Draguna
Ares Draguna

Reputation: 1661

This is an opinion based question, but a question nonetheless. So here goes my answer:

SELECT * 
FROM table 
WHERE field LIKE LOWER('%substring_1%') 
OR field LIKE LOWER ('%substring_2%') 
OR ... field LIKE LOWER ('%substring_100%')

this scenario depends on how well you DB is structured. If you don't have a lot of records in your database then it should not matter giving the "outstanding" performances that we have nowadays...

However: "Another way for case-insensitive matching is to use a different “collation”. The default collations used by SQL Server and MySQL do not distinguish between upper and lower case letters—they are case-insensitive by default." (extract from use-the-index-luke)

You should read that article, it's very good.

Now down to the second part. The most expensive, as a performance cost, in the PHP code will be the foreach() statement. HERE you can find more info on how much time does a foreach() loop takes and a very good comparison with for() and while() loops. It's good to know them. So basically, what you do here is first take some time preparing the query, which costs time, and then actually running it, which costs time.

In conclusion, the real battle here will be: Which is faster? A foreach() or the LOWER() function in SQL.

Providing that I gave you some good materials to read in, and form your own conclusions, in my opinion, I think that the best way to go about this is to just leave the query and drop the foreach(), because queryes are the most cost-expensive and adding a foreach() will just slow you performance down. At least you cut something out of the picture. :)

I really hope that this helps you!
Keep on coding!
Ares.

Upvotes: 2

Related Questions