it really depends what route you want to take from here, Intel or AMD, this is where your journey begins, i'm not that clued up on AMD, but i know quite a bit about Intel.

Firstly, a history lesson ;-)

I'm going to skip the first lot of processors that were pre-2000,

so, Pentium 4 and Celeron at socket 478, not really anything brilliant about these processors, if you ever come across a celeron, avoid them at all cost, these are described as "family processors", good for general productivity and not much else, Pentium 4 with hyper threading was ok, hyper threading on the p4 was a 1 core processor that simulated multiple cores, seemed to work ok but you could tell the difference when you multitasked.

now, moving onto socket LGA775, this socket introduced the first ever true multi-core processors on the mainstream market, with the core2duo and core2quad processors proving to be very good at almost everything, you could also get core2duo extreme and core2quad extreme processors which were for heavy duty gaming and multitasking(including heavy HD video editing), you can get a 775 socket P4 though, but with HT technology, and also the celeron family making an appearance.

now to the most recent socket LGA1366, this socket is exclusive to multi-core processors, with the likes of i3(2 core), i5(4 core) and i7(6 core) processors, the i7 family also incorporated an extra 2 simulated cores using intels hyper threading technology, which seems to work rather well, making it an 8 core processor, of course intel made the extreme family for i3, i5 and i7.

Now enough of the history, now to the present, it really boils down to what your poison is, Intel or AMD, so choose wisely and someone else could possibly fill you in on AMD's side of things?

MrNeeds

well, with a motherboard change, there's lots of things to consider, the new motherboard might not support the existing hardware that you have got, for example, most 478 Motherboards use DDR RAM at 800Mhz, whereas, most motherboards at 775 are DDR2 at 1066Mhz or above, also, newer motherboards only come with 1 IDE port, so if you got more that 1 hard drive and 1 CD drive on IDE then unfortunately you'll have to replace one of them, of course you need to consider the size of the new board so it'll fit in the existing case.

but most of this will come naturally so it doesn't seem as dramatic as all of this

Hope i've helped and not put you off.

MrNeeds

Also, if your planning a new build, be careful not to choose an end of life platform. For example, socket 775 is end of life and there will be no future 775 processors or boards developed or manufactured going forward.