MySQL : Does a query searches inside the whole table?

Question

1. So if I search for the word ball inside the toys table where I have 5.000.000 entries does it search for all the 5 millions?

I think the answer is yes because how should it know else, but let me know please.

2. If yes: If I need more informations from that table isn't more logic to query just once and work with the results?

An example

I have this table structure for example:

id | toy_name | state

Now I should query like this

mysql_query("SELECT * FROM toys WHERE STATE = 1");

But isn't more logical to query for all the table mysql_query("SELECT * FROM toys"); and then do this if($query['state'] == 1)?

3. And something else, if I put an ORDER BY id LIMIT 5 in the mysql_query will it search for the 5 million entries or just the last 5?

Thanks for the answers.

Answer 1

Read this about indexes :-)

http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

It makes it uber-fast :-)

Full table scan is here only if there are no matching indexes and indeed very slow operation. Sorting is also accelerated by indexes.

And for the #2 - this is slow because transfer rate from MySQL -> PHP is slow, and MySQL is MUCH faster at doing filtering.

Answer 2

1. Yes, unless you have a LIMIT clause it will look through all the rows. It will do a table scan unless it can use an index.

2. You should use a query with a WHERE clause here, not filter the results in PHP. Your RDBMS is designed to be able to do this kind of thing efficiently. Only when you need to do complex processing of the data is it more appropriate to load a resultset into PHP and do it there.

3. With the LIMIT 5, the RDBMS will look through the table until it has found you your five rows, and then it will stop looking. So, all I can say for sure is, it will look at between 5 and 5 million rows!

Answer 3

@Hammerite.

Limit gets applied AFTER the Where clause?

Answer 4

For your #1 question: Depends on how you're searching for 'ball'. If there's no index on the column(s) where you're searching, then the entire table has to be read. If there is an index, then...

1. WHERE field LIKE 'ball%' will use an index
2. WHERE field LIKE '%ball%' will NOT use an index

For your #2, think of it this way: Doing SELECT * FROM table and then perusing the results in your application is exactly the same as going to the local super walmart, loading the store's complete inventory into your car, driving it home, picking through every box/package, and throwing out everything except the pack of gum from the impulse buy rack by the front till that you'd wanted in the first place. The whole point of a database is to make it easy to search for data and filter by any kind of clause you could think of. By slurping everything across to your application and doing the filtering there, you've reduced that shiny database to a very expensive disk interface, and would probably be better off storing things in flat files. That's why there's WHERE clauses. "SELECT something FROM store WHERE type=pack_of_gum" gets you just the gum, and doesn't force you to truck home a few thousand bottles of shampoo and bags of kitty litter.

For your #3, yes. If you have an ORDER BY clause in a LIMIT query, the result set has to be sorted before the database can figure out what those 5 records should be. While it's not quite as bad as actually transferring the entire record set to your app and only picking out the first five records, it still involves a bit more work than just retrieving the first 5 records that match your WHERE clause.