Hello and welcome to our community! Is this your first visit?
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Regular Coder ajetrumpet's Avatar
    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:

    • 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:
    PHP Code:
    $q "select * from table where file = variable and
    library = var2 and class = var 3, etc, etc..." 
    there's that. and then there's:
    PHP Code:
    $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!

  • #2
    Super Moderator
    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.
    lamped.co.uk :: Design, Development & Hosting
    marcgray.co.uk :: Technical blog


    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts