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 not lose the autocomplete magic in your MCV views.

Like most MCV frameworks the controller passes the variable to the view.

Something like this in codeigniter:


$data["user"] = User::find($id);
$this->load->view('user/profile', $data);

Or Laravel:


$data["user"] = User::find($id);
return View::make('user.profile', $data);

Now if we would try to get some autocomplete the $user var in view we get nothing.

autocomplete, no suggestion

Was it getName(), getSurname() … no I’m pretty sure it was getLastName()..Ah we’ll need to look it up in the Class.

What! But Why? I’ve paid good money for this IDE and it’s awesome autocomplete function, why doesn’t it work??

First of all, don’t blame your IDE dummy. How would it know wat those resources are? It doesn’t.

Now heres the magic how it does:

Just add the @var PHPDoc to your view, just as you would in a Class.


/*@var $user User*/

No more “no suggestions”.

Whoohoo it works!

Whoohoo it works!

PS. MOST IDE will make the magic happen, the only one I’ve tested which doesn’t is Netbeans. Be free to add more in comments.

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 , ,

One of my favorite answers on stackoverflow

I’ll add the entire post below, but please also read it on stackoverflow the answer deserves the attention.

Thank you Jon for some great code and explanation.

Introducing: a very generalized solution for PHP 5.3+

I ‘d like to add my own solution here, since it offers features that other answers do not.

Specifically, advantages of this solution include:

  1. It’s reusable: you specify the sort column as a variable instead of hardcoding it.
  2. It’s flexible: you can specify multiple sort columns (as many as you want) — additional columns are used as tiebreakers between items that initially compare equal.
  3. It’s reversible: you can specify that the sort should be reversed — individually for each column.
  4. It’s extensible: if the data set contains columns that cannot be compared in a “dumb” manner (e.g. date strings) you can also specify how to convert these items to a value that can be directly compared (e.g. aDateTime instance).
  5. It’s associative if you want: this code takes care of sorting items, but you select the actual sort function (usort or uasort).
  6. Finally, it does not use array_multisort: while array_multisort is convenient, it depends on creating a projection of all your input data before sorting. This consumes time and memory and may be simply prohibitive if your data set is large.

Continue reading

Tagged , , ,

Non-procedural datetime conversions

Aren’t you tired of using  strtotime() and messing up timezones / DST now and then?

Here’s the OOP way, easy to understand and maintain! whoohoo!

Converting a UTC datetimestamp to your local date time.:

$my_utc_datetime = '11-14-2013 10:38:00'; // November 14th
$utc_datetime_object = DateTime::createFromFormat(
    'm-d-Y H:i:s',           // current format
    $my_utc_datetime,        // the datetimestamp
    new DateTimeZone('UTC')  // the origin timezone
);
// Now let's convert it to amsterdam time
$amsterdam_datetime = $utc_datetime_object;
$amsterdam_datetime->setTimeZone(new DateTimeZone('Europe/Amsterdam'));
echo $amsterdam_datetime->format('Y-m-d H:i:s'); // This will echo amsteram datetime

And the good news this will take DST into account!

No more daylight saving time problems!

PHP.net:

DateTime
DateTime::createFromFormat()

Tagged , , , ,