Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
12-05-2010, 04:32 AM #1
- Join Date
- Jul 2009
- Iowa City, IA
- Thanked 5 Times in 5 Posts
speed of 5 INNER JOINS vs. one table
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:
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:PHP Code:
$q = "select * from table where file = variable and
library = var2 and class = var 3, etc, etc..."
$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...
thanks for any input!
12-05-2010, 04:38 AM #2
- Join Date
- Feb 2009
- Thanked 63 Times in 54 Posts
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.