Thanks Kevin, that makes sense. Yeah, I understand the architectural
difference a bit more now. I also read that when you change a column
which is not index, all the indexes for that row need to be updated
anyway. Is that correct?
On 7 December 2016 at 05:27, <kbrannen@pwhome.com> wrote:
> 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
thediff is small enough I'm not going to worry about it, but math is such that some numbers are smaller than others. :)
Sothat should 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
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
