Optimizing Database

There are lots of articles about how to optimize your app’s database. But I haven’t seen actual tricks that you can use on your applications / websites.

There is pretty good starting article on Smashing Magazine – “Speeding up your website’s database“. I love Smashing’s contribution to the webdev community, but this article is a bit longer and more basic at the same time.

I DO understand the need for simplicity because of the wide audience of Smashing Magazine, but I’d wish they’d give something more than the absolute basics you could find in almost any other site out there. I also think there is a better approach on some of the methods mentioned there for profiling (or the code itself), so here is my quick thought to optimizing database performance. Which you can actually apply to your code.

Before thinking about optimizing :
Updating mysql server version (the more recent, the better/faster/stronger) using stored procedures, that spare multiple successive connections to the server, and their inherent costs

Optimizing Database by Query Structures

In my previous post on database optimization, I focused on improving query performance by optimizing schema – exploring indexing strategies by reading the execution plan. In this post I’ll show how different query structures can also have a major impact on performance.

Dealing with OR operators in a WHERE clause of an SQL statement in MySQL can be tricky. Up until Version 5, MySQL could only use one index per table referenced in a query. A multi-column index can be used for  filtering conditions with an AND operator (which is more restrictive by nature), but a condition added by OR must use a separate index because of the logical nature of the opertaor. (a union, as opposed to an intersection that the AND represents)

Database Profiling / Performance Analysis

Database performance is one of the major bottlenecks for most web applications. I saw most developers are not database experts (and I’m no exception), there are however several basic methods to analyze and optimize database performance without resorting to expert paid consultants.

The Performance Equation

Database performance is affected by many different variables – the running machine specs, OS, database engine and configuration, table schema and the queries running against it. As for OS and server specs, I’ll take them out of the equation as I want to talk about optimizing relative performance on the same machine.

In this post, I will talk about:

  • The structure of database tables (schema)
  • The structure of application-level queries (SELECT, UPDATE, INSERT, DELETE)

Install Mongodb on Mac and Windows

Mongo is an open source schemaless database system which is very different from the more popular MySQL. The most considerable differences are that MySQL is written using SQL queries, while MongoDB is focused on BSON (Binary JSON).

This means much of the functionality can be accessed directly through JavaScript notation.

In this post, I will show you how to install mongodb using:

  • MongoDB 2.4.5
  • Mac OS X 10.7.5
  • Windows 7