0

I'm working on a project where our team does have many static tables in our database. I want to create indexes on those tables to increase the performance. Unfortunetaly I'm getting the error:

Error Code: 1170. BLOB/TEXT column 'description' used in key specification without a key length

The mentioned column 'description' is defined as MEDIUMTEXT.

I found out that TEXT types cannot be indexed and want to change all the unnecessary TEXT types to VARCHAR(255) and apply SQL Indexes after that.

Most of the SQL Statements we use request all attributes of the table. In our most important table (in regards to indexing) only one attribute needs to stay as MEDIUMTEXT. But we always fetch the whole row (including the MEDIUMTEXT desription).

So my question is: Since I cannot change that specific MEDIUMTEXT to VARCHAR: shall I give up on indexing this table? Do I get a performance boost if I index every attribute despite the MEDIUMTEXT?

Thanks in advance...

Paul Erlenmeyer
  • 523
  • 2
  • 6
  • 29
  • Indices help with columns you are querying on. Are you querying on `description`? You can see https://stackoverflow.com/questions/1827063/mysql-error-key-specification-without-a-key-length for how to make the column (or X characters of it) indexable. You don't need to index every column, and dont `give up on indexing this table`, index the columns you need – user3783243 Jul 13 '20 at 18:59
  • Are you searching short strings, but allowing long strings? Is this a generic EAV schema? Show us the `SHOW CREATE TABLE`; it will probably clue us into what you are _really_ after. – Rick James Jul 13 '20 at 23:17

2 Answers2

2

Here is how you can create INDEX on MEDIUMTEXT field:

CREATE TABLE textindexexample(
   name MEDIUMTEXT,
   INDEX(name(255))
);

It will be the same if you need a KEY. On an existing table, you can add it like this:

CREATE INDEX idx ON textindexexample(name(255));

Note that I indexed only the first 255 characters. That means that if two different texts start with the same char sequence at the beginning, they will be threated as equal by the index. The limit for index size is 3072 bytes. So no more than name(3072) on regular ascii charset.

Regarding the second question: no, you will not get performance boost if you index everything. It's actually opposite - the performance may degrade because the indexes needs to be maintained (updated as soon as you add data).

You should index only columns that you use. For example the fields which you use in WHERE and JOIN.

Philip Petrov
  • 975
  • 4
  • 8
  • I checked out our Queries and most of them are SELECT *. So if I create an Index on the whole table I will not get a performance boost? In that case should I rather check the columns used in the WHERE clause? – Paul Erlenmeyer Jul 14 '20 at 07:16
  • Yes, check whatever is used in the WHERE clause. – Philip Petrov Jul 15 '20 at 09:56
1

In MySQL there is a such thing as FULLTEXT search where you can set an index.

FULLTEXT INDEX(description)

An index helps to filter results at the cost of additional storage space and additional writes to the disk. Having more indexes means writing to the tables will be slower. You should only be indexing the columns you are actually filtering by in your WHERE statements or that you are JOINing by.

If you really are searching descriptions than a FULLTEXT search is probably what you want. It is a very powerful feature.

https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html

Ralph Ritoch
  • 3,260
  • 27
  • 37