r93478 MediaWiki - Code Review archive

Repository:MediaWiki
Revision:< r93477‎ | r93478 | r93479 >
Date:18:54, 29 July 2011
Author:kipcool
Status:deferred
Tags:
Comment:
faster SQL queries for getSQLToSelectPossibleAttributes , getSQLForClasses , getSQLForCollection
Modified paths:
  • /trunk/extensions/Wikidata/OmegaWiki/SpecialSuggest.php (modified) (history)

Diff [purge]

Index: trunk/extensions/Wikidata/OmegaWiki/SpecialSuggest.php
——@@ -240,9 +240,7 @@
241241 $dc = wdGetDataSetContext();
242242 $dbr = wfGetDB( DB_SLAVE );
243243
244 - $language = $wgLang->getCode() ;
245 - $lng = ' ( SELECT language_id FROM language WHERE wikimedia_key = ' . $dbr->addQuotes( $language ) . ' ) ';
246 -
 244+ $userlang = ' ( SELECT language_id FROM language WHERE wikimedia_key = ' . $dbr->addQuotes( $wgLang->getCode() ) . ' LIMIT 1 ) ';
247245 $classMids = $wgDefaultClassMids ;
248246
249247 if ( $syntransId != 0 ) {
——@@ -252,8 +250,10 @@
253251 " FROM {$dc}_syntrans" .
254252 " JOIN {$dc}_expression ON {$dc}_expression.expression_id = {$dc}_syntrans.expression_id" .
255253 " WHERE {$dc}_syntrans.syntrans_sid = " . $syntransId .
256 - ' AND ' . getLatestTransactionRestriction( "{$dc}_syntrans" ) .
257 - ' AND ' . getLatestTransactionRestriction( "{$dc}_expression" );
 254+ " LIMIT 1 " ;
 255+ // not needed: syntransId is unique
 256+ // ' AND ' . getLatestTransactionRestriction( "{$dc}_syntrans" ) .
 257+ // ' AND ' . getLatestTransactionRestriction( "{$dc}_expression" );
258258 $lang_res = $dbr->query( $sql );
259259 $language_id = $dbr->fetchObject( $lang_res )->language_id;
260260
——@@ -263,17 +263,18 @@
264264 " WHERE language.language_id = $language_id" .
265265 " AND {$dc}_collection_contents.collection_id = 145264" .
266266 " AND language.iso639_3 = {$dc}_collection_contents.internal_member_id" .
267 - ' AND ' . getLatestTransactionRestriction( "{$dc}_collection_contents" );
 267+ ' AND ' . getLatestTransactionRestriction( "{$dc}_collection_contents" ) .
 268+ ' LIMIT 1 ' ;
268269 $lang_res = $dbr->query( $sql );
269270 $language_dm_id = $dbr->fetchObject( $lang_res )->member_mid;
270271
271272 $classMids = array_merge ( $wgDefaultClassMids , array($language_dm_id) ) ;
272273 }
273274
274 - if ( count( $classMids ) > 0 )
275 - $defaultClassRestriction = " OR {$dc}_class_attributes.class_mid IN (" . join( $classMids, ", " ) . ")";
276 - else
277 - $defaultClassRestriction = "";
 275+ $classRestriction = "";
 276+ if ( count( $classMids ) > 0 ) {
 277+ $classRestriction = " OR {$dc}_class_attributes.class_mid IN (" . join( $classMids, ", " ) . ")";
 278+ }
278279
279280 $filteredAttributesRestriction = $this->getFilteredAttributesRestriction( $annotationAttributeId );
280281
——@@ -288,13 +289,16 @@
289290 " AND {$dc}_expression.expression_id = {$dc}_syntrans.expression_id" .
290291 $filteredAttributesRestriction . " ";
291292
292 - $sql .=
293 - " AND ( language_id=$lng " .
294 - ' OR ( ' .
295 - ' language_id=85 ' .
296 - " AND {$dc}_syntrans.defined_meaning_id NOT IN ( SELECT defined_meaning_id FROM {$dc}_syntrans synt, {$dc}_expression exp WHERE exp.expression_id = synt.expression_id AND exp.language_id=$lng ) " .
297 - ' ) ) ' ;
 293+ // fallback is English
 294+ $sql .= " AND ( language_id=$userlang " ;
298295
 296+ if ( $userlang != 85 ) {
 297+ $sql .= ' OR ( ' .
 298+ ' language_id=85 ' .
 299+ " AND NOT EXISTS ( SELECT * FROM {$dc}_syntrans synt2, {$dc}_expression exp2 WHERE synt2.defined_meaning_id = {$dc}_syntrans.defined_meaning_id AND exp2.expression_id = synt2.expression_id AND exp2.language_id=$userlang AND synt2.remove_transaction_id IS NULL LIMIT 1 ) ) " ;
 300+ }
 301+ $sql .= ' ) ' ;
 302+
299303 $sql .=
300304 ' AND ' . getLatestTransactionRestriction( "{$dc}_class_attributes" ) .
301305 ' AND ' . getLatestTransactionRestriction( "{$dc}_expression" ) .
——@@ -305,7 +309,7 @@
306310 " WHERE {$dc}_class_membership.class_member_mid = " . $definedMeaningId .
307311 ' AND ' . getLatestTransactionRestriction( "{$dc}_class_membership" ) .
308312 ' )' .
309 - $defaultClassRestriction .
 313+ $classRestriction .
