MySQL | Find out if there is a match, SELECT or EXPLAIN SELECT?

G

Goodver2012-07-02 01:02:36

MySQL

Goodver, 2012-07-02 01:02:36

Task: Find out if there is a record at the specified ID=123 (ID is the primary key and index)

You can do this: SELECT id FROM table WHERE id=123;
And you can do this: EXPLAIN SELECT id FROM table WHERE id=123;

The point is that with explain, the selection itself is not made, that is, theoretically, such a query should work faster.

The question is: what is faster and is there a better way to simply determine if a match exists?

PS I tested the speed through the mysql terminal, for both options 1 row in set (0.00 sec)

Reply

Answer the question

In order to leave comments, you need to log in

6 answer(s)

E

eaa, 2012-07-02
@eaa

You yourself write “with explain, the selection itself is not made” and at the same time you want to get a result that can be obtained _only_ when selecting from the database. Don't you think this is, how to say, absurd?
In general, if we assume that explain select does make a selection, then it is clear that if, in addition to showing the results of the selection, it also paints how the query is executed, then additional time is needed for this. This means that it will take longer than a simple select.

K

kuzemchik, 2012-07-02
@kuzemchik

Explain can lie. If analyze has not been done for a long time.

S

Sergey, 2012-07-02
Protko @Fesor

Why save on matches? I'm afraid such a request can't be a bottleneck. Yes, and EXPLAIN is more for testing and analyzing the operation of the system.

T

tnz, 2012-07-02
@tnz

Explain will always give you 1 line. Only if there is no entry in the table, it will write something like “Impossible WHERE noticed after reading const tables”. And what's the point? Search by index is already so fast, it doesn’t even go to the table with such a query.

A

AxisPod, 2012-07-02
@AxisPod

SELECT count(*) FROM table WHERE id=123 LIMIT 1

G

Goodver, 2012-07-02
@Goodver

Actually, the point is that I DO NOT need the result, I just need to know if there is a match or not. EXPLAIN in principle cannot make a selection, it just shows what will be done when a select is selected. SELECT does the same as EXPLAIN + the selection itself, that is, when the query is first analyzed, and then the selection is made. So the idea is that if I do not need the selection itself, then why use the select if the exploit will already do enough.
No, it doesn't seem absurd to me.