Jeromakay

You’ve probably noticed that the AUTO_INCREMENT ID of a column doesn’t change after deleting rows from a table, and this may not be what you want in some situations (maybe you are testing an application, and you want ID’s to reset from 1, rather than the last available ID).

First solution would be to use TRUNCATE when deleting the rows from the table, instead of doing a row by row deletion (which is also slower in this situation).  To achieve this, just type:

TRUNCATE TABLE t1;

This automatically rests the auto_increment id of any column which was set to automatically increment the last row’s value when a new row is inserted, no matter if the table (in case you’re using MySQL) is using MyISAM or InnoDB storage engines.

Another way to achieve this is by throwing the ALTER TABLE command:

ALTER TABLE t1 AUTO_INCREMENT=1;

While this solution is also great, it requires two WRITE operations over the t1 table:

1. you must delete the content
2. reset the auto_increment ID.

Because writes are expensive for high load databases, using TRUNCATE is highly recommended.

There would be a third option to achieve this, and just like the above ones, you may not have enough permissions to throw it in the command line: dropping the table, and adding it again.

This of course, will clean everything related to that table, including the auto_increment ID.

Here’s how it goes:


INSERT INTO t1 (c2) VALUES ('IE');
INSERT INTO t1 (c2) VALUES ('UK');

SHOW TABLE STATUS LIKE 't1';

After throwing the SHOW TABLE statement, we just look for the “auto_increment” value which should be 3.

Let’s reset the auto_increment value:


DROP TABLE t1;



CREATE TABLE t1 (
`c1` SMALLINT(2) unsigned auto_increment NOT NULL,
`c2` char(2) NOT NULL,
PRIMARY KEY (`c1`)
) ENGINE="MyISAM";



SHOW TABLE STATUS LIKE 't1';


Now, the value of the auto_increment index is 1 again.

My conclusion: to reset the auto_increment index to 1, just go with the TRUNCATE command. It’s faster, especially if there’s content inside the table.

Internationalization can be messy when developing applications targeting multiple countries.

If you are developing a DB backed application where users can enter non latin characters, you may want to retrieve those rows which have non-latin characters used by different non-english languages, like: Hebrew, Arabic, Chinese, Thai, Hindi, Swedish, Romanian, etc. (such as: ăâîşţ).

If you want to check all the rows having content which doesn’t use only latin characters, you can try the following query which returns these rows.

SELECT `column` FROM `t` WHERE CHAR_LENGTH(`column`)<>LENGTH(`column`);

In my case, the table.column has an UTF-8 character set.

I’ll let you guess why rows containing non-Latin (non-ASCII) characters are returned by the above query.

Reference: MySQL’s String Functions

I’ve got my lesson learned today while working on a major MySQL based feature for Loopthing. While everything was planned and coded carefully, it seems that one issue got carried away in production mode where things didn’t go just right obviously.

The end result was a slow or non-responsive application. I never though it could be the database system until checking the system’s usage statistics and realizing that mysqld was using over 190% of the CPU power.

This was crazy and unexplainable but what I’m about to reveal now doesn’t have anything to do with database optimization practices, but rather with design practices.  Here’s what happened:

I am a true believer that premature optimization is the mother of all evil (Donald Knuth), after learning it the hard way. The problem now stands in the opposite of this: not optimizing things sufficiently enough: what I did was to put together all my tables structure, setting up the main indexes, and nothing more.

Built my queries around this basic structure, tested on small amounts of data, but then, forgot to fine tune things and see where potential indexes are needed. This was totally forgotten in development code as well, and everything was one day migrated into production.

When production activity started growing things changed and the ugly face of poor queries optimization and index setting was revealed to us here. Geesh.

Now, if your CPU’s cycling like crazy, it’s time to have a look over your db’s structure once again, maybe you’ve “missed” to consider some very important things you always knew you must not be careful not to miss.

On the other hand, there’s a pro of my issue today: it prevented useless hours of premature optimization and benchmarking, LOL