mysql joins fast?

Anon

Are mysql joins of tables fast or should i avoid using them on frequently accessed pages (5 hits per sec.)?

A example:

SELECT f.forum_name, c.cat_title
from forums f
left join categories c on c.cat_id = f.cat_id
left join x x on x.x= x.x

left join x x on x.x= x.x

Anon

I forgot to tell the size of the tables are around 1.000.000 rows.

Anon

MySQL joins are pretty fast, but every join is going to slow down your query and put more pressure on the database server.
There's no reason why joining 5 tables should cause any problems. You should make sure that the columns you are joining on are indexed. Where possible, use inner joins in preference to outer joins. Avoid using "OR" or "IN" in where clauses. Select only the columns you need to display, and only rows you intend to display (use "LIMIT" to limit the number of rows returned).
If you find your query becomes unbearably slow, you could always create a secondary table which is preloaded with the query results at regular intervals, but this should really be a last resort, as it will cause latency problems.
Hope these tips help.
James

[deleted]

Then you'll need memory, and lots of it 🙂

Anon

Sorry, but could you clearify your reply?

The result set returned by the query will not contain 1 million rows, maybe only 20 rows, there are only some tables in the database with each 1 million rows.

Or do you mean that the tables in the mysql database will be put into the memory?

Thanks,
Twan

Anon

Thanks for the very helpfull tips! I really appreciate your reply.

Thanks,
Twan

Anon

It doesn't need to load the whole table into memory, but the more memory you have, the more MySQL can cache, and the less it needs to access the disks. If your tables are well indexed, it you should be able to get reasonable performance from a modest server configuration.
You mention selecting 20 rows at a time. If your "main" table has a million rows, and it looks up values from other smaller tables, than I don't see any major problem.
Hope this helps.
James

[deleted]

James explained it alread.

In order to do a join, the database will have to have access to the data in the tables. If you don't have proper indexes, or you do the 'wrong' query, the database will have to read every record in the joined tables, and it may have to do that several times. If you are low on memory, that can take quite a while...