It’s important to know how to write your queries fast. And there are also ways to do your queries faster Inside mysql, called Indexes. They help mysql do the work faster, but you need to tell how…

When you start getting large tables you can sometimes end up with slow running queries.
It’s important to optimize your queries and tables from the beginning, so you don’t end up with slow queries one year later when your tables have increased.
Badly created tables and queries is hard to find when you only have limited data in your tables. But there are tools to use to get a hint on if you will have problems later…

First the basics behind the scen in databases. Let’s say you are combining three tables with a join. Then mysql has to look through all three tables and then find a way to combine them in the correct way.
Take this sql query that will find the person “donald duck” and information connected to him in three tables:

SELECT a.id
FROM a, b, c
WHERE a.id = b.a_id
AND b.id = c.b_id
AND c.name = “donald duck”

It’s a pretty silly query, but I hope you can get the point anyway :-)

Let’s say there are 2 rows in each table. What mysql does now is to start looking through all tables from the top down to the bottom and try to find matches.
The important thing to know about this is some math. Either you understand it or you just trust me that it is correct…
To combine all three tables, mysql needs to go through 2*2*2 combinations. This is 8 combinations to find the correct one.
Doesn’t sound that much of a deal, but let’s say we have 500 rows in the tables each. This gives our beloved database 500*500*500 = 1 250 000 different combinations to go through.

Luckily there are some smart people that have found a way to deal with this :-)

It’s called “Indexes” and they help the database to cut down on the number of rows it has to go through to find the match.

The first thing you should ALWAYS do is to make the id in your tables to a PRIMARY key. Then you get that column indexed.
Look at indexes as a way to decrease the number of rows to look through to find the match. You put an index on a seperate column and you should only put it on columns that need it, otherwise your queries will go slower instead.

A basic rule that works very well is that you should put indexes on columns that your “search on” and combine in JOIN’s.

So if we look at the query above, what columns might be interesting to put indexes on?
First of all, make a.id, b.id, c.id PRIMARY keys and they get indexed automaticly.
After that I would set b.a_id and c.b_id as indexes. That is because they are used to combine the tables. If we can decrease the number of rows that needs to be looked through there, the queries will be faster.
The last column that might be interesting is c.name.

A summary:

- Make the id in each table PRIMARY
- Make columns that are “foreign keys” Indexed.
- If you have any other columns that are use in the WHERE clause, make this Indexed aswell.

This is a simplified guide on how to fasten your sql queries, but very good to know the basics.
I will soon write a tutorial on how to check if your queries are slow or fast, and what to do about it…

Read more about Sub queries here at 999tutorials.com

Similar Posts:

  • Share/Bookmark