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

For an index to give you a speed improvement it needs to be an index that it likely to be used to identify rows to be retrieved. Also one tends to need to have built properly normalised tables. Can you give some sort of indication of the type of data held and the type of query run? — Captain Payalytic
– Captain Payalytic, Commented Aug 28, 2013 at 14:40

FelixEnescu · Accepted Answer · 2017-09-20 07:31:35Z

18

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

edited Sep 20, 2017 at 7:31

FelixEnescu

5,2022 gold badges35 silver badges34 bronze badges

answered Aug 28, 2013 at 14:47

sme

5,6817 gold badges35 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rahul Tripathi · Accepted Answer · 2013-08-28 14:37:33Z

2

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.

answered Aug 28, 2013 at 14:37

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

Collectives™ on Stack Overflow

Add Index to Existing MySQL Table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related