Partitioning Table - GPS Data
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.