How to select rows from partition in MySQL

Question

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?

Check this post: http://dba.stackexchange.com/questions/23138/unable-to-select-the-records-from-specific-partition-in-mysql. It's not supported. — bonCodigo, Jan 01 '13 at 17:03
Why do you need that construct? Can't you simply add a WHERE clause that hits p0? — Rick James, Nov 26 '15 at 06:29

score 19 · Answer 1 · answered Jun 06 '15 at 02:52

19

Actually since MySQL 5.6 the supported syntax is:

SELECT * FROM table PARTITION (partitionName);

answered Jun 06 '15 at 02:52

rudygodoy

438
3
8

1

Clear and precise! :) – nagendra547 Sep 30 '19 at 06:42

Ravi · Accepted Answer · 2015-06-06T05:40:46.230

13

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'

edited Jun 06 '15 at 05:40

answered Jan 01 '13 at 17:02

Ravi

30,829
42
119
173

ok no issue, just don't forget to accept, other will get the benefit – Ravi Jan 01 '13 at 17:10
Sorry, i tested it and it seems this command selects all rows – Kad Jan 01 '13 at 17:13
then, what was your problem?? you just said, you were unable to select rows. that is it. – Ravi Jan 01 '13 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) – Kad Jan 01 '13 at 17:15
As i mentioned above, check the quoted post. this SELECT is not supported in mysql 5.1 – Hanky Panky Jan 01 '13 at 17:47
Yes i saw, if i cant select from partition why making partition allowed with this version? – Kad Jan 01 '13 at 17:50
please check my updated post, that may help you to figure out, why it is there. – Ravi Jan 01 '13 at 17:55
find out the difference, when you executed the first query and when you executed the second one – Ravi Jan 01 '13 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 – Ravi Jan 01 '13 at 18:19
I used second command that you wrote and returned this : +----------+ | count(*) | +----------+ | 16612 | +----------+ 1 row in set (0.00 sec) – Kad Jan 01 '13 at 18:27
have you notice anything ?? now you got exact row count from your own partition (less number than previous one). – Ravi Jan 01 '13 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. – Kad Jan 02 '13 at 08:07
1

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. – ducin May 12 '14 at 14:54
@Kad any specific reason for removing from accepted answer after 4 Years ? :D – Ravi Oct 01 '17 at 17:19
@ravi I was just checking my questions and account – Kad Oct 01 '17 at 17:31

score 4 · Answer 3 · edited Sep 06 '18 at 11:55

4

The correct form of the query is as below it works for me fine.

select * from employees partition (`p0`);

edited Sep 06 '18 at 11:55

Hardik Shah

4,042
2
20
41

answered Sep 06 '18 at 10:52

Omer Mohamed Nafaal

41
1

score 3 · Answer 4 · edited Apr 13 '17 at 12:42

3

You are right, explicit selection of PARTITION is not supported in 5.1.54 Version. See this post

edited Apr 13 '17 at 12:42

Community

1
1

answered Jan 01 '13 at 17:02

Hanky Panky

46,730
8
72
95

score 2 · Answer 5 · answered Feb 22 '22 at 20:25

2

i was pulling my hair out and then realised my mistake

select * from user_info as ui PARTITION (`p0`)

That query fails because the alias has to be specified after the partition list

 select * from user_info PARTITION (`p0`) as ui

answered Feb 22 '22 at 20:25

Illegal Operator

656
6
14

score 1 · Answer 6 · edited Feb 08 '20 at 08:17

1

It's not supported in current version of MYSQL.

Check this question on DBA. You may also check out MYSQL dev article

edited Feb 08 '20 at 08:17

Bhargav Rao

50,140
28
121
140

answered Jan 01 '13 at 17:09

bonCodigo

14,268
1
48
91

Please add all relevant information to the question instead of linking to external pages – Nico Haase Feb 08 '20 at 08:13

Mohammad Ali Abdullah · Answer 7 · 2020-02-25T03:18:29.867

1

SELECT * FROM invoice_detail PARTITION (p1);

edited Feb 25 '20 at 03:18

answered Feb 08 '20 at 06:39

Mohammad Ali Abdullah

321
2
10

2

Please do not provide querys that are widely open for SQL injection – Nico Haase Feb 08 '20 at 08:12
1

@NicoHaase You should explain what you mean a little more. How is that susceptible to injection? – clearlight Feb 23 '20 at 19:12
@clearlight just have a look at the previous version of that answer – Nico Haase Feb 23 '20 at 21:09

score -1 · Answer 8 · answered Feb 08 '20 at 08:20

-1

I think its worth pointing out to others that may stumble upon this page, that a 300MB table does not need a partition.

300MB is a trivial amount of data for any modern (or even not so modern) database and thus everything will work better if you leave your data in one table in one database in one partition.

answered Feb 08 '20 at 08:20

Toby Allen

10,997
11
73
124

It doesn't answer the OP question plus its not specified the potential growth of this table during time to recommend not partitioning or whatsoever. You might share your opinions as a comments not answers to questions. – JMoura Oct 12 '20 at 20:11

How to select rows from partition in MySQL

8 Answers8