Table structure:

SQL:
The SELECT vod_id FROM ` ia_comment ` where vod_id & gt; 0 and account_id=88;
The SELECT vod_id FROM ` ia_comment ` where account_id=88;
Results:
SELECT [SQL] vod_id FROM ` ia_comment ` where vod_id & gt; 0 and account_id=88;
The affected line: 0
Time: 0.323 s

[SQL]
The SELECT vod_id FROM ` ia_comment ` where account_id=88;
The affected line: 0
Time: 0.152 s


Why the second SQL instead of scanning the rows of more efficiency higher
Data from two SQL is the same as
Vod_id all greater than 0

CodePudding user response:

Which version are you using MySQL?

CodePudding user response:

Execution plan, just reference, not necessarily accurate,
A lot of time, the implementation of planned, the efficiency is not necessarily good,

CodePudding user response:

Note key_len, the first SQL is 4 that only joint vod_id index of the first column, so vod_id this index is used only for vod_id & gt; 0 selection, read from the index data, and carries on the account_id=88 judgment, slow is slow in another judgment more account_id=88;
And the second key_len is 8, that is, directly in the where clause for judging index,
So the second will be a bit faster,

CodePudding user response:

Why possible_key is empty, the key is empty, not seen, extra content is not complete, difficult to determine the further,

CodePudding user response:

reference 4 floor metamorphosis of pain response:

why possible_key is null, the key is empty, not seen, extra content is not complete, difficult to determine the further,

Is Using the where; Using the index

CodePudding user response:

The

1/f, reference Dong Xuyang TonyDong response:

which version are you using MySQL?

Version 5.6

CodePudding user response:

Note key_len:
The first SQL is 4, indicates only the index of the first column vod_id, so vod_id this index is used only for vod_id & gt; 0 selection, read from the index data, also to determine account_id=88
Article 2 the term "SQL is 8 that is a composite index,

Rows:
When only the index void_id vod_id & gt; 25 w + 0, the index may scan, when made led combination, 51 w + may scan, this data sometimes is out of date is not correct (sometimes need table analysis), with 1 column index, 2 column index, has its own strategy, is not to say that there are 50 w index scan is 50 w, is likely to be 25 w,

Time:
First, according to an index scan, each need to determine whether=88,
Second, according to the index can be directly positioning to the target line,

All the focus to make partial, vod_id & gt; 0, should not write the where condition, account_id for a specific value, should not be after the combination index,
This index is also basic fee right now,