Changes between Version 3 and Version 4 of innodbIssue


Ignore:
Timestamp:
11/05/2010 05:14:05 PM (9 years ago)
Author:
jbecla
Comment:

--

Legend:

Unmodified
Added
Removed
Modified
  • innodbIssue

    v3 v4  
    251251|  1 | SIMPLE      | i01   | ref  | ra            | ra   | 4       | lsst_1.i02.ra |       17 | Using index |  
    252252+----+-------------+-------+------+---------------+------+---------+---------------+----------+-------------+ 
    253 2 rows in set (0.00 sec) 
     2532 rows in set (0.01 sec) 
    254254 
    255255mysql> explain select count(*) from i02, i03 where i02.ra=i03.ra and i02.ra<3 and i03.bMag>0.2; 
     
    2622622 rows in set (0.00 sec) 
    263263 
     264mysql> explain select count(*) from i03, i04 where i03.ra=i04.ra and i03.ra<3 and i04.bMag>0.2; 
     265+----+-------------+-------+------+---------------+------+---------+---------------+----------+-------------+ 
     266| id | select_type | table | type | possible_keys | key  | key_len | ref           | rows     | Extra       | 
     267+----+-------------+-------+------+---------------+------+---------+---------------+----------+-------------+ 
     268|  1 | SIMPLE      | i04   | ALL  | bMag,ra       | NULL | NULL    | NULL          | 30000445 | Using where |  
     269|  1 | SIMPLE      | i03   | ref  | ra            | ra   | 4       | lsst_1.i04.ra |       19 | Using index |  
     270+----+-------------+-------+------+---------------+------+---------+---------------+----------+-------------+ 
     2712 rows in set (0.00 sec) 
     272 
     273mysql> explain select count(*) from i04, i05 where i04.ra=i05.ra and i04.ra<3 and i05.bMag>0.2; 
     274+----+-------------+-------+------+---------------+------+---------+---------------+----------+-------------+ 
     275| id | select_type | table | type | possible_keys | key  | key_len | ref           | rows     | Extra       | 
     276+----+-------------+-------+------+---------------+------+---------+---------------+----------+-------------+ 
     277|  1 | SIMPLE      | i05   | ALL  | bMag,ra       | NULL | NULL    | NULL          | 30000507 | Using where |  
     278|  1 | SIMPLE      | i04   | ref  | ra            | ra   | 4       | lsst_1.i05.ra |       12 | Using index |  
     279+----+-------------+-------+------+---------------+------+---------+---------------+----------+-------------+ 
     2802 rows in set (0.00 sec) 
     281 
     282mysql> explain select count(*) from i05, i06 where i05.ra=i06.ra and i05.ra<3 and i06.bMag>0.2; 
     283+----+-------------+-------+-------+---------------+------+---------+---------------+---------+-------------+ 
     284| id | select_type | table | type  | possible_keys | key  | key_len | ref           | rows    | Extra       | 
     285+----+-------------+-------+-------+---------------+------+---------+---------------+---------+-------------+ 
     286|  1 | SIMPLE      | i06   | range | bMag,ra       | bMag | 4       | NULL          | 4611370 | Using where |  
     287|  1 | SIMPLE      | i05   | ref   | ra            | ra   | 4       | lsst_1.i06.ra |      14 | Using index |  
     288+----+-------------+-------+-------+---------------+------+---------+---------------+---------+-------------+ 
     2892 rows in set (0.00 sec) 
     290 
     291mysql> explain select count(*) from i06, i07 where i06.ra=i07.ra and i06.ra<3 and i07.bMag>0.2; 
     292+----+-------------+-------+------+---------------+------+---------+---------------+----------+-------------+ 
     293| id | select_type | table | type | possible_keys | key  | key_len | ref           | rows     | Extra       | 
     294+----+-------------+-------+------+---------------+------+---------+---------------+----------+-------------+ 
     295|  1 | SIMPLE      | i07   | ALL  | bMag,ra       | NULL | NULL    | NULL          | 30000474 | Using where |  
     296|  1 | SIMPLE      | i06   | ref  | ra            | ra   | 4       | lsst_1.i07.ra |       15 | Using index |  
     297+----+-------------+-------+------+---------------+------+---------+---------------+----------+-------------+ 
     2982 rows in set (0.00 sec) 
     299 
    264300}}} 
    265301 
    266 And it might take 20 sec (the former plan), or 17 min (the latter plan). 
    267  
    268  
    269  
    270  
     302And it might take 20 sec (the plan with "ALL"), or 17 min (the plan with "range"). 
     303 
     304 
     305 
     306