Results 1 to 12 of 12

Thread: performance

  1. #1
    Join Date
    Jun 2007
    Posts
    197

    Smile Unanswered: performance

    how we can test the performane of any select query in mysql

    as wel as increase the performance what we have to do

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you can test performance in two ways:

    1. by actually running the query

    you might take a clock reading before and after, to get elapsed time

    2. by examining the EXPLAIN output

    look for things like table scans and temporary tables


    there are many ways to increase performance—it is a career, not a task—but the most effective is to declare indexes on join columns, searched columns, and/or sort columns
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2007
    Posts
    197

    Smile

    Quote Originally Posted by r937
    you can test performance in two ways:

    1. by actually running the query

    you might take a clock reading before and after, to get elapsed time

    2. by examining the EXPLAIN output

    look for things like table scans and temporary tables


    there are many ways to increase performance—it is a career, not a task—but the most effective is to declare indexes on join columns, searched columns, and/or sort columns

    its not easy that you take clock and measure the time taken by query
    if there is some command to check the query time taken so please tell

    through EXPLAIN statement there is no information about performance
    Last edited by ankur02018; 10-01-07 at 11:09.

  4. #4
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    The EXPLAIN statement is ALL about performance, read the documentation if you don't understand the output you're getting http://dev.mysql.com/doc/refman/5.0/en/explain.html
    It won't give you a time, but it will tell you what your query is doing to get it's results which should help you identify where the slow parts are going to be.

  5. #5
    Join Date
    Jun 2007
    Posts
    197

    Smile

    Quote Originally Posted by aschk
    The EXPLAIN statement is ALL about performance, read the documentation if you don't understand the output you're getting http://dev.mysql.com/doc/refman/5.0/en/explain.html
    It won't give you a time, but it will tell you what your query is doing to get it's results which should help you identify where the slow parts are going to be.
    so is there any command through which we can get time taken by select query

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by ankur02018
    so is there any command through which we can get time taken by select query
    Do you want to know the time taken at the server, or the time taken at the client? If you need to know the time at the client, does it matter whether the client is local or remote (logically near or far from the MySQL server)?

    The problem with measuring performance comes in determining what you really want to measure!

    -PatP

  7. #7
    Join Date
    Jun 2007
    Posts
    197

    Smile

    Quote Originally Posted by Pat Phelan
    Do you want to know the time taken at the server, or the time taken at the client? If you need to know the time at the client, does it matter whether the client is local or remote (logically near or far from the MySQL server)?

    The problem with measuring performance comes in determining what you really want to measure!

    -PatP
    if you could tell any command either in server or client throgh which we can get time taken by any select query in secs

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The answers at the server and the client will be different, probably significantly different. Which do you want? If you want the answer at the client, which language(s) are you using there?

    -PatP

  9. #9
    Join Date
    Jun 2007
    Posts
    197

    Smile

    Quote Originally Posted by Pat Phelan
    The answers at the server and the client will be different, probably significantly different. Which do you want? If you want the answer at the client, which language(s) are you using there?

    -PatP
    answer on client

    application is on java

    i am DBA

    using mysql as backhand

  10. #10
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    In which case take a timestamp in java before you execute your SQL query and one after et voila, you have your answer...

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by aschk
    In which case take a timestamp in java before you execute your SQL query and one after et voila, you have your answer...
    Yeah, what aschk said!

    -PatP

  12. #12
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by ankur02018
    application is on java
    There are several alternatives (not a complete list!):

    1) Use P6Spy to log all DBMS activity and their timings

    2) (very cumbersome) write your own timing code in the source code

    3) (cumbersome) Copy and paste the the SQL statements from the application into a SQL client that displays the execution time of a statement

    4) Use a profiler (e.g. the one built into NetBeans http://www.netbeans.org) to profile your application and measure the time for the methods accessing the database

Posting Permissions

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