3

For example, table FOO

          id         name         type
---------------------------------------------
         bigint(20)  varchar(30)  smallint(4)

unique key name_type (name, type).

index will be created for (name, type). But if many queries involve type such as type=aNumber, do I need to create index for type separately?

In other words, does index(name, type) include index(type)?
How about mysql and oracle db?

Dave
  • 759
  • 2
  • 9
  • 31
  • Dave - what Juan said in his answer is true, but it doesn't answer your question. At least in Oracle, a composite index will be used if you filter by the FIRST column in the index, but not if you filter ONLY by the second column in the index. In your case, you do need an index on type. –  Oct 19 '16 at 05:15
  • _This_ question does not mention wildcards, so I do not feel that the _dup question_ is relevant. Voting to reopen. – Rick James Oct 19 '16 at 22:00

2 Answers2

4

These rules apply to MySQL. Probably they apply to other vendors.

INDEX(name, type) (or UNIQUE(name, type)) is useful for

WHERE name = ...
WHERE name = ... AND type = ...
WHERE name = ... AND something_else = ...
WHERE name LIKE 'Abc%'          -- note: does not start with wildcard
WHERE name BETWEEN 'x' AND 'y'
WHERE name = '123'      -- both are strings

but not useful for

WHERE type = ...        -- without `name`
WHERE name LIKE '%xyz'  -- because of _leading_ wildcard
WHERE name BETWEEN 'x' AND 'y' AND type = ... -- Only partially useful
WHERE name = 123      -- trouble between VARCHAR and number

Note: The order of the AND's clauses in WHERE does not matter; the order of the columns does matter.

So "But if many queries involve type such as type=aNumber, do I need to create index for type separately?" -- Extra index needed if you don't also have name = constant.

And "In other words, does index(name, type) include index(type)?" -- No, it is not 'included'.

See also: Index Cookbook

Rick James
  • 135,179
  • 13
  • 127
  • 222
1

Imagine you dont have index

This query will need scan the whole table to find 'somename'

 SELECT *
 FROM yourTable 
 WHERE name = 'somename'

Now imagine you have index for name, the same query will use the index to quickly find the occurences of somename, but then will have to go back to the table to find the rest of the field.

Finally if you have a composite index with (name, type) and the following query.

 SELECT type
 FROM yourTable 
 WHERE name = 'somename'

You will find very quickly someone and you already will have type because is part of the index and dont need do the aditional look up on the table.

So is great, but what is the problem then? Composite index need more db space, and make insertions slower. So is a consideration you need to make.

Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
  • His question was "index will be created for (name, type). But if many queries involve type such as type=aNumber, do I need to create index for type separately?" – Drew Oct 19 '16 at 04:18
  • @Drew Well I think I answer that. My example show a composite key and do a search for a single field. So the short answer is no, dont need create another index – Juan Carlos Oropeza Oct 19 '16 at 04:20
  • innodb assumed, looks like a tablescan to me http://i.imgur.com/nxzxKn9.jpg – Drew Oct 19 '16 at 04:33
  • @Drew can you put that test case in [rexter](http://rextester.com/l/mysql_online_compiler) for me? – Juan Carlos Oropeza Oct 19 '16 at 04:38
  • I can post some answers of mine that just self-generate tens of millions of rows. I use it to mess around with stuff, like [here](http://stackoverflow.com/questions/40121332) ... but I will post a few urls for you – Drew Oct 19 '16 at 04:40
  • [ratings table](http://stackoverflow.com/a/33666394) became a students table after a rename and adding more columns, [orig silly answer](http://stackoverflow.com/a/34015333) but not really that silly and could be practical, and then [innodb CBO and IN](http://stackoverflow.com/a/38002986) – Drew Oct 19 '16 at 04:48
  • @JuanCarlosOropeza - no, you didn't answer the question. The OP asked about filtering on the second index column ONLY, and your answer is about filtering on the leftmost column. The OP DOES need an index on type if he filters on it often, at least in Oracle, I don't know other db. –  Oct 19 '16 at 05:19