SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Hybrid View

  1. #1
    SitePoint Evangelist mrwooster's Avatar
    Join Date
    Jan 2006
    Posts
    518
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to optimise a large database

    I have a database which contains approx 80,000 records and receives about 2000 new records every day. The records are regularly queried and the database is approx 120MB in size.

    My server is using up a lot of RAM and I am thinking that it might well be due to the the database... is there any way to optimise the database to reduce the ram usage? I have heard of cases where the table is split up into several smaller tables which hold, for example, table_1 -> records 1-5000, table_2 -> records 5000-10,000 , and so on....

    Would this help reduce memory use? Or are there any other techniques?

    Thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    2000 new rows per day = less than a million in a year

    this is not a small table, but it's also not a very large table either

    how many tables in your database? sounds like only one

    what kind of indexes have you defined? are any of your queries running slowly, and if so, have you done an EXPLAIN on them?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist mrwooster's Avatar
    Join Date
    Jan 2006
    Posts
    518
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey,

    The database has 4 tables but the other 3 are tiny in comparison (they hold user preferences etc....).

    There is only 1 index defined on the table.... I am not an expert in Database design so I am not sure if this is good or bad... should I be defining more indexes?

    I get the impression that the queries are running a bit slow... I can browse pages of the site very fast, but as soon as you arrive on a page which is making database requests, the page takes a bit longer to load.... however... I have noticed that phpMyAdmin and my wordpress blog both run very fast and so is it possible that I have done some sloppy coding? Or does phpmyadmin cache database info?

    This is the output of the EXPLAIN:

    Code:
    +-------------+---------------------+------+-----+---------+----------------+
    | Field       | Type                | Null | Key | Default | Extra          |
    +-------------+---------------------+------+-----+---------+----------------+
    | id          | int(10) unsigned    | NO   | PRI | NULL    | auto_increment | 
    | timestamp   | int(10) unsigned    | NO   |     | NULL    |                | 
    | code        | varchar(10)         | NO   |     | NULL    |                | 
    | owner       | int(10) unsigned    | YES  |     | NULL    |                | 
    | title       | varchar(255)        | YES  |     | NULL    |                | 
    | description | text                | YES  |     | NULL    |                | 
    | author      | varchar(100)        | YES  |     | NULL    |                | 
    | location    | varchar(255)        | YES  |     | NULL    |                | 
    | hash        | varchar(128)        | YES  |     | NULL    |                | 
    | text        | int(10) unsigned    | YES  |     | 0       |                | 
    | file        | int(10) unsigned    | YES  |     | 0       |                | 
    | expiry      | int(10) unsigned    | YES  |     | NULL    |                | 
    | views       | int(10) unsigned    | YES  |     | NULL    |                | 
    | password    | varchar(128)        | YES  |     | NULL    |                | 
    | ip          | varchar(12)         | YES  |     | NULL    |                | 
    | origin      | tinyint(3) unsigned | NO   |     | NULL    |                | 
    | type        | tinyint(3) unsigned | YES  |     | NULL    |                | 
    | encrypted   | tinyint(4)          | NO   |     | 0       |                | 
    | client      | varchar(255)        | YES  |     | NULL    |                | 
    +-------------+---------------------+------+-----+---------+----------------+
    I really appreciate your help

    Ty

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    that's not an EXPLAIN, that's a phpmyadmin display of your table

    an EXPLAIN is obtained by putting the word EXPLAIN in front of the word SELECT for a given query, and it will tell you how the query is executed

    regarding indexes, please read this: http://articles.sitepoint.com/articl...ql-application
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist mrwooster's Avatar
    Join Date
    Jan 2006
    Posts
    518
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ah.... apologies for my naivety. Thanks for the link, off to work now so will have a look when I get back.

    Thanks

  6. #6
    SitePoint Evangelist mrwooster's Avatar
    Join Date
    Jan 2006
    Posts
    518
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937 .... I just read the article you posted and all I can say is ... WOW ... thanks a lot

  7. #7
    SitePoint Enthusiast freezea's Avatar
    Join Date
    Apr 2009
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    RAQ Report: Web-based Excel-like Java reporting tool.


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
  •