No keys/indexes on wiki databases?

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

No keys/indexes on wiki databases?

Maciej Jaros
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 ([hidden email])
https://lists.wikimedia.org/mailman/listinfo/toolserver-l
Posting guidelines for this list: https://wiki.toolserver.org/view/Mailing_list_etiquette
Reply | Threaded
Open this post in threaded view
|

Re: No keys/indexes on wiki databases?

John Doe-27
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 ([hidden email])
https://lists.wikimedia.org/mailman/listinfo/toolserver-l
Posting guidelines for this list: https://wiki.toolserver.org/view/Mailing_list_etiquette
Reply | Threaded
Open this post in threaded view
|

Re: No keys/indexes on wiki databases?

Tim Landscheidt
In reply to this post by Maciej Jaros
Maciej Jaros <[hidden email]> 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 ([hidden email])
https://lists.wikimedia.org/mailman/listinfo/toolserver-l
Posting guidelines for this list: https://wiki.toolserver.org/view/Mailing_list_etiquette
Reply | Threaded
Open this post in threaded view
|

Re: No keys/indexes on wiki databases?

Morten Wang
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 <[hidden email]> wrote:
Maciej Jaros <[hidden email]> 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 ([hidden email])
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 ([hidden email])
https://lists.wikimedia.org/mailman/listinfo/toolserver-l
Posting guidelines for this list: https://wiki.toolserver.org/view/Mailing_list_etiquette
Reply | Threaded
Open this post in threaded view
|

Re: No keys/indexes on wiki databases?

Tim Landscheidt
Morten Wang <[hidden email]> wrote:

> 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

> [...]

... the outdated SVN source :-).  Thanks, I fixed this.  Be
aware, though, that this doesn't reflect the actual configu-
ration of the Toolserver as shown recently when the Wikidata
schema changed.

Tim


_______________________________________________
Toolserver-l mailing list ([hidden email])
https://lists.wikimedia.org/mailman/listinfo/toolserver-l
Posting guidelines for this list: https://wiki.toolserver.org/view/Mailing_list_etiquette