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 ✉ [email protected] 2014-11-11 8:36 GMT-05:00 Brad Jorsch (Anomie) <[email protected]>: > On Tue, Nov 11, 2014 at 1:02 AM, Giovanni Luca Ciampaglia < > [email protected]> 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 > [email protected] > https://lists.wikimedia.org/mailman/listinfo/labs-l > >
_______________________________________________ Labs-l mailing list [email protected] https://lists.wikimedia.org/mailman/listinfo/labs-l
