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
Improve this question
6
  • 3
    Why don't you set null instead of zero. Null values will not be included in the secondary index. Commented Aug 26, 2015 at 2: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? Commented Aug 26, 2015 at 2:43
  • 2
    Yeah, "not a problem". All the "0"s will be at one end of the index, wasting disk space, but not performance. Commented Aug 26, 2015 at 4: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). Commented Aug 26, 2015 at 4: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. Commented Aug 26, 2015 at 6:23
Related questions
2
Proper indexing in a big table
2
mysql index for speed
1
Mysql not use index over huge table
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.