Mysql column options binary

Find centralized, depended on content material and collaborate around the technologies you operate maximum.

Q&A for paintings

Connect and proportion know-how inside a single vicinity that is dependent and smooth to look.

I’m seeking to index a binary column. It may be both 1 (which it is for 3million rows), or zero (which it commonly is for ~a hundred rows).

Using MySQL’s explain it appears to be absolutely ignoring the index I’ve created, even though I use USE INDEX in my question.

Is it just honestly no longer viable to index a column like this? Does that suggest any question depending on it’ll require a complete table experiment?

I can see one answer could be to split the choices records into tables, and it would type of make sense based on the commercial enterprise requirements.

When the fee is 1 (the common price), it is a lot forget about the index and without a doubt experiment the choices desk, tossing the choices few rows that don’t practice.

When the value is zero (the choices very uncommon price), the choices index can be used, and be very effective. However, this could follow most effective when you explicitly say WHERE col = zero.

Without seeing the SHOW CREATE TABLE and SELECT, it’s miles difficult to bet what different optimizations is probably beneficial. One that comes to mind: a composite index beginning with that column.

It is also tough to decide whether or not splitting into two tables could help. Would you from time to time want to UNION them lower back collectively? If splitting cleans up the choices code, then precise. Note that some different columns may work away in a single table or the other.

Your Answer

Thanks for contributing a solution to Stack Overflow!

To learn greater, see our hints on writing incredible solutions.

Required, but in no way shown

Required, however never shown

By clicking “Post Your Answer”, you settle to our phrases of carrier, privateness policy and cookie coverage

Not the answer you're looking for? Browse other questions tagged mysql indexing or ask your personal query.

site design / emblem © 2021 Stack Exchange Inc; consumer contributions certified below cc via-sa. rev 2021.9.23.40291

By clicking “Accept all cookies”, you settle Stack Exchange can store cookies to your tool and disclose information according with our Cookie Policy.