[#36433] - Add KEY to table structure to prevent filesort
- Closed
- 11 Jan 2022
- Medium
- Build: 4.0-dev
- # 36433
- aDaneInSpain:patch-4
This is more to open a discussion, I guess. When reviewing performance issues with a site, I noticed that even loading the core language selector triggers a filesort in MariaDB which is not ideal. As there will only ever be a few languages on a site this will most likely never be a huge performance issue, but every little optimization we can do, we should do, right? In order to not have a filesort for the query:
SELECT *
FROM #__languages
WHERE published=1
ORDER BY ordering ASC
We need to add a composite key for both published and ordering.
This does speed up loading very minutely and if done throughout the app so that at least there are keys for core functionality would have a real impact (although small) on performance.
Summary of Changes
Add KEY to table structure to prevent filesort
This will also need to be added to the upgrade SQL's but I guess @richard67 will be able to comment more on that.
docs: https://dev.mysql.com/doc/refman/8.0/en/order-by-optimization.html
and if this is needed on this table, maybe its needed on other tables too?
It needs to do the same for PostgreSQL, and it needs to add an update script for each MySQL and PostgreSQL.
This also breaks the unit tests... because a comma is missing...
i don't think this is needed cause with the low cardinality of these table items, the db-engine will always perform a table scan, so PK index apart, no need for other indexes
joomla-cms-bot
- | Category | ⇒ | SQL Installation |
i don't think this is needed cause with the low cardinality of these table items, the db-engine will always perform a table scan, so PK index apart, no need for other indexes
This is the kind of input I was after. I am not a DB optimization expert. But we have found minute performance improvements by adding additional keys to several tables. Milliseconds, but still. From EXPLAIN SELECT I can see that it is using "file_sort" which is my understanding is always ideal to avoid?
From what I can read around the web the general guideline is that you should provide indexes for your select queries to improve performance. The only downside is that inserts, deletes and some updates can take slightly longer (as it needs to also update the index). But that should only be an issue if you have a table with frequent i/o operations which #__languages and #__extensions certainly aren't.
While you are correct... in theory.
the #__languages table will only ever be a handful of languages... so the fraction of a millisecond is hardly worth arguing about.
Now, if the same applies on the content tables, then it would be worth it, as those tables can get very very large.
I have not reviewed everything yet, but I know it applies to the #__extensions table, for instance. And if it does, minutely help on performance (and shows less "file sort" warnings) in the Joomla debug then I think it is worth fixing throughout. I just want to make sure there is no reason NOT to do it.
Would still love to hear if anyone has any reasons for not to do this. If there are no objections, I will modify the pull request to handle all instances in all core tables and implement installation and migration for both MySQL and PostgreSQL.
I am going to close this as I don't believe it has much impact on performance and the debugger in J4 does not show the "explain selects", so it does not jump out at you (which is a shame really) so it will not "scare" users as much.
| Status | New | ⇒ | Closed |
| Closed_Date | 0000-00-00 00:00:00 | ⇒ | 2022-01-11 09:36:33 |
| Closed_By | ⇒ | aDaneInSpain | |
| Labels |
Added:
?
|
||
the debugger in J4 does not show the "explain selects", so it does not jump out at you (which is a shame really)
@aDaneInSpain Actually you have to activate this functionality in the plugin, then you have also an explain to each query.
@aDaneInSpain Actually you have to activate this functionality in the plugin, then you have also an explain to each query.
Oh! Was not aware. Thanks.
Does this change not also have to be made to the postgresql install AND the updates?