CMS Made Simple Forums

Posted: **Thu Jun 21, 2012 12:59 pm**

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:

install/upgrades/upgrade.35.to.36.php wrote:$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);

install/schemas/schema.php wrote: $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

Posted: **Thu Jun 21, 2012 2:07 pm**

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

Posted: **Thu Jun 21, 2012 7:14 pm**

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

CMS Made Simple Forums

[invalid] Content index created on wrong column?

[invalid] Content index created on wrong column?

Re: Content index created on wrong column?

Re: [invalid] Content index created on wrong column?