I'm sorry that your first cruise wasn't a great experience, sure wish it had been the other way around for you.
Yea, you definitely have to be careful which cabin you select, as a general rule watch out for the deck below where all the deck chairs and tables are, although, as you acknowledge, some ships do have a lot sound protection between those levels, we have experienced that before when we were unable to select a more choice deck and cabin and noise was a non-problem. Also, try and not be under the fitness center or a deck where they jog, as the pounding resonates down, and even though they try to place time restrictions on such activities there are those who ignore the rules.
Actually, I prefer to be more forward than midship, we always go the balcony route, I like to be just behind where the bow wave begins, you get the best flying fish viewing there, so I pick balconies that look straight down onto the sea and not into someone's balcony. We don't have problems with motion sickness so the location forward or aft or high or low makes no difference to us.
Don't know much about Carnival as we only cruise on HAL, Princess and Regent. I notice from your remarks you aren't enamored by cigarette or cigar smoke. We have been staying away from HAL as of late since they are the only one of the majors now that still allow cigar and cigarette smoking on the balconies.
The cruises of short duration usually attract the younger element who like to stay up late and party more. Particularly around the "Spring break" times. They call these "Booze Cruises" Haw, and for good reason. These are simply not relaxing, despite what the lines like to claim. The large resort ships stuff lots of guests into tiny cabins at a low initial cost in order to get one out and active in their nonstop nichol and diming activities and entertainment. But don't get me wrong, 50 years ago I would have been there with them.
The opposite is true as well, a lady I got to know at our fitness center once was really looking forward to her first cruise that her husband had surprised her with. When she returned I asked what she thought, "Yikes, nobody told me the ship would be full of old people, that is all I do every day is wait on old people." Turned out she was a hair dresser at a retirement home here.
You appear to be pretty congenial, so I bet you can find something that suites you, study the ship and cruise reviews but discount those that appear exaggerated, overly harsh or too romanticized. For us a cruise is an escape from the stresses and strains of life on land. It is, we hope, hassle free. It gives us a chance to sleep in the same bed every night and during the day a chance to explore new places and view some of the world's most beautiful horizons.
I like to say this to folks who are asking me questions and looking at cruising for the first time. "There is no such thing as a best cruise line or best cruise ship, only the ship and cruise that is best for you."