Re: Partition using non primary key
Posted by: Rick James
Date: April 05, 2012 07:57PM
PRIMARY KEY (`id`),
KEY `country_id` (`country_id`), -- DROP
KEY `country_id_2` (`country_id`,`cdate`), -- DROP
KEY `country_id_3` (`country_id`,`cdate`,`udid`),
KEY `udid` (`udid`), -- DROP
KEY `udid_2` (`udid`,`cdate`), -- DROP
KEY `udid_4` (`udid`,`cdate`,`country_id`),
KEY `udid_3` (`udid`,`country_id`,`cdate`),
KEY `cdate` (`cdate`), -- DROP
KEY `cdate_3` (`cdate`,`udid`), -- DROP
KEY `cdate_5` (`cdate`,`udid`,`country_id`)
KEY `cdate_2` (`cdate`,`country_id`), -- DROP
KEY `cdate_4` (`cdate`,`country_id`,`udid`),
Each "DROP" index is covered by the next index; you may as well get rid of them.
> i could not create a partiton on a non primary key column
Please elaborate on "could not".
Perhaps it is saying the "every UNIQUE (that includes PRIMARY) KEY must include the PARTITION key. If that is is, then simply add it onto the _end_ of the PRIMARY KEY.
This combination works ok:
id ... AUTO_INCREMENT
PRIMARY KEY(id, foo)
If you make the datatypes smaller, the table will be smaller, thereby perform better...
> `session_start_time` bigint(20) NOT NULL,
Are you using Java? Otherwise, why BIGINT?
> `os_version` int(11) NOT NULL,
Are you expecting 4 billion versions? Perhaps SMALLINT UNSIGNED (max 65K) would suffice?
> `lat` float NOT NULL DEFAULT '0',
This gives you resolution of 1.7 m or 5.6 ft. Possibly overkill?
> `country_id` int(11) NOT NULL,
INT takes 4 bytes. There are standard country codes that take 2 (or 3) letters. This would be smaller, and arguably better:
CHAR(2) NOT NULL CHARSET ascii
> `ip` int(32) NOT NULL,
This will not work for two reasons:
* It is SIGNED
* It can't handle the new IPv6. Recommend VARBINARY(39) (human readable) or BINARY(16) (packed a la your INT).
> `cdate` date NOT NULL DEFAULT '0000-00-00',
> `ctime` time NOT NULL DEFAULT '00:00:00',
It is almost never a good idea to split date and time. Anyway DATE is 3 bytes, TIME is 3. DATETIME is 8, TIMESTAMP is 4.
You don't have a million rows yet? Will it grow a lot? What do you hope to gain via PARTITIONing?