CODEWITHSUNDEEP
mysqlsqldatabase
Hasibur
Hasibur

Reputation: 205

Select query examines more rows than rows existing in table

Database content only 9100 article But while hit this query 

SELECT * 
FROM `ABC` 
WHERE st=6 AND publish_on <= '2018-02-01' 
ORDER BY rand() LIMIT 5

Query_time: 1.043072 Lock_time: 0.000081 Rows_sent: 5 Rows_examined: 19354

But :

Select count(*) from ABC;

it returns:

9100

Why it examined 19354 rows??

Upvotes: 5

Views: 584

Answers (2)

carexcer
carexcer

Reputation: 1427

You can see in this link how rand() function works.

On this link, you can see how ORDER BY works:

  1. Read the rows that match the WHERE clause, as before.
  2. For each row, record a tuple of values consisting of the sort key value and row position, and also the columns required for the query.
  3. Sort the tuples by sort key value
  4. Retrieve the rows in sorted order, but read the required columns directly from the sorted tuples rather than by accessing the table a second time.

I think your query examines 19354 rows (more rows than the total of the table rows) because of using the combination of order by rand(), rand() is not a column of the table (so there isn't a sort key) and therefore the dbms must scan the table more than 1 time. If you ordered by a column of the table, I'm not sure, but probably the dbms doesn't need to scan the table only 1 time.

Further, to do a ORDER BY rand() LIMIT 5 is very inefficient, because you are ordering the 9100 rows before than select only 5 rows.

Upvotes: 2

MustDie1Bit
MustDie1Bit

Reputation: 561

Rows_examined: 19354

meaning this query is bad optimized. It examine 19354 rows to produce result.

EXPLAIN SELECT output is only a guess made by the optimizer regarding what it believes will be the number of index records which will match the search criteria, based on the info MySQL has regarding the distribution of keys in the index. The actual number of rows returned, as you saw, can be different.

Upvotes: 1

Related Questions