List:General Discussion« Previous MessageNext Message »
From:Tompkins Neil Date:October 5 2010 8:42am
Subject:Re: Indexing question
View as plain text  
Hi

Find attached the first of my queries, I require to be checked over.  Let me
know if you need any more information, I've included the query, EXPLAIN
output, and the relavant SHOW CREATE TABLE

Regards
Neil

On Mon, Oct 4, 2010 at 9:50 PM, Neil Tompkins
<neil.tompkins@stripped>wrote:

> I've got a fair few number of queries to be checked over. Will send them
> tommorrow
>
>
> On 4 Oct 2010, at 18:27, Gavin Towey <gtowey@stripped> wrote:
>
>  Include the query, EXPLAIN output, and the relavant SHOW CREATE TABLE
>> table \G output.  Someone should be able to offer suggestions.
>>
>> -----Original Message-----
>> From: Tompkins Neil [mailto:neil.tompkins@stripped]
>> Sent: Monday, October 04, 2010 8:54 AM
>> To: Joerg Bruehe
>> Cc: [MySQL]
>> Subject: Re: Indexing question
>>
>> Jörg
>>
>> Thanks for the useful reply.  Maybe I can EXPLAIN my select queries for
>> you
>> to advise if any changes need to be made ?
>>
>> Regards
>> Neil
>>
>> On Mon, Oct 4, 2010 at 3:38 PM, Joerg Bruehe <joerg.bruehe@stripped
>> >wrote:
>>
>>  Hi!
>>>
>>>
>>> Neil Tompkins wrote:
>>>
>>>> Thanks for your reply. So should we create individual indexes on each
>>>> field or a multiple column index ??
>>>>
>>>
>>> This question cannot be answered without checking and measuring your
>>> installation. The decision whether to create an index is always an act
>>> of balancing:
>>>
>>> - If there is an index, the database server can use it to find data
>>> records by looking up the index, not scanning the base data.
>>> This results in load reduction (both CPU and disk IO) and speeds up
>>> query execution.
>>>
>>> - If there is an index, the database server must maintain it whenever
>>> data are altered (insert/update/delete), in addition to the base data.
>>> This is increased load (both CPU and disk IO) and slows down data
>>> changes.
>>>
>>> So obviously you want to create only those indexes that are helpful for
>>> query execution: you will never (voluntarily) create an index on a
>>> column which isn't used in search conditions, or whose use is already
>>> provided by other indexes.
>>> Of the remaining candidate indexes, you will never (voluntarily) create
>>> one that provides less gain in searches than it costs in data changes.
>>>
>>> With MySQL, AFAIK there is the limitation that on one table only one
>>> index can be used. As a result, the choice of indexes to create depends
>>> on the searches executed by your commands, their relative frequency, and
>>> the frequency of data changes.
>>>
>>>
>>> To answer your other question: If you run aggregate functions (like
>>> SUM(), MIN(), or MAX()) on all records of a table, their results could
>>> be computed by accessing a matching index only. I don't know whether
>>> MySQL does this, I propose you check that yourself using EXPLAIN.
>>>
>>> If you run them on subsets of a table only, an index on that column will
>>> not help in general.
>>>
>>> In database implementations, there is the concept of a "covering index":
>>> If you have an index on columns A and B of some table, its contents
>>> (without the base data) would suffice to answer
>>>  SELECT SUM(B) WHERE A = x
>>> Again, I don't know whether MySQL does this, and I refer you to EXPLAIN.
>>>
>>>
>>> HTH,
>>> Jörg
>>>
>>> --
>>> Joerg Bruehe,  MySQL Build Team,  joerg.bruehe@stripped
>>> ORACLE Deutschland B.V. & Co. KG,   Komturstrasse 18a,   D-12099 Berlin
>>> Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven
>>> Amtsgericht Muenchen: HRA 95603
>>>
>>>
>>>
>> This message contains confidential information and is intended only for
>> the individual named.  If you are not the named addressee, you are notified
>> that reviewing, disseminating, disclosing, copying or distributing this
>> e-mail is strictly prohibited.  Please notify the sender immediately by
>> e-mail if you have received this e-mail by mistake and delete this e-mail
>> from your system. E-mail transmission cannot be guaranteed to be secure or
>> error-free as information could be intercepted, corrupted, lost, destroyed,
>> arrive late or incomplete, or contain viruses. The sender therefore does not
>> accept liability for any loss or damage caused by viruses or errors or
>> omissions in the contents of this message, which arise as a result of e-mail
>> transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA
>> 94089, USA, FriendFinder.com
>>
>

