60 million entries, select entries from a certain month. How to optimize database?

by

To get entries in a particular month, for a particular year, faster – you will need to index the time column:

CREATE INDEX idx_time ON ENTRIES(time) USING BTREE;

Additionally, use:

SELECT e.* 
  FROM ENTRIES e
 WHERE e.time BETWEEN '2010-04-01' AND DATE_SUB('2010-05-01' INTERVAL 1 SECOND)

…because BETWEEN is inclusive, so you’d get anything dated “2010-05-01 00:00:00” with the query you posted.

I’d also like to select data from certain month from a given DataSourceID

You can either add a separate index for the datasourceid column:

CREATE INDEX idx_time ON ENTRIES(datasourceid) USING BTREE;

…or setup a covering index to include both columns:

CREATE INDEX idx_time ON ENTRIES(time, datasourceid) USING BTREE;

A covering index requires that the leftmost columns have to be used in the query for the index to be used. In this example, having time first will work for both situations you mentioned — datasourceid doesn’t have to be used for the index to be of use. But, you have to test your queries by viewing the EXPLAIN output to really know what works best for your data & the queries being performed on that data.

That said, indexes will slow down INSERT, UPDATE and DELETE statements. And an index doesn’t provide a lot of value if the column data is has few distinct values – IE: a boolean column is a bad choice to index, because the cardinality is low.

More Related Contents:

  1. How to do the Recursive SELECT query in MySQL?
  2. Subqueries with EXISTS vs IN – MySQL
  3. Checking multiple columns for one value
  4. Mysql Improve Search Performance with wildcards (%%)
  5. Whats the fastest way to lookup big tables for points within radius MySQL (latitude longitude)
  6. If two columns are 1 then make new column
  7. MySQL – Get row number on select
  8. Group by clause in mySQL and postgreSQL, why the error in postgreSQL?
  9. How can I tell when a MySQL table was last updated?
  10. Cast from VARCHAR to INT – MySQL
  11. Operand Should Contain 1 Column – MySQL NOT IN
  12. Is it possible to query a tree structure table in MySQL in a single query, to any depth?
  13. MySQL: Error Code: 1118 Row size too large (> 8126). Changing some columns to TEXT or BLOB
  14. What’s the difference between comma separated joins and join on syntax in MySQL? [duplicate]
  15. Determine Rank based on Multiple Columns in MySQL
  16. When to use SELECT … FOR UPDATE?
  17. Setting column values as column names in the SQL query result
  18. Select from one table where not in another
  19. How to get current date & time in MySQL?
  20. Laravel join with 3 Tables
  21. SELECT query return 1 row from each group
  22. MySql Single Table, Select last 7 days and include empty rows
  23. mysql SQL: specific item to be first and then to sort the rest of the items
  24. How to regex in a MySQL query
  25. When is a timestamp (auto) updated?
  26. MySQL Group By Hours
  27. RMySQL dbWriteTable with field.types
  28. Syntax error on MERGE statement
  29. MySQL: Computed Column
  30. mySQL query to search all tables within a database for a string?

Leave a Comment