Ping @alikon 👅

i confirm your findings.... not sure if splitting sql in more files can help... but ... if you have some free time, please explore it .... i'm currently busy in moving home, so i cannot be responsive like i should do

Will work on it today or tomorrow. Just wanted to leave an issue so others know it is already reported.

@alikon The problem is that the database schema checker can't really handle remove and later add back index with same name, and it would be not easy to change that.

Question is: Do we really need that index for the home column?

Or isn't it true that we in sql selects always ask only for value of home column for a particular client_id?

In this case we would have a combined index on columns client_id, home, so we could add that new index instead of adding back the old index one home column, and that new index would have a different name anyway so all would be ok.

Update: Or we just remove that index if not used or not performance relevant anyway. I guess there are no sites having hundreds of template styles.

How can we easily find out if that index is useful or not?

@alikon I've checked where clauses of select statements to the #__template_styles table and have seen that an index on the single column home does not really make sense.

But I saw that a combined index on columns client_id and home makes sense, see e.g. here:

• https://github.com/joomla/joomla-cms/blob/staging/administrator/components/com_templates/models/style.php#L629-L634
• https://github.com/joomla/joomla-cms/blob/staging/administrator/components/com_templates/tables/style.php#L94-L99

In addition, an index on the single column client_id makes sense, but it does not exist yet, see e.g. here:

https://github.com/joomla/joomla-cms/blob/staging/administrator/components/com_templates/models/styles.php#L117-L118

Also I've checked that the existing index on the single column template still makes sense, so we can keep it, see e.g. here:

https://github.com/joomla/joomla-cms/blob/staging/administrator/components/com_templates/models/template.php#L1228-L1229

Finally I've found a typo you made in the schema update for postgresql in your PR #24595 (ALTER TABLE "#__template_style" instead of ALTER TABLE "#__template_styles").

I will make PR to fix all that, and then this issue should be fixed, too.

Closing as having PR #25299 .

The problem is that the database schema checker can't really handle remove and later add back index with same name, and it would be not easy to change that.

my fault i I thought it was possible.... surely i remeber wrong .....

and yes indexes are not so much usefull for this table in general .... cause i think that this table never reach a cardinality were the QueryPlanner should find more "economic" to use an index instead of a table scan of few rows....

btw for a good db schema design we can use an index for the used where clause like you did

@alikon Could you the test or - if not much time - review the PR for this issue, #25299 ? Or both, test and review, if enough time?

done and thank you to dig in this