...

View Full Version : speed of 5 INNER JOINS vs. one table



ajetrumpet
12-05-2010, 04:32 AM
all,

I am going to have thousands of rows of data in a database very soon (maybe hundreds of thousands), and I'd like to know for instance, if I have 10-20 people concurrently connected to my site and all of them querying my database data in and around the same times, would I be better off to put all of the data in one large table and duplicate many of the fields that, naturally, should be related between different tables, or inner join them and write php queries using 5-10 tables?

here is an example of what I'll have:


file
library
class
interface
member
parameter
name
value
returnvalue
vartypeinfo
parent


what I'm wondering is, what's the difference in speed for querying the same db, if I have one table, with all of those above fields in it, and have 500,000 rows and writing one query and checking the fields against inputs like this:
$q = "select * from table where file = variable and
library = var2 and class = var 3, etc, etc..."there's that. and then there's:
$q = "select files.file, libraries.library, interfaces.interfaces, members.member

inner join files on files.file = libraries.file

inner join libraries.library on interfaces.library, etc, etc...does that make sense to anyone? which would be faster with PHP and MYSQL? thoughts anyone?

thanks for any input!

Lamped
12-05-2010, 04:38 AM
Speed will vary a lot on a case by case basis. I'm sure there will be performance gains to "flattening" your table like that, but I doubt it'll be much.

I would simply recommend ensuring you put indexes on every field that's part of a join condition.

Don't underestimate the speed increase a well designed database can have.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum