As an example from my BEACON tool, I want all properties that have a formatter property, with English label. That SQL is now:

SELECT DISTINCT page_title,term_text FROM pagelinks,page,wb_terms WHERE page_namespace=120 AND substr(page_title,2)=term_entity_id and term_entity_type='property' and term_language='en' and term_type='label' and pl_from=page_id and pl_title='P1630' and pl_namespace=120 and pl_from_namespace=120 ORDER BY term_text

Note the "substr". My first attempt was "page_title=concat('Q',term_entity_id)", but that took forever.

If we indeed get a full entity ID=page title column for wb_terms, and for wb_items_per_site etc., that would at least fix the on-the-fly compute. I shall thus wait with code updates until I get the full story, and not just piece-by-piece...

On Thu, Jun 1, 2017 at 3:29 PM Daniel Kinzler <daniel.kinzler@wikimedia.de> wrote:

Am 01.06.2017 um 16:07 schrieb Magnus Manske:
> So I'll be busy finding uses of this table, and changing them, for the next week
> or two...
>
> Note that since now I have to use substring comparisons in queries (instead of
> integer comparisons), for example with wb_terms, SQL queries will run slower as
> a result.

wb_terms will also get a column with the full entity ID, and will lose the
numeric ID column. We will announce this separately.

Magnus, can you give some examples of what you use wb_entity_per_page for? If
it's for building links, you can now use the IDs directly, without looking at
the page table. Are there other things besides the terms table that would use
substrings?

Thanks!

--
Daniel Kinzler
Principal Platform Engineer

Wikimedia Deutschland
Gesellschaft zur Förderung Freien Wissens e.V.