[Labs-l] Tool labs replicas are missing the indexes?

[Giovanni Luca Ciampaglia]

Nice catch Brad, setting a fixed (ahem) random number indeed runs much
faster:

select page_id, page_title, page_random from page where page_random > 0.002
and page_namespace = 0 order by page_random limit 10;

It's VERY unfortunate that explain does not work -- how am I supposed to
debug my queries then? No explain privilege => more unoptimized queries =>
more queries will be killed => more users will be unhappy => less people
will use the LabsDB.

Cheers,

G



Giovanni Luca Ciampaglia

✎ 919 E 10th ∙ Bloomington 47408 IN ∙ USA
☞ http://www.glciampaglia.com/
✆ +1 812 855-7261
✉ gciampag at indiana.edu

2014-11-11 8:36 GMT-05:00 Brad Jorsch (Anomie) <bjorsch at wikimedia.org>:

> On Tue, Nov 11, 2014 at 1:02 AM, Giovanni Luca Ciampaglia <
> gciampag at indiana.edu> wrote:
>
>> Is there a reason why the tables on the Labs replica are not indexed?
>>
>
> They are.
>
>
>> Drawing a list of random titles with page_random takes more than a minute!
>>
>
> Because you're doing it wrong. "page_random > rand()" evaluates rand()
> *for each row*. Since it's nowhere near constant, it can't use an index.
>
>
> MariaDB [enwiki_p]> show index from page;
>> Empty set (0.00 sec)
>>
>
> That's because enwiki_p.page is a view. The indexes are on enwiki.page,
> which you can see the definitions of with "show create table enwiki.page".
>
> ("show index from enwiki.page" gives a permission error, likely the same
> paranoia about exposing cardinality that makes a normal explain not work.)
>
>
> --
> Brad Jorsch (Anomie)
> Software Engineer
> Wikimedia Foundation
>
> _______________________________________________
> Labs-l mailing list
> Labs-l at lists.wikimedia.org
> https://lists.wikimedia.org/mailman/listinfo/labs-l
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.wikimedia.org/pipermail/labs-l/attachments/20141111/6c40a828/attachment.html>