That's quite a broad question, as they have been made since 89, so you have quite a choice. Until recently (2019) no MX5 has been blisteringly fast. Then they introduced the 184bhp 2.0l engine, which as you've stated makes 60 in about 6.5secs....which is pretty respectable for something with under 200bhp.
Most of the older NBs and NCs were 1.8l with approx 150ish hp. Some later ones were 2.0l with a bit more. None fast at all....BUT, like a Lotus, it's not all about the straight line speed - it's about the twisty bits.
Personally, if you have the budget (and you are 6ft or under) I would find a nice ND (Mk4) as even the later 184bhp can get mid 40s mpg on a run. My 1.5L (132bhp) gets nearly 50mpg on a run. All to do with the nice low weight 😉
I would say the sweet spot is a late NC 2.0L (160bhp) - but some say these are not great handling wise as standard.
I think the sweet spot for the ND is the 1.5....as you can literally thrash it to death, and still do legal speeds 😂 If you want a bit more oomph, then the 2019+ 2.0L
The purest to drive though are the original NA (Mk1). But like all the models, it's main enemy is rust. All will have had some work by now, with varying degrees of competence...so some repairs might not last. Even the NDs corrode underneath, so many owners regularly treat the underside.
Finding a really good Mk1 is difficult now - unless you want the spend a bit of money....