Samuel Williams <space.ship.traveller@gmail.com> wrote:
> So, uh, my main question was, does MySQL add null values to an index, and is this different from Postgres...
Samuel,
A quick google says that Mysql does index NULLs. Ask a Mysql group to get a more definitive answer.
More relevant to your original question, I'll go out on a limb as I struggle to recall a fuzzy memory.
The difference between Mysql and Postgresql is fundamental architecture, so yes the index creation will be very
different,as others have said. IIRC (and I may not be), Mysql stores where a row is on the disk via the PK index. That
meansthat secondary indexes point to the proper row in the PK index, which does mean that when you use a secondary
indexto get data that there is a double look up. They claim that's faster for updates and other stuff because a change
toa row only requires 1 index to be changed.
Postgresql stores the direct disk location in each index, which slows down updates a little, but makes selects faster
(andI find this really amusing because so many people say Mysql is great because it's fast at reads, yet
architecturallyPG is faster). If I'm wrong here, I'm sure I'll be corrected. :)
So you can see that Mysql indexes should be smaller than PG indexes because of what they carry. Personally, I think the
diffis small enough I'm not going to worry about it, but math is such that some numbers are smaller than others. :) So
thatshould explain what you're seeing.
My opinion is that you shouldn't worry about the index size. Which DB does what you want the best? That obviously
dependson what your needs are, but after using both Mysql and PG, I'll take PG whenever possible, thank you. Mysql has
gottenbetter over the last 5-8 years, but there are still many pits of quicksand ready to swallow you up there that PG
doesn'thave. If you know where those pits are and/or don't travel into that part of the jungle, great for you;
personally,I prefer to get the job done without having to watch where I'm walking. ;)
HTH,
Kevin