Scaling MySQL up

Yesterday I ran uptime on my new dedicated server and saw that the load was around 0.5 constantly. I expected it to be 0.00! I debugged and saw that MySQL took a lot of resources.

Todoist items table is approaching 170.000 rows and I thought this was a problem, but come on, 170.000 rows should be manageable.

Anyway, to scale a database up one can begin with index keys. Without index keys the database has to look through all the rows in a table [i.e. O(n), where n is the number of rows]. With index keys it only needs to look through a binary-tree structure [i.e. the worst-case time would be O(log(n))).

To my horror today or 7 days queries didn't use any index keys, i.e. MySQL had to look through 170.000 rows every time an user used the calendar functionality of Todoist. Putting an index key on user_id reduces the lookup to around 1500 rows. Putting an index key on user_id, in_history reduces the lookup to around 100 rows!

The load is currently around 0.02.

I also found MySQL's EXPLAIN statement very useful. With it one can analyze queries and see what keys they use.