A megapixel is a common way of stating the resolution of digital camera image sensors, representing (sensibly enough) one million pixels, or a 4:3 rectangle of roughly 1200 pixels across by 900 pixels down. As of 2014, the average pocket digital camera has between 12 and 20 megapixels of resolution, and optics that can't come close to being able to take advantage of it.
Why do we care?
It wasn't all that long ago that digital cameras were expensive toys that couldn't come close to matching film in resolution. A standard resolution was 640 by 480, which also coincides nicely with the visible area of an NTSC TV signal; this resolution is still used in inexpensive cameras used in toys, spy devices, and cheap cell phones. It also makes for a nice file size for modem transmission -- less than 100KB for high-quality compression compared to the 3-8MB common for what's on the market today -- and, with a decent lens, is just about the perfect minimum size for a 4x6in photo print.
As digital camera prices came down, chip densities were increasing, packing more and more photocells onto an image sensor for less and less money, and this turned what had been a device mainly limited to big media photojournalists (you don't need a lot of resolution for a newspaper or website) and deep-pocketed hobbyists into a mainstream device that was capable of producing print-ready images for relatively little money. Somewhere along the line, marketing people decided it was convenient to state resolution in megapixels instead of dimensions, and in the space of a little over ten years (from 2002 to 2013) pixel counts went from the equivalent of an SDTV screen to as much as 40 megapixels on some ultra-high-end pro cameras (as well as two rather weird Nokia phones).
The megapixel myth
Number inflation in marketing is a thing. The bigger the number, the more... um... something... a device seems to have to people who don't do the background work; that's why for example, when cordless phones ran out of bands to operate in at the 5.8GHz level, the US version of the DECT standard was branded version 6.0 for no particularly good reason except to make it look more than 5.8.  The same has applied to pixel count over the years, to the point where it's routine to put 16 to 20 million pixels on an image sensor smaller than your pinkie nail.
The obvious question would seem to be "How many megapixels is film equivalent to?" The SATSQ answer to that is "roughly five", but there's a reason that's a SATSQ; first off, it assumes you're talking about a standard 35mm still frame, and second, it doesn't specify film speed. Your effective resolution drops off as the film speed goes higher, as film with higher light sensitivity has larger grain; pros used to regularly shoot film in their SLRs that would have been consistently underexposed in any point-and-shoot, simply for the higher quality, and did a bunch of darkroom magic to bring up the image contrast.  The truth is, you can't really map film resolution to digital except in the most general terms. Kodak struggled for years with the resolution of their small film formats like Disc and 110, and every advance on the smaller formats propagated to the larger ones, putting the goal further out of reach. In any case, a 35mm "full-frame" sensor is a pretty expensive toy; most DSLRs use a frame size called APS-C, mirrorless system cameras usually go smaller, and point-and-shoot sensors (especially the ones on cell phones) are downright tiny.
Optics also plays a major role. The lenses on most cameras, digital or film, are the single most important part of the system, even more so than the image sensor medium. And while lenses on point-and-shoots are usually made to a very high standard, it's all but impossible to get them to work on the same level as lenses for bigger cameras, for the simple reason that larger lenses mean letting in more light. What seems like a sharp picture on a point-and-shoot can get very muddy on the pixel level, much more so than a thousand-dollar DSLR lens. In turn, the light-gathering point extends to the size of the sensor itself; while chip processes are sophisticated enough to cram 40 megapixels into a cell phone, the smaller the photocell sites on the chip get, the more problems you'll have with picture noise. This is actually getting almost into quantum effects -- in low light with a very small sensor, the individual photocells on the sensor might pick up wildly varying numbers of individual photons. The camera firmware will usually smooth this out as much as possible, but it doesn't always do a good job of it.
On top of all that, there's two further points. Image quality is also greatly affected by lighting; system cameras usually have multiple flash options from onboard flashes all the way up to networks of remote flashes set up in a studio. Point-and-shoots usually do not have this ability. The other is that the standard for glossy magazine publishing is ~9 megapixels. Virtually every dedicated camera on the market save toy models can do this or better now, and for the most part, that's only a guideline anyway; in a time where citizen photojournalism is a common part of most news websites and local TV newscasts, they'll take anything they can get, even if it's shot on a cell phone.
There are some people who do need 40 megapixels; these people spend upwards of US$10000 on their cameras alone (not counting lenses or other gear) and hand their image files off to graphic designers who may need to crop down to some very small areas indeed. These photographers have six figure paychecks from big publishers and ad agencies, their pictures appear in phone book-sized fashion magazines and on billboards, and they use brands like Hasselblad and Mamiya that most people have only ever seen in person through the glass of a counter case at a camera shop. And unless you're one of them, that's the closest you'll ever get to one as well.
Sports and nature photographers spend big bucks on expensive DSLRs like the Nikon D4 or Canon 1DX. These cameras go for upwards of $6000 a piece, and the kinds of people who buy them often mate them to lenses that cost even more than that. They might like that many megapixels, but as a general rule they don't need them; their cameras tend to max out around 25 megapixels  and they're generally more concerned with getting a blizzard of shots of one event in hopes of getting that one perfect image. Pushing too much data just slows down their work.
Architectural and landscape photographers, if they're not using the same gear everyone else does, tend to like using sheet film in view cameras. Those cameras are rare and use insanely expensive lenses, but are actually surprisingly easy to build . There's no meaningful equivalent to that in digital; large format digital is an entirely different beast from its film version.
Videographers (including digital cinema) certainly don't need that much; big screen quality work can be and has been done on standard definition video and even the "4K"  digital cinema cameras that the big studios are switching to only need about 8 megapixels.
And, on top of all that, postprocessing takes up a lot of storage and RAM. (There's a reason Apple, in its pre-Intel days, used to use Photoshop for their benchmarking demos.) Working with one of these multi-megabyte image files can make even a multicore monster system seem sluggish; digital cameras are catching up to smartphones and tablets for processing power and still do most of the heavy lifting in hardware, because if they tried to do it in software, it would run down the battery way too fast.
Which means, overall, that unless your needs are highly specialized, you don't need that much resolution either, and if you do, you're going to need some very expensive lenses and a lot of computing power to take proper advantage of it. That means that when you go camera shopping, the last thing you need to worry about is megapixels. Look at image quality, look at features, hell, look at the color of the camera if you want. As long as you're not digging around in the used department, megapixels simply don't matter.
- In actuality, depending on location, DECT uses a series of dedicated bands between 1880 and 1930 MHz, in order to stay out of the way of the very crowded 2.4 and 5.8 GHz bands, where Wifi, Bluetooth, and microwave ovens live.
- A full discussion of the science of chemical photography would be fascinating, and a bit harrowing in some places, but is entirely out of the scope of RationalWiki.
- APS-C is about half the size of a 35mm frame, and named for the now-dead APS film format.
- 16-20 is more common on the current market, and someone buying used gear on the cheap might wind up with as little as 8MPx.
- See, for example, The Blair Witch Project and 28 Days Later.
- 4K is about 4 times the resolution of 1080p, but the name refers to the horizontal resolution, around 4000 pixels.
- Wifi on a digital camera is really nice -- you can get DSLR-quality pictures from the restaurant table to your Instagram account without even getting up from your meal.
- This article is greatly indebted to the work of photographer Ken Rockwell, whose article The Megapixel Myth supplied much of the information for this one.