MySQL Performance: MySQL => which is fastest: a join or one big table?

which is fastest: a join or one big table? [message #1284]

Mon, 21 May 2007 22:43

bluem
Messages: 15
Registered: November 2006

Junior Member

I seem to be facing two possible scenarios. Which of these would offer the fastest SELECT?

Option 1) A master table that references 3 other tables via IDs. The nature of the SELECT will require that *every* row of the master table will be joined with a row from one or all of the 3 other tables. Any column in any affected table could be selectable, sortable or "countable" -- and so could benefit from an index; but will all the -joined- table indexes be usable?

Option 2) All data is in one big table already, with indexes on any selectable column, so virtually any query would be directly indexed; no joins are made. The number of columns has been multiplied greatly (and with much empty data) because I have essentially already joined everything myself and inserted the data into a big, fully indexed table. I wonder if this is better because MySQL need not create a temporary (joined) table.

Any advice is appreciated.

Report message to a moderator