Re: Can I use an index to see if a text column IS NULL?
Posted by: Rick James
Date: February 09, 2011 11:34AM

1. Prefix indexes (necessary when indexing a TEXT column), are notoriously poorly implemented.

2. There seems to be not optimization on NULL or LENGTH for TEXT columns.

But you seem to have found a case where it does the best it could.

The EXPLAIN clearly states that it is using the index:
key: text_index
But note that it must read through 1192500 entries in the index to do the COUNT(*). That could take ~15 seconds. Especially if your cache is too small. Read:
http://mysql.rjweb.org/doc.php/memory

SHOW TABLE STATUS LIKE 'text_table'
to see the Index_length. It had to read through about 1/3 (1192500/3M) of that to get your answer.

On an unrelated topic...
> unique incrementing id numbers
Sounds like you need to read about AUTO_INCREMENT.

Options: ReplyQuote


Subject
Written By
Posted
Re: Can I use an index to see if a text column IS NULL?
February 09, 2011 11:34AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.