I have a slow performing query on my table. It has a where clause such as:
where supplier= 'Microsoft'
The column type is text. In phpmyadmin I looked to see if I could add an index to the table but the option is disabled. Does this mean that you can not index a text column? Does this mean that every update query like this is performing a full table scan?
Would then the best thing to do is separate the column into it's own table and place an ID in the current table then place an index on that? Would this potent开发者_运维知识库ially speed up the query?
You need to add a prefix length to the index. Have a look at Column Indexes docs
The following creates an index on the first 50 bytes of supplier field:
mysql> create index supplier_ix on t(supplier(50));
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
But maybe you should rethink the datatype of supplier? Judging from the name, it doesn't sound like a typical text field...
You should do a check on
select max(length(supplier)) from the_table;
If the length is less than 255, you can (and you should) convert it to varchar(255) and built an index on it
Choosing a right data type is more important.
If the length is long, built an index on limited length will help.
Did I understand you right? It's a TEXT column? As in the type that corresponds to BLOB? Might I advise considering a VARCHAR for this column?
精彩评论