index a field

________

Hi,
I hava a database with a lot of rows - so I am thinking about indexing a field, there field that most of the querys search.
I run a test on a test database, and the query was faster.

What is the disadvantages of indexing a field?
Does anyone know of any good tutorials of articles about indexing fields? I tried google.com - but I only cound tutorials with wery little facts.

On the test database, some querys took 0.00 sec, but sometimes when I changes the search it took 0.5 seconds? Does anyone know why?

😕 😕 😕 😕

paulnaj

The principle of an field index is the same as a card index in a library, say.

A book has 'fields': Title, Author, ISBN, Category (etc).

Suppose the library is arranged by, say, only the author. If you only knew the title of the book, then you would look up the title in an alphabetical listing of the titles, find out who the author is, and then be able to locate the book on the shelves.

This is faster than looking at every book in the library until you find it, but still, if it was a big library, you'd still take longer than if the library was 'broken up' into categories, say. If the libray was quite small, it wouldn't take too long.

So, whether or not an index is efficient or not depends on how much many records there are to search, and on how many fields you are likely to search on.

In the end, you have to test. If the test-time between having index or not is negligible for a sizeable amount of records, it's probably not worth the effort.

Hope that helps a bit.

P.S. In order to get a good idea of how long a test takes, do the same test many times and take the average. There are a lot of factors involved (mysql, server, browser, op.sys, connection ...).

________

So by indexing a field, the SQL database only sorts it?

If I use WHERE on the catagory, and I know that the ISBN is over 200 - it will make the search faster?

paulnaj

Not quite sure what you're asking there.

You have to ask mysql to return a sorted list, it doesn't do it automatically.

Indexing helps mysql to find the records in the query by not having to look at every single record (subject to what I said before about the number of records in a table).

Your example wasn't really clear either:

Is this what you meant?
SELECT *
FROM books
WHERE ISBN > 200

This will return all books that have ISBN greater than 200.

I won't be able to respond for an hour or two, but I will get back.
P.

paulnaj

Had a minute to think.

When first starting out with databases, people often get a bit caught up with indices.

But, in a way, it should be easy. Most tables, if not all, will have a Primary Key ... that is, a not-null, unique identifier for each record. This is automatically an index.

For a lot of tables, no other index is necessary. See if that's the case for you. See how you get on without any more. Once things are up and running, you might test a index on a field.

________

Thanks!

Is it possible to undo a index?

paulnaj

I assume you mean ...

Can you add/drop an index to/from an existing field?

The answer is yes. Have a look at the ALTER TABLE syntax in the manual.

This will at least give you an idea of what you can do to a table whilst it's up and running.

I hasten to add ... be careful with these commands if you're working on live data ... they can't be undone.

Also, have a look at getting yourself a front-end for mysql ... it's a lot easier to create/alter.
Depends on what platform you're using but here's the one they list on the mysql site.

Edit

mysql.com acting up a bit ... try
here for an alternative download of ControlCenter.

Sxooter

Just an FYI for the new guy. Indexes can speed up selective selects like 'select * from table where id between 100 and 150' or something like that. however, every insert or update to the table will involve updates to the index, which takes time. So, you don't want to have indexes that aren't used costing you on inserts.