Re: Partitioning Table - GPS Data
My select is :
SELECT ddl_ct2 + 128
FROM gps_data
WHERE COORDINATE_DATE between SUBDATE(#currentCoordinateDate#,2) AND #currentCoordinateDate#
AND (ddl_ten + 128) * 0.5 > 8
AND vei_id = #vehicleID#
ORDER BY ddl_dh DESC
LIMIT 5
// I use that to get the last 5 coordinates from some vehicle. I need those data every minute to do some math
I tried did that, please tell me what you think about it
CREATE TABLE `gps_test` (
`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',
`MONTH` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`,`VEI_ID`,`COORDINATE_DATE `,`DDL_CTM`,`MONTH`),
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
PARTITION BY RANGE (`MONTH`)(
PARTITION p1 VALUES LESS THAN (2),
PARTITION p2 VALUES LESS THAN (3),
PARTITION p3 VALUES LESS THAN (4),
PARTITION p4 VALUES LESS THAN (5),
PARTITION p5 VALUES LESS THAN (6),
PARTITION p6 VALUES LESS THAN (7),
PARTITION p7 VALUES LESS THAN (8),
PARTITION p8 VALUES LESS THAN (9),
PARTITION p9 VALUES LESS THAN (10),
PARTITION p10 VALUES LESS THAN (11),
PARTITION p11 VALUES LESS THAN (12),
PARTITION p12 VALUES LESS THAN (13)
);
Then i inserted :
INSERT INTO `dado_lido_teste` (`ID`, `VEI_ID`, `COORDINATE_DATE`, `DDL_PRO`, `DDL_INF`, `DDL_TPL`, `DDL_LAT`, `DDL_LNG`, `DDL_DIR`, `DDL_VEL`, `DDL_BYT1`, `DDL_BYT2`, `DDL_BYT3`, `DDL_ODM`, `DDL_HRM`, `DDL_TEN`, `DDL_TMP`, `DDL_MROT`, `DDL_MCON`, `DDL_EVM`, `DDL_EVT`, `DDL_CT1`, `DDL_CT2`, `DDL_CT3`, `DDL_CTM`, `PTP_ID`, `PTP_IDR`, `DDL_PDIS`, `DDL_PDIR`, `DDL_DHS`, `ddl_mod`, `ARE_ID`, `CER_ID`, `DDL_CSM`, `mot_id`, `DDL_BYT4`, `DDL_BYT5`, `DDL_BYT6`, `DDL_BYT7`, `DDL_BYT8`, `DDL_BYT9`)
VALUES
(48699, 2617, '2011-09-01 16:18:55', 11562, -128, 0, -3.734751666666671, -38.46937500000001, 0, 2186, -125, -32, -124, -2141446048, -32742, -106, -42, -32768, -32768, 0, -128, 6, -114, -101, -10476, 1815, 0, 7996, 6, '2011-12-01 13:16:43', 513565, 0, 0, -32768, 0, -128, -128, -128, -120, -30, -82);
Then i did :
explain partitions select * from gps_test where COORDINATE_DATE between '2011-09-01 15:18:55' and '2011-09-01 17:18:55' and month = MONTH('2011-09-01 17:18:55');
and partitions scanned was just p9.
About advices you said in ddl_lat,ddl_lng,etc Im gonna do that.
Thanks in Advance!
Edited 1 time(s). Last edit at 12/02/2011 08:53AM by Daniel Alves.