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.

Database management by single PHP file

Yesterday I found myself in situation when I had to look for something else regarding MySQL database administration. Google search led me to something surprising. It is named "Adminer" and it exists in one single PHP file.

On it's web page, it has a detailed comparison with similar, well known tool, PhpMyAdmin which I had been using for years.

http://www.adminer.org/en/phpmyadmin/

I decided to give it a try and downloaded version "for Mysql".

Since there were no installation instructions, I just put it in my local htdocs directory. Then entered in browser address bar: localhost/adminer-3.2.2-mysql.php

And voila, login prompt.

After entering your credentials, it is straightforward to use if you have previous experience with similar tools.

I am not a power user of PhpMyAdmin, however, I see that Adminer has all features like the former tool.

Look and feel is simplistic. But you can choose some of available skins. Download css file and copy it in the same directory where you put adminer-x.x.x.php.

Do you know of something similar? Share your experience.