In the new schema, top-level tables do not contain full entity ids but only numeric ids, as the tables are seprated by type and the prefix was dropped to optimize space usage.
The old wb_terms containeed full entity ids, allowing it to be joined with other tables like page table.
How do I join terms in the new schema with other tables that store full entity ids?
SELECT term_text AS painter, COUNT(term_text) AS paintercount FROM page JOIN pagelinks AS colllink ON page_id=colllink.pl_from AND colllink.pl_from_namespace=0 AND colllink.pl_namespace=0 AND colllink.pl_title='Q18600731' LEFT JOIN pagelinks AS creatorlink ON page_id=creatorlink.pl_from AND creatorlink.pl_from_namespace=0 AND creatorlink.pl_namespace=120 AND creatorlink.pl_title='P170' JOIN wb_terms ON page_title=term_full_entity_id AND term_entity_type='item' AND term_language='en' AND term_type='description' AND term_text LIKE 'painting by %' WHERE page_namespace=0 AND page_is_redirect=0 AND creatorlink.pl_from IS NULL GROUP BY term_text ORDER BY COUNT(term_text) DESC, painter LIMIT 300;
Build full entity ids within the query (part of join condition or by doing a sub-query).
- find a way to write the query in a simpler and performant way
- test give solution on test node to get a sense on performance