CODEWITHSUNDEEP
mysqlindexing
JohnU43
JohnU43

Reputation: 33

Different ways to create index in mysql?

If i use 

CREATE INDEX status_index ON eligible_users (status)

or 

CREATE INDEX status ON eligible_users (status)

its the same thing no difference?

Also if i create alot of indexes will it actually help with queries or slow down?

Upvotes: 0

Views: 264

Answers (2)

Matteo Riva
Matteo Riva

Reputation: 25060

Both statements you wrote do the same exact thing, only difference is the name of the index.

As for usefulness, it depends on the database setup and usage. Indexes are useful to speed up queries, but they have to be maintained on every INSERT/UPDATE, so it depends. There are a lot of resources available online about this wide topic.

An index can make or break a query. The execution time for certain queries can go from minutes to fractions of a second just by adding the correct indexes. In case you need to improve a query you can always prepend EXPLAIN to it, to see what MySQL's execution plan is: it will show what indexes the query uses (if any) and will help you troubleshoot some bottlenecks.

As said, an index is useful but is not free. It has to be kept up to date, so every time you insert or modify data in an indexed field, then the index must be updated too. Generally in cases where you have a lot of reads and (relatively) few writes, indexes help a lot. But unnecessary indexes can degrade performance instead of improving it.

Upvotes: 3

bfavaretto
bfavaretto

Reputation: 71918

The short syntax for creating a single column index on column col from table tbl is:

CREATE INDEX [index_name] ON tbl (col)

Full details available in the MySQL Manual.

Upvotes: 0

Related Questions