[#25293] - Database error after new installation of current staging
- Closed
- 23 Jun 2019
- Medium
- Build: staging
- # 25293
Steps to reproduce the issue
Install current staging and check database view in backend.
Expected result
No database errors.
Actual result
1 error: Table 'xyz_template_styles' should not have index 'idx_home'. (From file 3.9.8-2019-06-15.sql.)
System information (as much as possible)
Current staging or nightly build 3.9.9-dev which includes merged PR #24595 .
MySQL database.
Additional comments
Problem is that database schema checker can't handle changes of indexes like done with PR #24595 in files 3.9.8-2019-06-15.sql in a proper way. Maybe it helps to put it into separate files.
joomla-cms-bot
- | Labels |
Added:
?
|
||
franz-wohlkoenig
- | Labels |
Added:
J3 Issue
|
||
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:
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:
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.
| Status | New | ⇒ | Closed |
| Closed_Date | 0000-00-00 00:00:00 | ⇒ | 2019-06-23 11:42:06 |
| Closed_By | ⇒ | richard67 |
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
done and thank you to dig in this
Ping @alikon?