There are actually two ways to wipe a mysql table either using delete or using truncate – which is better and why? So far my most commonly used form to wipe a table has simple been by using:
DELETE FROM tablename; which works fine for most cases, but mysql also offers a different method:
TRUNCATE tablename; Besides the fact that it seems to be faster (even on small tables with few rows) – which in itself is a reason to favour this method - it also has another nice feature, which may be desirable.
Mysql is usually pretty fast by default, but to keep performance to the max sometimes requires knowledge on how mysql works and how to write queries that does their job most efficiently. Today we ran across a simple example which illustrates that mysql’s ability to use an index depends on the way you write the query. Let’s make a simple table and add an index on the date column: create table members ( m_id integer auto_increment primary key, m_name char(20), m_since date not null ); create index ix_m_since on members (m_since);
Most databases seems to be well designed when the systems needing them are launched in the first version – but the slowly as the systems evolve (often grow beyond their initial requirements) – the database design seems to go down the drain. Here are a few tips on what to think about when you design a database or alter it.
First rule: Remember the goal of the system. Different systems have different requirements and thus require different database designs.
SQL is a common language implemented by most databases. While it’s a nice language it does lack some features available in some databases (which often differ from database to database). Some argue, that you shouldn’t go beyond the contrains and limitations in SQL because that removes your ability to switch to a different database. That’s just wrong (in my opinion). SQL usually allows you to do all the searches and CRUD operations you need, and by not going beyond the limitations posed by SQL you can switch your application to another database with an absolute minimum number of changes in your application - especially if you utilize the Perl DBI or the Pear DB (in PHP).
To write efficient Mysql-powered applications a little insight in how Mysql works is needed. One often overlooked example of this is Mysqls silent column changes features, which may cause your tables to look different than you specified. I’m sure most people know, that using chars is generally faster than varchars, but did you know that Mysql may actually change your char-columns to varchars? This happens if you have a variable length column in a table (ie.