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.