Re: [Toolserver-l] No keys/indexes on wiki databases?
If you wonder what the standard indexes are, there's a link to the SVN source on https://wiki.toolserver.org/view/Database_access#Database_schema Regards, Morten On 16 March 2013 12:20, Tim Landscheidt wrote: > Maciej Jaros wrote: > > > I've notice that there are no keys and so probably no > > indexes on toolserver databases. Is this intentional? > > I've used below queries - both show no keys/indexes on revision table: > > > SHOW COLUMNS FROMplwiki_p.revision; > > > SHOW INDEX FROM plwiki_p.revision; > > > I've also exported the whole schema and see no indexes at > > all... The same for random wiki on s2 - nlwiki_p. > > > [...] > > They are /views/, not tables; use "SHOW CREATE VIEW > revision;" to see the definition. Unfortunately, MySQL > doesn't make this obvious. You cannot see if there are > keys/indexes defined for the underlying tables as a normal > user. > > Tim > > > ___ > Toolserver-l mailing list (Toolserver-l@lists.wikimedia.org) > https://lists.wikimedia.org/mailman/listinfo/toolserver-l > Posting guidelines for this list: > https://wiki.toolserver.org/view/Mailing_list_etiquette ___ Toolserver-l mailing list (Toolserver-l@lists.wikimedia.org) https://lists.wikimedia.org/mailman/listinfo/toolserver-l Posting guidelines for this list: https://wiki.toolserver.org/view/Mailing_list_etiquette
Re: [Toolserver-l] No keys/indexes on wiki databases?
Maciej Jaros wrote: > I've notice that there are no keys and so probably no > indexes on toolserver databases. Is this intentional? > I've used below queries - both show no keys/indexes on revision table: > SHOW COLUMNS FROMplwiki_p.revision; > SHOW INDEX FROM plwiki_p.revision; > I've also exported the whole schema and see no indexes at > all... The same for random wiki on s2 - nlwiki_p. > [...] They are /views/, not tables; use "SHOW CREATE VIEW revision;" to see the definition. Unfortunately, MySQL doesn't make this obvious. You cannot see if there are keys/indexes defined for the underlying tables as a normal user. Tim ___ Toolserver-l mailing list (Toolserver-l@lists.wikimedia.org) https://lists.wikimedia.org/mailman/listinfo/toolserver-l Posting guidelines for this list: https://wiki.toolserver.org/view/Mailing_list_etiquette
Re: [Toolserver-l] No keys/indexes on wiki databases?
You are only seeing views, not the actual tables. There are the standard index plus a few TS specific indexes On Saturday, March 16, 2013, Maciej Jaros wrote: > Hi. > > I've notice that there are no keys and so probably no indexes on > toolserver databases. Is this intentional? > I've used below queries - both show no keys/indexes on revision table: > > SHOW COLUMNS FROM plwiki_p.revision; > > SHOW INDEX FROM plwiki_p.revision; > > I've also exported the whole schema and see no indexes at all... The same > for random wiki on s2 - nlwiki_p. > > At first I thought this might be do to the replication, but even with > replication you need to select some stuff and only insert latest data. And > besides those are read-only databases so selecting should occur far more > frequently then inserting and so indexing is crucial for performance. Is it > not? > > Why am I asking this? Well from a month or so I received a lot of query > kills which should never be killed. Why they should never be killed? > Because they are (or should be) working only on indexes: > >- revision.rev_id - should be PRIMARY KEY > - revision.rev_page - should be KEY / FOREIGN KEY > > My query is: > > SELECT /* SLOW_OK */ MIN(rev_id) as first_rev_id > FROM revision > WHERE rev_page IN > (2,4,6,8,12,13,15,16,18,20,21,22,23,24,25,26,28,29,...) > GROUP BY rev_page > > I admit the IN list is rather large, but still... > > Regards, > Nux. > ___ Toolserver-l mailing list (Toolserver-l@lists.wikimedia.org) https://lists.wikimedia.org/mailman/listinfo/toolserver-l Posting guidelines for this list: https://wiki.toolserver.org/view/Mailing_list_etiquette
[Toolserver-l] No keys/indexes on wiki databases?
Hi. I've notice that there are no keys and so probably no indexes on toolserver databases. Is this intentional? I've used below queries - both show no keys/indexes on revision table: SHOW COLUMNS FROMplwiki_p.revision; SHOW INDEX FROM plwiki_p.revision; I've also exported the whole schema and see no indexes at all... The same for random wiki on s2 - nlwiki_p. At first I thought this might be do to the replication, but even with replication you need to select some stuff and only insert latest data. And besides those are read-only databases so selecting should occur far more frequently then inserting and so indexing is crucial for performance. Is it not? Why am I asking this? Well from a month or so I received a lot of query kills which should never be killed. Why they should never be killed? Because they are (or should be) working only on indexes: * revision.rev_id - should be PRIMARY KEY * revision.rev_page - should be KEY / FOREIGN KEY My query is: SELECT /* SLOW_OK */ MIN(rev_id) as first_rev_id FROM revision WHERE rev_page IN (2,4,6,8,12,13,15,16,18,20,21,22,23,24,25,26,28,29,...) GROUP BY rev_page I admit the IN list is rather large, but still... Regards, Nux. ___ Toolserver-l mailing list (Toolserver-l@lists.wikimedia.org) https://lists.wikimedia.org/mailman/listinfo/toolserver-l Posting guidelines for this list: https://wiki.toolserver.org/view/Mailing_list_etiquette
