MySQL - avoid beginner traps

Photo by pcambraf

When my client's project (http://connectotravel.com) got online and started to chew real data, we found out that some functions perform too slow.

I started to evaluate application code and performance, using CodeIgniter Profiler class. It revealed that some SQL queries were too slow.

Some of it had unnecessary tables, left hanging there after quick development without enough testing. Removing those tables from FROM statement helped significantly.

Photo by taberandrew

But the most important issue was the way how I used to link tables. There were some SQL queries, SELECT queries linking seven, eight or more tables. I used WHERE all the time. Like this: (pk - primary key, fk - corresponding foreign key)

SELECT tbl1.field_A,tbl2.field_D FROM tbl1,tbl2 WHERE tbl1.id=tbl2.fk
It is OK if you use it in a simple example like this. But for linking more tables, that would be no way to do it, instead, one should use INNER JOIN statement.

Let's say, you need to select something from 3 tables. This is a simple example:

SELECT tbl1.field_a,tbl2.field_c,tbl3.field_g FROM tbl1 INNER JOIN tbl2 ON tbl1.pk=tbl2.fk INNER JOIN tbl3 ON tbl2.pk=tbl3.fk

After implementing this on all big SQL queries, performance speed increased up to ten times or more!

You have an excellent (visual) explanation of SQL joins in this article.

Photo by
woodleywonderworks

Other thing that helps a lot are SQL table indexes.

Whenever you create table, it has one index and that is primary key.
But you should create indexes on every column that is used to link to other tables or column that would be used in WHERE criteria.

In other words, index should be on fields where server does search.
That is usually foreign keys. Another case are table fields mentioned in WHERE clause.
So making an index on the column speeds up the query.

Photo by kyz

Simple syntax for making an index on tbl2.fk would be:

ALTER TABLE tbl2 ADD INDEX ( `fk` )

This is of course nothing new, just an example from a real situation and experience.