Optimising MySQL Database Indexes InnoDB

I have been Googleling for hours now and I haven't quite figured out how indexes work, because there are so many mixed answers available.

Let's say I have an query 1:

SELECT subid, subid2 FROM clicks WHERE wid=100 AND uid=123 AND time >= 1387886885

And a query 2:

SELECT subid, subid2 FROM clicks WHERE fid=100 AND time >= 1387886885 AND uid=123

1) How should I place indexes here? Should I use multi column index or single column indexes?

2) I found an answer on stackoverflow that suggested adding indexes to subid and subid2 also, should I do that? If not, under what circumstances would one do that?

Answers


How should I place indexes here? Should I use multi column index or single column indexes?

You should create compound indexes (read as multi column indexes) because these have better index selectivity

I found an answer on stackoverflow that suggested adding indexes to subid and subid2 also, should I do that? If not, under what circumstances would one do that?

I think you mean an covering index. An covering index is very fast for selecting data because all data can be read from the index data without accessing the table data..

Query 1

SELECT subid, subid2 FROM clicks WHERE wid=100 AND uid=123 AND time >= 1387886885

Needs this index

INDEX(wid, uid, time) 

or this covering index

INDEX(wid, uid, time, subid, subid2) 

Query 2

SELECT subid, subid2 FROM clicks WHERE fid=100 AND time >= 1387886885 AND uid=123

Needs this index

INDEX(fid, uid, time) 

or this covering index

INDEX(fid, uid, time, subid, subid2) 

Here is a link of trying to simplify indexes. From a beginner perspective, the simple example of the original post and explaining how the index correlates to what they were looking for might help.

So, in your scenario, I would have one index based on

( wid, uid, time )

(not sure if the wid vs fid was just a type-o)

An index should be done with a basis of what your most common querying components may be asking for... typically some ID, or User basis, maybe date ranges. But per the link example, get your criteria with the field that will be most precise to the front of the list of indexed fields.


Need Your Help

CSS selector for empty or whitespace

html css css-selectors

We have a selector :empty that can match an element when it is completely empty: