How to select rows from a section in MySQL

Question

I made a section of my 300 MB table and tried to make a selection request from the p0 partition using this command

 SELECT * FROM employees PARTITION (p0); 

But I get the 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 a selection request to retrieve data from a specific section?

Answer 1

Depending on your version of MySql, the PARTITION keyword does not exist until MySQL 5.6.2 . You will use MySQL 5.5 or even 5.1, but not 5.6. In case you are using MySQL 5.1 , then you can do a workaround as shown 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 a workaround to get the desired result.

You can also try this query to calculate the total number of rows for your section.

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

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

Answer 2

In fact, since MySQL 5.6 the syntax is supported:

 SELECT * FROM table PARTITION (partitionName); 

Answer 3

You are right, the explicit choice of PARTITION is not supported in version 5.1.54. Cm.

Answer 4

I don't know why my answer was converted to a comment;)

Come back. Check out this DBA question . It is not supported in the current version of MYSQL.

You can also check MYSQL dev article