MySQL and partitioning tables with millions of rows

While the write up was good, the author makes assumptions about what's happening seemingly without checking. He didn't post the actual query or the original and final query plans.<p>Partitioning is a rather complex solution to his problem. See the limitations at <a href="http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations.html" rel="nofollow">http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitati...</a><p>I can't know for sure since there's not enough information there, but I'd suspect a simple index would have done the trick.<p><pre><code> CREATE INDEX my_index ON Location (device_id, date_added desc) SELECT * FROM Locations USE INDEX (my_index) WHERE device_id = ... ORDER BY date_added DESC LIMIT 6 SELECT * FROM Locations USE INDEX (my_index) WHERE device_id = ... AND date_added BETWEEN ... AND ... ORDER BY date_added DESC </code></pre> I'm assuming that he had the index and it just wasn't getting used, so the index hints are there in case the query planner was somehow missing them.<p>That said, table partitioning is an awesome feature. I've seen it be most useful when you spread the partitions over tiered storage (latest on SSD, archive on spindles), or when you want to drop a whole range of data quickly on a regular basis (like last month's logs).<p>* Edit: Missed something:<p>You may need to specify more hints than I originally thought if you want to force index usage for the order by clause as well. See <a href="http://dev.mysql.com/doc/refman/5.1/en/index-hints.html" rel="nofollow">http://dev.mysql.com/doc/refman/5.1/en/index-hints.html</a>

Be advised that partitioning a MySQL table makes seemingly unrelated but important features (like <i>foreign keys</i> and <i>subqueries</i>) stop working.

Honestly, the first thing I did was add indexes. On device_id, user_id, and date_added.<p>I'm using Linode and I kept getting these I/O notifications...and the site would load extremely slow at times.<p>After looking at the log of my Rails app, I saw that some requests were taking &#62; 20 seconds. I determined that this was some kind of blocking at the database.<p>I connected to MySQL and ran something simple...<p>select count(*) from location;<p>It took a long time...I turned profiling on and saw that it was taking a really long time in "table lock".<p>I assumed (which probably wasn't a good idea) that it was going through and counting all the rows.<p>I didn't really know what to make of this..because I didn't think that getting the count would take so long.<p>I embarked on reading about partitioning, which may have been a solution for a problem that didn't actually exist (based on the feedback here). I attempted to partition (on what I put in the article), and everything seemed much snappier after that. If the indexes should have solved the problem (given that they were correct), I don't know why the location queries were taking so long.<p>Anyway, I still have lots to learn on the database front, and maybe the fact that my VM had 7MB free of memory was causing weird things to happen, I'm not sure.<p>Thanks for all the feedback and I have definitely learned a lot in this thread.<p>kogir:<p>I'm using MyISAM (which, in retrospect, seems stupid), so I don't even have foreign key constraints (InnoDB only, I believe).

I wish he would do an explain on the original slow query. 12 million rows is not that much, this looks a lot like he was just missing an index on device_id.

had this guy an index on "device_id" and "date_added", there is no way such a query would take 3-4 seconds.