Tag Info

New answers tagged

2

Yes, creating a PK on a big table takes time. When you don't have PK (more precisely a clustered index - the two is the same in MySQL using InnoDB) the table is called heap and the DBMS adds a 'row identifier' to each row to track them. This ID is used to identify records in indexes and other places. When you have a clustered index (PK in MySQL), the PK ...


1

DISKSPACE FOR EVERYTHING INNODB SELECT FORMAT(SUM(data_length+index_length)/POWER(1024,3),2) InnoDB_DiskSpace FROM information_schema.tables WHERE engine='InnoDB'; DISKSPACE FOR DATABASE mydb BY TABLE SELECT IFNULL(tbl,'Total') table_name, FORMAT(SUM(table_bytes)/POWER(1024,3),2) table_size FROM ( SELECT table_name ...


0

Rolando's answer worked for me with some additions. I had the same problem, with these 5 tables showing via SHOW TABLES, but SELECT or other operations on the table resulted in table not found. To resolve the issue, using Rolando's answer, I needed to: DROP TABLE <tablename> -- all 5 tables In the file system, delete the remaining .ibd files (the ...


-2

create table notification_temp as select * from notification where CreatedAt < DATE_SUB(CURDATE(), INTERVAL 30 day) ; drop table notification; RENAME notification_temp TO NOTIFICATION;


1

Make a temp table, switch it in and out, and copy the last 30 days data into it. # # Make empty temp table # CREATE TABLE NOTIFICATION_NEW LIKE NOTIFICATION; # # Switch in new empty temp table # RENAME TABLE NOTIFICATION TO NOTIFICATION_OLD,NOTIFICATION_NEW TO NOTIFICATION; # # Retrieve last 30 days data # INSERT INTO NOTIFICATION SELECT * FROM ...


1

My favorite is pt-archiver from Percona Toolkit. It takes care of MySQL load, replication lag.


1

Your issue may involve the configuration settings for either OS or MySQL HISTORICAL EXAMPLES Years ago when I worked at a website firm, I used --skip-extended-insert to load a DB Server that had 2GB RAM. That made a whole world of sense to me because the server I dumped it from had 64GB RAM and the client wanted the data on a smaller machine. At my ...


2

Change group by t1.id to group by t1.id, t3.id1 ^^^^^^^^ Output: | ID | TITLE | DESCRIPTION | INFO1 | INFO2 | ID1 | INFO | NUM | NAME | SOME | LID | SID | SNAME | ADDR | URL | ...



Top 50 recent answers are included