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 -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On Jul 20, 3:12 pm, "Jeffrey L. Taylor" <r...-f/t7CGFWhwGcvWdFBKKxig@public.gmane.org> 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> > 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?hard to say without said output :-) Fred> > TIA, > Jeffrey-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Quoting Frederick Cheung <frederick.cheung-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>:> > > On Jul 20, 3:12 pm, "Jeffrey L. Taylor" <r...-f/t7CGFWhwGcvWdFBKKxig@public.gmane.org> 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 > > > > > 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? > > 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 -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On Jul 20, 3:55 pm, "Jeffrey L. Taylor" <r...-f/t7CGFWhwGcvWdFBKKxig@public.gmane.org> 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> > 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-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Quoting Frederick Cheung <frederick.cheung-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>:> On Jul 20, 3:55 pm, "Jeffrey L. Taylor" <r...-f/t7CGFWhwGcvWdFBKKxig@public.gmane.org> 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 >Fred, Thank you. Deleting the other indexes should speed up writes. Jeffrey -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.