How to select rows from partition in MySQL

L

8

20

I made partition my 300MB table and trying to make select query from p0 partition with this command

SELECT * FROM employees PARTITION (p0);

But I am getting following error

ERROR 1064 (42000): 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 '(p0)' at line 1

How to write select query to get data from specific partition?

Lichtenfeld answered 1/1, 2013 at 16:53 Comment(3)

Check this post: dba.stackexchange.com/questions/23138/…. It's not supported. – Hadwyn 1/1, 2013 at 17:3

Why do you need that construct? Can't you simply add a WHERE clause that hits p0? – Ferry 26/11, 2015 at 6:29

Can you share how you created the table+partition? – Erg 19/2, 2020 at 15:4

V

14

Depending on you MySql version, PARTITION keyword does not exist until MySQL 5.6.2. You would be using MySQL 5.5 or even 5.1, but not 5.6. In case, you are using MySQL 5.1, then you can do some workaround like below

SELECT partition, count(ID)
FROM
(
    SELECT ID,
      case when condition then p1
           when condition then p2
      .....
      end as partition

    FROM
      table
) s1
GROUP BY partition

Note : The above solution is just workaround to get you desire output.

You may also try this query to count total number of rows for your partition.

SELECT table_rows as 'count(*)' FROM information_schema.partitions WHERE table_schema = schema() and table_name ='employees' and partition_name = 'p0';

Note : you may change table_schema = schema() to table_schema = 'yourschema'

Violoncellist answered 1/1, 2013 at 17:2 Comment(15)

ok no issue, just don't forget to accept, other will get the benefit – Violoncellist 1/1, 2013 at 17:10

Sorry, i tested it and it seems this command selects all rows – Lichtenfeld 1/1, 2013 at 17:13

then, what was your problem?? you just said, you were unable to select rows. that is it. – Violoncellist 1/1, 2013 at 17:14

I want to select rows from p0 partition of table. My table has 138167 row and result is : 138167 rows in set (17.42 sec) – Lichtenfeld 1/1, 2013 at 17:15

As i mentioned above, check the quoted post. this SELECT is not supported in mysql 5.1 – Jurgen 1/1, 2013 at 17:47

Yes i saw, if i cant select from partition why making partition allowed with this version? – Lichtenfeld 1/1, 2013 at 17:50

please check my updated post, that may help you to figure out, why it is there. – Violoncellist 1/1, 2013 at 17:55

find out the difference, when you executed the first query and when you executed the second one – Violoncellist 1/1, 2013 at 17:58

please check my Note, you need to change that with your schema name, whatever would be your schema name, just change it with that – Violoncellist 1/1, 2013 at 18:19

I used second command that you wrote and returned this : +----------+ | count(*) | +----------+ | 16612 | +----------+ 1 row in set (0.00 sec) – Lichtenfeld 1/1, 2013 at 18:27

have you notice anything ?? now you got exact row count from your own partition (less number than previous one). – Violoncellist 1/1, 2013 at 18:46

Yes i already know that partition done before. But my need is select rows from that partition. I think this row count comes from information_schema.partitions not actual partition. – Lichtenfeld 2/1, 2013 at 8:7

Hey, select * from employees p0; - this is completely wrong! It returns all results, since p0 is treated as the table alias and not the partition name. – Finnie 12/5, 2014 at 14:54

@Lichtenfeld any specific reason for removing from accepted answer after 4 Years ? :D – Violoncellist 1/10, 2017 at 17:19

@ravi I was just checking my questions and account – Lichtenfeld 1/10, 2017 at 17:31

L

22

Actually since MySQL 5.6 the supported syntax is:

SELECT * FROM table PARTITION (partitionName);

Lunate answered 6/6, 2015 at 2:52 Comment(1)

Clear and precise! :) – Nimble 30/9, 2019 at 6:42

V

14

Depending on you MySql version, PARTITION keyword does not exist until MySQL 5.6.2. You would be using MySQL 5.5 or even 5.1, but not 5.6. In case, you are using MySQL 5.1, then you can do some workaround like below

SELECT partition, count(ID)
FROM
(
    SELECT ID,
      case when condition then p1
           when condition then p2
      .....
      end as partition

    FROM
      table
) s1
GROUP BY partition

Note : The above solution is just workaround to get you desire output.

You may also try this query to count total number of rows for your partition.

SELECT table_rows as 'count(*)' FROM information_schema.partitions WHERE table_schema = schema() and table_name ='employees' and partition_name = 'p0';