This is a great question. You'll probably get a number of answers, but you should see a difference between MMO, RTS, and FPS gamers. MMO gamers want a high DPI mouse with multiple buttons for key bindings. RTS also needs high DPI, but multiple buttons are meh. For RTS I used to use a Logitech G9x, which I absolutely loved until it broke.

Good FPS gamers use a different mouse. Accuracy and precision are significantly more important to FPS players. The difference of being even one pixel off your target can result in death. This is why most FPS players choose optical mice. Some even use mice with a ball.

Laser sensors are (unfortunately) not as accurate as optical sensors. Most modern mice have memory and attempt to predict movement and/or have acceleration. This is BAD. FPS gaming requires muscle memory, and having a mouse that constantly changes its speed prevents your body from learning one consistent motion.

The polling speed of your mouse is more important than DPI. Polling speed is how frequently the computer "asks" the mouse where it is. The default on Windows asks the mouse where it is every 8ms. That means that if you move your mouse, the computer will not know you moved the mouse until 8ms later. This is bad for a fluid motion. If you try this in MS Paint, you'll get slightly jagged lines when your draw a circle at a low polling speed if you turn off prediction and acceleration. This is why most mice have prediction.

For normal computing activities, you'll want to draw a nice "circle". Since the computer checks the mouse at certain intervals of time, it uses your motion to predict where your mouse will be in 8ms, and some go even farther. As you can imagine, letting your pointer in a FPS game move on its own after you stop moving your hand will cause some serious aiming problems.

Higher polling speed (500 or even 1,000 hz) will smooth out the movement of your mouse, making it better mimic the motion of your hand. FPS gaming is about making fluid and steady motions. A mouse that jitters and jerks around is ._.

I made a post here asking what the default polling speed on the PS3 is, and if it could be changed. I noticed that my motions were not as fluid as they are on a PC. (never used a mouse on a PS3 before lol.) The PS3's default speed on USB ports appears to be 100hz, which sucks. It also reduces the need for high dpi mice because the computer cannot register movement. Wireless mice are fail because they have a slow polling speed that can never be changed.

In case you're wondering, I use Wheel Mouse Optical 1.1a. I know other FPS gamers like IntelliMouse, IntelliMouse Explorer 3.0 and Razer Deathadder. There are various new mice that come on to the market every now and then. You can do some more research into the new ones if you desire. The nice thing about WMOs is that they can be picked up for $10-15 USD new.

In FPS games I have 400 dpi. RTS definitely benefit from the super high DPI mice. Turret rotation on vehicles and installations is much slower on a mouse at 400 dpi than a controller. This guy is 100% tank with no points in infantry, my alts are infantry. Just to test my mouse, I made a character, and in less than 24 hours after creation and a few hours of play time, I sniped 100/0. Never left a game, and I never died. Head shots ftw.

I hope this helps you ^_^