MySQL 5.7 – SQL Statement Timing from Client Using Script

MySQL

When I run a SQL statement interactively in a MySQL 5.7 client it tells me how long the query ran:

mysql> select now();
--------------
select now()
--------------

+---------------------+
| now()               |
+---------------------+
| 2018-03-09 14:27:42 |
+---------------------+
1 row in set (0.07 sec)

In this case it ran for 0.07 seconds.

But when I run the same SQL statement through a script the output does not include the time the statement took.

>mysql -f -v --table -u myuser -pmypassword -h myhost mydb < test.sql

mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
select now()
--------------

+---------------------+
| now()               |
+---------------------+
| 2018-03-09 14:31:00 |
+---------------------+

How can I run a SQL statement from a script and still get back the length of time that it ran using the MySQL 5.7 client?

Thanks!
Bobby

Best Answer

You can use -vvv with mysql command.

mysql -f -vvv --table -u myuser -pmypassword -h myhost mydb < test.sql

> mysql -vvv < mysql.sql 
--------------
select now()
--------------

+---------------------+
| now()               |
+---------------------+
| 2018-03-10 04:08:33 |
+---------------------+
1 row in set (0.00 sec)

Bye

Hope it will fulfill your requirement :)

Related Solutions

Mysql – index doesn’t work properly

I am not a MySQL expert (or even a user!) but the ideal index for your query appears to be as shown below:

create table `single` (
  `game_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` mediumint(9) NOT NULL,
  `stage` tinyint(4) NOT NULL,
  `highscore` bigint(20) unsigned NOT NULL,
  `played_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`game_id`),
  KEY `user_id_stage` (`stage`,`user_id`, `highscore`)
)

The plan I get is:

Explain

See the SQLfiddle

MySQL Select Statement – How to Make a Select Statement Get Blocked

Deadlocking by SELECTs can be done in a variety of ways. I have written posts about them

You can have SELECTs get deadlocked by UPDATEs and DELETEs
- Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE?
You can have UPDATEs and DELETEs blocked by SELECTs
- How are DB locks tied to connections and sessions?
- Is Oracle DB immune to the InnoDB deadlocks found in MySQL?
You can just lock the table before the SELECT and unlock it afterwards
- LOCK TABLES table READ;
- SELECT ... ;
- UNLOCK TABLES;
You could perform a large UPDATE in a transaction with the SERIALIZEABLE isoaltion level. The MySQL Documentation on SERIALIZEABLE isoaltion level says:

This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... LOCK IN SHARE MODE if autocommit is disabled. If autocommit is enabled, the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (nonlocking) read and need not block for other transactions. (To force a plain SELECT to block if other transactions have modified the selected rows, disable autocommit.)

Cutting right to the chase, you could just use

SELECT ... FOR UPDATE, then try SELECT against those rows
SELECT ... LOCK IN SHARE MODE, then try to UPDATE or DELETE those rows
See the MySQL Documentation

Give it a Try !!!

Best Answer

Related Solutions

Mysql – index doesn’t work properly

MySQL Select Statement – How to Make a Select Statement Get Blocked

Related Question