Mysql full table scan

Ahmed Merghani
Ahmed Merghani used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dave BaldwinFixer of Problems
Most Valuable Expert 2014

Commented:
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.
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Ahmed MerghaniSoftware Engineer

Author

Commented:
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
Dave BaldwinFixer of Problems
Most Valuable Expert 2014
Commented:
I want to determine the table(s) and then list all related queries.
It doesn't work that way.  It all depends on the queries.  If you do a SELECT on a table that has an index and you use the indexed column in a WHERE statement, then the index will be used.  If you use a non-indexed column in a WHERE statement, then the entire table must be scanned (every time) because there isn't an index to use to speed things up.  It's really that simple.  And it will happen Every Time you run a query like that.
You should not focus on Full scans but you should optimize just queries consuming the reasonable amount of time. There is almost nothing to optimize if the full scan takes 1 microsecond. OTOH, if the query takes minutes without full scan then it seems to be a candidate for optimization (index optimization obviously).

You should also evaluate the query execution frequency. I don't see any reason to optimize a query consuming one minute every moth but the query taking 100 ms every second must be optimized.

So as Dave Baldvin wrote - you have to start with queries not with tables. And you have to decide what is critical for your application.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial