Category Archives: mysql

A good way to add a column to a table with millions of records, without locking resources.

Whenever you manage a database and reach a certain amount of records, some things just aren’t the same anymore.

Try adding a column, an index or even worse a FULL TEXT index. This can lock up your resources and hurt your i/o drastically, you don’t want this to happen on a production environment.
But sometimes you just have to do what you have to do.
Note:the effect often depends on your database engine e.g. innoDB

Here is a cool way to add a column without locking your resources:


CREATE TABLE big_table_copy LIKE big_table;
ALTER TABLE big_table_copy ADD COLUMN some_new_column varchar(256);
INSERT INTO big_table_copy (fields_in_big_table) SELECT * FROM big_table;
RENAME TABLE big_table TO big_table_old, big_table_copy TO big_table ; 
# DROP TABLE big_table_old;

Note: You might not want to delete the big_table_old to check if any new records were added while this query was running. And make sure they also are added in the new big_table

Tagged , , , ,

How to check when a field value from one table does not exists in another table

Let’s say we need to list every person which does not have an account.

Thus a person record that does not have a account record.

There’s multiple ways of doing this, each way may be more efficient depending on table sizes.

NOT IN
The easiest but not necessarily the most efficient depending on the account table size.

SELECT *
FROM Persons
WHERE id NOT IN (SELECT person_id FROM accounts)

NOT EXISTS
The title says it, and it’s designed specially for our goal

SELECT *
FROM Persons
WHERE NOT EXISTS
(SELECT person_id
FROM accounts
WHERE accounts.person_id = persons.id)

LEFT OUTER JOIN (IS NULL)
The left outer join is probably quickest in the general case as it prevents repeated execution of the subquery.

SELECT *
FROM Persons
LEFT OUTER JOIN accounts
ON (persons.id = accounts.person_id)
WHERE accounts.person_id IS NULL
Tagged , ,