CODEWITHSUNDEEP
sqlmysql
gumpi
gumpi

Reputation: 281

Avoiding a Full Table Scan in MySQL

How can I avoid a full table scan on mysql?

Upvotes: 4

Views: 2213

Answers (4)

Infotekka
Infotekka

Reputation: 10443

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

Upvotes: 0

MaLo
MaLo

Reputation: 11

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.

Upvotes: 1

Andy Lester
Andy Lester

Reputation: 93795

Index your data.

Write queries that use those indexes.

Anything more than that we need specifics.

Upvotes: 5

Daniel Vassallo
Daniel Vassallo

Reputation: 344511

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

Upvotes: 14

Related Questions