All Questions

Tagged with
Filter by
Sorted by
Tagged with
0
votes
1answer
25 views

MySQL - InnoDB - Partitioning a table with uniqueness

Following a previous post I posted: date-based-partitioning This is my solution for partitioning a table with primary key. I wanted to add partitioning by months and delete old record. CREATE TABLE ...
0
votes
1answer
53 views

Table Partitioning and Performance

I have to migrate one large table and need to do partitioning of the same. I would like to know whether i do Partitioning first and migrate the data second or I do migrate the data first and ...
1
vote
3answers
48 views

How to PARTITION a table in mysql?

I read a lot of articles that PARTITIONing is not helpful in most cases. I believe it should speed up the queries in my case. I have a table with the structure of CREATE TABLE pages ( page_id int(11) ...
3
votes
1answer
30 views

Is PRIMARY(ColumnA, ColumnB) + INDEX(ColumnB) + PARTITIONING(ColumnB) redundant?

I have a big table (30 millions rows) defined like this: CREATE TABLE `drawing` ( `id` int unsigned NOT NULL, `artist` int unsigned NOT NULL, `imageUrl` text NOT NULL, `uploadDate` timestamp NOT ...
2
votes
0answers
242 views

missing ibd files

I'm running mysql 5.6. For reasons entirely my own fault, I've got some missing ibd that make up partitions for several tables. When I try to mysql I get the following messages for the missing ...
-1
votes
1answer
333 views

Fragmentation in InnoDB tables: dump all data together reduced size, but fragmentation got worse [closed]

I have a question about tables and fragmentation in MySQL My starting point is this question: How find and fix fragmented MySQL tables I have a table with 1 million of rows and 128MB of space. This ...
0
votes
1answer
115 views

Vertical Partition MySQL table

I am reviewing a case, where I have 200+ columns mostly varchar(100). The columns are from several external data sources like CRM / Demographics data etc. I cannot hold them in one MySQL table with ...
2
votes
1answer
453 views

Partitioning increases CPU usage in mariadb

I'm using mariadb 10.2, and I had a table with a structure similar to the one below, currently with 15gb, where I insert a few thousand records per minute all the time, each batch for a specific ...
1
vote
1answer
963 views

What are the options for archiving old data of mariadb tables if partitioning can not be implemented due to a restriction

We are planning to archive some old data of our MariaDB tables to some other volume(or mount point) of the linux server apart from the one the tables are on. We are using partitioning for this and ...
0
votes
2answers
56 views

Would CRUD operations get very slow on an mysql innodb table which got more than 50 million rows?

I'm about to design a DB for a finance institution and the last normalization of tables shows that the necessity of creating a single table for all the bank's transaction. As per the calculations, ...
1
vote
2answers
81 views

how to partition the mysql table

I have a badly designed table and to top that number of columns is increasing (~500)because each time a new machine is discovered.(eg: router,adapter windows server etc)..it will store the output of ...
1
vote
3answers
3k views

Optimising latitude and longitude selection query

I have table as follows the schema below. CREATE TABLE `gdata` ( `alarmTypeID` tinyint(4) NOT NULL DEFAULT '0', `fleetID` smallint(11) NOT NULL, `fleetGroupID` smallint(11) DEFAULT NULL, `...
0
votes
1answer
136 views

Partitioned mariadb table files(the .ibd ones) placed outside of data directory return back to data directory itself after altering the table in use

While Partitioning large tables in Mariadb we want some partitions to be placed in another storage device with different speed and capacity. That is our requirement. For that we are trying to perform ...
2
votes
1answer
386 views

mariadb (mysql) innodb not optimizing SELECT for partitions?

I have a table partitioned by on column "level" -- I have logs where level 7 is the verbose info, which is like 20x as many entries as all the other levels. When purging old logs in level 7, it was ...
5
votes
3answers
7k views

how to remove 6 months old data automatically from MYSQL innodb table

I have a very big table in which I am fetching data everyday and it's size is getting bigger everyday but for my software, only 6 months data is useful. So, I am planning to remove data which is older ...
1
vote
1answer
439 views

Partition Wise Joins in Mysql

Is it possible to create or simulate a partition-wise join in Mysql/Innodb?? Here is a good description of partition-wise joins.... https://blogs.oracle.com/datawarehousing/entry/...
1
vote
0answers
109 views

Table clustered-index and partitioning in innoDB

I have a table with 45 columns, 3 of which are relevant: table Transactions(tr_id : int primary key, client : int, tr_date : datetime); The inserts to the table are done client-by-client The selects ...
1
vote
1answer
111 views

Split Mysql Table internally

How to split the huge mysql table (size around 200GB) internally? Is there any mechanism other than partition? The table size is daily growing. Table created using innodb with indices. Thanks in ...
2
votes
1answer
604 views

MySQL Create Table with Partition Slow on server machine

Following create table statement with Partition runs in 10 Second on My Development Machine windows 7, i5 , 8GBRam, single disk runs in 16 Second on Virtual Machine windows server 2012 , xeon 2GHz , ...
1
vote
0answers
2k views

Using partitioned table, and dealing with auto_increment PK

I have a big table (>100 million rows) that tracks user's activities. It has an ID, which is PK and has auto_increment property. This is the only unique key. Table also has userid and datetime column. ...
2
votes
1answer
532 views

How does subpartitioning actually work and what physical files are created?

I can't understand how the subpartitioning works in mysql. I am reading the following example: create table invoices ( customer_id int not null, product_id int not null, amount decimal (8,...
0
votes
1answer
631 views

What is the best partition technique for large table with TEXT column?

MySQL: What is the best partition technique for large InnoDB table (around 1.5M rows) with TEXT type of column? There is a FULLTEXT index on the TEXT column, which is very essential for my searching ...
2
votes
0answers
2k views

Incorrect information in .frm file after a power outage?

CentOS release 6.3 (Final) x86_64 mysql Ver 14.14 Distrib 5.5.31, for Linux (x86_64) using readline 5.1 SHOW SLAVE STATUS\G: Slave_IO_Running: Yes Slave_SQL_Running: No ...
2
votes
1answer
1k views

InnoDB, Hash Table, Reference Table and Fragmentation

I have a big list of domains and URLs that I store in a database, there are around 150M domains and 300M URLs, I'm using InnoDB to store each in the format: CREATE TABLE IF NOT EXISTS `domains_list` (...
1
vote
1answer
283 views

Is there an advantage to moving to 'real' date partitions on MySQL 5.5?

We are using partitions per day for some our tables. We started with MySQL 5.1, so we had to partition using the to_days (end_time is a datetime column): ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=...
0
votes
1answer
117 views

Will Partitions and Indexes on the same table help in performace of Inserts and Selects?

I have a table containing the list of visitors and this table has the following information. Visitor Browser Information Visitor Location Information Visitor Time Information No of Visits I have a ...
4
votes
2answers
10k views

Partitioning and freeing disk space

I am running MySql innodb with innodb_file_per_table. As a daily script, I would like to create a new partition for a set of tables, as well as delete yesterday's partitions. What command would need ...
6
votes
3answers
20k views

Avoiding “Waiting for table metadata lock” when `ALTER TABLE DROP PARTITION`?

I have some tables that many users need to access to: mysql> show create table v3_cam_date\G *************************** 1. row *************************** Table: v3_cam_date Create Table: ...
6
votes
2answers
3k views

Do I need to index a key that I partition by?

I have an InnoDB table of customers, partitioned by KEY, with each partition being one US state. I have an index on phone number, and the performance is great. We also need to SELECT by last name in ...
1
vote
1answer
7k views

Unable to select the records from specific partition in mysql?

I've created a table using the following query, CREATE TABLE employees ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, fname VARCHAR(25) NOT NULL, lname VARCHAR(25) NOT NULL, store_id ...