Mysql – Unable to select the records from specific partition in thesql

innodbmyisamMySQLpartitioning

I've created a table using the following query,

CREATE TABLE employees  (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    fname VARCHAR(25) NOT NULL,
    lname VARCHAR(25) NOT NULL,
    store_id INT NOT NULL,
    department_id INT NOT NULL
)   
    PARTITION BY RANGE(id)  (
        PARTITION p0 VALUES LESS THAN (5),
        PARTITION p1 VALUES LESS THAN (10),
        PARTITION p2 VALUES LESS THAN (15),
        PARTITION p3 VALUES LESS THAN MAXVALUE
);

Then, i've inserted the data to the table using the partition information and the query as follows,

INSERT INTO employees VALUES
    ('', 'Bob', 'Taylor', 3, 2), ('', 'Frank', 'Williams', 1, 2), 
    ('', 'Ellen', 'Johnson', 3, 4), ('', 'Jim', 'Smith', 2, 4),  
    ('', 'Mary', 'Jones', 1, 1), ('', 'Linda', 'Black', 2, 3),
    ('', 'Ed', 'Jones', 2, 1), ('', 'June', 'Wilson', 3, 1), 
    ('', 'Andy', 'Smith', 1, 3), ('', 'Lou', 'Waters', 2, 4),  
    ('', 'Jill', 'Stone', 1, 4), ('', 'Roger', 'White', 3, 2),
    ('', 'Howard', 'Andrews', 1, 2), ('', 'Fred', 'Goldberg', 3, 3), 
    ('', 'Barbara', 'Brown', 2, 3), ('', 'Alice', 'Rogers', 2, 2),  
    ('', 'Mark', 'Morgan', 3, 3), ('', 'Karen', 'Cole', 3, 2);

Now, I'm trying to select the records from specific partition, and my query was,

SELECT * FROM employees PARTITION (p1);

But i'm keep on getting the same error as,

1064 – You have an 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, 30' at line 1

FYI, initially i was using InnoDB database engine, now i've changed it to MyISAM, but nothing helped me out.

Best Answer

The syntax you are proposing (being able to explicitly select from a partition list) does not exist until MySQL 5.6.2:

Starting in MySQL 5.6.2, SELECT supports explicit partition selection using the PARTITION keyword with a list of partitions or subpartitions (or both)... [src]

And indeed, your example comes from the MySQL 5.6 manual. I am assuming you are running MySQL 5.5 or even 5.1, but not 5.6.

Related Question