I won't say I've never seen or used confusion effects in my games, 'cuz I have! But it usually hasn't been the result of a spell casting. Typically it's been the "spell-like" effect of a monster or item. Looking at the BX spell descriptions, I can see why it's not a commonly chosen spell.
Foe one thing, it's 4th level MU, so it's competing for a slot against Wall of Fire, Wall of Ice, and Charm Monster (among others). Also, despite the fact it last for twelve rounds and 2HD or less targets get no save, it's a fiddly spell to run.
- First, the number of targets is random (3d6), so you're uncertain of the result right out of the gate.
- Secondly, 2+1 HD and up targets have to roll their save each round.
- Thirdly, the confusion effect is re-rolled for each target each round with a failed save. That's a lot of rolling!
- Finally, there is a better than 1:3 chance that the target(s) attack you anyway, and that's after they fail their save vs. spell!
Confusion is a fun effect, but it's understandable why PC/NPC wizards aren't desperate to memorize it for the day.