The Ultimate Guide to High Availability with MariaDB

Comments - Index with up to 24 columns

5 years ago Beno!t P.
Re: Index with up to 24 columns

Hello Ian,

Here's the create table statement for the first table:

CREATE TABLE `stats_cb_account_201305` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `day` tinyint(2) unsigned zerofill NOT NULL,
 `id_product` tinyint(3) unsigned NOT NULL,
 `id_cb_offer_type` char(10) NOT NULL,
 `id_cb_offer` smallint(5) unsigned NOT NULL,
 `id_cb_biller` char(5) NOT NULL,
 `id_group_cb_biller` smallint(4) unsigned NOT NULL DEFAULT '0',
 `id_tool_type` varchar(10) NOT NULL,
 `id_tool` mediumint(8) NOT NULL,
 `id_account` mediumint(5) unsigned NOT NULL,
 `id_cb_program_type` tinyint(3) unsigned NOT NULL,
 `id_isp` mediumint(7) NOT NULL DEFAULT '1',
 `id_connection_type` smallint(4) unsigned NOT NULL DEFAULT '1',
 `id_device_system` mediumint(7) NOT NULL DEFAULT '1',
 `id_device_type` mediumint(7) NOT NULL DEFAULT '1',
 `id_device_vendor` mediumint(7) NOT NULL DEFAULT '1',
 `id_device_model` mediumint(7) NOT NULL DEFAULT '1',
 `id_tool_cat` smallint(4) unsigned NOT NULL DEFAULT '0',
 `tracker` varchar(120) NOT NULL,
 `tracker_int` varchar(120) NOT NULL,
 `referer` varchar(255) NOT NULL,
 `referer_orig` varchar(255) NOT NULL,
 `referer_orig_kw` varchar(100) NOT NULL,
 `language` char(2) NOT NULL,
 `country` char(2) NOT NULL,
 `nb_freetrial` smallint(4) unsigned DEFAULT '0',
 `nb_suite_freetrial` smallint(4) unsigned DEFAULT '0',
 `nb_no_suite_freetrial` smallint(4) unsigned DEFAULT '0',
 `nb_trial` smallint(4) unsigned DEFAULT '0',
 `nb_suite_trial` smallint(4) unsigned DEFAULT '0',
 `nb_no_suite_trial` smallint(4) unsigned DEFAULT '0',
 `nb_abo_direct` smallint(4) unsigned DEFAULT '0',
 `nb_rebill` smallint(4) unsigned DEFAULT '0',
 `nb_no_rebill` smallint(4) unsigned DEFAULT '0',
 `nb_oneshot` smallint(4) unsigned DEFAULT '0',
 `nb_chargeback` smallint(4) unsigned DEFAULT '0',
 `total_freetrial` mediumint(6) DEFAULT '0',
 `total_suite_freetrial` mediumint(6) DEFAULT '0',
 `total_no_suite_freetrial` mediumint(6) DEFAULT '0',
 `total_trial` mediumint(6) DEFAULT '0',
 `total_suite_trial` mediumint(6) DEFAULT '0',
 `total_no_suite_trial` mediumint(6) DEFAULT '0',
 `total_abo_direct` mediumint(6) DEFAULT '0',
 `total_rebill` mediumint(6) DEFAULT '0',
 `total_no_rebill` mediumint(6) DEFAULT '0',
 `total_oneshot` mediumint(6) DEFAULT '0',
 `total_chargeback` mediumint(6) DEFAULT '0',
 `total` mediumint(7) DEFAULT '0',
 PRIMARY KEY (`id`),
 UNIQUE KEY `unique_stats` (`day`,`id_product`,`id_cb_offer_type`,`id_cb_offer`,`id_tool_type`,`id_cb_biller`,`id_group_cb_biller`,`id_tool`,`id_account`,`id_cb_program_type`,`language`,`country`,`tracker`,`tracker_int`,`referer`,`referer_orig`,`referer_orig_kw`,`id_tool_cat`,`id_isp`,`id_connection_type`,`id_device_system`,`id_device_type`,`id_device_vendor`,`id_device_model`),
 KEY `key_account_day` (`id_account`,`day`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

And for the second one:

	
CREATE TABLE `stats_cb_account_201306` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `day` tinyint(2) unsigned zerofill NOT NULL,
 `id_product` tinyint(3) unsigned NOT NULL,
 `id_cb_offer_type` char(10) NOT NULL,
 `id_cb_offer` smallint(5) unsigned NOT NULL,
 `id_cb_biller` char(5) NOT NULL,
 `id_group_cb_biller` smallint(4) unsigned NOT NULL DEFAULT '0',
 `id_tool_type` varchar(15) NOT NULL,
 `id_tool` mediumint(8) NOT NULL,
 `id_account` mediumint(5) unsigned NOT NULL,
 `id_cb_program_type` tinyint(3) unsigned NOT NULL,
 `id_isp` mediumint(7) NOT NULL DEFAULT '1',
 `id_connection_type` smallint(4) unsigned NOT NULL DEFAULT '1',
 `id_device_system` mediumint(7) NOT NULL DEFAULT '1',
 `id_device_type` mediumint(7) NOT NULL DEFAULT '1',
 `id_device_vendor` mediumint(7) NOT NULL DEFAULT '1',
 `id_device_model` mediumint(7) NOT NULL DEFAULT '1',
 `id_tool_cat` smallint(4) unsigned NOT NULL DEFAULT '0',
 `tracker` varchar(255) NOT NULL,
 `tracker_int` varchar(255) NOT NULL,
 `referer` varchar(255) NOT NULL,
 `referer_orig` varchar(255) NOT NULL,
 `referer_orig_kw` varchar(100) NOT NULL,
 `language` char(2) NOT NULL,
 `country` char(2) NOT NULL,
 `nb_freetrial` smallint(4) unsigned DEFAULT '0',
 `nb_suite_freetrial` smallint(4) unsigned DEFAULT '0',
 `nb_no_suite_freetrial` smallint(4) unsigned DEFAULT '0',
 `nb_trial` smallint(4) unsigned DEFAULT '0',
 `nb_suite_trial` smallint(4) unsigned DEFAULT '0',
 `nb_no_suite_trial` smallint(4) unsigned DEFAULT '0',
 `nb_abo_direct` smallint(4) unsigned DEFAULT '0',
 `nb_rebill` smallint(4) unsigned DEFAULT '0',
 `nb_no_rebill` smallint(4) unsigned DEFAULT '0',
 `nb_oneshot` smallint(4) unsigned DEFAULT '0',
 `nb_chargeback` smallint(4) unsigned DEFAULT '0',
 `total_freetrial` mediumint(6) DEFAULT '0',
 `total_suite_freetrial` mediumint(6) DEFAULT '0',
 `total_no_suite_freetrial` mediumint(6) DEFAULT '0',
 `total_trial` mediumint(6) DEFAULT '0',
 `total_suite_trial` mediumint(6) DEFAULT '0',
 `total_no_suite_trial` mediumint(6) DEFAULT '0',
 `total_abo_direct` mediumint(6) DEFAULT '0',
 `total_rebill` mediumint(6) DEFAULT '0',
 `total_no_rebill` mediumint(6) DEFAULT '0',
 `total_oneshot` mediumint(6) DEFAULT '0',
 `total_chargeback` mediumint(6) DEFAULT '0',
 `total` mediumint(7) DEFAULT '0',
 PRIMARY KEY (`id`),
 UNIQUE KEY `unique_stats` (`day`,`id_product`,`id_cb_offer_type`,`id_cb_offer`,`id_tool_type`,`id_cb_biller`,`id_group_cb_biller`,`id_tool`,`id_account`,`id_cb_program_type`,`language`,`country`,`tracker`,`tracker_int`,`referer`,`referer_orig`,`referer_orig_kw`,`id_tool_cat`,`id_isp`,`id_connection_type`,`id_device_system`,`id_device_type`,`id_device_vendor`,`id_device_model`),
 KEY `key_account_day` (`id_account`,`day`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Changes are the following:

  • id_tool_type changed from VARCHAR(10) to VARCHAR(15)
  • tracker changed from VARCHAR(120) to VARCHAR(255)
  • tracker_int changed from VARCHAR(120) to VARCHAR(255)

Both tables worked under MariaDb 5.5, 10.0, 10.1, but the 2nd one fails when upgrading to 10.2.

Thank you, Ben

 
4 years, 11 months ago Ian Gilfillan
Re: Index with up to 24 columns [Answer]

How did you get the second table working on the older versions? Running the above on MariaDB 5.5 in my environment returns 

ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

If this is related to your error, you can overcome this by using a portion of a column for an index, for example UNIQUE KEY `unique_stats` ... `tracker_int`(10), But am still interested in more details on your setup as to how it worked in the first place.

 
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.