310314 ')';
311315
312316 $sql .= ') AS filtered GROUP BY attribute_mid';
——@@ -383,7 +387,7 @@
384388 $dc = wdGetDataSetContext();
385389
386390 $dbr = wfGetDB( DB_SLAVE );
387 - $lng = '( SELECT language_id FROM language WHERE wikimedia_key = ' . $dbr->addQuotes( $language ) . ' )';
 391+ $userlang = ' ( SELECT language_id FROM language WHERE wikimedia_key = ' . $dbr->addQuotes( $language ) . ' LIMIT 1 ) ';
388392
389393 // exp.spelling, txt.text_text
390394 $sql = "SELECT member_mid, spelling " .
——@@ -394,18 +398,18 @@
395399 " AND synt.defined_meaning_id = col_contents.member_mid " .
396400 // " AND synt.identical_meaning=1 " .
397401 " AND exp.expression_id = synt.expression_id " .
398 - " AND dm.defined_meaning_id = synt.defined_meaning_id " .
399 - " AND ( " .
400 - " exp.language_id=$lng " .
401 - " OR (" .
402 - " exp.language_id=85 " .
403 - " AND dm.defined_meaning_id NOT IN " .
404 - " ( SELECT defined_meaning_id FROM {$dc}_syntrans synt_bis, {$dc}_expression exp_bis " .
405 - " WHERE exp_bis.expression_id = synt_bis.expression_id AND exp_bis.language_id=$lng " .
406 - " AND synt_bis.remove_transaction_id IS NULL AND exp_bis.remove_transaction_id IS NULL ) " .
407 - " ) " .
408 - " ) " .
409 - " AND " . getLatestTransactionRestriction( "col" ) .
 402+ " AND dm.defined_meaning_id = synt.defined_meaning_id " ;
 403+
 404+ // fallback is English
 405+ $sql .= " AND ( exp.language_id=$userlang " ;
 406+ if ( $userlang != 85 ) {
 407+ $sql .= ' OR ( ' .
 408+ ' language_id=85 ' .
 409+ " AND NOT EXISTS ( SELECT * FROM {$dc}_syntrans synt2, {$dc}_expression exp2 WHERE synt2.defined_meaning_id = synt.defined_meaning_id AND exp2.expression_id = synt2.expression_id AND exp2.language_id=$userlang AND synt2.remove_transaction_id IS NULL LIMIT 1 ) ) " ;
 410+ }
 411+ $sql .= ' ) ' ;
 412+
 413+ $sql .= " AND " . getLatestTransactionRestriction( "col" ) .
410414 " AND " . getLatestTransactionRestriction( "col_contents" ) .
411415 " AND " . getLatestTransactionRestriction( "synt" ) .
412416 " AND " . getLatestTransactionRestriction( "exp" ) .
——@@ -442,25 +446,25 @@
443447 private function getSQLForCollection( $language ) {
444448 $dc = wdGetDataSetContext();
445449 $dbr = wfGetDB( DB_SLAVE );
446 - $lng = '( SELECT language_id FROM language WHERE wikimedia_key = ' . $dbr->addQuotes( $language ) . ' )';
 450+ $userlang = ' ( SELECT language_id FROM language WHERE wikimedia_key = ' . $dbr->addQuotes( $language ) . ' LIMIT 1 ) ';
447451
448452 $sql = "SELECT collection_id, spelling " .
449453 " FROM {$dc}_expression exp, {$dc}_collection col, {$dc}_syntrans synt, {$dc}_defined_meaning dm " .
450454 " WHERE exp.expression_id=synt.expression_id " .
451455 " AND synt.defined_meaning_id=col.collection_mid " .
452 - " AND dm.defined_meaning_id = synt.defined_meaning_id " .
 456+ " AND dm.defined_meaning_id = synt.defined_meaning_id " ;
453457 // " AND synt.identical_meaning=1" .
454 - " AND ( " .
455 - " exp.language_id=$lng " .
456 - " OR (" .
457 - " exp.language_id=85 " .
458 - " AND dm.defined_meaning_id NOT IN " .
459 - " ( SELECT defined_meaning_id FROM {$dc}_syntrans synt_bis, {$dc}_expression exp_bis " .
460 - " WHERE exp_bis.expression_id = synt_bis.expression_id AND exp_bis.language_id=$lng " .
461 - " AND synt_bis.remove_transaction_id IS NULL AND exp_bis.remove_transaction_id IS NULL ) " .
462 - " ) " .
463 - " ) " .
464 - " AND " . getLatestTransactionRestriction( "synt" ) .
 458+
 459+ // fallback is English
 460+ $sql .= " AND ( exp.language_id=$userlang " ;
 461+ if ( $userlang != 85 ) {
 462+ $sql .= ' OR ( ' .
 463+ ' language_id=85 ' .
 464+ " AND NOT EXISTS ( SELECT * FROM {$dc}_syntrans synt2, {$dc}_expression exp2 WHERE synt2.defined_meaning_id = synt.defined_meaning_id AND exp2.expression_id = synt2.expression_id AND exp2.language_id=$userlang AND synt2.remove_transaction_id IS NULL LIMIT 1 ) ) " ;
 465+ }
 466+ $sql .= ' ) ' ;
 467+
 468+ $sql .= " AND " . getLatestTransactionRestriction( "synt" ) .
465469 " AND " . getLatestTransactionRestriction( "exp" ) .
466470 " AND " . getLatestTransactionRestriction( "col" ) .
467471 " AND " . getLatestTransactionRestriction( "dm" );

Status & tagging log