CODEWITHSUNDEEP
androidmysqlsqlsqlite
Sami Eltamawy
Sami Eltamawy

Reputation: 9999

When to use CREATE INDEX and when to use USE INDEX

I am using SQLite in an Android application that should stores thousands of sound Tracks. I have decided to use the Track_title as an INDEX for faster selection and search. After a long reading session I have understood from this source LINK that the INDEX will make the SELECT faster but will make the INSERT slower. They recommend to drop the index from the table before the insertion of big data and re-put it again after it. Using this syntax:

CREATE INDEX my_idx ON my_table (my_attribute);

Some other sources, recommended to use index while performing the select query not on the Creation of the TABLE from this LINK. using this syntax:

SELECT * FROM my_table USE INDEX (col1)
WHERE col1= name;

The Question is: Which one of those methods will give me better performance? and what is the Advantages and disadvantages of both?

Upvotes: 0

Views: 573

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269553

The two are not incompatible. You cannot use an index unless it is already created.

Your table is pretty small, with just a few thousand rows. I would not worry about table creation performance, unless you specifically experience that.

An index works like the index at the end of a book. If you want to find a particular title, the engine can quickly scan the index and determine which data records contain that title. This does come with an overhead when data is being inserted/updated/deleted in the table, because the index has to be maintained.

This can be a big performance issue, when inserting millions of rows into a table, particularly an empty table. That is the situation where you want to drop the index and then add it again after the data is in the table. The idea is that the engine can create the index faster than it can insert one row after another.

One final note. A query typically uses an index when the columns in the index are being referenced in the on, where, or order by. In your example, the index contains a column called my_attribute but the query references col1. The index would not be used for this query.

Upvotes: 1

Related Questions