I noticed the IS NOT NULL version appear. Forcing the index in that case may not help; I suspect not-null will just cause an index scan and double the overhead compared to the table scan (because the secondary index isn't clustered).

So what's the best thing to do for now? Not adding "IS NOT NULL" and letting it do a full table scan? That global query is indeed the last one that remains noticeably slow (it's running over 120+ million rows).


On Mon, May 12, 2014 at 4:46 PM, Sean Pringle <springle@wikimedia.org> wrote:


On May 12, 2014 11:36 PM, "Gilles Dubuc" <gilles@wikimedia.org> wrote:
>
> Thanks for adding that index, that's exactly what I needed. I'll update the queries to what you suggested now. I actually looked into doing exactly that change earlier today, but couldn't get it to hit the timestamp index. I wasn't specifying a value for "wiki" (it's one of the queries we run, the "global" counts), and it wasn't hitting the index, probably because of NULL values. Now I can force it hit that new index, so everything should be back to sane execution time for those tables.

I noticed the IS NOT NULL version appear. Forcing the index in that case may not help; I suspect not-null will just cause an index scan and double the overhead compared to the table scan (because the secondary index isn't clustered).

However the log tables are using TokuDB, instead of InnoDB, now, so it should be possible to have multiple clustered indexes. Will experiment with it more tomorrow and report back.


_______________________________________________
Analytics mailing list
Analytics@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/analytics