• 17 August 2016Querying the database. Bleep, bloop.

    Yes I am alive! I've been somewhat busy since January but I've been looking into the website code now for about a week or so. Some database queries are really slow and I always wanted to fix those. Browsing the Study pages ought to work much better.

    I'm reading "High Performance MySQL" and looking into ways to improve the database schema. Some things that stood out is that I used multiple column keys as primary keys in a way that is inefficient and prevents so useful techniques, such as "Join Decomposition". Another thing is called "de normalization", and I should probably have stored the count of votes / reports along with the stories in order to avoid the "Temporary table" problem. I realized also that storing stories as "TEXT" instead of "VARCHAR" causes the temporary tables to be on disk instead of in memory!

    All kinds of fun stuff. It'll probably take a few weeks and then I'll have to take down the site for an hour or so, make a backup first, then do all kind of wizardry to make major changes to the schema and queries. I should probably do this in different phases because it's quite tricky.