inmysocks (version: awoo) @inmysocks@awoo.space

I need to figure out if hyperswarm is set up to make the windows and android, which don't play well with mdns, work on the local network anyway.

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

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...

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

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.

SQL has been momentarily defeated, somehow.

Or perhaps it beat me considering I have no idea why these queries are finishing very quickly while the others that got the same data took like a minute each the other day.

Either way, I pulled out the data and we can see trends in the data that aren't visible without processing. So SUCCESS!!!

So screw doing complex sql queries, I am going to use python scripts to combine results for anything more complex than using WHERE statements on a single table.

Sql is confusing.

SELECT COUNT(id) FROM Documents WHERE EXTRACT(YEAR FROM date)=2000;

runs in 0.03 seconds

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

runs in about 0.1 second

Every way I have come up with to combine the two takes minutes.

After redesigning the schema to have a bunch of duplicate information in different tables and rebuilding the whole thing over night the queries are taking much less time.
Like half a second instead of minutes.

So that was worth it.

I still don't really approve of this 'sql' thing.

I am being a terrible database person and duplicating data all over the place because these queries are taking way too long.

Who cares if I can have the date for all of these entries in a single place, storage is cheap and I am impatient.

My finely crafted SQL query has been running for about 3 minutes so far.

My previous record for how long a query would take is 50 seconds.

I made the mistake of asking if anyone wanted to help with finding bugs in Bob.

Next time I ask for something like this from people who use my software just punch me, it would be less painful.

Well, that just took the analysis time down from about 30-50 seconds per article to 2-10 seconds per article.

Optimizing loops can make a difference when you are dealing with 50000 inputs.

I continue my battle with SQL for the third day in a row.

But progress is being made!

semi-related note:

The checks in these two if statements

if index % 500:
foo

and

if index % 500 == 0:
foo

are the logical negations of each other, at least in languages where any positive non-zero number evaluates to true.

So remembering that '==0' is important.

This is the first time I have used tabbed terminal windows and it hasn't seemed terrible.

I have an sql shell open in one terminal and the python environment using the sql database in the other.

I see how this could be useful.

I have successfully defeated sql for the night. I am sure that tomorrow it will be back for revenge.

I could make this table have 301 columns instead of being about 1.3 times the size it needs to be.

But then I really don't want to type out the command to create a table with 301 columns.

I am bad at database schema stuff.