1. In the process of creating an index for a table, it is found that when a column is added to the rightmost of an existing index, the scope of the query can be narrowed according to the filtering criteria. Then from the results of explain’s execution, it is found that in fact, sometimes the number of rows scanned is more than that after adding a new column.
In some cases, the size of rows is not inversely proportional to the last query time.
2. For example, my table structure is as follows:
CREATE TABLE `test` ( `flag` tinyint(4) NOT NULL DEFAULT '0', `type` smallint(6) NOT NULL DEFAULT '0', `poid` int(11) NOT NULL DEFAULT '0', `new_nums` int(11) NOT NULL DEFAULT '0', `addup_nums` int(11) NOT NULL DEFAULT '0', `r_time` int(11) NOT NULL DEFAULT '0', `event_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `dtype` tinyint(6) NOT NULL DEFAULT '1' COMMENT '1.day 2.week 3.month', `dtime` char(10) NOT NULL DEFAULT '', KEY `one` (`dtime`,`r_time`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
When I tried to add a poid column at the end of index one, I found that rows after explain had increased.
So, how did rows calculate it?
This rows is explained in the official website document:
The rows column indicates the number of rows MySQL believes it must examine to execute the query.
This rows is the number of records mysql believes must be checked and judged line by line.
For example, if there is a statement SELECT * FROM tWHere COLUMN _ A = 1 and COLUMN _ B = 2;
The whole table assumes 100 records, column_a field has index (non-union index), column_b has no index.
There are 20 records with column_a = 1 and 5 records with column_a = 1 and column_b = 2.
Then the final query result should display 5 records. Rows in the explain result should be 20. Because the mysql engine must check line by line whether the where condition is met.