2021年5月3日 (一) 13:55 Riguz

2021-05-03T13:55:58Z

←上一版本		2021年5月3日 (一) 13:55的版本
第1行：		第1行：
	<span class="article-label">Index Condition Pushdown (ICP)</span> is an optimization for the case where MySQL retrieves rows from a table using an index. Without ICP, the storage engine traverses the index to locate rows in the base table and returns them to the MySQL server which evaluates the WHERE condition for the rows. With ICP enabled, and if parts of the WHERE condition can be evaluated by using only columns from the index, the MySQL server pushes this part of the WHERE condition down to the storage engine. The storage engine then evaluates the pushed index condition by using the index entry and only if this is satisfied is the row read from the table. ICP can reduce the number of times the storage engine must access the base table and the number of times the MySQL server must access the storage engine.		<span class="article-label">Index Condition Pushdown (ICP)</span> is an optimization for the case where MySQL retrieves rows from a table using an index. Without ICP, the storage engine traverses the index to locate rows in the base table and returns them to the MySQL server which evaluates the WHERE condition for the rows. With ICP enabled, and if parts of the WHERE condition can be evaluated by using only columns from the index, the MySQL server pushes this part of the WHERE condition down to the storage engine. The storage engine then evaluates the pushed index condition by using the index entry and only if this is satisfied is the row read from the table. ICP can reduce the number of times the storage engine must access the base table and the number of times the MySQL server must access the storage engine.

			EXPLAIN output shows <span class="article-label">Using index condition</span> in the Extra column when Index Condition Pushdown is used. It does not show Using index because that does not apply when full table rows must be read.

			Suppose that a table contains information about people and their addresses and that the table has an index defined as <span class="article-label">INDEX (zipcode, lastname, firstname)</span>. If we know a person's zipcode value but are not sure about the last name, we can search like this:


			<syntaxhighlight lang="sql">
			SELECT * FROM people
			WHERE zipcode='95054'
			AND lastname LIKE '%etrunia%'
			AND address LIKE '%Main Street%';
			</syntaxhighlight>

			With Index Condition Pushdown, MySQL checks the lastname LIKE '%etrunia%' part before reading the full table row. This avoids reading full rows corresponding to index tuples that match the zipcode condition but not the lastname condition.

	[[Category:Database]]		[[Category:Database]]

Riguz：建立內容為「Index Condition Pushdown (ICP) is an optimization for the case where MySQL retrieves rows from a table using an index. Without…」的新頁面

2021-05-03T13:50:02Z

建立內容為「<span class="article-label">Index Condition Pushdown (ICP)</span> is an optimization for the case where MySQL retrieves rows from a table using an index. Without…」的新頁面

新页面

<span class="article-label">Index Condition Pushdown (ICP)</span> is an optimization for the case where MySQL retrieves rows from a table using an index. Without ICP, the storage engine traverses the index to locate rows in the base table and returns them to the MySQL server which evaluates the WHERE condition for the rows. With ICP enabled, and if parts of the WHERE condition can be evaluated by using only columns from the index, the MySQL server pushes this part of the WHERE condition down to the storage engine. The storage engine then evaluates the pushed index condition by using the index entry and only if this is satisfied is the row read from the table. ICP can reduce the number of times the storage engine must access the base table and the number of times the MySQL server must access the storage engine.

[[Category:Database]]

MySQL:Index Condition Pushdown - 版本历史

2021年5月3日 (一) 13:55 Riguz

Riguz：​建立內容為「Index Condition Pushdown (ICP) is an optimization for the case where MySQL retrieves rows from a table using an index. Without…」的新頁面

Riguz：建立內容為「Index Condition Pushdown (ICP) is an optimization for the case where MySQL retrieves rows from a table using an index. Without…」的新頁面