SQL query : inner joins optimization between big tables

Question

I have the 3 following tables in a MySQL 4.x DB :

• hosts: (300.000 records)
  • id (UNSIGNED INT) PRIMARY KEY
  • name (VARCHAR 100)
• paths: (6.000.000 records)
  • id (UNSIGNED INT) PRIMARY KEY
  • name (VARCHAR 100)
• urls: (7.000.000 records)
  • host (UNSIGNED INT) PRIMARY KEY <--- links to hosts.id
  • path (UNSIGNED INT) PRIMARY KEY <--- links to paths.id

As you can see, the schema is really simple but the problem is the amount of data in these tables.

Here is the query I'm running :

SELECT CONCAT(H.name, P.name)
FROM hosts AS H
INNER JOIN urls as U ON H.id = U.host
INNER JOIN paths AS P ON U.path = P.id;

This query works perfectly fine, but takes 50 minutes to run. Does anyone have any idea about how I could speed up that query?

Thanks in advance. Nicolas

Answer 1

Perhaps you should include a WHERE clause? Or do you really need ALL the data?