Tavern Performance Optimizations

[Almad]

After experimenting with EXPLAIN, there is no working around the fact that any GROUP BY id on Tavern Tables causes MySQL to go on and use temporary table for sorting. To avoid it:

• [ ] Drop TavernAccess and use privileges only from TavernTableVisitor that can be LEFT OUTER JOINed. Store amount of unreads aggreggate there.
• [ ] Properly update aggregates from the tavern table on the tavern tables
• [ ] Write manage.py command for fixing the "number of unread" cache, consider regular cron
• [ ] Use TavernTableVisitor instead of TavernBookmark and burn TavernBookmark to the ground
  • [ ] Migration script for the bookmarks to visitors first, though

[Almad]

There is, of course, discrepancy between how TavernBookmark and TavernTableVisitor data is maintained:

mysql> select count(oblibenost), oblibenost from putyka_uzivatele group by oblibenost; 
+-------------------+------------+
| count(oblibenost) | oblibenost |
+-------------------+------------+
|             13011 |          1 |
|             52596 |          0 |
|              8559 |         -1 |
|                 1 |       4584 |
|                 1 |       6193 |
|                 1 |       5561 |
|                 1 |      15394 |
+-------------------+------------+
7 rows in set (0.06 sec)

mysql> select count(*) from putyka_book;
+----------+
| count(*) |
+----------+
|    13251 |
+----------+
1 row in set (0.00 sec)

mysql>