SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member netwalkman's Avatar
    Join Date
    Aug 2003
    Location
    Hefei,China
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Can this query be optimized?

    Now I have 2 tables with the following structure:
    Code:
    CREATE TABLE t1 (
      a_id int(11) NOT NULL auto_increment,
      b_id int(11) NOT NULL default '0',
      key_a int(11) NOT NULL default '0',
      PRIMARY KEY  (a_id),
      KEY b_id (b_id),
      KEY key_a (key_a)
    ) TYPE=MyISAM AUTO_INCREMENT=1 ;
    Code:
    CREATE TABLE t2 (
      b_id int(11) NOT NULL auto_increment,
      key_b int(11) NOT NULL default '0',
      PRIMARY KEY  (b_id),
      KEY key_b (key_b)
    ) TYPE=MyISAM AUTO_INCREMENT=1 ;

    t1.b_id is a foreign key here.


    In this case,how can the following query be optimized?

    Code:
    select count(*) from t1 left join t2 on t1.b_id=t2.b_id
    where (t1.key_a=3333 and (t2.key_b=0 or t2.b_id is null)) or (t2.key_b in (1,2,777));
    Since t2 is set to be dependant on t1,in this query mysql will first
    run a full scan on t1 -- if t1 contains 1M records ,this would take
    too much time.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    why would it run a full scan when you've given it a value for key_a which has an index?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member netwalkman's Avatar
    Join Date
    Aug 2003
    Location
    Hefei,China
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    why would it run a full scan when you've given it a value for key_a which has an index?
    Because 't2.key_b in (1,2,777)' can also match the where clause.

    Here I provide some datas for testing.

    Code:
    INSERT INTO t1 VALUES (1, 1, 333);
    INSERT INTO t1 VALUES (2, 2, 3333);
    INSERT INTO t1 VALUES (3, 1, 399);
    INSERT INTO t1 VALUES (4, 2, 69);
    INSERT INTO t1 VALUES (5, 3, 924);
    INSERT INTO t1 VALUES (6, 4, 199);
    INSERT INTO t1 VALUES (7, 5, 736);
    INSERT INTO t1 VALUES (8, 6, 613);
    INSERT INTO t1 VALUES (9, 7, 712);
    INSERT INTO t1 VALUES (10, 8, 107);
    INSERT INTO t1 VALUES (11, 9, 942);
    INSERT INTO t1 VALUES (12, 10, 233);
    INSERT INTO t1 VALUES (13, 11, 611);
    INSERT INTO t1 VALUES (14, 12, 260);
    INSERT INTO t1 VALUES (15, 13, 247);
    INSERT INTO t1 VALUES (16, 14, 861);
    INSERT INTO t1 VALUES (17, 15, 169);
    INSERT INTO t1 VALUES (18, 16, 78);
    INSERT INTO t1 VALUES (19, 17, 265);
    INSERT INTO t1 VALUES (20, 18, 455);
    INSERT INTO t1 VALUES (21, 19, 596);
    INSERT INTO t1 VALUES (22, 20, 180);
    
    
    INSERT INTO t2 VALUES (1, 1);
    INSERT INTO t2 VALUES (2, 2);
    INSERT INTO t2 VALUES (3, 3);
    INSERT INTO t2 VALUES (4, 4);
    INSERT INTO t2 VALUES (5, 5);
    INSERT INTO t2 VALUES (6, 6);
    INSERT INTO t2 VALUES (7, 7);
    INSERT INTO t2 VALUES (8, 8);
    INSERT INTO t2 VALUES (9, 9);
    INSERT INTO t2 VALUES (10, 10);
    INSERT INTO t2 VALUES (11, 11);
    INSERT INTO t2 VALUES (12, 12);
    INSERT INTO t2 VALUES (13, 13);
    INSERT INTO t2 VALUES (14, 14);
    INSERT INTO t2 VALUES (15, 15);
    INSERT INTO t2 VALUES (16, 16);
    INSERT INTO t2 VALUES (17, 17);
    INSERT INTO t2 VALUES (18, 18);
    INSERT INTO t2 VALUES (19, 19);
    INSERT INTO t2 VALUES (20, 20);
    The `explain` result is like this:
    Code:
    +-------+--------+---------------+---------+---------+---------+------+-------------+
     | table | type   | possible_keys | key     | key_len | ref     | rows | Extra       |
    +-------+--------+---------------+---------+---------+---------+------+-------------+
     | t1     | ALL    | key_a         | NULL    |    NULL | NULL    |   22 |              |
     | t2    | eq_ref | PRIMARY       | PRIMARY |       4 | t1.b_id |    1 | Using where |
    +-------+--------+---------------+---------+---------+---------+------+-------------+
    Does it mean mysql will run a full scan on t1?

  4. #4
    SitePoint Member
    Join Date
    May 2004
    Location
    Stockholm Sweden
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes it will perform a table scan on that column.

    Try this combined index:
    Code:
    alter table t1 add index t1_ix_b_id_key_a (b_id, key_a);
    It gives a better usage of indexes:
    Code:
    id select_type table type   possible_keys key              key_len ref     rows Extra      
    == =========== ===== ====== ============= ================ ======= ======= ==== ===========
    1  SIMPLE      t1    index  key_a         t1_ix_b_id_key_a 8       {null}  22   Using index
    1  SIMPLE      t2    eq_ref PRIMARY       PRIMARY          4       t1.b_id 1    Using where
    One problem in your query is that t2.key_b is on different sides of an OR and MySQL can't utilize an index for a query where the same column exists on different sides of an OR.

  5. #5
    SitePoint Member netwalkman's Avatar
    Join Date
    Aug 2003
    Location
    Hefei,China
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a lot.
    But if t1 gets very large the query will still be very slow.Maybe I should try to use two queries instead.Let php utilize t2.key_b.


Bookmarks

Posting Permissions

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