MySQL Forums
Forum List  »  Partitioning

Re: Partitioning Table - GPS Data
Posted by: Daniel Alves
Date: December 02, 2011 08:51AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
3026
November 30, 2011 01:53PM
1449
November 30, 2011 03:15PM
1752
December 01, 2011 09:57PM
Re: Partitioning Table - GPS Data
1356
December 02, 2011 08:51AM
1491
December 03, 2011 10:26AM
1408
December 05, 2011 05:43AM
1645
December 06, 2011 08:46PM
1332
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.