All Questions
Tagged with database-partitioning mysql
266 questions
0
votes
1
answer
33
views
How partition by custom hash function in MySql?
I am trying to create table like this:
CREATE TABLE `my_table` (
`query_id` varchar(255) NOT NULL,
PRIMARY KEY (`query_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
PARTITION BY hash (TO_DAYS(...
- 33.7k
0
votes
1
answer
57
views
How to create composite partition in mysql 5.7?
I have one table parcels, where I have 3 columns ID, PAYMENT_STATUS, CREATED_AT. It does not have any partition yet. This table has 30 million rows. Now I want to optimize this table, want to ...
- 2,953
0
votes
0
answers
132
views
MySQL Partition table based on two columns
I have an events table which holds historical data of 10 year's with daily coming new data. Now the total rows in this table are 22560041.
Table has column's id, title, status (1-4), start_date and I ...
0
votes
1
answer
140
views
Unable to partition in MySQL Sakila database
[CLOSED] I try to write a query in Sakila Database with MySQL Workbench 8.0 that partitioning the customer table into 5 partitions using the hash type on the customer_id. Check the partitioning ...
- 3
1
vote
1
answer
46
views
MySQL 8.0 EXPLAIN Select shows that more query will read more subpartitions than necessary
I am creating a table to register the events from multiple sensors in a MySQL 8.0 database.
Since the data is going to be accessed mainly over time ranges (from one day to a month), I decided to ...
- 2,263
0
votes
0
answers
53
views
Mysql Table Partitioned and not not applying CURRENT_TIMESTAMP
I am wondering why my default time value has stopped working on my database when I partitioned it into months. MySQL 8.1
Is this normal behavior?
Is there a alternative solution of setting this ...
- 11
0
votes
0
answers
34
views
Dynamic partitioning by id
i need to create dynamic partitioning range by id of 1000,
stored procedure possible in mysql?
here it is stored procedure i tried
DELIMITER //
CREATE PROCEDURE AddPartitions()
BEGIN
DECLARE ...
- 13
-1
votes
1
answer
2k
views
Why partitioning key must be part of unique/primary key in MySQL?
I know from documentation and from lots of questions on stack overflow that whenever I am partitioning a table, I require a partition key and that partition key must be included in unique/primary key. ...
- 339
1
vote
1
answer
652
views
Identify the partition of the hash partitioned table when inserting new data
I have created a new table and hash partition like this:
CREATE TABLE employees (
id INT NOT NULL,
store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;`
Then I insert the value into the ...
- 21
0
votes
1
answer
342
views
MySQL Open Files (MyISAM, Parititioned Large Database)
I have a quite large database that I am working on, a collection of measurement data. I am running into issues with hitting the max open files (status global variable Open_files hitting the global ...
- 29
3
votes
0
answers
96
views
Prepared statement doesn't return any rows from a partitioned Mysql 8.0 table using IN clause
If we have two identical tables, except one uses partitions and the other doesn't, like this:
CREATE TABLE t1 (
created_at DATE
) ;
INSERT INTO t1 VALUES ('2023-02-03'), ('2023-02-25');
CREATE ...
- 2,065
0
votes
2
answers
2k
views
How actually does GROUP BY + SUM + OVER + ORDER BY work?
I understand that this query will produce numbers and cumulative tallies:
SELECT number
, SUM(number) OVER (ORDER BY number) cumulative_number
FROM (SELECT 1 number UNION SELECT 2 UNION SELECT ...
- 39.5k
0
votes
2
answers
4k
views
How to partition a table by year and then subpartition by month in mysql 8
I have a table that contains a month and a year column.
I have a query which usually looks something like WHERE month=1 AND year=2022
Given how large this table is i would like to make it more ...
- 19.7k
0
votes
0
answers
256
views
Performance issues with large MySQL tables with 100million of rows
I've had some performance issues with MySQL tables and I'm trying to get some help. The SQL knowledge I have is pretty limited, so far over the years I was able to figure out most by using Google for ...
- 659
-1
votes
1
answer
48
views
Mysql: how to optimize occasionally slow query where all indexes are set and tables are partitioned?
Consider the following query:
SELECT *
FROM product_related pr
LEFT JOIN product p ON (pr.related_id = p.product_id)
LEFT JOIN product_to_store p2s ON (p.product_id = p2s.product_id)
WHERE ...
- 69