MySQL Forums
Forum List  »  Partitioning

Partitioning Table - GPS Data
Posted by: Daniel Alves
Date: November 30, 2011 01:53PM

Hello Guys,

I have an application that handles gps coordinates. The main table that store coordinates has a large number of rows, so i decided use mysql partition. I have used the function MONTH() and set up to 12 partitions. Below is the create syntax. Most of my selects use that column COORDINATE_DATE. If i insert some gps coordinate for a month X and if i do 'EXLAIN PARTITIONS SELECT * FROM gps_data where COORDINATE_DATE BETWEEN 'FIRST DAY OF MONTH X' AND 'LAST DAY OF MONTH X' i can see that my select scan all partitions. How can i do this better? I wanna best perfomance for this table. Could you guys help me to improve that partition table? Thanks!

CREATE TABLE `gps_data` (
`ID` int(64) NOT NULL AUTO_INCREMENT,
`VEI_ID` int(10) unsigned NOT NULL DEFAULT '0',
`COORDINATE_DATE` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`DDL_PRO` smallint(5) unsigned NOT NULL DEFAULT '0',
`DDL_INF` tinyint(4) NOT NULL DEFAULT '-128',
`DDL_TPL` tinyint(3) unsigned NOT NULL DEFAULT '0',
`DDL_LAT` double NOT NULL DEFAULT '0',
`DDL_LNG` double NOT NULL DEFAULT '0',
`DDL_DIR` tinyint(3) unsigned NOT NULL DEFAULT '0',
`DDL_VEL` smallint(5) unsigned NOT NULL DEFAULT '0',
`DDL_BYT1` tinyint(4) NOT NULL DEFAULT '-128',
`DDL_BYT2` tinyint(4) NOT NULL DEFAULT '-128',
`DDL_BYT3` tinyint(4) NOT NULL DEFAULT '-128',
`DDL_ODM` int(11) NOT NULL DEFAULT '-2147483648',
`DDL_HRM` smallint(6) NOT NULL DEFAULT '-32768',
`DDL_TEN` tinyint(4) NOT NULL DEFAULT '-128',
`DDL_TMP` tinyint(4) NOT NULL DEFAULT '-128',
`DDL_MROT` smallint(6) NOT NULL DEFAULT '-32768',
`DDL_MCON` smallint(6) NOT NULL DEFAULT '-32768',
`DDL_EVM` tinyint(4) NOT NULL DEFAULT '-128',
`DDL_EVT` tinyint(4) NOT NULL DEFAULT '-128',
`DDL_CT1` smallint(6) NOT NULL DEFAULT '-128',
`DDL_CT2` smallint(6) NOT NULL DEFAULT '-128',
`DDL_CT3` smallint(6) NOT NULL DEFAULT '-128',
`DDL_CTM` smallint(6) NOT NULL DEFAULT '-32768',
`PTP_ID` int(10) unsigned NOT NULL DEFAULT '0',
`PTP_IDR` int(10) unsigned NOT NULL DEFAULT '0',
`DDL_PDIS` float NOT NULL DEFAULT '0',
`DDL_PDIR` tinyint(3) unsigned NOT NULL DEFAULT '0',
`DDL_DHS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`ddl_mod` int(10) unsigned NOT NULL DEFAULT '0',
`ARE_ID` smallint(5) unsigned NOT NULL DEFAULT '0',
`CER_ID` smallint(5) NOT NULL DEFAULT '0',
`DDL_CSM` smallint(6) NOT NULL DEFAULT '-32768',
`mot_id` smallint(6) unsigned NOT NULL DEFAULT '0',
`DDL_BYT4` tinyint(4) NOT NULL DEFAULT '-128',
`DDL_BYT5` tinyint(4) NOT NULL DEFAULT '-128',
`DDL_BYT6` tinyint(4) NOT NULL DEFAULT '-128',
`DDL_BYT7` tinyint(4) NOT NULL DEFAULT '-128',
`DDL_BYT8` tinyint(4) NOT NULL DEFAULT '-128',
`DDL_BYT9` tinyint(4) NOT NULL DEFAULT '-128',
PRIMARY KEY (`ID`,`VEI_ID`,`COORDINATE_DATE `,`DDL_CTM`),
KEY `IDX_DDL_DHS` (`DDL_DHS`),
KEY `IDX_DDL_PTP_IDR` (`VEI_ID`,`COORDINATE_DATE `,`PTP_IDR`),
KEY `IDX_DDL_DH` (`COORDINATE_DATE `)
) ENGINE=MyISAM AUTO_INCREMENT=9805 DEFAULT CHARSET=latin1
PARTITION BY HASH ( MONTH(COORDINATE_DATE))
PARTITIONS 12 ;



Edited 1 time(s). Last edit at 11/30/2011 03:18PM by Daniel Alves.

Options: ReplyQuote


Subject
Views
Written By
Posted
Partitioning Table - GPS Data
3126
November 30, 2011 01:53PM
1512
November 30, 2011 03:15PM
1820
December 01, 2011 09:57PM
1418
December 02, 2011 08:51AM
1556
December 03, 2011 10:26AM
1481
December 05, 2011 05:43AM
1736
December 06, 2011 08:46PM
1404
December 02, 2011 08:52AM


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.