Database

If you’re working on heavy duty websites, knowing your database and how to use it best can make a world of difference in terms of performance, and thus you should always optimize the database. That’s pretty much obvious.

The tricky part is how you do the optimization? Often it requires a lot of reading up on how the database works - strengths, weaknesses and other details — and loads of experience. Having a DBA available to help you optimize would be ideal in some cases, but often you need to do it yourself.

If you’re a developer and use mysql, I’m sure you’re aware that it’s a database and it quite good at storing data, but one of the neat things about Mysql (and most other databases) is also their ability to provide meta-data on the contents of the database.

Most people know how to use the meta-data queries in the commandline, but if you want you can also use them in your (php/perl/some-other- ) language. Here is a quick guide to some of them.

It seems there are two camps when it comes to SQL and how to do database optimizations - the “generic camp” and “the specialist camp”. While I don’t consider myself an extremist, I am absolutely in the specialist camp and this little post is an explanation of why.

SQL is a generic database langauge . There are a few different standards in use (the language has progressed over time), but the core of the SQL language is pretty much the standard in most databases. It’s probably also standard - in any database - that the SQL standard has been extended with database-specific extensions which provides optimizations, functions or other options not available in the SQL standard.

Navigating around in mysql is quite easy. You can see which databases by using the command “show databases”. There will always exist a database called “mysql” which is used by mysql itself.

If your user account has access to a database, you can access it by using the “use DATABASENAME” command. You can se which tables exist within a database by using the “show tables” command - and see details for each table by issuing the “desc TABLENAME” command.

Micro tip of the day: How many days has past since a date field in the database?

SELECT (TO\_DAYS(NOW()) - TO\_DAYS(date\_field)) AS days\_past FROM tablename