Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

sql question

Options
  • 21-10-2006 10:46am
    #1
    miju
    Banned (with Prison Access) Posts: 8,486 ✭✭✭


    can anyone offer any pointers for speeding up a SQL database, one of my sites has been running very slow the last week and the only reason i can think of is becuase of the db

    pointers very much appreciated guys


Comments

  • Options
    #2
    steve-hosting36
    Closed Accounts Posts: 2,161 ✭✭✭steve-hosting36


    move the db to it's own server? add more ram?


  • Options
    #3
    Webmonkey
    Registered Users Posts: 9,579 ✭✭✭Webmonkey


    Not that all big of an expert on DB's but don't indexes make db faster or something. Would have to look into it, sure you'd find info on google


  • Options
    #4
    is_that_so
    Registered Users Posts: 32,136 ✭✭✭✭is_that_so


    First two questions

    Have you tested performance of the website without the DB data ?

    Have you checked the site code for things that could be locking or
    slowing it down. Session variables, newly amended code etc.

    If so then a few things to check

    What is the Db?

    Anything other Access

    Where is it ?

    As posted earlier a separate server is good both for performance and security.
    How is it set up? If you have lots of traffic this may need to be optimised.

    DB Structure

    What kind of tables and how many tables have you got.
    What are you storing in there? BLOB(Binary Large Objects) can get big
    Are the tables fully normalised?

    Check the size of the fields in the tables. Are the field sizes appropiate?
    No point using a BLOB when a char(20) or similar will do. It just makes for a bigger table.


    Indexes

    Add indexes to the lookup columns . It will make them faster.
    DO NOT index the whole table, just the lookup fields( where x=y)
    Remember full table scans on large tables can take longer(select * from table)

    Queries

    Look at your queries
    Is any query the result of multiple joins ?



    Check out the DB supplier

    MySQL has a number of tools you can use to optimise but you need to know what you are doing. Just search for "optimize" on the www.mysql.com

    All other DB suppliers will offer something similar although google and forums tend to give better support.


  • Options
    #5
    miju
    Banned (with Prison Access) Posts: 8,486 ✭✭✭miju


    steve-hosting36 wrote:
    move the db to it's own server? add more ram?

    two dbs on two seperate servers already , 1 running slow the other is perfectly fine


    thanks very much for the pointers is_that_so, i'll check a few of them out , theres a few aps running of one of the dbs but only 1 would be SQL intensive with the others being minimal , just realised theres been about 10-15 crawlers / spam bots at a time on the site so my answer may well lay there


Advertisement