Optimizing sql join query, comparing query effectiveness

I'm a student working on a module for moodle cms (course management system) of my college. I have to write some join queries for my module. I can not make changes to table structures, they are pretty much set in stone (I didn't make them, they were given to me).

I have no experience with writing queries for large databases. I've created a working prototype of my module and now I'm trying to organize the code/optimize queries etc.

Tasks:

| id     | task    |
--------------------
| 1      | task1   |
| 2      | task3   |
| 3      | task3   |
| 4      | task4   | 
| ...    | ...     |

Assets:

| id     | asset   |
--------------------
| 1      | task1   |
| 2      | task3   |
| 3      | task3   |
| 4      | task4   |
| ...    |   ...   |

TaskAsset:

| id     | taskid  | assetid  | coefficient   |
-----------------------------------------------
| 1      |    2    |     33   | coefficient1  |
| 2      |    5    |     35   | coefficient2  |
| 3      |    6    |     36   | coefficient3  |
| 4      |    8    |     37   | coefficient4  |
| 5      |   ...   |    ...   |      ...      |

$query = "SELECT TaskAsset.id as id, Assets.asset AS asset, Tasks.task AS task
, coefficient
FROM Tasks, Assets, Taskasset
WHERE Taskasset.taskid= Tasks.id AND TaskAsset.assetid = Assets.id";

$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result))
{
  echo $row['id']." - ".$row['asset']." - ".$row['task'] . $row['coefficient'];
  echo "<br />";
}

Questions:

1.) So, if table structures are like t开发者_Python百科hese, is my query effective? If they are, is a simple join still effective if I have to join more tables? Like 4 or 5?

2.) How do I rate effectiveness of queries? In phpmyadmin, I can see the time it took for the query to run. I've never used anything else for this because my tables had very few records, so it did not matter.

The only thing that I would do differently is explicitly specify the joins.

$query = "SELECT ta.id as id, a.asset AS asset, t.task AS task
, coefficient
FROM TaskAsset ta
JOIN Tasks t ON ta.taskId = t.id
JOIN Assets a ON ta.assetId = a.id";

This does the same thing but I personally prefer it a lot better. That said, you should try to run an EXPLAIN on your query. That is where you'll see the pressure points.

Your query is fine as is from an optimality standpoint, assuming indexes are present on the id fields of the tables. With the right indexes, you can join many more tables and the performance will still be good.

You should try to get yourself familiar with the ANSI join syntax - as this is much easier to read than the old FROM x, y, z ... style joins - and it's also more difficult to get wrong!

This query is appropriate for the results that you want.

TaskAssets is a mapping table that is meant to join columns of Task and Asset together by foreign keys. You need to view columns from all three tables for your result set so this is the most efficient way for it to be done.

What might be even more important than the query are the indexes in the tables.

You are doing

SELECT ta.id as id, a.asset AS asset, t.task AS task, coefficient
FROM TaskAsset ta
JOIN Tasks t ON ta.taskId = t.id      <-- equi join here
JOIN Assets a ON ta.assetId = a.id    <-- another equi join.

This query has two equi joins.

1. Always assign indexes on fields involved in an equi-join.
2. Consider assigning indexes on fields involved in a where clause (this query doesn't have any but that's beside the point)
3. Strongly consider putting an index on a field used in a group by clause