SQL optimization: "multiple queries" vs. "big join"

[mihai11]

Originally Posted by Marco van Herwaarden

First of all, the absolute number of queries is largely overrated as a measurement of how effective the script is. I can have 10 queries that have less impact on performence then a single badly written query.

Now to answer your question: based on the information you provide this is not possible to give a good answer as it will depend on many factors.

Some of them:
- How many rows in each table.
- The datatype of the columns used to JOIN/WHERE
- The cardinality of the data in these columns (the number of unique values compaired to the total number of rows). Selecting on a column containing a unique ID is something totally different then using a column with only 2 unique values (yes/no for example).
- The order in which the JOINS are done
- the amount of memory needed versus the amount available to MySQL on the server
- The storage type of the table.
- ...and many more

I have 4 tables:

1. story table - thousands of entries
2. thread table - thousands of entries (in fact this is the thread table from VBulletin)
3. user table - thousands of entries on a big forum (this is the user table from VBulletin)
4. icon table - less than 50 items; this is the "icon" table from Vbulletin.

The joins will be made on primary keys of type int.

I am not sure about the storage type, but I think it is INNODB. I have to check about this on the server, if you think that it is important.


First of all, just by answering your question I got a different perspective. Tables 1, 2 and 3 can all be HUGE tables. That being the case it might be better to avoid the join completely because it can use insane amounts of RAM when the forum grows.

Currently, I have a join between "story" table and "thread" table. I don't know if I should keep this join. Hmm.... what to do ? If I will kill this join then I will easily get 60-70 queries per page.