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.

Finding information should be easy for people. It must be accessible, fast, and flexible.Flexible, thanks to filtering.

I’m happy to say Loopthing’s search got a new dimension, it’s a lot more relevant, and it is crazy fast.

The way it works is simple: one enters the search string into the search form, selects the type of results to return (businesses or individuals) and hits the “Search” button.

The results are extracted from the database and the less relevant ones don’t show up initially, unless the user is willing to expand that list.

Here’s a sample search for the keyword branding. If you have any ideas and questions, please send them over, I’m more than happy to adjust or comment.

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