Hi all,

See KEY his_ids(his_id)
CREATE TABLE foobar (
my_id smallint(5) UNSIGNED NOT NULL,
his_id mediumint(8) UNSIGNED NOT NULL,
my_name varchar(150),
my_desc text,
PRIMARY KEY (my_id),
KEY his_ids (his_id),
);

in KEY his_ids(his_id) , the Key is also known as index for mysql, my questions are:
1. how does this indexing of his_id help in searching it more efficiently
it says it will stop searching when out of range is reached, can help me visualize this?...

2. Can a type other than integer be index-ed??

thank you

1) An index basically creates a searchable list of whatever is in that column. Think of it like an index you would see at the end of a book which contains keywords and a page number where it can be found.

I found that adding indexes to the ID column will improve your searches by quite a bit. A table with over 200K records took over 9 seconds to find a specific piece of data without any indexes. Once I added the index, the search time for the same piece of data when down to under 2 seconds.

2) Yes, practically any field can be indexed, except for the 'text' and 'blob' field types.

You should be able to use full text indexing on text fields. But yeah, indecis are good on joined columns, columns you search by, and also columns used in order by clause.

Originally posted by Pheaton
1)
2) Hi thanks alot... your real life example of reducing a 9-sec search to a 2 sec one looks indeed promising to index!! :)

So as a rule of thumb, can I say it is advisable to index columns on those "WHERE" clause ??

thanks!

A given query will only use 1 index per table, and only equality (column = ...) and range (< > BETWEEN IN) tests can use an index. So index fields that relate it to other tables (foreign keys) and WHERE clauses that eliminate the most rows.

EXPLAIN SELECT is great for testing how your queries use indexes.

As hiryuu has stated, optimising queries is not as simple as just indexing everything. A carefully crafted set of indexes can provide speed improvements many many many times (perhaps from hours to seconds!), a useless set of indexes won't help you much at all and infact will just slow things down more (when inserting records).

To understand what indexing is - it's a method of accessing.
Databases usually consist of data file and index file. Index file is used to find things fast within data file.
Data file is usually much much larger than index file and it's faster to search trough index file and then just position yourself to where index says you should go (in data file).

To explain how indexed searching is faster in comparision with sequential search, I'll give out this example:

imagine that you have 32 cards in a deck - I take 1 card and you have to ask me questions to know which card I took out. If you go in sequential way, you'd ask questions like - "Is your car ace of spades" - no. "Is your car ace of hearts" - no.
And so on for each card in the deck (meaning you'd at most ask arround 31 question to find out which card i took from the deck).

Indexed searching works like this - "Is your card colored red?" - no. Immedeately, you know that my card is black and you have 16 options left.
Then you ask "Is your card spades?" - yes. You are now down to 8 options (2 questions and you're down to 8 possibilities and still you can use some more filters to get even less options).
I think you got the hang of how indexes work now (and how much faster they can be when you search in a database with millions of records).

I won't go to theory of how indexes are stored, which algorythms are used etc, but as people previously mentioned - use EXPLAIN SELECT, choose indexes carefully and your system will work as fast and as good as it can.

thank you for the detailed explanation !!! :)

here is a speed test i did in another forum to express the importance of index's in large db's

http://www.linux-noob.com/forums/index.php?showtopic=1395

I just created a simple table with something like 8million rows.. and a search for 1 row took like 6 seconds with no index's and under a .1th of a second with them

Originally posted by zupanm
I just created a simple table with something like 8million rows.. and a search for 1 row took like 6 seconds with no index's and under a .1th of a second with them WOw...
thanks zupanm ,
that is indeed a great demo... i am fully convinced.

So it is perfectly alright to alter an existing table(huge table) to add in the index?? It will index those existing data as well??

Yes, it will index the existing data, but it will lock the table while it builds the index. If there's a lot of traffic to the table, the logjam will hurt.

Originally posted by hiryuu
Yes, it will index the existing data, but it will lock the table while it builds the index. If there's a lot of traffic to the table, the logjam will hurt. thx!!
this is exactly what I need!!