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

as wel as increase the performance what we have to do

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

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

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

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

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

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