Timing Queries

[Anon]

Hi,

I'm testing the performance of a series of queries in a batch file with and without indexes. I'm doing it like this:

mysql databasename < queries.sql > results.txt

This works fine, but I want to be able to time the queries. Oracle provides the commands 'start time' and 'stop time' that you can include in your SQL statements to achieve this. Is there a similar thing in MySQL, I couldn't find anything in the MySQL manaual :-(

[Anon]

I'm not sure, but if you are running it on a unix box you could just alter your command to look like this:

date; mysql databasename < queries.sql > results.txt; date

This will print a date/time string before and after your command that looks like:

Fri Jan 19 09:10:57 PST 2001

But that's not really what you want to do. You don't want to judge your queries efectiveness on the time it takes it to run since that is completely dependant on the data. What you should do is run your queries through your database EXPLAIN operator. This operator will parse your query and tell you everything that the database is doing to make it happen, so you can track down where you aren't using Indexes, or where you need to add them.

For MySQL checkout: http://www.mysql.com/doc/E/X/EXPLAIN.html

[jblallement]

If running on a unix box use time:

time mysql databasename < queries.sql > results.txt

This will output a lot of useful infos.
( Be careful with redirects )