MySQL Forums
Forum List  »  Partitioning

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
3654
April 04, 2012 10:57PM
Re: Partition using non primary key
2174
April 05, 2012 07:57PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.