[#35707] - [com_search] Performance Improvement: Removes unnecessary Lower Checks
- Closed
- 30 Sep 2021
- Medium
- Build: 3.10-dev
- # 35707
- Diff
- AndySDH:patch-20
User tests: Successful: Unsuccessful:
Summary of Changes
This is a database performance improvement queries used by com_search.
Converting strings to LOWER in the LIKE statements make the queries slower and heavier. Plus, indexes are not used when queries have 'LOWER', which makes things worse.
Those checks are unnecessary as LIKE comparison in MySQL are already case insensitive.
https://tecadmin.net/mysql-case-sensitive-search-with-like/
Actual result BEFORE applying this Pull Request
Queries are a lot slower and indexes are not used.
Expected result AFTER applying this Pull Request
Queries are a lot faster. I've done some tests and I've seen some queries go from:
0.5830 seconds
to:
0.0005 seconds
with the same exact results.
Documentation Changes Required
None
| Status | New | ⇒ | Pending |
joomla-cms-bot
- | Category | ⇒ | Front End Plugins |
Good point. I'm not familiar with those other database types, so I might have overlooked that =/
Such things can be investigated by checking database documentation, and it is assumed that people who make pull requests for database stuff do that.
database documentation
I actually checked that but I couldn't find anything regarding the use of LOWER. Anyway, if this really breaks things, I guess nevermind then.
Do a Google search for „PostgreSQL like case insensitive“ and you will find several results which show that in PostgreSQL „like“ is case sensitive and that there is an „ilike“ operator for case insensitive „like“. I don’t know how it is in MS SQL but it should be same easy to find out.
| Status | Pending | ⇒ | Closed |
| Closed_Date | 0000-00-00 00:00:00 | ⇒ | 2021-09-30 20:18:36 |
| Closed_By | ⇒ | HLeithner | |
| Labels |
Added:
?
|
||
@richard67 please claim down, it's hard to track 3 database engines.
I'm closing this, thanks @AndySDH for your trying to improve joomla.
Re-thinking this, @HLeithner & @richard67
Wouldn't it be a good idea to do an if condition based on database type, if possible?
So if database type is MySQL, we can omit the LOWER stuff for better performance?
As you can read from the opening post, the performance benefit would be very significant.
Are you sure that the performance benefit comes from your changes and not from MySQL query cache or similar things?
You are aware of the fact that Joomla has to support PostgreSQL databases, too, and in case of Joomla 3 even MS SQL Server?
At least on PostgreSQL this PR will not work.