MySQL Performance

by bekoehn, Thursday, October 03, 2013, 20:28 (3995 days ago) @ bekoehn

OK, I think I have this figured out.

When update runs, it apparently fetches each feed and compares the items in the feed to the items in the 'items' table in the database. Which is fine, in concept, except that it is doing this one item at a time:

SELECT COUNT(*) AS amount FROM items WHERE uid='http://www.bbc.co.uk/news/business-24369542'
SELECT COUNT(*) AS amount FROM items WHERE uid='http://www.bbc.co.uk/news/in-pictures-24329416'
...

This is extremely inefficient. I have over 10,000 rows in the items table; selfoss is running over 10,000 queries for every update, when almost every update simply returns a 1.

You could increase speed my an order of magnitude by querying n items at a time (where n is the number of items that can be put into an IN clause):

select uid from items where uid in ('http://www.bbc.co.uk/news/business-24369542', 'http://www.bbc.co.uk/news/in-pictures-24329416', ...)

Put the results into a set. If any item from the feed isn't in the set you know you need to fetch that item.

The API for the Items DAO needs to change, as does the query it runs. But it should be massively faster. I'll see what I can do.