Link to home
Start Free TrialLog in
Avatar of Ahmed Merghani
Ahmed MerghaniFlag for Sudan

asked on

Mysql full table scan

Hi all,

I have system that using MySQL DB. I run MONyog tool for monitoring and performance tuning purpose. It shows in "index usage" parameter that Percentage of full table scans is very high !!
My question is how to know which table(s) cases this full table scan?
Avatar of Dave Baldwin
Dave Baldwin
Flag of United States of America image

It's caused by queries that can't use the indexes that exist.  Anytime you use WHERE on a column or combination that is not indexed, you will see a full table scan.
Avatar of Anwar Saiah
Anwar Saiah

A full table scan is caused by your code, not the table it's self!
"Percentage of full table scans is very high !!"
This means that you fully scan tables too often.(Or at least that's what I think it means!)
So you will have to check with your queries and try to make them more efficient.
MySQL uses Full table scan for several reasons:
The table is so small that it is faster to perform a table scan than to bother with a key lookup. This is common for tables with fewer than 10 rows and a short row length.
There are no usable restrictions in the ON or WHERE clause for indexed columns.
You are comparing indexed columns with constant values and MySQL has calculated (based on the index tree) that the constants cover too large a part of the table and that a table scan would be faster. See Section 8.2.1.2, “How MySQL Optimizes WHERE Clauses”.
You are using a key with low cardinality (many rows match the key value) through another column. In this case, MySQL assumes that by using the key it probably will do many key lookups and that a table scan would be faster.

Source: https://dev.mysql.com/doc/refman/5.0/en/how-to-avoid-table-scan.html

I would also recommend to read something about clustered indexes and SELECT optimization and also about the Query Execution Plan.
Avatar of Ahmed Merghani

ASKER

Thanks all for your valuable comments.
aboo_s, I meant the table(s) that had been full scanned.
Dave Baldwin, pcelba so, how I can figure out the table(s) been full scanned? Is there any MySQL command or script can help ?? I do not want to pickup queries one by one instead I want to determine the table(s) and then list all related queries.

Thanks in advance
SOLUTION
Avatar of Dave Baldwin
Dave Baldwin
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial