[invalid] Content index created on wrong column?

[fredp]

Hi,

While investigating a possible upgrade issue in beta1, I noticed something curious about the new cms_index_content_by_idhier index creation code. I could be wrong, but I'm guessing the following code should be using the column id_hierarchy instead of hierarchy:

$sqlarray = $dbdict->CreateIndexSQL($db_prefix.'index_content_by_idhier', $db_prefix."content", 'hierarchy');
$return = $dbdict->ExecuteSQLArray($sqlarray);
$ado_ret = ($return == 2) ? ilang('done') : ilang('failed');
echo ilang('install_creating_index', 'content', $ado_ret);

$sqlarray = $dbdict->CreateIndexSQL($db_prefix.'index_content_by_hierarchy', $db_prefix."content", 'hierarchy');
$return = $dbdict->ExecuteSQLArray($sqlarray);
$ado_ret = ($return == 2) ? ilang('done') : ilang('failed');
echo ilang('install_creating_index', 'content', $ado_ret);

$sqlarray = $dbdict->CreateIndexSQL($db_prefix.'index_content_by_idhier', $db_prefix."content", 'hierarchy');
$return = $dbdict->ExecuteSQLArray($sqlarray);
$ado_ret = ($return == 2) ? ilang('done') : ilang('failed');
echo ilang('install_creating_index', 'content', $ado_ret);

Hope this helps,
fredp

[calguy1000]

No, it's not the wrong column. sorry.

[fredp]

Ok, I stand corrected. But, I am curious, is there some advantage to having two identical single-column indexes on the same column, hierarchy?

• cms_index_content_by_hierarchy
• cms_index_content_by_idhier

I'm more familiar with Oracle than MySQL, but I would think duplicate indexes would generally slow things down on the db server:

• More disk space for db and db backups
• More query plans to consider during optimizer phase
• Extra work on inserts, updates, and deletes

Thanks

Edit: Added reasons for my concern