In todays 'WAT?' SQL story, this query:

SELECT DISTINCT EXTRACT(YEAR FROM date) AS Year FROM Documents ORDER BY Year;

takes about half a second and returns a list of years.

This query:

SELECT (SELECT Documents.publication FROM Documents WHERE id=WordFrequencies.doc) AS publication,(SELECT Documents.title FROM Documents WHERE id=WordFrequencies.doc) AS headline,(SELECT Documents.sentiment FROM Documents WHERE id=WordFrequencies.doc) AS articlesentiment,doc,word,count,(SELECT Words.sentiment FROM Words WHERE Words.word=WordFrequencies.word) AS wordsentiment,date FROM WordFrequencies WHERE doc IN (SELECT doc FROM Sentences WHERE YEAR(date) IN (2000, 2003, 2004, 2005,2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017) AND raw REGEXP 'trump') AND partofspeech='ADJ';

takes a bit less than 15 seconds, the list of years is the output from the first query.

But this query never seems to end:

SELECT (SELECT Documents.publication FROM Documents WHERE id=WordFrequencies.doc) AS publication,(SELECT Documents.title FROM Documents WHERE id=WordFrequencies.doc) AS headline,(SELECT Documents.sentiment FROM Documents WHERE id=WordFrequencies.doc) AS articlesentiment,doc,word,count,(SELECT Words.sentiment FROM Words WHERE Words.word=WordFrequencies.word) AS wordsentiment,date FROM WordFrequencies WHERE doc IN (SELECT doc FROM Sentences WHERE YEAR(date) IN (SELECT DISTINCT EXTRACT(YEAR FROM date) AS Year FROM Documents ORDER BY Year) AND raw REGEXP 'trump') AND partofspeech='ADJ';

I suspect that there is some bit of optimisation that I am unknowingly expecting mysql to have that it doesn't have.

It is easy enough to have a simple python script to do the first query and then insert the result into the second one, but I am very confused about why it is necessary.

I suspect that the sub-query in a sub-query may be doing it.

of course, now that I look at it, I could just leave out the date selection in the subquery because at the moment I am checking all of the years represented anyway so I don't need to filter them...

Don't worry everyone, I am a professional....

@inmysocks is there a reason you're not using JOINs at all?

@zatnosk mainly because I am not very good with SQL.

@inmysocks Try this: https://gist.github.com/Zatnosk/e4e9924b4f283ec79d0847878d46eff6