From 7ce421df8109ef47ef6d300c135f3b3539d30143 Mon Sep 17 00:00:00 2001
From: Frederick Muriuki Muriithi
Date: Wed, 23 Mar 2022 09:51:40 +0300
Subject: Add `Slow Query` Issue from GitHub

---
 issues/slow-query.gmi | 46 ++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 46 insertions(+)
 create mode 100644 issues/slow-query.gmi

(limited to 'issues')

diff --git a/issues/slow-query.gmi b/issues/slow-query.gmi
new file mode 100644
index 0000000..c169334
--- /dev/null
+++ b/issues/slow-query.gmi
@@ -0,0 +1,46 @@
+# Slow query
+
+## Tags
+
+* assigned: Uditgulati
+* priority: high
+* status: stalled
+* keywords: slow query, from github
+
+## Description
+
+=> https://github.com/genenetwork/genenetwork2/issues/419 From GitHub
+
+These queries take 3 minutes and are pretty insane!
+
+```
+ Query_time: 187.220555  Lock_time: 0.075028  Rows_sent: 71  Rows_examined: 21265516
+ Query_time: 188.479018  Lock_time: 0.204036  Rows_sent: 71  Rows_examined: 21202414
+```
+
+```
+(SELECT 0, PublishXRef.Id, PublishFreeze.createtime as thistable, Publication.PubMed_ID as Publication_PubMed_ID, Phenotype.Post_publication_description as Phe
+notype_Name FROM  PublishFreeze, Publication, PublishXRef, Phenotype WHERE PublishXRef.InbredSetId = 1 and  ((Phenotype.Post_publication_description REGEXP "[[
+:<:]]Gene.refGene[[:>:]]" OR Phenotype.Pre_publication_description REGEXP "[[:<:]]Gene.refGene[[:>:]]" OR Phenotype.Pre_publication_abbreviation REGEXP "[[:<:]
+]Gene.refGene[[:>:]]" OR Phenotype.Post_publication_abbreviation REGEXP "[[:<:]]Gene.refGene[[:>:]]" OR Phenotype.Lab_code REGEXP "[[:<:]]Gene.refGene[[:>:]]"
+OR Publication.PubMed_ID REGEXP "[[:<:]]Gene.refGene[[:>:]]" OR Publication.Abstract REGEXP "[[:<:]]Gene.refGene[[:>:]]" OR Publication.Title REGEXP "[[:<:]]Ge
+ne.refGene[[:>:]]" OR Publication.Authors REGEXP "[[:<:]]Gene.refGene[[:>:]]" OR PublishXRef.Id REGEXP "[[:<:]]Gene.refGene[[:>:]]"))  and PublishXRef.Phenotyp
+eId = Phenotype.Id and PublishXRef.PublicationId = Publication.Id and PublishFreeze.Id = 1)  UNION (SELECT 0, PublishXRef.Id, PublishFreeze.createtime as thist
+able, Publication.PubMed_ID as Publication_PubMed_ID, Phenotype.Post_publication_description as Phenotype_Name FROM  PublishFreeze, Publication, PublishXRef, P
+henotype WHERE PublishXRef.InbredSetId = 1 and  ((Phenotype.Post_publication_description REGEXP "[[:<:]]PIP5K1B[[:>:]]" OR Phenotype.Pre_publication_descriptio
+n REGEXP "[[:<:]]PIP5K1B[[:>:]]" OR Phenotype.Pre_publication_abbreviation REGEXP "[[:<:]]PIP5K1B[[:>:]]" OR Phenotype.Post_publication_abbreviation REGEXP "[[
+:<:]]PIP5K1B[[:>:]]" OR Phenotype.Lab_code REGEXP "[[:<:]]PIP5K1B[[:>:]]" OR Publication.PubMed_ID REGEXP "[[:<:]]PIP5K1B[[:>:]]" OR Publication.Abstract REGEX
+P "[[:<:]]PIP5K1B[[:>:]]" OR Publication.Title REGEXP "[[:<:]]PIP5K1B[[:>:]]" OR Publication.Authors REGEXP "[[:<:]]PIP5K1B[[:>:]]" OR PublishXRef.Id REGEXP "[
+[:<:]]PIP5K1B[[:>:]]"))  and PublishXRef.PhenotypeId = Phenotype.Id and PublishXRef.PublicationId = Publication.Id and PublishFreeze.Id = 1)  UNION (SELECT 0,
+PublishXRef.Id, PublishFreeze.createtime as thistable, Publication.PubMed_ID as Publication_PubMed_ID, Phenotype.Post_publication_description as Phenotype_Name
+ FROM  PublishFreeze, Publication, PublishXRef, Phenotype WHERE PublishXRef.InbredSetId = 1 and  ((Phenotype.Post_publication_description REGEXP "[[:<:]]PIP5K1
+B[[:>:]]" OR Phenotype.Pre_publication_description REGEXP "[[:<:]]PIP5K1B[[:>:]]" OR Phenotype.Pre_publication_abbreviation REGEXP "[[:<:]]PIP5K1B[[:>:]]" OR P
+henotype.Post_publication_abbreviation REGEXP "[[:<:]]PIP5K1B[[:>:]]" OR Phenotype.Lab_code REGEXP "[[:<:]]PIP5K1B[[:>:]]" OR Publication.PubMed_ID REGEXP "[[:
+<:]]PIP5K1B[[:>:]]" OR Publication.Abstract REGEXP "[[:<:]]PIP5K1B[[:>:]]" OR Publication.Title REGEXP "[[:<:]]PIP5K1B[[:>:]]" OR Publication.Authors REGEXP "[
+[:<:]]PIP5K1B[[:>:]]" OR PublishXRef.Id REGEXP "[[:<:]]PIP5K1B[[:>:]]"))  and PublishXRef.PhenotypeId = Phenotype.Id and PublishXRef.PublicationId = Publicatio
+n.Id and PublishFreeze.Id = 1)  UNION (SELECT 0, PublishXRef.Id, PublishFreeze.createtime as thistable, Publication.PubMed_ID as Publication_PubMed_ID, Phenoty
+pe.Post_publication_description as Phenotype_Name FROM  PublishFreeze, Publication, PublishXRef, Phenotype WHERE PublishXRef.InbredSetId = 1 and  ((Phenotype.P
+ost_publication_description REGEXP "[[:<:]]PIP5K1B[[:>:]]" OR
+```
+
+and just keeps going on. Amazing MariaDB actually manages to do them ;)
-- 
cgit v1.2.3