Attachment: [text/html]
'players_bids', 'CREATE TABLE `players_bids` ( `players_bids_id` bigint(20) NOT NULL auto_increment, `worlds_id` int(10) NOT NULL, `seasons_id` int(10) NOT NULL, `teams_id_from` bigint(20) NOT NULL, `teams_id_to` bigint(20) NOT NULL, `users_id_from` bigint(20) NOT NULL, `users_id_to` bigint(20) NOT NULL, `players_id` bigint(20) NOT NULL, `bid_value` double NOT NULL default ''0'', `bid_type` enum(''transfer'',''loan'',''exchange'') collate utf8_unicode_ci NOT NULL, `bid_status` varchar(45) collate utf8_unicode_ci NOT NULL, `bid_date` datetime NOT NULL, `bid_completed_date` datetime default NULL, PRIMARY KEY (`players_bids_id`), KEY `FK_players_bids_worlds_id` (`worlds_id`), KEY `IDX_bid_date` (`bid_date`), KEY `IDX_bid_status` (`bid_status`), KEY `IDX_bid_type` (`bid_type`), KEY `FK_players_bids_players_id` (`worlds_id`,`players_id`), KEY `FK_players_bids_teams_id_from` (`worlds_id`,`teams_id_from`), KEY `FK_players_bids_teams_id_to` (`worlds_id`,`teams_id_to`), KEY `FK_players_bids_users_id_from` (`users_id_from`), KEY `FK_players_bids_users_id_to` (`users_id_to`), KEY `IDX_bid_completed_date` (`bid_completed_date`), KEY `FK_players_bids_seasons_id` (`seasons_id`), CONSTRAINT `FK_players_bids_players_id` FOREIGN KEY (`worlds_id`, `players_id`) REFERENCES `players` (`worlds_id`, `players_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_players_bids_seasons_id` FOREIGN KEY (`seasons_id`) REFERENCES `seasons` (`seasons_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_players_bids_teams_id_from` FOREIGN KEY (`worlds_id`, `teams_id_from`) REFERENCES `teams` (`worlds_id`, `teams_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_players_bids_teams_id_to` FOREIGN KEY (`worlds_id`, `teams_id_to`) REFERENCES `teams` (`worlds_id`, `teams_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_players_bids_users_id_from` FOREIGN KEY (`users_id_from`) REFERENCES `users` (`users_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_players_bids_users_id_to` FOREIGN KEY (`users_id_to`) REFERENCES `users` (`users_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_players_bids_worlds_id` FOREIGN KEY (`worlds_id`) REFERENCES `worlds` (`worlds_id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=266 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci' SELECT players_bids.players_bids_id, players_bids.bid_value, players_bids.teams_id_from, players_bids.teams_id_to, players_bids.users_id_from, players_bids.bid_completed_date, players_bids.users_id_to, players_bids.players_id, users.gamer_tag, players_master.first_name, players_master.second_name, players_master.known_as, teams_master.team_name FROM players_bids INNER JOIN users ON players_bids.users_id_from = users.users_id INNER JOIN players_master ON players_bids.players_id = players_master.players_id INNER JOIN teams_master ON players_bids.teams_id_to = teams_master.teams_id WHERE (players_bids.teams_id_from = 15) AND players_bids.bid_status = 'approved' AND players_bids.worlds_id = 1 ORDER BY bid_value DESC LIMIT 1 '1', 'SIMPLE', 'players_bids', 'index_merge', 'FK_players_bids_worlds_id,IDX_bid_status,FK_players_bids_players_id,FK_players_bids_teams_id_from,FK_players_bids_teams_id_to,FK_players_bids_users_id_from', 'FK_players_bids_teams_id_from,IDX_bid_status', '12,137', NULL, '4', 'Using intersect(FK_players_bids_teams_id_from,IDX_bid_status); Using where; Using filesort' '1', 'SIMPLE', 'teams_master', 'ref', 'PRIMARY', 'PRIMARY', '8', 'fifaleagues.players_bids.teams_id_to', '1', 'Using index' '1', 'SIMPLE', 'users', 'eq_ref', 'PRIMARY', 'PRIMARY', '8', 'fifaleagues.players_bids.users_id_from', '1', '' '1', 'SIMPLE', 'players_master', 'ref', 'PRIMARY', 'PRIMARY', '8', 'fifaleagues.players_bids.players_id', '1', ''
Thread
Introduction and InvitationBill Doerrfeld11 Mar
  • Indexing questionJonas Galvez1 Oct
    • RE: Indexing questionGavin Towey1 Oct
      • Re: Indexing questionTompkins Neil3 Oct
        • Re: Indexing questionJoerg Bruehe3 Oct
          • Re: Indexing questionNeil Tompkins3 Oct
            • Re: Indexing questionJoerg Bruehe4 Oct
              • Re: Indexing questionTompkins Neil4 Oct
                • RE: Indexing questionGavin Towey4 Oct
                  • Re: Indexing questionNeil Tompkins4 Oct
                    • Re: Indexing questionTompkins Neil5 Oct
          • Re: Indexing questionNeil Tompkins3 Oct
          • Re: Indexing questionJonas Galvez6 Oct