slow website

by themba, Sunday, February 17, 2019, 11:45 (1887 days ago) @ jtojnar

Hi,

I did a small experiment, manually rewriting my database to contain a categorytags table that joins the tags to the categories.

However, I found that this does not enable faster queries. Apparently mysql does execute the joins in the optimal order. Joining my 43 sources with the 11 tags I have in my current database is very fast independent on the way the data is linked... That makes sense now.

I already created an index on the "unread" column of the items table some time ago hoping to speed things up. I had, so far, assumed that counting unread items would not be faster/slower depending on the number of unread items (i.e. my thought was "if you have to check them all it's not faster if you count the row once or zero times..."). I had one feed with many unread items that I wasn't following much anymore (y-combinator). It had something like 60000 unread items in it. I marked them all as read and now my queries are much faster again....

Thinking again this makes sense: Filtering on a table column is cheap even if the table is large. Especially if you have an index on the column in question. Joining tables is more expensive but with the filter already executed there are now a lot less items to join. Also the other part of the join (tags and sources) are very small making the join not too bad performance wise (in my case each source has one tag so this join does not increase the table size at all). Probably the most part is spent counting and aggregating the unread counts. For this it needs to loop over the result of the join. This is probably not making use of any kind of index. I'm not sure that there exist index types that can solve this...

Maybe a stats table like suggested in here could be an elegant solution: https://stackoverflow.com/questions/19267507/how-to-optimize-count-performance-on-innodb-by-using-index

You might also check if it helps to loop over tags in php and then count the matching items for each tag in a separate sql statement. That way sql might be able to make better use of the index on source (assuming you have an index on that).