2

What is the difference between Full Scan and No Index Used Handler in MySQL?

enter image description here

2
  • Please don't post textual information as an image. Dec 28, 2022 at 8:13
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Dec 28, 2022 at 8:13

2 Answers 2

3

If your query incurs a full index scan, then you will see full scan but not no index used.

For example, here is the world.city table definition:

CREATE TABLE `city` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
);

If you try your query using an indexed field, you will see the difference. Here's an example query to try:

select CountryCode from world.city;

Percona's query analyzer is leveraging the performance schema, so you can also query the performance schema directly in your tests, like this:

select DIGEST_TEXT, ROWS_SENT, ROWS_EXAMINED, SELECT_SCAN, NO_INDEX_USED from performance_schema.events_statements_history;
+--------------------------------------------+-----------+---------------+-------------+---------------+
| DIGEST_TEXT                                | ROWS_SENT | ROWS_EXAMINED | SELECT_SCAN | NO_INDEX_USED |
+--------------------------------------------+-----------+---------------+-------------+---------------+
| SELECT * FROM `world` . `city`             |      4079 |          4079 |           1 |             1 |
| SELECT `CountryCode` FROM `world` . `city` |      4079 |          4079 |           1 |             0 |
+--------------------------------------------+-----------+---------------+-------------+---------------+

The explain plans will also show you the index scan vs. not:

explain select * from world.city;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | city  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4046 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

explain select CountryCode from world.city;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | index | NULL          | CountryCode | 12      | NULL | 4046 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
1
  • 1
    Note that one scanned the entire data B+Tree (without touching any index); the other scanned the entire index B+Tree (without needing to touch the data tree)..
    – Rick James
    Dec 28, 2022 at 18:47
1

Just from a semantics point-of-view, there are three(3) perspectives to this answer

PERSPECTIVE #1

SELECT * FROM world.city;

This query would not need an index due to no specific column in SELECT clause and no WHERE clause. Query Optimizer would concur.

PERSPECTIVE #2

SELECT CountryCode FROM world.city;

This query would demand the use of an index on CountryCode if such an index was present.

  • If there an index on CountryCode, you get a full index scan.
  • If there is no index on CountryCode, you get a full table scan.

PERSPECTIVE #3

SELECT * FROM world.city WHERE CountryCode=21;

This query would demand the use of an index on CountryCode if such an index was present.

  • If there an index on CountryCode, you get an index lookup and row retrieval from world.city.
  • If there is no index on CountryCode, you get a full table scan. Query Optimizer would surmise that no appropriate index was available to do an index lookup.
  • Depending on key value distribution, Query Optimizer may decide not to use the index if a high number of keys would need to be referenced out of the index. This would make it more cost-effective to do a full table scan rather than goring through an index.

INDEX ANALYSIS FOR PERSPECTIVE #3

In the world Database, there 239 countries and 4079 cities.

Here is the number of cities per country

mysql> SELECT IFNULL(A.name,'Totals for All Cities') Country,COUNT(B.name) City
    -> FROM country A LEFT JOIN city B
    -> ON A.Code = B.CountryCode
    -> GROUP BY A.name WITH ROLLUP;
