There are two problems with the database prosody creates in MySQL.
1. "key" is used as a column name in the both tables. "key" is a reserved MySQL word and should not be used as a column name.
2. There is no primary or unique column in the table "prosody." A column should be added to uniquely identify each row, reducing ambiguity and allowing easier modification to the table.
Fixing either of these issues will both improve performance and prevent undesired behavior.
Hi Drew, thanks for the report!
Regarding the column name... yes, it's a reserved word in MySQL. But that's only a problem if you don't quote column names. This is from the MySQL documentation:
"Reserved words are permitted as identifiers if you quote them as described in Section 10.2, “Schema Object Names”: "
Quoting is always a sensible choice in any case, because the set of reserved words may change between releases (rarely, but it can happen). I don't see any performance issues caused by this. I'm not aware of any "undesired behaviour", as we always use prepared statements and quote column names (whether currently reserved words or not).
Regarding the lack of a primary/unique column... I know this is a very common schema design, but it wouldn't have any meaning or use in the schema we are using today. I totally understand that our schema is not a traditional design, and this may surprise people expecting to see one. However the current design was chosen for a variety of reasons, and has served us fine for a number of years now.
Regarding performance, we create indexes for the columns that we query on, which is a sensible thing to do, whether you have a primary key in your schema or not. When using a primary key that is used for queries, you can often get away with not creating indexes.
Regarding "undesired behaviour", again I don't see any problems.
I assure you that we designed our schema with many considerations. If you have evidence of performance problems or undesired behaviour, I'm all ears.
On a final note: our storage system is plugin-based. If you/anyone wants to make a storage plugin that uses a different kind of schema, that's totally possible and we would help guide you with that. Such a module would be welcomed in our community modules repository, where it is easily accessible for others to use in their Prosody deployments if they choose.