Does adding an index on the column you are selecting speed up the query?

Question

I have a fairly common setup, I have a table of tags and record pairs. Querying it is slow so adding an index to the column of the tag I am querying helps speed it up.

tag  | site
123  | 456
789  | 101

My question is whether it is beneficial to add an index on both these columns as one index. It will never be selected by the site, so is there any benefit to doing this? I also cannot guarantee that each pairing is unique without making some changes but if I did would this help with performance?

A typical query might look like this:

SELECT site, tag FROM sitetags WHERE tag ='123' OR tag = '789'

if you have a small table then indexing is not good but it can reduce the performance and if you have large table then indexing is good it can increase the searching performance significantly. — Muhammad
The unique constraint is nothing else but an index. So if you define the pair as unique with the unique constraint, there is alredy an index of the pair. — S.Babovic

Zamrony P. Juhara Zamrony P. Juhara · Accepted Answer · 2017-09-10T10:18:39

If you always search by tag then you only need to index tag column.

Adding column to index when it is not used, introduce unneeded overhead when you insert or update record and also consume more storages.

But composite index (tag, site) may give additional optimization as MySQL only need to read index to satify your query (EXPLAIN usually marks this optimization as using index).

If your operation is mostly read rather than write, then using composite index may not be a bad idea.

It will be better if tag column has high cardinality, meaning that there is high chance that their values are different between each rows.

But I suggest you consult EXPLAIN output first.

Does adding an index on the column you are selecting speed up the query?

2 Answers