Page MenuHomePhabricator
Create Task
Maniphest T77898

query taking 10s: TermSqlIndex::getMatchingIDs
Closed, ResolvedPublic
Actions

Description

Example:
SlowTimer [10385ms] at runtime/ext_mysql: slow query: SELECT /* Wikibase\TermSqlIndex::getMatchingIDs IP.REM.O.VED */ DISTINCT term_entity_id,term_weight FROM wb_terms WHERE (term_language='pl' AND term_search_key LIKE 'p%' AND term_type='label' AND term_entity_type='item') OR (term_language='pl' AND term_search_key LIKE 'p%' AND term_type='alias' AND term_entity_type='item') LIMIT 5000

About 200 matching "at runtime/ext_mysql: slow query: SELECT /* Wikibase\\TermSqlIndex::getMatchingIDs" in the last 2 days.

Related Objects
Search...

StatusSubtypeAssignedTask
 Resolved• Wikidata-bugsT77898 query taking 10s: TermSqlIndex::getMatchingIDs
 DeclinedNoneT78156 [Task] Cache wbsearchentities responses for short prefixes
 ResolvedSmalyshevT78157 [Story] Use ElasticSearch for entity search on wikidata.org
 ResolvedLydia_PintscherT88535 [Task] Investigate preconditions & constraints for using Elastic directly
 DuplicateSmalyshevT117520 Index Wikidata labels, aliases and descriptions as separate fields in ElasticSearch
 ResolvedSmalyshevT125500 [Epic] Index Wikidata labels and descriptions as separate fields in ElasticSearch
 InvalidNoneT132444 [Task] Create repository for WikibaseElastic
 ResolvedSmalyshevT150891 Find a good way to represent multi-lingual text fields in Elastic
 ResolvedSmalyshevT155139 'ContentHandlerForModelID' hook allows creating of handlers that aren't registered
 ResolvedSmalyshevT157604 Make Wikibase register all its content handlers
ResolvedSmalyshevT157626 ContentHandlerSanityTest assumes every handler can create empty content, but Wikibase can't
 ResolvedSmalyshevT171548 Wikidata Elastic search ignores limit
 ResolveddebtT172422 Add stats marker to wikidata entity search
 ResolvedSmalyshevT172467 Make good prefix search profile for Wikidata entities
 ResolvedSmalyshevT173231 Wikidata Elastic search drops results with matches in different language label
 ResolvedSmalyshevT175741 Set ElasticSearch implementation as default for wbsearchentites on Wikidata
 ResolvedSmalyshevT178851 Use label & description index for fulltext search
 ResolvedSmalyshevT176903 Index wikidata descriptions
 ResolvedSmalyshevT180169 Make list of languages where using stemmed analyzer for Wikibase is beneficial
 ResolvedSmalyshevT182271 Wikidata fulltext search should handle search syntax properly
 ResolveddcausseT182293 Tune wikidata fulltext search similarity parameters
 ResolvedSmalyshevT181426 Reindex wikidata to enable description index
 DuplicateNoneT117522 Create standard "completion suggestion" API for Search
 DeclinedNoneT120089 Add an internal completion or suggestions API to core SearchEngine
 DeclinedNoneT170392 Create gadget that enables the use of the elastic search backend for the entity selector
 ResolveddanielT170400 Define metrics for search result quality for the entity selector widget on wikidata.
 ResolvedLydia_PintscherT170405 Manually evaluate cirrus based entity search on test system
 OpenNoneT170547 Metrics to evaluate new search for item suggestor
 DeclinedNoneT170549 Provide A/B test for item suggestor
ResolvedSmalyshevT162292 Reindex wikidata to pick up labels/descriptions mappings
 ResolveddcausseT160926 Make noop script be able replace whole fields with nested subfields
 ResolveddcausseT166589 Update wikidata code to take advantage of nested fields noop script
 ResolvedSmalyshevT175199 Index certain statements for Wikidata items
 Invalid• Wikidata-bugsT85415 [Task] limit prefix match queries of table wb_terms to more than 3 chars

Event Timeline

JanZerebecki created this task.Dec 8 2014, 7:43 PM
JanZerebecki assigned this task to • Wikidata-bugs.
JanZerebecki raised the priority of this task from to High.
JanZerebecki updated the task description. (Show Details)
JanZerebecki added a project: Wikidata.
JanZerebecki changed Security from none to None.
JanZerebecki subscribed.
Tobi_WMDE_SW added a project: Wikidata-Sprint-2014-12-09§.Dec 9 2014, 4:32 PM
JanZerebecki added a comment.Dec 9 2014, 4:39 PM

This query modified for pol instead of p still takes 12s on the analytics slave (down from about 16s for p). With 4 letters it goes below 4seconds.

There is an index named tmp1 over term_language, term_type, term_entity_type, term_search_key on that table, which is not in the source (we might want to fix that).

JanZerebecki moved this task from Backlog to Done on the Wikidata-Sprint-2014-12-09§ board.Dec 9 2014, 4:52 PM
daniel subscribed.Dec 9 2014, 5:01 PM

One solution would be to do type-ahead queries via Cirrus (Elastic (Lucene)). We would probably want to implement T78011 for that.

daniel added a comment.Dec 9 2014, 5:05 PM

As a stop gap, we could start type ahead queries only after at last 3 (or 2, or 4) characters are present. This should be configurable.

We should also look into avoiding fetching 5000 hits all the time. There gotta be a better way.

JanZerebecki added a comment.Dec 9 2014, 6:52 PM

This is only used from the wbsearchentities API used for autosuggest. The code currently first gets an exact match on entity id, then exact matches, then in a 3rd query prefix matches if more matches are wanted. Quite many 3 or less letter combinations have enough exact matches. The query in question is a 3rd one. Limiting prefix matches to 4 or more letters is probably sensible for now. Long term I like the cirrus way.

Request that causes this query: https://www.wikidata.org/w/api.php?action=wbsearchentities&search=p&format=json&language=pl&type=item&continue=0

Aklapper added a project: Performance Issue.Dec 9 2014, 8:37 PM
daniel added a comment.Dec 10 2014, 10:55 AM

On a related note, we should not use the terms table for uniqueness checks. See T74430

daniel added a subtask: T78156: [Task] Cache wbsearchentities responses for short prefixes.Dec 10 2014, 4:28 PM
Lydia_Pintscher moved this task from incoming to needs discussion or investigation on the Wikidata board.Dec 27 2014, 8:08 PM
Lydia_Pintscher subscribed.

What are the next steps here? Can we create tasks for them? It's been moved to done in the sprint but not closed.

JanZerebecki mentioned this in T85414: wikibase: synchronize schema on production with what is created on install.Dec 28 2014, 5:31 PM
JanZerebecki added a comment.Dec 28 2014, 5:50 PM

The investigation was done. All the work needed is now in subtasks.

Lydia_Pintscher closed this task as Resolved.Dec 28 2014, 5:57 PM

Ok thanks. Closing this one then.

Lydia_Pintscher closed subtask T85415: [Task] limit prefix match queries of table wb_terms to more than 3 chars as Invalid.Apr 23 2017, 6:18 PM
Smalyshev closed subtask T78157: [Story] Use ElasticSearch for entity search on wikidata.org as Resolved.Nov 27 2017, 6:42 PM
Addshore closed subtask T78156: [Task] Cache wbsearchentities responses for short prefixes as Declined.Jul 16 2021, 8:53 AM
Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL · Credits