CMS Made Simple Forums
https://forum.cmsmadesimple.org/

[invalid] Content index created on wrong column?
https://forum.cmsmadesimple.org/viewtopic.php?f=80&t=61625
Page 1 of 1

Author:  fredp [ Thu Jun 21, 2012 12:59 pm ]
Post subject:  [invalid] Content index created on wrong column?

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 \install/upgrades/upgrade.35.to.36.php:
$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 \install/schemas/schema.php:
$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

Author:  calguy1000 [ Thu Jun 21, 2012 2:07 pm ]
Post subject:  Re: Content index created on wrong column?

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

Author:  fredp [ Thu Jun 21, 2012 7:14 pm ]
Post subject:  Re: [invalid] Content index created on wrong column?

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

Page 1 of 1 All times are UTC
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/