On Fri, Aug 12, 2016 at 6:01 PM, Chris Depetris <cdepet...@olisystems.com> wrote:
> Select * FROM TABLE where ((M1 IN (0, > 1,2,7,15,150)) AND (M2 IN (0, 1,2,7,15,150) Or M2 IS NULL ) > > AND (M2 IN (0, 1,2,7,15,150) Or M2 IS NULL) AND (M3 IN (0, 1,2,7,15,150) > Or > M3 IS NULL) > > AND (M4 IN (0, 1,2,7,15,150) Or M4 IS NULL) AND (M5 IN (0, 1,2,7,15,150) > Or > M5 IS NULL) > > AND (M6 IN (0, 1,2,7,15,150) Or M6 IS NULL) AND (M7 IN (0, 1,2,7,15,150) > Or > M7 IS NULL) > > AND (M8 IN (0, 1,2,7,15,150) Or M8 IS NULL) AND (M9 IN (0, 1,2,7,15,150) Or > M9 IS NULL) > > AND (M10 IN (0, 1,2,7,15,150) Or M10 IS NULL)) > > This query works and has reasonable performance right now for > us, but I feel like there should be a more efficient way to do this. > Start by showing the query plan for this query, and tell us if you have indexes, if any. If you have none, try with an index on M1 only, and compare the plans and performance. See if making it a compound index of (M1, M2) helps. But given your data, I think that the more cols (or indexes) you add on those Mx columns, won't translate into any gains (diminishing returns on investment in a way). --DD _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
