I have frequent stops of my MySQL. After some searches I found that It’s caused by UPDATE query/
UPDATE ‘sqlth_sce’ SET ‘end_time’=1510052581045 WHERE ‘scid’=2 and ‘rate’=10000 and ‘start_time’<=1510052581045 and ‘end_time’>=1510052561045
The most interesting that there is one row which according to ‘where clause’ but query is hanging till server stop. Could someone tell me what is the reason?
Do you have indices on the columns in your where clause? Particularly start_time and end_time. If not, your server has to scan the entire table every time you ask it to do this operation. If the table is large and other stuff depends on it, all of those dependencies will stop.
No, there are no indices. It’s table created by Ignition which keeps records of scanclasses. I don’t call this query, it’s updating by Ignition automatically.
You’ll have to get your database group to add indices at least on those columns. Ignition doesn’t always make the indices one needs.