CODEWITHSUNDEEP
mysqlsqlindexing
Bachzen
Bachzen

Reputation: 323

Add Index to Existing MySQL Table

I have a database in which my tables are currently in use with no index defined. I'm looking to increase the speed if possible. I have ready up on indexes, but wondering does that need to be the primary key as well? Also, the index will be null for the old records in the table. Is that an issue? I'm wondering what the best approach is.

Here is current table stucture (I was planning on just inserting 'id' as index)

Field   Type    Collation   Attributes  Null
user    varchar(255)    utf8_general_ci     No
pass    varchar(255)    utf8_general_ci     No
ts  varchar(255)    utf8_general_ci     No
lat varchar(255)    utf8_general_ci         No

Upvotes: 5

Views: 29625

Answers (2)

sme
sme

Reputation: 5671

There seems to be a misconception about what an index really is. You don't need additional fields. You can create an index on one or more on the existing fields in the table like this:

alter table xxxx add index idx_user (user);

Creating this index will not add any fields to your table. Instead MySQL will create an additional tree in the background for faster lookups. This tree is automatically updated whenever you change the data in the table, so it does not come for free. But it is probably a good idea to create an index on a column that you typically use to look up rows in the table.

You should also probably read up on indexes in general: https://dev.mysql.com/doc/refman/5.7/en/mysql-indexes.html

Upvotes: 18

Rahul Tripathi
Rahul Tripathi

Reputation: 172418

Try like this:-

CREATE FULLTEXT INDEX `idxtags` ON `table1`(`tags`)

and if you want you can try this also:-

ALTER TABLE Tablename ADD id Int NOT NULL AUTO_INCREMENT PRIMARY KEY;

this will add unique index on rows.

Upvotes: 2

Related Questions