• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 413
  • Last Modified:

how does mysql handle NULLs in indexes

Hello

I'm a little unsure about how mysql handles nulls in indexes.

Can I have an index on a column that contains Nulls?

If I have an index based on a text field which can contain Nulls will mysql add the nulls to the index? Will they be added at the top or bottom if they are added (not that that matters 0 I'm just curious).

thanks
0
andieje
Asked:
andieje
2 Solutions
 
Kevin CrossChief Technology OfficerCommented:
Hi.

Yes, you can have an index on a column that contains NULLs.  According to the manual, MySQL can leverage an index on column with NULLs to optimize IS NULL condition in WHERE clause.  Therefore, the answer is yes to your questions.

MySQL orders NULLs first if I remember correctly.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
from what I know, NULL values are NOT indexed.

however, indeed some optimizations are done for NULL values vs NOT NULL query:
http://dev.mysql.com/doc/refman/5.0/en/is-null-optimization.html
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now