{how to optimize mysql database}

Discussion in 'Databases' started by Sunil Keshari, Dec 6, 2012.

0
  1. #1
    I am running an article website where it consume more CPU usage
    hosting company says optimize your database it consuming more then 25% because its on shared server.

    Some one please guide me
    Mysql
    or suggest me some database caching
     
    Sunil Keshari, Dec 6, 2012 IP
  2. Rukbat

    Rukbat Well-Known Member

    Messages:
    2,908
    Likes Received:
    37
    Best Answers:
    51
    Trophy Points:
    125
    #2
    No one can give you any specifics without being able to get to the database itself, and seeing your code. And that's not a 5 minute web post, that's a multi-day analysis.

    To start, study what 'normalization' means, and normalize your database.

    Then look at your code and make sure it's making the most efficient use of the database. Making 10 calls to the database where 1 will return the same data is inefficient.
     
    Rukbat, Dec 6, 2012 IP
  3. arsalankhan

    arsalankhan Peon

    Messages:
    66
    Likes Received:
    0
    Best Answers:
    6
    Trophy Points:
    0
    #3
    As Rukbat said that no one could give you an exact solution without looking at your current system, so it is better to hire a database pro. But you can try these 4 general solutions and see how they work for you:

    1) In your case since you aren't twitter or facebook normalizing database will work because it will reduce the number of queries.
    2) Try database sharding.
    3) If you are a programmer try to pre-compute complex or large queries, save the result in separate table and query it to display the results to end user.
    4) Try to implement caching.
     
    arsalankhan, Dec 6, 2012 IP
  4. logicrays

    logicrays Greenhorn

    Messages:
    17
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    11
    #4
    - Try to reduce large result set
    - Checkout MySQL variables and optimize it based on your requirement
    - The main and key point is to add Indexing on columns which you use in where condition which reduce a load upto 20-30%.
    - Use query caching and get slow SQL query log which helps you better on slow queries.
    - Use explain on slow queries which gives you idea how to optimize that query.
     
    logicrays, Dec 12, 2012 IP
  5. HostRush

    HostRush Active Member

    Messages:
    42
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    56
    #5
    One thing you could do is use the Mysqli functions if your using the old Mysql class.

    You could index your tables, so a query doesn't have to do a table scan.
     
    HostRush, Dec 15, 2012 IP
  6. Niktator

    Niktator Member

    Messages:
    30
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    48
    #6
    Well, you need to know how often he does write data. Creating to many indexes will impact heavy on write performance....

    greets, Nik
     
    Niktator, Dec 18, 2012 IP
  7. iceh

    iceh Greenhorn

    Messages:
    14
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    11
    #7
    Optimize Database makes website faster, Its true..First look for the Tables- Compare for usage, Delete unnecessary comments and words.Then Check, Repair, Tables to rebuild there
     
    iceh, Dec 20, 2012 IP