2

I have an explain result below , problem is how to calculate the total examined rows? please explain this detail ~ （first ask question~ If there is any mistake, please correct me, I will be very grateful）

id  select_type type    possible_keys   key_len rows  
1   PRIMARY     ALL                             1423656  
1   PRIMARY     eq_ref  PRIMARY         8       1  
1   PRIMARY     ref                     152     1  
1   PRIMARY     ALL                             138   
1   PRIMARY     ALL                             1388  
1   PRIMARY     ALL                             1564  
3   DERIVED     ALL                             1684  
3   DERIVED     eq_ref  PRIMARY         8       1  
2   DERIVED     ALL                             141
eric
  • 33
  • 3
  • 3
    Around 1.4 million. The exact number seems unimportant. What do you really want to know? – juergen d May 31 '17 at 11:41
  • the "rows" column is the approximation of how many rows MySQL will have to examine before obtaining the correct resultset. This is an estimate. And as @juergend said, what do you actually want to know from this? – Florian Humblot May 31 '17 at 11:42
  • you need to multiply the ALL's type together because there are proberly JOIN(S) involved and add the eq_ref, ref types to that count to get the calculation correctly – Raymond Nijland May 31 '17 at 11:47
  • @juergend I just want estimate how many rows will be scan. I think this is a simple way to judge a SQL performance, although it may be inaccurate. – eric May 31 '17 at 12:02
  • Does this have nothing to do with select_type ? @RaymondNijland – eric May 31 '17 at 12:04
  • I have read the [Answer](https://stackoverflow.com/a/16595466/8090593) accord that the total row maybe related on `id` column . i'm very doubt about this @RaymondNijland – eric May 31 '17 at 12:16

1 Answers1

2

From the manual : https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

rows (JSON name: rows)

The rows column indicates the number of rows MySQL believes it must examine to execute the query.

For InnoDB tables, this number is an estimate, and may not always be exact.

You have a very high number of 1.4 million for one of your table but the possible_keys column is empty. That means this is a table that is desperately crying out to be indexed.

A large number of rows to be examined, means just that. Mysql needs to read all those rows to give you your result.

If you had posted your tables and your query, we could have helped you figure out what those indexes ought to be.

e4c5
  • 52,766
  • 11
  • 101
  • 134