+----------------------------------------------+------+
| Country                                      | City |
+----------------------------------------------+------+
| Afghanistan                                  |    4 |
| Albania                                      |    1 |
| Algeria                                      |   18 |
| American Samoa                               |    2 |
| Andorra                                      |    1 |
| Angola                                       |    5 |
| Anguilla                                     |    2 |
| Antarctica                                   |    0 |
| Antigua and Barbuda                          |    1 |
| Argentina                                    |   57 |
| Armenia                                      |    3 |
| Aruba                                        |    1 |
| Australia                                    |   14 |
| Austria                                      |    6 |
| Azerbaijan                                   |    4 |
| Bahamas                                      |    1 |
| Bahrain                                      |    1 |
| Bangladesh                                   |   24 |
| Barbados                                     |    1 |
| Belarus                                      |   16 |
| Belgium                                      |    9 |
| Belize                                       |    2 |
| Benin                                        |    4 |
| Bermuda                                      |    2 |
| Bhutan                                       |    1 |
| Bolivia                                      |    8 |
| Bosnia and Herzegovina                       |    3 |
| Botswana                                     |    2 |
| Bouvet Island                                |    0 |
| Brazil                                       |  250 |
| British Indian Ocean Territory               |    0 |
| Brunei                                       |    1 |
| Bulgaria                                     |   10 |
| Burkina Faso                                 |    3 |
| Burundi                                      |    1 |
| Cambodia                                     |    3 |
| Cameroon                                     |    7 |
| Canada                                       |   49 |
| Cape Verde                                   |    1 |
| Cayman Islands                               |    1 |
| Central African Republic                     |    1 |
| Chad                                         |    2 |
| Chile                                        |   29 |
| China                                        |  363 |
| Christmas Island                             |    1 |
| Cocos (Keeling) Islands                      |    2 |
| Colombia                                     |   38 |
| Comoros                                      |    1 |
| Congo                                        |    2 |
| Congo, The Democratic Republic of the        |   18 |
| Cook Islands                                 |    1 |
| Costa Rica                                   |    1 |
| Côte d’Ivoire                                |    5 |
| Croatia                                      |    4 |
| Cuba                                         |   14 |
| Cyprus                                       |    2 |
| Czech Republic                               |   10 |
| Denmark                                      |    5 |
| Djibouti                                     |    1 |
| Dominica                                     |    1 |
| Dominican Republic                           |    6 |
| East Timor                                   |    1 |
| Ecuador                                      |   15 |
| Egypt                                        |   37 |
| El Salvador                                  |    7 |
| Equatorial Guinea                            |    1 |
| Eritrea                                      |    1 |
| Estonia                                      |    2 |
| Ethiopia                                     |    7 |
| Falkland Islands                             |    1 |
| Faroe Islands                                |    1 |
| Fiji Islands                                 |    1 |
| Finland                                      |    7 |
| France                                       |   40 |
| French Guiana                                |    1 |
| French Polynesia                             |    2 |
| French Southern territories                  |    0 |
| Gabon                                        |    1 |
| Gambia                                       |    2 |
| Georgia                                      |    5 |
| Germany                                      |   93 |
| Ghana                                        |    5 |
| Gibraltar                                    |    1 |
| Greece                                       |    8 |
| Greenland                                    |    1 |
| Grenada                                      |    1 |
| Guadeloupe                                   |    2 |
| Guam                                         |    2 |
| Guatemala                                    |    4 |
| Guinea                                       |    1 |
| Guinea-Bissau                                |    1 |
| Guyana                                       |    1 |
| Haiti                                        |    4 |
| Heard Island and McDonald Islands            |    0 |
| Holy See (Vatican City State)                |    1 |
| Honduras                                     |    3 |
| Hong Kong                                    |    2 |
| Hungary                                      |    9 |
| Iceland                                      |    1 |
| India                                        |  341 |
| Indonesia                                    |   85 |
| Iran                                         |   67 |
| Iraq                                         |   15 |
| Ireland                                      |    2 |
| Israel                                       |   14 |
| Italy                                        |   58 |
| Jamaica                                      |    3 |
| Japan                                        |  248 |
| Jordan                                       |    5 |
| Kazakstan                                    |   21 |
| Kenya                                        |    8 |
| Kiribati                                     |    2 |
| Kuwait                                       |    3 |
| Kyrgyzstan                                   |    2 |
| Laos                                         |    2 |
| Latvia                                       |    3 |
| Lebanon                                      |    2 |
| Lesotho                                      |    1 |
| Liberia                                      |    1 |
| Libyan Arab Jamahiriya                       |    4 |
| Liechtenstein                                |    2 |
| Lithuania                                    |    5 |
| Luxembourg                                   |    1 |
| Macao                                        |    1 |
| Macedonia                                    |    1 |
| Madagascar                                   |    5 |
| Malawi                                       |    2 |
| Malaysia                                     |   18 |
| Maldives                                     |    1 |
| Mali                                         |    1 |
| Malta                                        |    2 |
| Marshall Islands                             |    1 |
| Martinique                                   |    1 |
| Mauritania                                   |    2 |
| Mauritius                                    |    3 |
| Mayotte                                      |    1 |
| Mexico                                       |  173 |
| Micronesia, Federated States of              |    2 |
| Moldova                                      |    4 |
| Monaco                                       |    2 |
| Mongolia                                     |    1 |
| Montserrat                                   |    1 |
| Morocco                                      |   22 |
| Mozambique                                   |   12 |
| Myanmar                                      |   16 |
| Namibia                                      |    1 |
| Nauru                                        |    2 |
| Nepal                                        |    5 |
| Netherlands                                  |   28 |
| Netherlands Antilles                         |    1 |
| New Caledonia                                |    1 |
| New Zealand                                  |    9 |
| Nicaragua                                    |    4 |
| Niger                                        |    3 |
| Nigeria                                      |   64 |
| Niue                                         |    1 |
| Norfolk Island                               |    1 |
| North Korea                                  |   13 |
| Northern Mariana Islands                     |    1 |
| Norway                                       |    5 |
| Oman                                         |    5 |
| Pakistan                                     |   59 |
| Palau                                        |    1 |
| Palestine                                    |    6 |
| Panama                                       |    2 |
| Papua New Guinea                             |    1 |
| Paraguay                                     |    5 |
| Peru                                         |   22 |
| Philippines                                  |  136 |
| Pitcairn                                     |    1 |
| Poland                                       |   44 |
| Portugal                                     |    5 |
| Puerto Rico                                  |    9 |
| Qatar                                        |    1 |
| Réunion                                      |    1 |
| Romania                                      |   29 |
| Russian Federation                           |  189 |
| Rwanda                                       |    1 |
| Saint Helena                                 |    1 |
| Saint Kitts and Nevis                        |    1 |
| Saint Lucia                                  |    1 |
| Saint Pierre and Miquelon                    |    1 |
| Saint Vincent and the Grenadines             |    1 |
| Samoa                                        |    1 |
| San Marino                                   |    2 |
| Sao Tome and Principe                        |    1 |
| Saudi Arabia                                 |   24 |
| Senegal                                      |    9 |
| Seychelles                                   |    1 |
| Sierra Leone                                 |    1 |
| Singapore                                    |    1 |
| Slovakia                                     |    3 |
| Slovenia                                     |    2 |
| Solomon Islands                              |    1 |
| Somalia                                      |    3 |
| South Africa                                 |   44 |
| South Georgia and the South Sandwich Islands |    0 |
| South Korea                                  |   70 |
| Spain                                        |   59 |
| Sri Lanka                                    |    7 |
| Sudan                                        |   12 |
| Suriname                                     |    1 |
| Svalbard and Jan Mayen                       |    1 |
| Swaziland                                    |    1 |
| Sweden                                       |   15 |
| Switzerland                                  |    5 |
| Syria                                        |   11 |
| Taiwan                                       |   42 |
| Tajikistan                                   |    2 |
| Tanzania                                     |   10 |
| Thailand                                     |   12 |
| Togo                                         |    1 |
| Tokelau                                      |    1 |
| Tonga                                        |    1 |
| Trinidad and Tobago                          |    2 |
| Tunisia                                      |    8 |
| Turkey                                       |   62 |
| Turkmenistan                                 |    4 |
| Turks and Caicos Islands                     |    1 |
| Tuvalu                                       |    1 |
| Uganda                                       |    1 |
| Ukraine                                      |   57 |
| United Arab Emirates                         |    5 |
| United Kingdom                               |   81 |
| United States                                |  274 |
| United States Minor Outlying Islands         |    0 |
| Uruguay                                      |    1 |
| Uzbekistan                                   |   17 |
| Vanuatu                                      |    1 |
| Venezuela                                    |   41 |
| Vietnam                                      |   22 |
| Virgin Islands, British                      |    1 |
| Virgin Islands, U.S.                         |    1 |
| Wallis and Futuna                            |    1 |
| Western Sahara                               |    1 |
| Yemen                                        |    6 |
| Yugoslavia                                   |    8 |
| Zambia                                       |    7 |
| Zimbabwe                                     |    6 |
| Totals for All Cities                        | 4079 |
+----------------------------------------------+------+
240 rows in set (0.04 sec)

