Get time for MySQL result without showing result

Question

I realized that using phpMyAdmin for testing the speed of queries might be dumb: it automatically applies a LIMIT clause.

I tried a certain query on a fairly large number of records (31,595) with a GROUP BY clause. phpMyAdmin, adding LIMIT 0, 200, took 0.1556 seconds to fetch the results.

I decided to try the same query from the command line without the LIMIT clause and it took 0.20 seconds. Great, so now I have the real time it takes for that query.

But the downside is I had to wait for 30,000+ records to print on the screen.

Is there a better solution?

EDIT: To clarify, I am looking for a way to suppress the screen output of a select query while still getting an accurate time for running the query. And I want it to be something that could be typed in and timed at any time (i.e. I don't want to have to tweak slow log settings to capture results).

Przemyslaw Kruglej · Accepted Answer · 2013-11-09T00:53:10.883

6

You could enclose your query in SELECT COUNT(1) to count the number of rows returned, without having all the rows printed out:

SELECT COUNT(1)
  FROM (
    <<you query goes here>>
) t;

edited Nov 09 '13 at 00:53

answered Nov 09 '13 at 00:46

Przemyslaw Kruglej

8,003
2
26
41

This seems like it could work. I will test it and get back to you. – Buttle Butkus Nov 11 '13 at 07:31
@ButtleButkus, it's good solution, but with this query you cannot measure wasting time on network communication. – sectus Nov 11 '13 at 07:47
I have not actually tried this yet but I see no reason why it wouldn't work. It's a pretty obvious "hack" that I should have thought of myself! – Buttle Butkus Dec 12 '13 at 00:04
@sectus I'm really mostly concerned with MySQL's execution time, not communication time. I'm comparing 2 queries that return the same result, so the communication time should be the same. – Buttle Butkus Dec 12 '13 at 00:05

sectus · Answer 2 · 2013-11-11T07:45:25.703

1

You could use console client mysql and time

$ time mysql -u user -h host -ppassword -e "show databases;" > /dev/null
real    0m0.036s
user    0m0.008s
sys     0m0.008s

edited Nov 11 '13 at 07:45

answered Nov 09 '13 at 00:48

sectus

15,605
5
55
97

Good idea. But it doesn't quite work. But a password is required for my mysql user. `time` starts counting as I'm entering my password. My fastest time so far is 7 seconds. – Buttle Butkus Nov 11 '13 at 07:28
@ButtleButkus, add password to command. See [manual](http://dev.mysql.com/doc/refman/5.6/en/mysql-command-options.html) and updated answer. – sectus Nov 11 '13 at 07:45
For this method, you'd add `user` and `sys` values to get the total CPU time. I'm curious how that would compare with MySQL's own statistics. Also, it's not secure to use passwords on the command line, but you can use `--login-path` option. I occasionaly alias 'mysql` to `mysql --login-path=local` for example. So I can run your command like this: `time mysql -u user -h host -e "show databases;" > /dev/null` https://dev.mysql.com/doc/refman/8.0/en/mysql-config-editor.html – Buttle Butkus Dec 15 '22 at 07:54

score 1 · Answer 3 · answered Nov 09 '13 at 12:10

1

I guess that what you really want is to obtain the best possible speed for your query, not really to time it.

If it's the case, type your query in phpMyAdmin (its adding a LIMIT clause is not important) then click on the "Explain SQL" link to see whether you are using indexes or full-table scans.

answered Nov 09 '13 at 12:10

Marc Delisle

8,879
3
29
29

Marc you are right, but I do want to be able to see the time it takes for my queries to run without the LIMIT. Then I want to be able to see the time taken by an updated query, or same query with updated schema. I know I could use slow log to record these queries. But I'm just looking for a quick way to do this for any manually entered query any time. – Buttle Butkus Nov 11 '13 at 07:24

Get time for MySQL result without showing result

3 Answers3