Truncating big MySQL (InnoDB) tables

Truncating big tables, with millions of rows, in InnoDB can be really slow. Luckily there is a small trick to speed this up.

The trick is to first create a table that has the same structure as the table you are truncating:

CREATE TABLE `new_table` LIKE `bigtable`;

Then you can swap the to be truncated table for the empty table:

RENAME TABLE `bigtable` TO `old_table`, `new_table` TO `bigtable`;

After this your system will use the new (and empty) table, and you can do a quick drop of the old big table:

DROP TABLE `old_table`;

Done, you have succesfully truncated your huge table.

sql database technical mysql development code opensource engine example software

Swapping two columns in MySQL

Sometimes the edges in our MySQL database are referring into the wrong direction. In those case...

AMNESIA

AMNESIA is an Erlang library providing an abstraction layer for interfacing relational DBMSs. The...

MySQL 5.0 Reference Manual

MySQL is one of the most popular databases for websites. The main reasons are price and speed :-...

SQLAlchemy - The Database Toolkit for Python

SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application develope...

MonetDB - Query Processing at Light Speed

MonetDB/SQL and MonetDB/XQuery, fast query language support in an open-source column-orienated da...

MySQL AB :: MySQL Documentation

The MySDQL Reference Manual for almost all MySQL versions that are now in use.

Multi-Threaded Programming With POSIX Threads

This tutorial is an attempt to help you become familiar with multi-threaded programming with the ...

Sphinx - Free open-source SQL full-text search engine

Sphinx is a full-text search engine, distributed under GPL version 2. Commercial license is also ...

PrimeBase Technologies - PBXT Storage Engine for MySQL

PrimeBase XT (PBXT) is a transactional storage engine for MySQL. As illustrated below, a MySQL st...

GeSHi - Generic Syntax Highlighter

GeSHi - Generic Syntax Highlighter for php. Highlight many languages, including PHP, CSS, HTML, S...

mysql sql database technical opensource engine dbms software abstraction development

AMNESIA

AMNESIA is an Erlang library providing an abstraction layer for interfacing relational DBMSs. The...

MonetDB - Query Processing at Light Speed

MonetDB/SQL and MonetDB/XQuery, fast query language support in an open-source column-orienated da...

Sphinx - Free open-source SQL full-text search engine

Sphinx is a full-text search engine, distributed under GPL version 2. Commercial license is also ...

PrimeBase Technologies - PBXT Storage Engine for MySQL

PrimeBase XT (PBXT) is a transactional storage engine for MySQL. As illustrated below, a MySQL st...

development technical database mysql sql python source code activerecord documentation

SQLAlchemy - The Database Toolkit for Python

SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application develope...

MySQL AB :: MySQL Documentation

The MySDQL Reference Manual for almost all MySQL versions that are now in use.

Multi-Threaded Programming With POSIX Threads

This tutorial is an attempt to help you become familiar with multi-threaded programming with the ...

Sphinx - Free open-source SQL full-text search engine

Sphinx is a full-text search engine, distributed under GPL version 2. Commercial license is also ...

code example sql technical highlighting beautifier generic colorizer highlighter php

Swapping two columns in MySQL

Sometimes the edges in our MySQL database are referring into the wrong direction. In those case...

Multi-Threaded Programming With POSIX Threads

This tutorial is an attempt to help you become familiar with multi-threaded programming with the ...

GeSHi - Generic Syntax Highlighter

GeSHi - Generic Syntax Highlighter for php. Highlight many languages, including PHP, CSS, HTML, S...

MarcWorrell.com/ created on 2008-01-24 18:34:09/ modified on 2008-01-24 18:48:59/ mail me at