Many hyperlinks are disabled.

Use anonymous login
to enable hyperlinks.

Overview

Comment: | In the query planner, do not consider index X to be a proper subset of index Y if X is a covering index but Y is not. |
---|---|

Downloads: | Tarball | ZIP archive | SQL archive |

Timelines: | family | ancestors | descendants | both | trunk |

Files: | files | file ages | folders |

SHA3-256: |
ee31c04353cd75ea4bbadee2994c30d3 |

User & Date: | drh 2017-10-15 22:16:25 |

Context

2017-10-16
| ||

11:50 | Improved comments, including a typo fix, on the whereLoopCheaperProperSubset() function. check-in: 700a3c69 user: drh tags: trunk | |

2017-10-15
| ||

22:16 | In the query planner, do not consider index X to be a proper subset of index Y if X is a covering index but Y is not. check-in: ee31c043 user: drh tags: trunk | |

2017-10-14
| ||

19:54 | Updates to the Makefiles for MSVC. check-in: ac8786f3 user: mistachkin tags: trunk | |

Changes

Changes to src/where.c.

1881 1881 } 1882 1882 } 1883 1883 1884 1884 /* 1885 1885 ** Return TRUE if all of the following are true: 1886 1886 ** 1887 1887 ** (1) X has the same or lower cost that Y 1888 -** (2) X is a proper subset of Y 1889 -** (3) X skips at least as many columns as Y 1888 +** (2) X users fewer WHERE clause terms than Y 1889 +** (3) Every WHERE clause term used by X is also used by Y 1890 +** (4) X skips at least as many columns as Y 1891 +** (5) If X is a covering index, than Y is too 1890 1892 ** 1891 -** By "proper subset" we mean that X uses fewer WHERE clause terms 1892 -** than Y and that every WHERE clause term used by X is also used 1893 -** by Y. 1894 -** 1893 +** Conditions (2) and (3) mean that X is a "proper subset" of Y. 1895 1894 ** If X is a proper subset of Y then Y is a better choice and ought 1896 1895 ** to have a lower cost. This routine returns TRUE when that cost 1897 -** relationship is inverted and needs to be adjusted. The third rule 1896 +** relationship is inverted and needs to be adjusted. Constraint (4) 1898 1897 ** was added because if X uses skip-scan less than Y it still might 1899 1898 ** deserve a lower cost even if it is a proper subset of Y. 1900 1899 */ 1901 1900 static int whereLoopCheaperProperSubset( 1902 1901 const WhereLoop *pX, /* First WhereLoop to compare */ 1903 1902 const WhereLoop *pY /* Compare against this WhereLoop */ 1904 1903 ){ ................................................................................ 1913 1912 } 1914 1913 for(i=pX->nLTerm-1; i>=0; i--){ 1915 1914 if( pX->aLTerm[i]==0 ) continue; 1916 1915 for(j=pY->nLTerm-1; j>=0; j--){ 1917 1916 if( pY->aLTerm[j]==pX->aLTerm[i] ) break; 1918 1917 } 1919 1918 if( j<0 ) return 0; /* X not a subset of Y since term X[i] not used by Y */ 1919 + } 1920 + if( (pX->wsFlags&WHERE_IDX_ONLY)!=0 1921 + && (pY->wsFlags&WHERE_IDX_ONLY)==0 ){ 1922 + return 0; /* Constraint (5) */ 1920 1923 } 1921 1924 return 1; /* All conditions meet */ 1922 1925 } 1923 1926 1924 1927 /* 1925 1928 ** Try to adjust the cost of WhereLoop pTemplate upwards or downwards so 1926 1929 ** that:

Changes to test/analyze9.test.

1048 1048 INSERT INTO t4 SELECT a, b, c, d, e, f FROM data; 1049 1049 ANALYZE; 1050 1050 } {} 1051 1051 1052 1052 do_eqp_test 23.1 { 1053 1053 SELECT * FROM t4 WHERE 1054 1054 (e=1 AND b='xyz' AND c='zyx' AND a<'AEA') AND f<300 1055 + -- Formerly used index i41. But i41 is not a covering index whereas 1056 + -- the PRIMARY KEY is a covering index, and so as of 2017-10-15, the 1057 + -- PRIMARY KEY is preferred. 1055 1058 } { 1056 - 0 0 0 {SEARCH TABLE t4 USING INDEX i41 (e=? AND c=? AND b=? AND a<?)} 1059 + 0 0 0 {SEARCH TABLE t4 USING PRIMARY KEY (c=? AND b=? AND a<?)} 1057 1060 } 1058 1061 do_eqp_test 23.2 { 1059 1062 SELECT * FROM t4 WHERE 1060 1063 (e=1 AND b='xyz' AND c='zyx' AND a<'JJJ') AND f<300 1061 1064 } { 1062 1065 0 0 0 {SEARCH TABLE t4 USING INDEX i42 (f<?)} 1063 1066 }