mysql>

Note that 5% of 4079 is 203.95

How many countries have more than 5% of the rows (How many have more than 203 cities) ???

mysql> SELECT IFNULL(A.name,'Totals for All Cities') Country,COUNT(B.name) City
    -> FROM country A LEFT JOIN city B
    -> ON A.Code = B.CountryCode
    -> GROUP BY A.name HAVING COUNT(B.name) > 203;
+---------------+------+
| Country       | City |
+---------------+------+
| Brazil        |  250 |
| China         |  363 |
| India         |  341 |
| Japan         |  248 |
| United States |  274 |
+---------------+------+
5 rows in set (0.04 sec)

mysql> SELECT Code,Name FROM country WHERE Name in
    -> ('Brazil','China','India','Japan','United States');
+------+---------------+
| Code | Name          |
+------+---------------+
| BRA  | Brazil        |
| CHN  | China         |
| IND  | India         |
| JPN  | Japan         |
| USA  | United States |
+------+---------------+
5 rows in set (0.01 sec)

mysql>

How would the Query Optimizer behave when querying too many cities ???

Look at the countries with the two highest values : China and India

EXPLAIN PLAN for retrieving from China, India, and Both

mysql> EXPLAIN SELECT Name FROM city WHERE CountryCode = 'CHN';
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | city  | NULL       | ref  | CountryCode   | CountryCode | 12      | const |  363 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT Name FROM city WHERE CountryCode = 'IND';
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | city  | NULL       | ref  | CountryCode   | CountryCode | 12      | const |  341 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

mysql> EXPLAIN SELECT Name FROM city WHERE CountryCode IN ('CHN','IND');
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | ALL  | CountryCode   | NULL | NULL    | NULL | 4046 |    17.40 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

mysql>

Notice how the number of index entries being examined impacts the final Query Optimizer path. Individually, each single country code would use the index. When asking for both in the one query, Query Optimizer decided against using the index and found using the full table scan more efficient. In that instance, a full table scan occurred because the index scan would be too expensive. This would be tabulated as No index use. In theory, if there was a country with 700 cities in the city table, the explain plan would look the same as asking for China and India together and again tabulated as No index use.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.