4

Back ground: I have 8 million records and i want to speed up the Query time this is my table

    CREATE TABLE vehiclelog3 (
  ID INT(100) NOT NULL AUTO_INCREMENT,

`PNumber` VARCHAR(30) DEFAULT NULL,

  `Date` DATE DEFAULT NULL,

  `Time` TIME DEFAULT NULL,

  `Offset` VARCHAR(45) DEFAULT NULL,

  `Street1` VARCHAR(60) DEFAULT NULL,

  `Street2` VARCHAR(60) DEFAULT NULL,

`City` VARCHAR(60) DEFAULT NULL,

  `Region` VARCHAR(60) DEFAULT NULL,
ect...

  PRIMARY KEY (`ID`,`Date`),
  UNIQUE KEY ID (`ID`,`Date`),
  KEY date (`Date`),
  KEY plate (`PNumber`)
) ENGINE=INNODB
PARTITION BY HASH( MONTH(`Date`) )
   PARTITIONS 12;

My problem is i want to avoid full scan to table for example if my query is like 

EXPLAIN PARTITIONS Select * from vehiclelog3 where PNumber = "bla" and Date = "2014-01-18"

output:

 1  SIMPLE       vehiclelog3  p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12  index   (NULL)         PRIMARY  7        (NULL)  3346217  Using where

i just want to scan only the partition that have that data. Is it possible?

zero
  • 95
  • 1
  • 8
  • I used your `create` and `select`, and the query only touched one partition, as it is supposed to. Could you post a non-truncated version of the create query and a real select query(your select has `plate` column which is a key, not a column in the create)? – Sergey Eremin Jan 18 '14 at 09:50
  • Oh! Sorry for that i edit the plate to PNumber in Select query. – zero Jan 20 '14 at 05:58

1 Answers1

1

SELECT * FROM tableNmae PARTITION (partitionName); as taken from http://dev.mysql.com/doc/refman/5.6/en/partitioning-selection.html can be used to restrict SELECT's to only one partition. So because you know the relation between the partition and the date that you are using, this is trivial.

Peter
  • 1,769
  • 1
  • 14
  • 18
  • i try this `SELECT * FROM vehiclelog3 PARTITION(p1); but i got this error `error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(p1) LIMIT 0, 1000' at line 11` – zero Jan 20 '14 at 01:33
  • Yes, sorry. This is a feature introduced in MySQL 5.6, if you are using a older version this query will fail. So this leads to the question: can you update your MySQL - version? – Peter Jan 20 '14 at 01:35
  • awts ok ill try to contact our database web server admin if they can update there mysql. thanks Peter – zero Jan 20 '14 at 02:02