MySQL optimization people, lend me your ears!

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • StuartD
    Sofa King Band
    • Jul 2002
    • 29903
    #1

    MySQL optimization people, lend me your ears!

    Alright, I'm doing up a database for a new project and would like to hear your opinion on something.

    It's a member base site.... where they enter stuff into the db. No biggy there. Now, is there any benefits other than what's listed below to having each user have their own table?

    In other words, instead of having a table that has ALL entries and a "userid" field to index by, is it worth having individual tables for each user: "entries_1", "entries_2", "entries_3" and so on?

    The benefits I can see off hand are that if a user's table becomes corrupted or locked or what have you... it won't affect the other users.

    The thing that makes me wonder is... would it have any impact on performance? Is it faster to have user's info separated so that there's less "searching" through records or is it slower to have a larger volume of tables to have to reference?

    Thoughts?
    This is me on facebook
    This is me on twitter
    Tags: None
    • Babaganoosh
      ♥♥♥ Likes Hugs ♥♥♥
      • Nov 2001
      • 15841
      #2
      Giving the users their own table would be a huge waste. Put them all in one table and index that table properly.
      I like pie.

        Comment

        • adonis
          Confirmed User
          • Oct 2002
          • 231
          #3
          I would use one table for all. Remember to put "index" on Userid field, this would speed up your queries based on Userid.

            Comment

            • tical
              Confirmed User
              • Feb 2002
              • 6504
              #4
              yup 1 table
              112.020.756

                Comment

                • StuartD
                  Sofa King Band
                  • Jul 2002
                  • 29903
                  #5
                  Originally posted by adonis
                  I would use one table for all. Remember to put "index" on Userid field, this would speed up your queries based on Userid.
                  Not indexing it would be stupid, so yeah, I do have it indexed.

                  Has anyone actually tried to test the differences in performance between the two? Seen any actual results either way?
                  This is me on facebook
                  This is me on twitter

                    Comment

                    • Trafficbrokercom
                      Confirmed User
                      • Dec 2002
                      • 542
                      #6
                      a) InnoDB

                      or

                      b) Use Mysql's "Handler" Function:

                      http://dev.mysql.com/doc/mysql/en/handler.html


                      There are several reasons to use the HANDLER interface instead of normal SELECT statements:

                      *

                      HANDLER is faster than SELECT:
                      o

                      A designated storage engine handler object is allocated for the HANDLER ... OPEN. The object is reused for the following HANDLER statements for the table; it need not be reinitialized for each one.
                      o

                      There is less parsing involved.
                      o

                      There is no optimizer or query-checking overhead.
                      o

                      The table doesn't have to be locked between two handler requests.
                      o

                      The handler interface doesn't have to provide a consistent look of the data (for example, dirty reads are allowed), so the storage engine can use optimizations that SELECT doesn't normally allow.
                      Basically what happens is that instead of one query locking the tables eg on a join the query is executed independently for each row and thus avoids locking.

                      this would be the professional solution ;)
                      Last edited by Trafficbrokercom; 04-06-2005, 11:59 AM.

                        Comment

                        • Robertf
                          Confirmed User
                          • Feb 2004
                          • 392
                          #7
                          1 table ...

                          I run a mainstream membership site with about 25k members at teh moment .. the user table does not only hold name password and stuff but also other variables wich get updated/changed constantly .. and it is no problem.

                          Special when you want to do search functions you want to have 1 table ..

                          Incase it becomes to big .. and you need to balance teh load .. make 2 tables a-n m-z .

                          Incase your usertable .. only holds 'static' information ... username password blah blah .. also make just 1 table .. but have a seperate memory loaded table for the people that are logged in .. (and log them out after a 'set' time of no activity).

                          Meh did that help?
                          Anyhow i just tried to say .. iwould go for one table .. because 1 table can easely handle it ..unless you talk about 100k's of members .. and just easier to throw search queries on it.
                          ....

                            Comment

                            • aleck
                              Confirmed User
                              • Jan 2001
                              • 940
                              #8
                              ONE fucking table.
                              come trade shemale traffic here

                                Comment

                                • StuartD
                                  Sofa King Band
                                  • Jul 2002
                                  • 29903
                                  #9
                                  hmm... handler... interesting. That seems to work much better.
                                  This is me on facebook
                                  This is me on twitter

                                    Comment

                                    • JSA Matt
                                      So Fucking Banned
                                      • Aug 2003
                                      • 5464
                                      #10
                                      A table for every member? I take it you don't plan to have a lot of members?

                                        Comment

                                        • StuartD
                                          Sofa King Band
                                          • Jul 2002
                                          • 29903
                                          #11
                                          Originally posted by JSA Matt
                                          A table for every member? I take it you don't plan to have a lot of members?
                                          nope, not really... a smallish mainstream project. Lots of programming work but not a major database hog... just something that will definitely need to be kept separated from user to user. I don't want them inconvenienced by mysql "glitches"
                                          This is me on facebook
                                          This is me on twitter

                                            Comment

                                            Previous template Next
                                            Working...