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?
LVL 8
Ahmed MerghaniSoftware EngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
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.
aboo_sCommented:
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.
pcelbaCommented:
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.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

LEARN MORE
Ahmed MerghaniSoftware EngineerAuthor 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 ProblemsCommented:
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.
pcelbaCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.