Query taking too long

[scripting]

The other day, this query started taking 27 seconds to run on my account, which is longer than the timeout.

select * from items where flDeleted=false and feedurl in (select feedUrl from subscriptions where listName='davewiner') order by pubDate desc limit 175;

It just started happening, and at least one other user is seeing it. It has rendered FeedLand basically useless to me.

Now I'm in over my head as far as MySQL is concerned. Not sure how to debug this, or how to fix the problem. I'm to grind at it but if anyone has an idea how to approach this, help would be most welcome.

[scripting]

I tried the part in parens, and it took 0.11 sec and returned 594 rows.

(select feedUrl from subscriptions where listName='davewiner')

[scripting]

Since this performance problem just started, I looked at the server, and found this. I don't know what this means, but it doesn't look very good.

[scripting]

Is there a missing index?

https://github.com/scripting/feedland/blob/main/database/database.md

[scotthansonde]

@scripting I notice that the flDeleted field in the items table is not indexed. On my instance I tried adding an index for flDeleted:

ALTER TABLE`feedland`.`items` ADD INDEX (`flDeleted`);

That reduced the time of the query from 3.4 seconds to 0.006 seconds

[scripting]

@scotthansonde -- thank you.

it's so funny, you posted this fix as I was announcing the open source release of The FeedLand Platform.

so that we could get help with this. ;-)

i'll report on how it affects this issue.

[scripting]

@scotthansonde -- before doing anything on the feedland.org database, I want to see how this will be documented on the database.md page.

At the end we create three indexes:

create index itemFeed on items(feedUrl);
create index itemPubDate on items(pubDate);
create index subscriptionsListname on subscriptions(listname);

I propose to add:

create index itemDeleted on items(flDeleted);

If this is correct, you can just Like this comment.

[iolaire]

Hey Dave, a partial suggestion since I'm most familiar with Microsoft SQL Server. Using an EXIST statement over an IN statement is usually much faster. According to this post it is similar in MySQL https://www.aurigait.com/blog/mysql-in-vs-exist/, therefore I believe if you change your code to something like this it should be much faster:
select * from items as i where flDeleted=false and EXISTS (select * from subscriptions as s where s.listName='davewiner' and i.feedUrl = s.feedUrl ) order by pubDate desc limit 175;

[iolaire]

Also, in SQL server we do something like this in the exists statement (select top 1 1 from subscriptions as s where s.listName='davewiner' and i.feedUrl = s.feedUrl ) so it doesn't return all the results, it just ends once the first result is hit, so you might also try something like this: select * from items as i where flDeleted=false and EXISTS (select 1 from subscriptions as s where s.listName='davewiner' and i.feedUrl = s.feedUrl LIMIT 1 ) order by pubDate desc limit 175; and use EXPLAIN to see if that improves things future.

[scripting]

@scotthansonde -- it didn't make the query faster, actually i had tried it without the flDeleted part of the query as follows:

select * from items where feedurl in (select feedUrl from subscriptions where listName='davewiner') order by pubDate desc limit 175;

And it took 29 seconds.

So I'm getting different results here (and I should have read your initial comment more carefully).

[scripting]

I brought the problem to ChatGPT because that's how we do it these days with some interesting results.

http://scripting.com/publicfolder/chatgpt/2023/04/24/mysql-exists-statement.html

I thought this bit was interesting:

Add an index: You can add an index on the feedurl column in the items table and the feedUrl and listName columns in the subscriptions table. This can speed up the search and join operations in the query.

There already is an index on feedurl in the items table, and an index on listName in subscriptions, but there is no index on feedurl in subscriptions.

create index subscriptionsFeedurl on subscriptions(feedUrl);

Result: No difference in performance.

[scripting]

Here's an interesting piece of data.

Somehow I lost my categories in the Settings command, and that's why I'm getting the huge timeout.

Once I reinstated my categories, things went back to how they were, performing as before.

https://feedland.org/?river=true&screenname=davewiner&catname=all

So this is only affecting people who have no categories, ie newbies.

[iolaire]

I've wanted to suggest that doing all those join on text columns are way less efficient than on an integer-based AUTO_INCREMENT ID column from each table. So here is the ChatGTP explanation:

in mysql is it faster to join on an integer column or a text column
In general, joining on an integer column will be faster than joining on a text column in MySQL. This is because integer comparisons are simpler and faster for the database to perform than text comparisons.

When you join on an integer column, the database can use a simple numeric comparison to match the values in the join. This comparison is straightforward and efficient, allowing the database to quickly identify the matching rows.

On the other hand, when you join on a text column, the database needs to perform a string comparison. String comparisons are more complex than integer comparisons, and they can be slower to execute. Additionally, if the text column is indexed, the index size will be larger and more memory-intensive than an index on an integer column, which can also slow down the join performance.

That being said, there may be cases where joining on a text column is necessary or more appropriate. For example, if the values in the column are not easily represented as integers (e.g. names, addresses, or other non-numeric identifiers), joining on a text column may be necessary. Additionally, in some cases, using a text column for a join may allow for more flexibility in how the join is performed.

[scripting]

I'm going to take a fresh look at this tomorrow. To prepare for it, I went through the problem with ChatGPT.

http://scripting.com/publicfolder/chatgpt/2023/04/24/mysql-database-assistance.html

[mterenzio]

There are a number of long term changes to the modeling that I think would improve performance and ease of maintenance. Some have been mentioned like indexes, serial columns but also unique constraints and foreign keys, but these would also likely require code changes and query rewrites.

That said, in my experience with queries that suddenly start performing poorly, a surprising amount of time it also reflects not being able to fit the data set into memory. In other words, the table outgrew the available memory and is needing to go to disk.

Without more information, I can't say if that's the case, but thought it was worth a quick try, especially if you are on a cloud based system where adding RAM is just a few clicks.

Or before doing that, while the query is running, run the free command and note the memory and swap usage. I believe I recall you are on linux.

[scripting]

Thanks @mterenzio -- yes this is running on Ubuntu, hosted at Digital Ocean.