When to use an index?

Question

I have a query here:

SELECT MAX(Sales.Revenue-Sales.Costs), Company.Name
FROM Sales 
INNER JOIN Company
ON Company.ID = Sales.ID
WHERE Sales.Date <= DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH);

My question: where should I use an index and why?

Conceptually, but I'm looking to learn from specific examples as well, like the one I've listed (I'll study examples from this link as well, thank you) — Stephen6895, Dec 04 '15 at 18:21
For one thing, we'd need to know what **concrete database** you're using - indexing strategies are often **vendor-specific**, so they differ from SQL Server to Oracle to DB2 to MySQL to PostgreSQL etc. — marc_s, Dec 04 '15 at 18:26
You should tag your question with the database you are using (which is presumably MySQL based on the syntax). — Gordon Linoff, Dec 04 '15 at 18:53

score 1 · Answer 1 · answered Dec 04 '15 at 18:54

For this query:

SELECT MAX(s.Revenue - s.Costs),  c.Name
FROM Sales s INNER JOIN
     Company c
     ON c.ID = s.ID
WHERE s.Date <= DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH)
GROUP BY c.Name;

The optimal indexes are composite index (i.e. having more than one column): sales(date, id, revenue, costs) and company(id, name). These indexes cover the query. That means that all the data is in the indexes, so the original data pages do not need to be accessed. As the query is structured, MySQL will still need to do an aggregation using a filesort.

Notes:

I added the group by so the query makes sense.
Table aliases make the query easier to write and to read.

score 0 · Answer 2 · answered Dec 04 '15 at 18:21

If yours sales and company tables have a lot of data then you may need to create following indexes:-

Sales -> Sales.ID
Sales -> Sales.Date
Company -> Company.ID

You need these indexes as your are using these columns in joins and WHERE clauses. In case tables have large number of records absence of indexes will slow down your queries.

When to use an index?

2 Answers2