As an anonymous user, you can only add new data. If you would like to also modify existing data, please create an account and indicate your languages on your user page.

User:Kipcool/dev/sql query time

From OmegaWiki
Jump to: navigation, search

no query cache[edit]

does not work:

  • show variables like '%cache%';
  • RESET QUERY CACHE ;
  • SET GLOBAL query_cache_type = 0 ;
  • SET SESSION query_cache_type = OFF ;
  • SET GLOBAL query_cache_size = 0 ;

language names[edit]

Possibility 1[edit]

SELECT language_id, language_name
FROM language_names
WHERE name_language_id = 86
OR ( name_language_id = 85
AND language_id NOT IN ( SELECT language_id FROM language_names WHERE name_language_id = 86 ) ) ;

Possibility 2[edit]

SELECT language.language_id AS row_id, language_names.language_name AS language_name
FROM language
JOIN language_names ON language.language_id = language_names.language_id
WHERE language_names.name_language_id = 86
OR ( language_names.name_language_id = 85
AND language.language_id NOT IN ( SELECT language_id FROM language_names WHERE language_names.name_language_id = 86 ) ) ;

Possibility 3 - join, coalesce[edit]

SELECT language.language_id AS row_id,COALESCE(ln1.language_name,ln2.language_name) AS language_name
FROM language
LEFT JOIN language_names AS ln1 ON language.language_id = ln1.language_id AND ln1.name_language_id = 86
JOIN language_names AS ln2 ON language.language_id = ln2.language_id AND ln2.name_language_id = 85 ;

getSQLToSelectPossibleAttributes[edit]

Examples with URL attributes.

Possibility 1[edit]

SELECT object_id, attribute_mid, MAX(spelling) as spelling FROM (
SELECT object_id, attribute_mid, spelling
FROM uw_bootstrapped_defined_meanings, uw_class_attributes, uw_syntrans, uw_expression
WHERE uw_bootstrapped_defined_meanings.name = "SynTrans"
AND uw_bootstrapped_defined_meanings.defined_meaning_id = uw_class_attributes.level_mid
AND uw_class_attributes.attribute_type = "URL"
AND uw_syntrans.defined_meaning_id = uw_class_attributes.attribute_mid
AND uw_expression.expression_id = uw_syntrans.expression_id
AND ( language_id = 86
OR ( language_id=85
AND NOT EXISTS ( SELECT * FROM uw_syntrans synt2, uw_expression exp2
 WHERE synt2.defined_meaning_id = uw_syntrans.defined_meaning_id
 AND exp2.expression_id = synt2.expression_id
 AND exp2.language_id= 86
 AND synt2.remove_transaction_id IS NULL LIMIT 1 ) ) )
AND uw_class_attributes.remove_transaction_id IS NULL
AND uw_expression.remove_transaction_id IS NULL
AND uw_syntrans.remove_transaction_id IS NULL
AND (uw_class_attributes.class_mid IN (
 SELECT class_mid
 FROM uw_class_membership
 WHERE uw_class_membership.class_member_mid = 1310091
 AND uw_class_membership.remove_transaction_id IS NULL
 )
OR uw_class_attributes.class_mid IN ( 402295 )
)
) AS filtered GROUP BY object_id ;

Possibility 2: coalesce[edit]

SELECT * FROM (SELECT 
COALESCE(actual.object_id, fallback.object_id) as object_id,
COALESCE(actual.attribute_mid, fallback.attribute_mid) as attribute_mid,
COALESCE(actual.spelling, fallback.spelling) as spelling
FROM
(
SELECT object_id, attribute_mid, MAX(spelling) as spelling FROM (
SELECT object_id, attribute_mid, spelling
FROM uw_bootstrapped_defined_meanings, uw_class_attributes, uw_syntrans, uw_expression
WHERE uw_bootstrapped_defined_meanings.name = "SynTrans"
AND uw_bootstrapped_defined_meanings.defined_meaning_id = uw_class_attributes.level_mid
AND uw_class_attributes.attribute_type = "URL"
AND uw_syntrans.defined_meaning_id = uw_class_attributes.attribute_mid
AND uw_expression.expression_id = uw_syntrans.expression_id
AND language_id = 85
AND uw_class_attributes.remove_transaction_id IS NULL
AND uw_expression.remove_transaction_id IS NULL
AND uw_syntrans.remove_transaction_id IS NULL
AND (uw_class_attributes.class_mid IN (
 SELECT class_mid
 FROM uw_class_membership
 WHERE uw_class_membership.class_member_mid = 1310091
 AND uw_class_membership.remove_transaction_id IS NULL
 )
OR uw_class_attributes.class_mid IN ( 402295 )
)
) AS filtered GROUP BY object_id
) AS fallback
LEFT JOIN
(
SELECT object_id, attribute_mid, MAX(spelling) as spelling FROM (
SELECT object_id, attribute_mid, spelling
FROM uw_bootstrapped_defined_meanings, uw_class_attributes, uw_syntrans, uw_expression
WHERE uw_bootstrapped_defined_meanings.name = "SynTrans"
AND uw_bootstrapped_defined_meanings.defined_meaning_id = uw_class_attributes.level_mid
AND uw_class_attributes.attribute_type = "URL"
AND uw_syntrans.defined_meaning_id = uw_class_attributes.attribute_mid
AND uw_expression.expression_id = uw_syntrans.expression_id
AND language_id = 86
AND uw_class_attributes.remove_transaction_id IS NULL
AND uw_expression.remove_transaction_id IS NULL
AND uw_syntrans.remove_transaction_id IS NULL
AND (uw_class_attributes.class_mid IN (
 SELECT class_mid
 FROM uw_class_membership
 WHERE uw_class_membership.class_member_mid = 1310091
 AND uw_class_membership.remove_transaction_id IS NULL
 )
OR uw_class_attributes.class_mid IN ( 402295 )
)
) AS filtered GROUP BY object_id
) AS actual
ON actual.object_id = fallback.object_id
) as coalesced ;

Possibility 3 : with coalesce and join only for languages[edit]

SELECT object_id, attribute_mid, COALESCE( exp_lng.spelling, exp_en.spelling, attribute_mid ) AS spelling
FROM uw_bootstrapped_defined_meanings, uw_class_attributes
LEFT JOIN ( uw_syntrans synt_lng, uw_expression exp_lng )
ON ( uw_class_attributes.attribute_mid = synt_lng.defined_meaning_id
AND synt_lng.remove_transaction_id IS NULL
AND exp_lng.expression_id = synt_lng.expression_id
AND exp_lng.language_id= 97 )
LEFT JOIN ( uw_syntrans synt_en, uw_expression exp_en )
ON ( uw_class_attributes.attribute_mid = synt_en.defined_meaning_id
AND synt_en.remove_transaction_id IS NULL
AND exp_en.expression_id = synt_en.expression_id
AND exp_en.language_id= 92 )
WHERE uw_bootstrapped_defined_meanings.name = "SynTrans"
AND uw_bootstrapped_defined_meanings.defined_meaning_id = uw_class_attributes.level_mid
AND uw_class_attributes.attribute_type = "URL"
AND uw_class_attributes.remove_transaction_id IS NULL
AND (uw_class_attributes.class_mid IN (
SELECT class_mid
FROM uw_class_membership
WHERE uw_class_membership.class_member_mid = 1310091
AND uw_class_membership.remove_transaction_id IS NULL
)
OR uw_class_attributes.class_mid IN ( 402295 )
) GROUP BY object_id ;