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, <kbran...@pwhome.com> wrote: > Samuel Williams <space.ship.travel...@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 > means that secondary indexes point to the proper row in the PK index, which > does mean that when you use a secondary index to get data that there is a > double look up. They claim that's faster for updates and other stuff because > a change to a 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 (and I find this really amusing > because so many people say Mysql is great because it's fast at reads, yet > architecturally PG 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 diff is small enough I'm not > going to worry about it, but math is such that some numbers are smaller than > others. :) So that 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 depends on what your needs are, but > after using both Mysql and PG, I'll take PG whenever possible, thank you. > Mysql has gotten better over the last 5-8 years, but there are still many > pits of quicksand ready to swallow you up there that PG doesn't have. 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
