A question about mysql innodb index query?

suppose you have a sql query select * from table where id=xxxx order by time
that has two indexes on this table, one is an id clustered index and the other is a time nonclustered index.
so let"s assume that it is forced to take the time nonclustered index , then the first step in executing this sql is to take out the sorted leaf nodes directly. The data field of this leaf node holds the primary key name, id. So the question is, the next step is to match the where condition, whether to where match id, directly on the data field of the leaf node of the time index, or to get the id and then get the row data through the primary key index to where match id?

think: select * from table where time=xxxx order by time , what will happen? If the answer to the above question is the former (where matches id), directly on the data field of the leaf node of the time index, then the query efficiency of this sql and the above sql should be about the same?

Mysql
Jul.27,2021 
EXPLAIN 
SELECT `id`
FROM `table`
FORCE INDEX(`idx_time`)
WHERE `id`=xxx
    ORDER BY `time`

see that Extra has Using index, instructions to complete the query inside the index, that is, there is no need to query back to the table

2 sql statements, do not understand why to compare efficiency, the first one to take the primary key must be efficient; the second statement, may scan the index idx_time

how to check what you are looking for.
the first sql will find the id, that meets the where condition in the time index, and then take the id to the primary key to aggregate to get other fields
. The second is also to find the id, on the time index that meets the where condition and then to get other data
. The difference is that the first case traverses all time index segments, and the second only needs to retrieve time index segments that meet the criteria

.

Previous: Why did you sign up for: Value of key 'el.datepicker.weeks.sun' is not a string! The problem

Next: What does it mean to use width:100%;height:auto; in combination?

css mysql arrays josn react html typescript webpack npm sass R objective-c .net sql-server jquery python-3.x angularjs django angular excel regex iphone ajax linux xml pandas vba spring database wordpress string wpf xcode windows bash postgresql oracle multithreading eclipse list firebase algorithm macos forms image scala visual-studio azure bootstrap spring-boot react-native python-2.7 docker performance function winforms matlab powershell apache dataframe api sqlite numpy rest shell selenium flutter dart maven loops qt swing android-studio csv express file class tensorflow sorting codeigniter perl
© 2021 CodesHelper
Advertising Contact us About us
Menu