SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot Offspring2099's Avatar
    Join Date
    May 2005
    Location
    Los Angeles, CA
    Posts
    182
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question DB tables are getting out of hand...

    Some of my tables, which contain transaction info are now up to and over 2 million records. This number will only continue to grow. I can see that the queries are now taking more time to process. At this point can someone point me into a direction of what general steps to take to improve the query time.

    I write pretty good sql, that's not the problem, because most of the queries are simple. I know a little about indexies and try to use them where I think it's needed.

    Can some of you share what steps you take, to set up a DB to handle a huge load or entries?

    Thank you.

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    start with EXPLAIN. do you know how to use that? you want to make sure your queries are using the most useful indexes.

    from there you move on to tweaking the mysql memory settings. start with making the key cache large enough to hold indexes from all of your commonly used tables.

    then make the table cache big enough to hold the results from your largest queries.

    also make sure that if your EXPLAIN's show temp table usage, then you need to configure your temp table memory settings large enough to hold those results.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    proper indexes would take 2 million records in stride

    2 billion or 2 trillion might reqiure special attention

    the other strategy you might employ is to archive your older ones off to an archive table, which, yes, will be slow to access, but then you wouldn't allow joe public to access it, it would be there just for you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Zealot Offspring2099's Avatar
    Join Date
    May 2005
    Location
    Los Angeles, CA
    Posts
    182
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    proper indexes would take 2 million records in stride

    2 billion or 2 trillion might reqiure special attention

    the other strategy you might employ is to archive your older ones off to an archive table, which, yes, will be slow to access, but then you wouldn't allow joe public to access it, it would be there just for you
    Do you know a good tutrial for indexing?

  5. #5
    SitePoint Zealot Offspring2099's Avatar
    Join Date
    May 2005
    Location
    Los Angeles, CA
    Posts
    182
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    start with EXPLAIN. do you know how to use that? you want to make sure your queries are using the most useful indexes.

    from there you move on to tweaking the mysql memory settings. start with making the key cache large enough to hold indexes from all of your commonly used tables.

    then make the table cache big enough to hold the results from your largest queries.

    also make sure that if your EXPLAIN's show temp table usage, then you need to configure your temp table memory settings large enough to hold those results.
    Thank you, I will look into Explain. I have never used it, so if you know a link to a tutorial or any site let me know. If not thx for the help so far.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Offspring2099
    Do you know a good tutrial for indexing?
    i'd start with mysql.com

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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