There is a data table, data storage and about 100 w, this is the SQL statement:
Select * from data_value where data_point_id='41607' order by create_time desc limit 0, 1.
Currently the only table (except the primary key) data_point_id an index
The explain results:

Check out time consuming:

Add joint index: data_point_id, create_time
The alter table data_value add index myindex (data_point_id, create_time)
The explain results:
Query takes: '0.00032400', 'the explain the select * from data_value where data_point_id=\' 41607 \ 'order by create_time desc limit 0, 1,'
Speed dropped a lot, why is this

CodePudding user response:

If you are not the index that statement is also carried out many times, this is not the first time the result of the execution? A cache, execution speed is also quickly again,
In addition, this would not have been slow, plus every time query system resource scheduling situation is not the same, there are fluctuations are normal,

CodePudding user response:

In addition, if you want to do performance contrast test, the data quantity to be big enough, the 1 million doesn't consider as what, what's more, this condition the results of the 41607 to 110000, so little data, under the condition of computer performance is very good not contrast.