1

Based on this question i believe mysql does indeed index null value. I am attempting to optimize a sql server and i would like to know if the server has index null values; is there a way to check the indices for NULL values?

Fuseteam
  • 376
  • 2
  • 15
  • A old MySQL 5.1 manual is mentioning *"An SQL NULL value reserves one or two bytes in the record directory. Besides that, an SQL NULL value reserves zero bytes in the data part of the record if stored in a variable length column. In a fixed-length column, it reserves the fixed length of the column in the data part of the record. Reserving the fixed space for NULL values enables an update of the column from NULL to a non-NULL value to be done in place without causing fragmentation of the index page. "* So `NULL` values are indeed stored in the index – Raymond Nijland Mar 15 '19 at 17:29
  • hmm but then the question is how do we know if it any column has null, thus slowing down queries – Fuseteam Mar 15 '19 at 17:59
  • *"but then the question is how do we know if it any column has null, thus slowing down queries "* `NULL` is to indicatie `"nothing"` in SQL, so is this question about if you are using `NULL` correctly in your data model.. Because you can't compare performance between a "good" and a "bad" model, which is modelled after a user case datamodel which expects to allow filling in "nothing", if you catch mine point here. – Raymond Nijland Mar 15 '19 at 18:04
  • i'm not talking about good or bad models i'm talking about reducing the amount of rows the query has to scan by finding a way to throw the nulls out of the index or otherwise minimize nulls the use case here is "i have no idea if there null in any of my index, are they nulls? are those slowing me down? – Fuseteam Mar 15 '19 at 18:06
  • *"i'm talking about reducing the amount of rows the query has to scan by finding a way to throw the nulls out of the index or otherwise minimize nulls "* only way to do that is is to disallow `NULL` in the column with `NOT NULL` so the index can't store `NULL` value .. Otherwise No now you know why i mentioned "good" and a "bad" model in the other comment – Raymond Nijland Mar 15 '19 at 18:12
  • *""i have no idea if there null in any of my index, are they nulls?"* Search with `indexed_column IS NULL` in combination with `COUNT()` ? – Raymond Nijland Mar 15 '19 at 18:13
  • *" are those slowing me down? "* See [EXPLAIN](https://dev.mysql.com/doc/refman/8.0/en/explain.html) – Raymond Nijland Mar 15 '19 at 18:14
  • ah interesting i'll try those two comments you gave me, i am already using explain haven't anything about null in there – Fuseteam Mar 15 '19 at 18:41
  • Please provide an example of a table and a query. – Rick James Mar 16 '19 at 15:32
  • an example? of a query that's slow? not sure how feasible that is with dummy data – Fuseteam Mar 16 '19 at 20:07

3 Answers3

1

Ignore, ignore, ignore!

Whether the index has 0 bytes, 1 byte, 2 bytes, or several bytes for NULLs -- this is insignificant in the grand scheme of things.

A BTree (and you are talking about an index that is a BTree) drills down through very few levels (perhaps 5 levels for a billion rows). Even if each 'row' in the index doubles in size, the BTree might still be only 5 levels deep. The "5" is the cost of a "point query". This is independent of fluff in the entries.

The manual pages are probably hangovers from when MyISAM was the only Engine. Details like this have not necessarily been updated with the advent of InnoDB, much less its 4 ROW_FORMATs.

Don't trust EXPLAIN, either. When it says Key_len it is giving only a clue of the size of the entry.

INT NOT NULL -- 4 -- meaning 4 bytes
INT     NULL -- 5 -- implying an extra byte for NULL
VARCHAR(10) CHARACTER SET utf8 -- 32 -- 2 for length (might be only 1) + 3*10

But varchars do not take 32 bytes; they are variable length.

Rick James
  • 135,179
  • 13
  • 127
  • 222
0

If the column of the table the index points to has null values means that would be also in the index.

Fucio
  • 475
  • 3
  • 11
0

As far as I know, you cannot query the index itself for null values, but you can always use where column_name IS NULL condition to check a column for NULL values, and then go from there.

Nikola Pavlovic
  • 91
  • 10