0
Follow
4
View

MySQL optimizer index choice for simple select query

csf211289 注册会员
6 days ago

This composite, covering, index should be perfect for that query:

INDEX(COMPANYID, LOGINCREDENTIALID,   -- in either order
      date, id)    -- last, in this order

The first two columns are tested via =, allowing the INDEX rows to be precisely found.

The last two rows can be scanned backward to perfectly go through the index.

Since all the necessary rows are in the index (hence "covering" aka "Using index"), the data's BTree does not need to be touched.

The entire table lives in a B+Tree; it is ordered by the PRIMARY KEY. Hence, it is efficient to lookup a single row or range of rows based on the PK.

Each "secondary" index is a very similar B+Tree. It contains all the column(s) specified in the index, plus (silently) all the column(s) of the PK. That is, with

PRIMARY KEY(id),  INDEX(foo, bar)

the secondary index is really a B+Tree indexed by (foo, bar, id). When those columns are all that is needed for a SELECT, the index is "covering" and only that B+Tree is looked at. If you need other columns, then id (in this example) is used to reach into the data's BTree to find the other columns, based on id.

If no index (PK, nor secondary) is useful locating the requested row(s), the query will do a "full table scan", checking each row for whether it is relevant. Similarly, it may use a "full index scan" when a "covering" index is being used.

Continuing with the example above (and assuming another column x not in any index),

SELECT *        FROM t WHERE id=5;   -- point query
SELECT COUNT(*) FROM t WHERE foo=5;  -- covering
SELECT bar      FROM t WHERE foo=5;  -- covering
SELECT x        FROM t WHERE foo=5;  -- well indexed (but not covering)
SELECT COUNT(*) FROM t WHERE bar=5;  -- full index scan (covering but slow)
SELECT *        FROM t WHERE bar=5;  -- full index scan (plus lookup)
SELECT COUNT(*) FROM t WHERE x=5;    -- full table scan
SELECT *        FROM t WHERE x=5;    -- full table scan

(These examples are ordered, fastest first.)

SELECT COUNT(*) ... returns 1 row. SELECT * ... potentially returns many rows, so potentially slower.

lwaooo0777 注册会员
6 days ago

Your optimized query would be one that includes the where clause FIRST, then secondarily the order by. So I would have an index on

( COMPANYID, LOGINCREDENTIALID, DATE, ID )

Company and credentials covers the where clause. Then the date and ID for the order by clause.

About the Author

Question Info

Publish Time
6 days ago
Update Time
6 days ago

Related Question

如何连接mysql中有不同连接的mysql在不同的服务器laravel的两个表

找到MySQL触发器中已经更改的列

如何使用PATINDEX获得这个模式?

从Mysql 5.7迁移到MariaDb 10.6.5时出现错误

服务器安装mysql5.7

mysql为什么安装不了啊

爬虫时显示报错:IndexError: list index out of range

关于MySQL安装是出现报错

Python和MySQL UPDATE, TypeError: 'int'对象是不可迭代的[重复]

mysql,外键设置相关问题,如图中第三个表,不知道怎么建