Add Index to Existing MySQL Table

Question

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

Solution 1

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.

Solution 2

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