Database indexes - almost none?

[luya-bot]

This issue has originally been reported by @lubosdz at luyadev/luya#1775.
Moved here by @nadar.

---

Hi guys,
I just evaluated luya first install and I would like to suggest optimizing DB performance by adding indexes on at least ID-related columns. There are so many missing ... e.g. did you stress tested admin_user_group with 100.000 users and few tens of groups? This table will become slow query without indexes. Same for many other tables ... or is there some extra SQL to add indexes? I executed all migrations ...

[arollmann]

In the same vein, foreign key constraints are missing (eg in the cms_nav_permission table, and other relational tables).

[lubosdz]

Regarding foreign key constraints are missing ---
even though it might be true & many developers tend to use FK heavily, IME (in my experience) they are often pain in the ass when porting application between databases - e.g. make data import sometime painful, are much harder to test with cross-database support. Since Yii2 supports all necessary events (afterInsert, before/afterDelete, ...) I usually prefer relying on those events rather than on foreign keys. FKs are quick & cheap solution bringing in other issues. If, however, Luya has sufficient cross-RDBMS testing infrastructure, then FKs shoud not be a problem.

[arollmann]

Cannot really agree. Foreign keys and referential integrity constraints play an essential role in database design. A professional tool like LUYA should consider this (as you said about indexes). The storage engine should support foreign keys (use InnoDB tables in MySQL, or use PostgreSQL...). Furthermore, CRUD generator can automatically create relations for generated models if corresponding tables in your DB have foreign keys. And since v2.0.8 the Yii migration tool supports foreign keys. If used correctly, it can be independent of specific DBMS.

[nadar]

@arollmann @lubosdz Thanks for the discussion. Personally i am fine with FK's for tables where this makes sense. We would need to update all the models in order to support FK's this would take some time, but its worth it.

Regarding this issue i will track the sql statements of the admin in order to make an index suggestions (like we did here: luyadev/luya-module-cms#84).

Maybe for FK's we should create another issue, in order to make detailed plans.

[nadar]

I added some basic fk fields indexes and a few more for given queries. For more detailed inputs please make another issue with specific informations. Thanks everyone for making LUYA better!