Avoiding a Full Table Scan in MySQL

How can I avoid a full table scan on mysql? 开发者_开发技巧

In general, by making sure you have a usable index on fields that appear in WHERE, JOIN and ORDER BY clauses.

Index your data.

Write queries that use those indexes.

Anything more than that we need specifics.

Also note that sometimes you just can not rid of a full table scan, i.e. When you need all the rows from your table... or when the cost of scanning the index is gt the cost of scanning the full table.

Use a LIMIT clause when you know how many rows you are expecting to return, for example if you are looking for a record with a known ID field that is unique, limit your select to 1, that way mysql will stop searching after it finds the first record. The same goes for updates and deletes.

SELECT * FROM `yourTable` WHERE `idField` = 123 LIMIT 1