I'm not rooting for Discovery to fail. I'm not rooting for it to succeed, either. It doesn't interest me, and I don't care what happens to it. If you're enjoying it, enjoy away. What I find foolish is the notion that I have some obligation to support whatever is the current incarnation of the franchise, because there are other incarnations of it that I like and enjoy.
I would like to see more science fiction on TV that I like and enjoy, yes. But so far as I'm concerned, the name Star Trek is not some magic talisman that guarantees high quality or a pleasurable viewing experience. If at some point there's another incarnation of Star Trek that appeals to me, I'll gladly watch it. If there's not -- if there's never another incarnation of Star Trek at all -- I'll watch something else. For me it is really just that simple.
There is a possibility that what applies to the shows that don't interest you will affect those that do.
But I think it would be overstating the case dramatically to say that what I do or say will have much effect on any of them. As has been pointed out by other posters, it's the overall numbers that matter, not any one viewer's actions.