Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

MySQL and partitioning tables with millions of rows

chrismoos.com

Post reply on HN
Print view View original

1–10 of 23 posts

Re: MySQL and partitioning tables with millions of rows

#4

Post by chrismoos »

dr_strangelove wrote: post #3

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

I had indexes on id, user_id, device_id, and date_added, but maybe I was doing something else wrong. I'm not a database expert :(

Re: MySQL and partitioning tables with millions of rows

#5

Post by jbellis »

chrismoos wrote: post #4
dr_strangelove wrote: post #3

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

I had indexes on id, user_id, device_id, and date_added, but maybe I was doing something else wrong. I'm not a database expert :(

What SQL was running, and what was the query plan?

ActiveRecord is notorious for generating terrible, terrible SQL.

Edit: What I would expect to see would be index scan on device id, then sort + limit. So the important factor would be rows per device, not total rows.

Re: MySQL and partitioning tables with millions of rows

#6

Post by chrismoos »

jbellis wrote: post #5
chrismoos wrote: post #4

Earlier quoted context omitted.

I had indexes on id, user_id, device_id, and date_added, but maybe I was doing something else wrong. I'm not a database expert :(

What SQL was running, and what was the query plan? ActiveRecord is notorious for generating terrible, terrible SQL. Edit: What I would expect to see would be index scan on device id, then sort + limit. So the important factor would be rows per device, not total rows.

ActiveRecord...

The SQL was pretty simple I believe..select * from location where device_id = ? order by date_added desc limit 6...something like that.

Edit: Also, I don't know how much it matters, but MySQL probably only has ~256mb memory available to it (its hosted on a Xen box).

Re: MySQL and partitioning tables with millions of rows

#7

Post by dr_strangelove »

chrismoos wrote: post #4
dr_strangelove wrote: post #3

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

I had indexes on id, user_id, device_id, and date_added, but maybe I was doing something else wrong. I'm not a database expert :(

if you had an index on device_id there shouldn't be a difference in performance between the original table and the partioned one. the index is used to reduce the rows that need to be scanned so there wouldn't be a full table scan. btw, why do you use a table lock?

Re: MySQL and partitioning tables with millions of rows

#8

Post by tlack »

chrismoos wrote: post #6
jbellis wrote: post #5

Earlier quoted context omitted.

What SQL was running, and what was the query plan? ActiveRecord is notorious for generating terrible, terrible SQL. Edit: What I would expect to see would be index scan on device id, then sort + limit. So the important factor would be rows per device, not total rows.

ActiveRecord... The SQL was pretty simple I believe..select * from location where device_id = ? order by date_added desc limit 6...something like that. Edit: Also, I don't know how much it matters, but MySQL probably only has ~256mb memory available to it (its hosted on a Xen box).

Queries like that should be very, very fast even with low amounts of ram. Good article, but consider using EXPLAIN a few times first before embarking on such adventures. :)

Re: MySQL and partitioning tables with millions of rows

#10

Post by kogir »

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.

Partitioning is a rather complex solution to his problem. See the limitations at http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitati...

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.

  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
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.

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).

* Edit: Missed something:

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 http://dev.mysql.com/doc/refman/5.1/en/index-hints.html

Post reply on HN