3 ms·

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

chrismoos 17y ago
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 :(
- jbellis 17y ago
  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.
  - chrismoos 17y ago
    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).
    - tlack 17y ago
      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. :)
      - tom_pinckney 17y ago
        Definitely want to second the advice to use EXPLAIN. MySQL has a lot of very specific limitations about when it will and will not use the available indices. It also matters how you created the indices (one index on multiple columns vs multiple indices on individual columns). For example, if you created a single index with the columns (date_added, device_id) MySQL would not be able to use the index since device_id is the second part of the index, not the first and thus not available for use in the WHERE clause. See http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html for more limitations.
- dr_strangelove 17y ago
  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?
- deleted 17y ago
  [deleted]
- kogir 17y ago
  Were those single indexes like CREATE INDEX index_1 ON Locations(id) CREATE INDEX index_2 ON Locations(user_id) ...? If you have multiple single column indexes it won't help you here. The mysql query planner probably correctly guessed the table scan was a better option than hash joining the results of two seeks given the machine's limited memory. It also probably underestimated the cost of disk IO on a virtual machine though.
- rythie 17y ago
  MySQL can only use one index per query. If you need it be on two or more columns you need a composite index with both columns in, populated from the left. If MySQL finds a column in the index you not keying off (WHERE/SORT etc.) then it will stop processing the index at that point. The key_len column in the EXPLAIN shows you how far it got through the index, based on the size of the columns. In your case it says 4 which if device id is of type INT it just read one column in the index (though there is probably only one column in that index)

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 :(

jbellis 17y ago
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.
- chrismoos 17y ago
  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).
  - tlack 17y ago
    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. :)
    - tom_pinckney 17y ago
      Definitely want to second the advice to use EXPLAIN. MySQL has a lot of very specific limitations about when it will and will not use the available indices. It also matters how you created the indices (one index on multiple columns vs multiple indices on individual columns). For example, if you created a single index with the columns (date_added, device_id) MySQL would not be able to use the index since device_id is the second part of the index, not the first and thus not available for use in the WHERE clause. See http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html for more limitations.
dr_strangelove 17y ago
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?
deleted 17y ago
[deleted]
kogir 17y ago
Were those single indexes like CREATE INDEX index_1 ON Locations(id) CREATE INDEX index_2 ON Locations(user_id) ...? If you have multiple single column indexes it won't help you here. The mysql query planner probably correctly guessed the table scan was a better option than hash joining the results of two seeks given the machine's limited memory. It also probably underestimated the cost of disk IO on a virtual machine though.
rythie 17y ago
MySQL can only use one index per query. If you need it be on two or more columns you need a composite index with both columns in, populated from the left. If MySQL finds a column in the index you not keying off (WHERE/SORT etc.) then it will stop processing the index at that point. The key_len column in the EXPLAIN shows you how far it got through the index, based on the size of the columns. In your case it says 4 which if device id is of type INT it just read one column in the index (though there is probably only one column in that index)