MySQL Execution Time

mysql execution time

mysql has a builtin profiler. You can enable profiling by issuing set profiling=1; and use show profiles; to get execution times.

Measuring actual MySQL query time

Start the profiler with

SET profiling = 1;

Then execute your Query.

With

SHOW PROFILES;

you see a list of queries the profiler has statistics for. And finally you choose which query to examine with

SHOW PROFILE FOR QUERY 1;

or whatever number your query has.

What you get is a list where exactly how much time was spent during the query.

More info in the manual.

mysql command line return execution time?

You can use

set profiling=1

and then, later,

show profiles

which will give a list of commands and times.

See http://dev.mysql.com/doc/refman/5.0/en/show-profiles.html

h/t http://ronaldbradford.com/blog/timing-your-sql-queries-2010-07-07/

What is a good/healthy mysql query execution time?

Your calculations are on the right path for determining the load on your server. However, it misses the fact that a database server can handle multiple queries at the same time.

This is the difference between "bandwidth" and "latency". "Bandwidth" is the number of queries that can be processed during a period of time. "Latency" is the amount of time it take to get results back from one query.

In general, when you are looking at returning values for users, you want the database portion of the queries to finish in less than a couple hundred milliseconds (and probably within tens of milliseconds). This allows the other components of the UI -- particularly the network, but also application-side logic -- to return within a second or two. That is usually a reasonable response time for many applications. You should choose an appropriate value -- 50 msec, 100 msec, 200 msec -- for your application and then design the database and hardware to achieve that goal.

You accomplish such times through several strategies, particularly focusing on indexes to speed database searches and on keeping commonly used data in memory.

Mysql execution time is too long

Try separating the IN clause. I have personally experienced IN clause making queries slow as internally MySQL assumes full table scan and ignores the index in certain situations. Also, DATE(created_at) will ignore the index completely as it's indexed on datetime.

You can try below UNION query and hope it speeds up the output:

SELECT SUM(total) AS total
  FROM (
    SELECT COUNT(*) AS total
      FROM `table` 
    WHERE `created_at` BETWEEN '2017-08-11 00:00:00' AND  '2017-08-11 23:59:59'
      AND `type` = 4 
      AND `status` = 1
    UNION
    SELECT COUNT(*) AS total
      FROM `table` 
    WHERE `created_at` BETWEEN '2017-08-11 00:00:00' AND  '2017-08-11 23:59:59'
      AND `type` = 13 
      AND `status` = 1
    UNION
    SELECT COUNT(*) AS total
      FROM `table` 
    WHERE `created_at` BETWEEN '2017-08-11 00:00:00' AND  '2017-08-11 23:59:59'
      AND `type` = 15 
      AND `status` = 1
  ) z;

You can also add a column that contains only date or remove time from created_at and store it in a new time column, if feasible for your existing system.

Execution time running long. What do I index?

This probably requires two sorts:

    group by  c.car_id, c.date_created
    ORDER BY  c.date_created

This would give you similar results faster:

    group by  c.date_created, c.car_id
    ORDER BY  c.date_created, c.car_id

because it can now do a single sort for both steps.

Please provide the EXPLAIN SELECT.... Meanwhile, I will guess that the Optimizer would prefer to start with the only table with filtering:

cus:  (driver_licence_id, cus_id)
c:    (cus_id, date_created, car_id)
pic:  (car_id, part_id)  -- This, or the swapped version could be the PK
p:    (part_id, manufacturers_id) -- probably useless, since part_id is PK

Each is a "covering index", thereby allowing all the work to be done in a INDEX BTrees. Note: The difference in index for c (compared to Gorden's suggestion) may or may not actually help. I based mine on the modified GROUP BY.

With simple JOINs (same as INNER JOIN), the Optimizer almost always starts with the table for which there is filtering (WHERE...). After that, the order of the tables in your query is forced by the ON clauses. So, it was relatively straightforward to decide on the 4 indexes needed.

In other situations, where it is not obvious what order is best for doing the joins, some of the indexes may need flipping.

In particular, if you remove the WHERE, the optimal starting point would be an index on c starting with the two columns in the GROUP BY. That would probably eliminate a sort. Next would come either cus or pic. p would come after pic.

Many:Many

Is parts_in_car a "many-to-many" table? If so, get rid of the PK that you have; it hurts performance. See this for more discussion: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

How do I display execution time of a MySQL query in PHP?

This worked like a charm!

    $db->query('set profiling=1'); //optional if profiling is already enabled
    $db->query($_POST['query']);
    $stmt = $db->query('show profiles');
    $db->query('set profiling=0'); //optional as well

    $records = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $errmsg = $stmt->errorInfo()[2]; //Output the error message 

UPDATE (The following now works on innodb on my present setup)

$db->query('set profiling=1'); //optional if profiling is already enabled
$db->query($_POST['query']);
$res = $db->query('show profiles');
$records = $res->fetchAll(PDO::FETCH_ASSOC);
$duration = $records[0]['Duration'];  // get the first record [0] and the Duration column ['Duration'] from the first record

Result of (show profiles) from phpmyadmin.

Query_ID    Duration    Query   
1           0.00010575  SELECT DATABASE()

Getting the actual (absolute) execution time of the last query in PHP (excluding network latency etc)

---

Related Topics