has always thought that index optimization is effective in where
is useless in select
but it seems that there is a performance difference between select nonclustered index columns, clustered index columns, unindexed columns and select *?
has always thought that index optimization is effective in where
is useless in select
but it seems that there is a performance difference between select nonclustered index columns, clustered index columns, unindexed columns and select *?
take MySQL's InnoDB storage engine as an example, which is explained as follows:
each index contains different fields. A clustered index contains all fields, while a nonclustered index contains only index fields + primary key fields, so if you need to use other fields (including in the where condition or in the select clause) after using a nonclustered index, you need to go back to the clustered index to get other fields through the primary key index back to the table . If a nonclustered index satisfies all the fields of the SQL statement, it is called fully covered index , and there is no overhead of returning to the table.
returning a table is a process of re-querying a clustered index through the primary key field, so if a large number of records need to return to the table, the query cost will be higher than the cost of range scanning directly on the clustered index. So in some cases, it is more efficient not to use nonclustered indexes.
Why is it limited to the InnoDB storage engine? Because the MyISAM storage engine data file and index file are separate, there is no concept of clustered index.
for more details and principles, please refer to my recently published article: Database Index Integration
Previous: Mac golang vscode Fabric test case debug. Ltdl.h could not be found by running the main function
if the table table has a federated index (sex, id) and id itself is the primary key , does the following sql go to the index? select * from table where sex= m order by id limit 1000000,10 my feeling is that according to the execution order of sql ...
basic information: The datasheet has almost 10 million pieces of data, using the mycat sublibrary. the indexes in the data table are PRIMARY id AppName (AppName, custidStatus, channel) the table creation statement is as follows CREATE TABLE...
MySQL,select uses in to query. There are about 5000 items in the collection after in. There are many records in this table. The fields in in query are indexed . the original SQL is similar to this SELECT a FROM table WHERE an in ( 1x code 2 ) and o...