The 16.04 seconds is a timer on the client that measures the amount of time that passed between the call and the execution of the query (if you want to be more specific than that, the wall clock time between calling start_timer() and mysql_end_timer(), something that can lead to hilarious results like this one I got).
The reason that you think that it took you more than 30 minutes to execute is probably because it doesn't have into account the output to the stdout. You can check that it really takes 16 second by doing:
mysql> pager cat > /dev/null
mysql> <your query here>
mysql> nopager
If you want to measure how much time takes to write to the standard output, you can do:
$ time mysql -u <your user> -p<your password> <your database> \
-e "select population1 < 200000 from city"
Note: Are you sure you want to print a 0 or a 1 for ALL rows from that table? Maybe the option --safe-updates
and/or using a GUI can help you a bit with your queries?
Given your sample data, your view t_pivot
has the following EXPLAIN
plan:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|--------|--------------|----------------|------|---------------|--------|---------|--------|--------|-----------------|
| 1 | PRIMARY | t | ALL | (null) | (null) | (null) | (null) | 44 | (null) |
| 2 | UNION | t | ALL | (null) | (null) | (null) | (null) | 44 | (null) |
| 3 | UNION | t | ALL | (null) | (null) | (null) | (null) | 44 | (null) |
| 4 | UNION | t | ALL | (null) | (null) | (null) | (null) | 44 | (null) |
| (null) | UNION RESULT | <union1,2,3,4> | ALL | (null) | (null) | (null) | (null) | (null) | Using temporary |
It's doing a full-table scan four times just to create the view.
Create the following indexes on your table t
:
CREATE INDEX idx_t_ws_ts_engoil ON t(wellsite, ts, EngOilP_sd);
CREATE INDEX idx_t_ws_ts_compoil ON t(wellsite, ts, CompOilLVL_sd);
CREATE INDEX idx_t_ws_ts_brake ON t(wellsite, ts, Brake_sd);
CREATE INDEX idx_t_ws_ts_hitemp ON t(wellsite, ts, HiTemp_sd);
Then you should get an explain
like this:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|--------|--------------|----------------|-------|---------------|---------------------|---------|--------|--------|-----------------|
| 1 | PRIMARY | t | index | (null) | idx_t_ws_ts_engoil | 70 | (null) | 44 | Using index |
| 2 | UNION | t | index | (null) | idx_t_ws_ts_compoil | 70 | (null) | 44 | Using index |
| 3 | UNION | t | index | (null) | idx_t_ws_ts_brake | 70 | (null) | 44 | Using index |
| 4 | UNION | t | index | (null) | idx_t_ws_ts_hitemp | 70 | (null) | 44 | Using index |
| (null) | UNION RESULT | <union1,2,3,4> | ALL | (null) | (null) | (null) | (null) | (null) | Using temporary |
Of course it still reads the same amount of rows, but it reads them from the indexes. The indexes are put into memory (if they fit), not the whole table (unless it fits in the query cache (I wouldn't recommend to increase the query cache to an absurd large value, just to make this query run faster. Especially since you don't run this query very often. It will hurt you in the long run...)).
Now you have to look at this query:
SELECT sd, wellsite, TIMEDIFF(MAX(ts), MIN(ts)) AS duration, MIN(ts) AS starttime, MAX(ts) AS endtime
FROM (
SELECT
tp.*
, @val_change := IF(@prev_val != tp.val, @val_change + 1, @val_change) AS vc
, @prev_val := tp.val
FROM
t_pivot tp
, (SELECT @prev_val := NULL, @val_change := 0) var_init_subquery
ORDER BY wellsite, sd, ts
) sq
WHERE val = 1
GROUP BY sd, vc
sqlfiddle.com stopped working right now somehow, so I can't show you the explain
output, but I've seen it before and I'm not sure, if there's much more you can do about it. But there's an Using filesort
which can be avoided. Add an ORDER BY NULL
after GROUP BY sd, vc
. The GROUP BY
does an implicit ORDER BY
which is not really needed here, but I guess this won't help much. See if the above mentioned indexes help already.
Best Answer
The execution speed of a database server depends on a great many factors, for example
However to seek a single row out of 800,000 rows on a single table assuming the presence of an index on the column "id" that is specifying the row of data you want, it is likely to take a tiny fraction of a second on modern hardware. In fact the network round trip to your database server is likely to take longer than the database operation.