WEB Holy Shit. I just increased the speed of my site by about 3-fold by doing two things

Discussion in 'OT Technology' started by TurkeyChicken, Aug 24, 2007.

  1. TurkeyChicken

    TurkeyChicken New Member

    Joined:
    Jun 26, 2003
    Messages:
    42,913
    Likes Received:
    0
    Location:
    Albuquerque, NM
    I'm nearing 7k active users on ClearCheckbook, many of which have been there for 8-12 months so far and have entered thousands of transactions (Over 668,000 to be exact). These two things combined made for extremely long load times as the database was freaking out with all the data in it.

    Well, last night I did two things to fix this:

    1) Index columns in my database tables. Holy fucking shit, this is the greatest thing ever. simply index whatever you commonly search on and that alone speeds things up like crazy. Beware though... I just added about 45megs to the size of my table by indexing 3 columns (from the 668k row table).

    2) Built a new way to calculate the balances for different accounts. I had previously been calculating all of the balances on the fly, so I built a script that stores everyones balances in a table and only has to make one query to grab that, rather than calculate it on the fly each time. Whenever someone adds/edits/deletes a transaction, it updates that one table.

    I've already had people email me and tell me how much of an improvement in speed they're seeing :bowdown:


    Anyone who hasn't indexed your mysql tables, do it right now.
     
  2. CrazyInteg

    CrazyInteg Honda-Acura.net OG

    Joined:
    Dec 30, 2000
    Messages:
    12,205
    Likes Received:
    3
    Location:
    Omaha
  3. P07r0457

    P07r0457 New Member

    Joined:
    Sep 20, 2004
    Messages:
    28,491
    Likes Received:
    0
    Location:
    Southern Oregon
    wow, so now you violate 3NF. I hope you are running a script to audit those balances.

    I would NOT trust you with my dogs name, let alone my financial data.
     
  4. crazybenf

    crazybenf Active Member

    Joined:
    Nov 14, 2001
    Messages:
    15,575
    Likes Received:
    2
    :rofl: I love this forum.
     
  5. drpepper

    drpepper Active Member

    Joined:
    Nov 13, 2006
    Messages:
    38,076
    Likes Received:
    2
    Location:
    San Antonio
    :hsugh:

    you're an idiot.
     
  6. Kevin

    Kevin New Member

    Joined:
    Aug 27, 2002
    Messages:
    87,634
    Likes Received:
    0
    Location:
    Michigan
    Wow. :hsugh:

    i've always loved yoru work but... maybe take a database class.

    Adding an index to tables is one of the first things you should have done. :o
     
  7. auveeb

    auveeb DATS CUTE

    Joined:
    Mar 22, 2005
    Messages:
    11,467
    Likes Received:
    0
    Location:
    New York City, NY
    Is this a joke?
     
  8. Kevin

    Kevin New Member

    Joined:
    Aug 27, 2002
    Messages:
    87,634
    Likes Received:
    0
    Location:
    Michigan
    ANY database, not just mysql. :hs:

    you need indexes. period.
     
  9. TurkeyChicken

    TurkeyChicken New Member

    Joined:
    Jun 26, 2003
    Messages:
    42,913
    Likes Received:
    0
    Location:
    Albuquerque, NM
    I taught myself all this shit, what do you expect :o
     
  10. TurkeyChicken

    TurkeyChicken New Member

    Joined:
    Jun 26, 2003
    Messages:
    42,913
    Likes Received:
    0
    Location:
    Albuquerque, NM
    Way to know what the site's about :io:
     
  11. Kevin

    Kevin New Member

    Joined:
    Aug 27, 2002
    Messages:
    87,634
    Likes Received:
    0
    Location:
    Michigan
    :cool:

    Keep it up.

    if you have any database questions, i know quite a bit, but a guy i share an office with is the DBA of a very large database, he knows almost all there is to know about optimization, etc. let me know and i can answer most questions.
     
  12. Kevin

    Kevin New Member

    Joined:
    Aug 27, 2002
    Messages:
    87,634
    Likes Received:
    0
    Location:
    Michigan
    your site is causing my car to run lean :mad:
     
  13. Aslan

    Aslan My avatar is the new hotness... yours... Old and b

    Joined:
    Jun 1, 2001
    Messages:
    4,823
    Likes Received:
    0
    Location:
    NW burbs o' Chicago
    cmon, what's his name... :dunno:
     
  14. Aslan

    Aslan My avatar is the new hotness... yours... Old and b

    Joined:
    Jun 1, 2001
    Messages:
    4,823
    Likes Received:
    0
    Location:
    NW burbs o' Chicago
    yup usually an id number
     
  15. Kevin

    Kevin New Member

    Joined:
    Aug 27, 2002
    Messages:
    87,634
    Likes Received:
    0
    Location:
    Michigan
    table locks make a huge difference in speed too
     
  16. hsmith

    hsmith OT Supporter

    Joined:
    Feb 24, 2002
    Messages:
    124,469
    Likes Received:
    564
    Location:
    Your mother.
    are you using stored procedures in mysql?
     
  17. hsmith

    hsmith OT Supporter

    Joined:
    Feb 24, 2002
    Messages:
    124,469
    Likes Received:
    564
    Location:
    Your mother.
    and lol @ you for just now figuring out indexes :o
     
  18. Kevin

    Kevin New Member

    Joined:
    Aug 27, 2002
    Messages:
    87,634
    Likes Received:
    0
    Location:
    Michigan
    :mysql: :php: :o
     
  19. TurkeyChicken

    TurkeyChicken New Member

    Joined:
    Jun 26, 2003
    Messages:
    42,913
    Likes Received:
    0
    Location:
    Albuquerque, NM
    I know :Owned:

    The only thing I used to previously index was the primary key
     
  20. TurkeyChicken

    TurkeyChicken New Member

    Joined:
    Jun 26, 2003
    Messages:
    42,913
    Likes Received:
    0
    Location:
    Albuquerque, NM
    No. that's one thing that's always confused me. I need to learn more about them :eek4:
     
  21. Kevin

    Kevin New Member

    Joined:
    Aug 27, 2002
    Messages:
    87,634
    Likes Received:
    0
    Location:
    Michigan
    :hsugh:

    At the core, its just select statements that don't need to be compiled everytime they're called.
     
  22. hsmith

    hsmith OT Supporter

    Joined:
    Feb 24, 2002
    Messages:
    124,469
    Likes Received:
    564
    Location:
    Your mother.
    SP's are a godsend
     
  23. TurkeyChicken

    TurkeyChicken New Member

    Joined:
    Jun 26, 2003
    Messages:
    42,913
    Likes Received:
    0
    Location:
    Albuquerque, NM
    but when 99% of things are completely based on dynamic input such as userid, dates, etc, how does a stored procedure do anything?

    I don't want to store literally hundreds of thousands of queries when they work just fine being called in php.
     
  24. Kevin

    Kevin New Member

    Joined:
    Aug 27, 2002
    Messages:
    87,634
    Likes Received:
    0
    Location:
    Michigan
    thats exactly what stored procedures are built for, and why they're better.

    They take inputs, just like a function. they are 1000 times more powerful than any inline sql from php
     
  25. Kevin

    Kevin New Member

    Joined:
    Aug 27, 2002
    Messages:
    87,634
    Likes Received:
    0
    Location:
    Michigan
    sig +1 for when you're balls deep in stored procedures wondering how you ever tried a sql based website without them :o
     

Share This Page