3

Scenario: You want to create a secondary index for MYSQL (category_id, for example); but 80% of the time it's zero (we never search for it); and 20% it's meaningful (we will actual search for this).

How could you create the secondary index so that it doesn't index the zero value (which is useless)?

Example table:

create table Test(
id int primary key,
secondary_id int,
data text
)

The query is:

select * from Test where secondary_id=some_value
hiro
  • 261
  • 7
  • 19
  • 3
    Why don't you set null instead of zero. Null values will not be included in the secondary index. – ntalbs Aug 26 '15 at 02:41
  • 3
    Do you have an performance issue if the zeros are indexed? What is the problem if you just create a secondary index that does include the zeros? – Dijkgraaf Aug 26 '15 at 02:43
  • 2
    Yeah, "not a problem". All the "0"s will be at one end of the index, wasting disk space, but not performance. – Rick James Aug 26 '15 at 04:17
  • @hiro: I think your problem is that 80% field value is 0 i.e. same so mysql will not use index instead use table scan, to use index may be work if you create composit index on (secondary_id,id). – Zafar Malik Aug 26 '15 at 04:33
  • @Dijkgraaf An obvious consequence is wasting disk space as Rick James mentioned. I do not have enough data to know if it affects performance. – hiro Aug 26 '15 at 06:23
  • @ntalbs The answer to this question seems to indicate that it does index NULL's http://stackoverflow.com/questions/289001/does-mysql-index-null-values Which would make sense as you can select for NULL or NOT NULL in queries. – Dijkgraaf Aug 26 '15 at 20:52

0 Answers0