I am trying to speed up some DB operations and perhaps have gone
overboard
with indexes. Does MySQL usually use only one index per query and
simply
match keys on the results of the indexed first part? For example:
DELETE FROM tokens WHERE 6813946236211560448 <= fnv AND fnv <
6818449835838930944 AND updated_at<‘2010-06-20 14:08:55’ AND
occurrences=0;
If I understand correctly from the output of:
EXPLAIN SELECT * FROM tokens WHERE 6813946236211560448 <= fnv AND fnv <
6818449835838930944 AND updated_at<‘2010-06-20 14:08:55’ AND
occurrences=0;
only the index on fnv is used (there are indexes on updated_at and
occurrences).
Is this correct?
TIA,
Jeffrey
On Jul 20, 3:12 pm, “Jeffrey L. Taylor” [email protected] wrote:
I am trying to speed up some DB operations and perhaps have gone overboard
with indexes. Does MySQL usually use only one index per query and simply
match keys on the results of the indexed first part? For example:
one one index will be used per table
Is this correct?
hard to say without said output
Fred
Quoting Frederick C. [email protected]:
DELETE FROM tokens WHERE 6813946236211560448 <= fnv AND fnv < 6818449835838930944 AND updated_at<‘2010-06-20 14:08:55’ AND occurrences=0;
hard to say without said output
mysql> EXPLAIN EXTENDED SELECT * FROM tokens WHERE 6813946236211560448
<= fnv AND fnv < 6818449835838930944 AND updated_at<‘2010-06-20
14:08:55’ AND occurrences=0;
±—±------------±-------±------±-------------------------------------------------------±--------------------------------±--------±-----±-----±------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±-------±------±-------------------------------------------------------±--------------------------------±--------±-----±-----±------------+
| 1 | SIMPLE | tokens | range | index_tokens_on_fnv_and_user_id,updated_at,occurrences | index_tokens_on_fnv_and_user_id | 8 | NULL | 6153 | Using where |
±—±------------±-------±------±-------------------------------------------------------±--------------------------------±--------±-----±-----±------------+
1 row in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
| Note | 1003 | select amethyst_production
.tokens
.fnv
AS fnv
,amethyst_production
.tokens
.user_id
AS user_id
,amethyst_production
.tokens
.occurrences
AS occurrences
,amethyst_production
.tokens
.clicks
AS clicks
,amethyst_production
.tokens
.hides
AS hides
,amethyst_production
.tokens
.ups
AS ups
,amethyst_production
.tokens
.downs
AS downs
,amethyst_production
.tokens
.expires
AS expires
,amethyst_production
.tokens
.token
AS token
,amethyst_production
.tokens
.created_at
AS created_at
,amethyst_production
.tokens
.updated_at
AS updated_at
from amethyst_production
.tokens
where ((amethyst_production
.tokens
.occurrences
= 0) and (6813946236211560448 <= amethyst_production
.tokens
.fnv
) and (amethyst_production
.tokens
.fnv
< 6818449835838930944) and (amethyst_production
.tokens
.updated_at
< _latin1’2010-06-20 14:08:55’))
Jeffrey
On Jul 20, 3:55 pm, “Jeffrey L. Taylor” [email protected] wrote:
mysql> EXPLAIN EXTENDED SELECT * FROM tokens WHERE 6813946236211560448 <= fnv AND fnv < 6818449835838930944 AND updated_at<‘2010-06-20 14:08:55’ AND occurrences=0;
±—±------------±-------±------±------------------------------------- ------------------±--------------------------------±--------±-----±---- -±------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±-------±------±------------------------------------- ------------------±--------------------------------±--------±-----±---- -±------------+
| 1 | SIMPLE | tokens | range | index_tokens_on_fnv_and_user_id,updated_at,occurrences | index_tokens_on_fnv_and_user_id | 8 | NULL | 6153 | Using where |
±—±------------±-------±------±------------------------------------- ------------------±--------------------------------±--------±-----±---- -±------------+
1 row in set, 1 warning (0.00 sec)
Yup, that is just using the index on fnv.
Fred
Quoting Frederick C. [email protected]:
Yup, that is just using the index on fnv.
Fred
Fred,
Thank you. Deleting the other indexes should speed up writes.
Jeffrey