Indexes help?

Hello.

I need to speed up my MySQL Table. I do not understand indexes. I have a primary key that already checks to see if there are any duplicates, but I recently read that indexes can speed up the table. If I am only searching one row, should I add an index? How?

Thanks!
yoshi
datera@datera.com http://www.datera.com

the judicious use of indexes can optimize your SELECT speeds greatly. On the other hand, overuse of indexes can use extra processor cycles without speeding up your queries. The key is relevancy.

For example, let's say you have a table called 'users' containing a primary key column called 'user_id', then 'first_name', 'last_name', 'email_address', and 'password'.

Of course you usually want to index your primary key, but since you do a lot of queries based on the user's last name, your next most likely place to put an index is last_name. This is especially relevant if you do many queries that for example show all users with last names beginning with "b", or something like that. You very rarely do searches based on the password column, because the user has already been identified before that point, and your script is just checking the password of the relevant record (based on user_id). Therefore, unless you had a special reason, indexing the 'password' column is not relevant.

So, your table create query would look like:

CREATE TABLE indextest(
user_id INT not null , first_name VARCHAR(30) not null ,
last_name VARCHAR (30) not null , email_address TINYTEXT not null ,
password VARCHAR (12) not null ,
PRIMARY KEY (user_id),
INDEX (user_id, last_name),
UNIQUE (user_id)
)


Now the good stuff:

MySQL let's you index not just a full colunm but also just a prefix of the column (This applies only to CHAR and VARCHAR columns). So you could make the primary key of *just* the first character of the last name. That way, searches based on first letter of the last name are optimized, but no energy is spent further indexing that column.

So:
{table create, etc...}
INDEX (user_id, last_name(1)),
UNIQUE (user_id)
)

would index the first character of last_name. You could do last_name(2) for the first 2 characters, etc...

In MySQL, you can index up to 16 columns in a table, so if your table has complex search needs, you might want to use several indexes. Most of the time it is pointless to index more than two or three columns, though. If you need more, perhaps your table should be split into multiple tables.

There's a lot more to it than what I can describe above, so you should read: http://www.mysql.com/documentation/...nce.html#IDX252

[This message has been edited by rycamor (edited August 09, 2000).]

rycamor,

Thank you so much for the reply. I am using your advice right now.

There are 7 columns in my table in all, but I am always searching in the integer column (there can be more than one row with the same integer). Should I index only that column? How does indexing improve the speed?

Thank you again,
yoshi
datera@datera.com http://www.datera.com

Yes, you should index the integer column, even if it contains duplicates.

It's purely a logical thing: indexing a column will only improve the speed if you are using that column as a basis for your searches, such as
SELECT * FROM mytable WHERE my_indexed_column = 34;

or

SELECT * FROM mytable WHERE my_indexed_column > 22 LIMIT 15;

etc...

Again, read the link I gave you above carefully, because there is much more to this for complicated searches, for example; you can tie indexes of different columns together for 'cross-optimization'.

[This message has been edited by rycamor (edited August 10, 2000).]