Add `Slow Query` Issue from GitHub

1 files changed, 46 insertions, 0 deletions

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 ;)