Honestly, the first thing I did was add indexes. On device_id, user_id, and date_added. I'm using Linode and I kept getting these I/O notifications...and the site would load extremely slow at times. After looking at the log of my Rails app, I saw that some requests were taking > 20 seconds. I determined that this was some kind of blocking at the database. I connected to MySQL and ran something simple... select count(…
MySQL uses one key per query, so having an index on (device_id) and an index on (date_added) individually will do no (or very little) good for the queries you listed.
2. MyISAM uses table-level locking. This will block all reads (selects) while writes (insert, update, delete) are happening. It does not prevent multiple selects from happening at the same time. If you have a write-heavy table that you are also reading from you need to be using InnoDB.
3. SELECT COUNT(*) FROM location should be instantaneous in MyISAM . It will be slow(er) in InnoDB. If this query was blocking it's because something was trying to write to the table.
4. Foreign key constraints have nothing to do with anything here. That's one advantage of InnoDB, although most web apps I know don't use them, but not one that would help you. Rather, what will help you is the table-level vs. row-level for MyISAM vs. InnoDB.
This allows selects to happen to unaffected rows while writes are happening.