CODEWITHSUNDEEP
phpmysqlsqldatabase
user188962
user188962

Reputation:

Which MySql line is faster:

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

Upvotes: 1

Views: 163

Answers (4)

Unreason
Unreason

Reputation: 12704

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).

Upvotes: 2

Your Common Sense
Your Common Sense

Reputation: 157828

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.

Upvotes: 4

Bryan Drewery
Bryan Drewery

Reputation: 2636

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

CREATE INDEX classified_idx ON table (classified_id);

Upvotes: 2

John Conde
John Conde

Reputation: 219794

You want to use LIMIT:

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

Upvotes: 6

Related Questions