Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2016
    Posts
    4
    Provided Answers: 1

    Answered: What is the appropriate index?

    I am using MySQL Workbench 6.3, trying to build an index that will avoid a full table scan on a query listed below.
    I have two tables in the query, MemberMatch and MemberChoice. Here are simplified versions of those tables

    MemberMatch:
    id INT(11) PK
    group_id INT(11)
    member1_id INT(11)
    member2_id INT(11)

    MemberChoice:
    id INT(11) PK
    member_match_id INT(11)
    member_id INT(11)
    choice ENUM('YES', 'NO')
    timestamp DATETIME

    MemberMatch represents the combination of members within a group, taken two at a time, so a group with 1000 members would have 499,500 records.

    MemberChoice represents a yes or no by one member with respect to the other member in the referenced MemberMatch record and the time of the choice.

    The query below is to determine how many 'YES' choices were made in a give time period:

    Code:
    set @in_group_id := 1;
    set @in_end_date := now();
    set @start_date := date_add(@in_end_date,interval -31 DAY);
    
    select count(*)
    from `MemberMatch` `mm`
    join `MemberChoice` `mc` on `mm`.`id` = `mc`.`member_match_id` and `sh`.`timestamp` between @start_date and @in_end_date and `sh`.`choice` = 'YES'
    where `mm`.`group_id` = @in_group_id
    I have an index comprised of timestamp,choice and member_match_id in MemberChoice
    The explain plan informs me that a full table scan is being done on MemberChoice, with a primary key lookup on MemberMatch
    If I leave out "and `sh`.`choice` = 'YES', it performs an index range scan on the index, rather than a full table scan.
    I've tried re-ordering the components of the index, but that has no effect.
    What do I need to do to have the query use the index?
    TIA,
    Ed

  2. Best Answer
    Posted by eguy

    "It turns out I needed a 2nd index. The components are choice, timestamp and member_match_id, in that order, for the query with choice = 'YES'. A query without that condition requires an index with just timestamp and member_match_id."


  3. #2
    Join Date
    Jun 2016
    Posts
    4
    Provided Answers: 1
    It turns out I needed a 2nd index. The components are choice, timestamp and member_match_id, in that order, for the query with choice = 'YES'. A query without that condition requires an index with just timestamp and member_match_id.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •