Mysql – I need help to optimize a long query

MySQLoptimization

I have a table Chron containing the following columns:

ID | timestamp | …

Some timestamps have been erroneously changed to a specific date D. I want to change them to the timestamp of the nearest rows (in terms of ID) which have not been changed. My query is:

UPDATE Chron as C1
SET C1.timestamp = (
                    SELECT C2.timestamp
                    FROM Chron as C2
                    WHERE C2.timestamp != D
                    AND NOT EXISTS (
                        SELECT *
                        FROM Chron as C3
                        WHERE ABS(C2.id - C1.id) >
                              ABS(C3.id - C1.id)
                    )
)
WHERE C1.timestamp = D

The problem is that my table contains more than 300K rows and this query would take a lot of time to run. How can I optimize it? I know that it is not necessary to compare a given row C1 with the whole table, but just the neighbourhood is sufficient, but the optimal size of the neighbourhood is not always the same.

Best Answer

Here I suppose that id is ordered chronologically just like timestamp field.

UPDATE Chron AS w
   SET w.timestamp = IF( w.timestamp = D, @ts, @ts := w.timestamp )
 ORDER BY w.id ASC
;

The only requirement is that first (by id) record should have the correct timestamp.

Also that query update the broken timestamp to the value of the previous correct/corrected row and if you have the long continuous run of rows with broken timestamps result will be different from the "closest value". Anyway the "closest value" need a complicated query so "previous value" is good enough if you need some fast solution.

Related Solutions

Mysql – Help the database isn’t performing fast enough! 100M Merge with 6M need < 1 hour!

If your table has a table with more than 1000 columns, it cannot be converted to InnoDB. In that case, run this query

SELECT CEILING(SUM(index_length)/POWER(1024,2)) num
FROM information_schema.tables WHERE engine='MyISAM';

This will give you the correct size for key_buffer_size in MB.

Since you are doing an UPSERT, you should set concurrent_insert to 2 to make INSERTs go faster. You may want to consider changing the table's row format to Fixed. I wrote about why to do both in StackOverflow. In essence, if you make the table's row format Fixed, all table rows are the same size. Thus, INSERTs and UPDATEs would operate on the exact same length of data. Management of row access is far more reasonable.

Since MyISAM only caches indexes (in the key buffer), all data must be read from disk. anything you can do to getting better RAID performance (as asked by @TomTom) would help your cause as well.

Mysql – Optimize query or model, to get an population overview in time

Ok, i tried to reproduse your case:

mysql> insert into status values (1,'a','NEW',NOW(),'test'); Query OK, 1 row affected (0.00 sec)

mysql> insert into status values (2,'b','NEW',NOW(),'test2'); Query OK, 1 row affected (0.00 sec)

mysql> insert into status values (3,'c','NEW',NOW(),'test3'); Query OK, 1 row affected (0.00 sec)

mysql> explain  SELECT MAX(id) FROM `status` WHERE timestamp <= NOW() GROUP BY user_id;
+----+-------------+--------+-------+---------------+--------+---------+------+------+--------------------------+
| id | select_type | table  | type  | possible_keys | key    | key_len | ref  | rows | Extra                    |
+----+-------------+--------+-------+---------------+--------+---------+------+------+--------------------------+
|  1 | SIMPLE      | status | index | index2        | index1 | 41      | NULL |    3 | Using where; Using index |
+----+-------------+--------+-------+---------------+--------+---------+------+------+--------------------------+

Mysql uses index like you wanted.

And then:

mysql> alter table status drop index index1;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain  SELECT MAX(id) FROM `status` WHERE timestamp <= NOW() GROUP BY user_id;
+----+-------------+--------+-------+---------------+--------+---------+------+------+-----------------------------------------------------------+
| id | select_type | table  | type  | possible_keys | key    | key_len | ref  | rows | Extra                                                     |
+----+-------------+--------+-------+---------------+--------+---------+------+------+-----------------------------------------------------------+
|  1 | SIMPLE      | status | index | index2        | index2 | 41      | NULL |    3 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+--------+-------+---------------+--------+---------+------+------+-----------------------------------------------------------+
1 row in set (0.00 sec)

The plan is like yours. Please ensure that index1 is really present.

I am runnig mysql 5.5.33-31.1-log You can even try 5.6.

Also please try to force index1 and post the results.

Best Answer

Related Solutions

Mysql – Help the database isn’t performing fast enough! 100M Merge with 6M need < 1 hour!

Mysql – Optimize query or model, to get an population overview in time

Related Question