Which MySql line is faster:

Question

I have a classified_id variable which matches one document in a MySql table.

I am currently fetching the information about that one record like this:

   SELECT * FROM table WHERE table.classified_id = $classified_id

I wonder if there is a faster approach, for example like this:

   SELECT 1 FROM table WHERE table.classified_id = $classified_id

Wont the last one only select 1 record, which is exactly what I need, so that it doesn't have to scan the entire table but instead stops searching for records after 1 is found?

Or am I dreaming this?

Thanks

Answer 1

You want to use LIMIT:

SELECT * FROM table WHERE table.classified_id = $classified_id LIMIT 1

Answer 2

You should add an index to the classified_id column to avoid a table scan.

CREATE INDEX classified_idx ON table (classified_id);

Answer 3

Yes, you're dreaming this.
There are 2 major faults in your reasoning:

1. The question itself. Most newbies fail to that hole though. They ask "what if I do something - will it be faster?". But the proper question is "My application Runs slow. How to find a bottleneck?" and "I have certain bottleneck. How to eliminate it?"
2. You suppose fieldlist part influencing database search. That's wrong. The field list is responsible only for the returned fields of the found rows. In both cases the number of rows would be the same.

Answer 4

Why don't you try it?

SELECT 1 FROM table;

returns

+---+
| 1 |
+---+
| 1 | 
| 1 | 
| 1 | 
| 1 | 
| 1 | 
| 1 | 
+---+
6 rows in set (0.00 sec)

which is a computed column, a constant value of one in this case (for all 6 rows in my test case).

For your question

SELECT * FROM table WHERE table.classified_id = $classified_id

this is the fastest way to retrieve data (assuming that you need all the columns from the table)

There are following things that you can do:

• check if there is an index on classified_id, if there is and if using the index is faster the database will use the index and not scan the whole table, so you'll get what you want (using index can be slower if there are just a few records or if a high percentage of records satisfy a criteria, but having index will not bring any penalty to reading data, database will chose the best way to retrieve it)
• if you don't need all the columns from the table then specify exactly which ones you need
• if there is more then one record that satisfy the criteria you can use LIMIT keyword to get only one or only a few records

other then this for such a simple query the only next step would be to partition the table to several hard drives (which might not be an option on your system).