possible db optimisations

hi,

i’ve just been looking at several tables - i think it’d be easy to make them more efficient:

document_groups
site_tmplvar_contentvalues

both of the above have primary keys called ’id’. unless i’ve missed something in the code, it’d be much more efficient to use composite primary keys. this would have a dual benefit of removing a ton of useless information, and it would speed up retrieval of records via selects, and probably other CRUD operations too.

document_groups should have a composite pk of (document, document_group), with an extra index on document_group.
site_tmplvar_contentvalues should have a composite pk of (contentid, tmplvarid)

i checked the revolution code and these fields are represented by children of xPDOSimpleObjects.

it should be simple to change them to xPDOObjects, and apply alteration to a user’s database during an upgrade of evolution to revolution which drops these columns and applies a new composite primary key.

would there be any problem making these changes? any thoughts?

Quote from: redman at Feb 03, 2009, 04:13 AM

i’ve just been looking at several tables - i think it’d be easy to make them more efficient:

document_groups
site_tmplvar_contentvalues

both of the above have primary keys called ’id’. unless i’ve missed something in the code, it’d be much more efficient to use composite primary keys. this would have a dual benefit of removing a ton of useless information, and it would speed up retrieval of records via selects, and probably other CRUD operations too.

document_groups should have a composite pk of (document, document_group), with an extra index on document_group.
site_tmplvar_contentvalues should have a composite pk of (contentid, tmplvarid)

i checked the revolution code and these fields are represented by children of xPDOSimpleObjects.

it should be simple to change them to xPDOObjects, and apply alteration to a user’s database during an upgrade of evolution to revolution which drops these columns and applies a new composite primary key.

would there be any problem making these changes? any thoughts?

No, I don’t think these changes are going to make it into 2.0, because it is a stepping stone release that tries not to break data structures for components that are written against the legacy tables. And in any case, it is my opinion that a table should always have a single primary key column; it may sometimes make management less efficient, but there is not likely any other affect on efficiency, though I suppose using compound primary keys could actually be less efficient in some cases, as well as more complex and more prone to table damage.

Quote from: OpenGeek at Feb 03, 2009, 09:05 AM

No, I don’t think these changes are going to make it into 2.0, because it is a stepping stone release that tries not to break data structures for components that are written against the legacy tables.

ok, fair enough
Quote from: OpenGeek at Feb 03, 2009, 09:05 AM

And in any case, it is my opinion that a table should always have a single primary key column; it may sometimes make management less efficient, but there is not likely any other affect on efficiency, though I suppose using compound primary keys could actually be less efficient in some cases, as well as more complex and more prone to table damage.

using compound primary keys could also be more efficient - as long as they’re defined in the right order for the queries typically being executed against a table.

with myisam drawbacks to having a dedicated primary key in these cases could include more difficult table management, and the fact that it’s possible to insert duplicate data that could logically break data structures. however, with innodb especially it seems it’s possible to gain improvements in performance by using composite keys carefully:

http://www.mysqlperformanceblog.com/2008/12/22/high-performance-click-analysis-with-mysql/

i’m sure this is worth considering for a future release...

Agreed, and if you notice, the new intersection tables in Revo do use compound primary keys where appropriate. We’ll bring them all in-line for 2.1 when we also address the unix timestamp fields, introduce modContent, modRevision, and some supporting classes/tables, and other changes that can be better managed once we get everyone using the new model API (i.e. fewer or more isolated dependencies on direct SQL calls).

ok cool