How to select rows from partition in MySQL
Solution 1
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'
Solution 2
Actually since MySQL 5.6 the supported syntax is:
SELECT * FROM table PARTITION (partitionName);
Solution 3
The correct form of the query
is as below it works for me fine.
select * from employees partition (`p0`);
Solution 4
You are right, explicit selection of PARTITION is not supported in 5.1.54 Version. See this post
Solution 5
SELECT * FROM invoice_detail
PARTITION (p1);
Kad
Quartz crystal resonant > signal > binary data > hex > assembler > ascii > c, c++, phyton etc > OS > desktop
Updated on February 25, 2020Comments
-
Kad about 4 years
I made partition my 300MB table and trying to make select query from
p0
partition with this commandSELECT * 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?
-
Ravi over 11 yearsok no issue, just don't forget to accept, other will get the benefit
-
Kad over 11 yearsSorry, i tested it and it seems this command selects all rows
-
Ravi over 11 yearsthen, what was your problem?? you just said, you were unable to select rows. that is it.
-
Kad over 11 yearsI want to select rows from p0 partition of table. My table has 138167 row and result is : 138167 rows in set (17.42 sec)
-
Hanky Panky over 11 yearsAs i mentioned above, check the quoted post. this SELECT is not supported in mysql 5.1
-
Kad over 11 yearsYes i saw, if i cant select from partition why making partition allowed with this version?
-
Ravi over 11 yearsplease check my updated post, that may help you to figure out, why it is there.
-
Ravi over 11 yearsfind out the difference, when you executed the first query and when you executed the second one
-
Ravi over 11 yearsplease check my Note, you need to change that with your schema name, whatever would be your schema name, just change it with that
-
Kad over 11 yearsI used second command that you wrote and returned this : +----------+ | count(*) | +----------+ | 16612 | +----------+ 1 row in set (0.00 sec)
-
Ravi over 11 yearshave you notice anything ?? now you got exact row count from your own partition (less number than previous one).
-
Kad over 11 yearsYes 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.
-
ducin almost 10 yearsHey,
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. -
Ravi over 6 years@Kad any specific reason for removing from accepted answer after 4 Years ? :D
-
Kad over 6 years@ravi I was just checking my questions and account
-
nagendra547 over 4 yearsClear and precise! :)
-
Nico Haase about 4 yearsPlease do not provide querys that are widely open for SQL injection
-
Nico Haase about 4 yearsPlease add all relevant information to the question instead of linking to external pages
-
clearlight about 4 years@NicoHaase You should explain what you mean a little more. How is that susceptible to injection?
-
Nico Haase about 4 years@clearlight just have a look at the previous version of that answer
-
JMoura over 3 yearsIt 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.