indexing columns

[LordRogaine]

I'm using php with mysql database. I'm trying to index my database as efficiently as possible but there are a few things I don't understand fully. First off, I know not to index too many columns. when I call up the command to show indexes from my_table there are a few columns headings I'm fuzzy on.

First one is - Seq_in_index
I noticed that every index is marked in the field as one "1". I'm assuming that somehow it would be beneficial for me to change the numbering sequece to better sort through things. Am I wrong here? If not what is the command to change the number sequence?

Second is - Cardinality
I see that I have different numbers in these fields ranging from 3 to the total amount of members I have in this particular table. Is there something I should be doing here?

Third is - Collation
Every field has an "A" in it. Something I need to do here?

Fourth and last - sub_part
When creating two of the indexes, I read that it is sometimes beneficial to do something like this.. alter table table_name add index aaa(column(10)); This supposedly makes the sorting faster.

I know these are a lot of questions but I can' seem to find anything to read that answers them clearly for me to understand. ANY help to explain this better to me would be greatly appreciated.

Lord Rogaine

[eoghain]

First off I'd suggest that you forget/ignore all of the stuff in your original post.

The best way to optomize your database with indexes is to run your queries through the explain process. This will tell you where you need indexes.

As for the number of indexes on a table the only reason to keep the number down is to speed up insert/update statements. The more indexes you have the better chance you have that any given query will use them.

So here is a brief explain of a query and how to analyze it:
Query:

SELECT aae.AutoAttendantEventID,
                         h.Name,
                         aae.EventDateTime,
                         aae.Port,
                         aas.DTMF,
                         aas.MenuName
                    FROM HierarchyNodes h
                   INNER JOIN AutoAttendantEvents aae 
                      ON (h.HierarchyNodeID = aae.StoreID)
                   INNER JOIN AutoAttendantStages aas
                      ON (aae.AutoAttendantStageID = aas.AutoAttendantStageID)
                   WHERE h.HierarchyNodeID IN (19,26,27)
                     AND aae.EventDateTime >= "2004-03-01"
                     AND aae.EventDateTime < "2004-04-01";

This query selects from 3 of my tables to get it's data.
Explain

Table:		h
Type:		range
Possible_Keys:	PRIMARY
Key:		PRIMARY
Key_Len:	4
Ref:		
Rows:		3
Extra:		Using where

Table:		aae
Type:		ref
Possible_Keys:	IDX_AutoAttendantEvents_1,
		IDX_AutoAttendantEvents_3
Key:		IDX_AutoAttendantEvents_3
Key_Len:	4
Ref:		h.HierarchyNodeID
Rows:		30126
Extra:		Using where

Table:		aas
Type:		eq_ref
Possible_Keys:	PRIMARY
Key:		PRIMARY
Key_Len:	4
Ref:		aae.AutoAttendantStageID
Rows:		1
Extra:		

Now I've already optomized this query, but the things to look for in an Explain are 1 the order of the tables, as you can see this explains that the query processor will use HierarchyNodes, AutoAttendantEvents and then AutoAttendantStages. This is very importat because when you look at the rows field it will tell you how the query processor is going to work.

For this query the first thing that will happen is 3 rows will be selected from HierarchyNodes using the Primary Key index, and constrained by the where clause. Now for each of those rows a query will be run against the AutoAttendantEvents table using IDX_AutoAttendantEvents_3 (which is an index I have on the StoreID field) and 30126 rows will be returned, also restrained by the where clause. Finally for each of those 30126 rows a query will be run against the AutoAttendantStages table each returning 1 row.

As you can see for this 1 query the database is doing quite a bit of work. But because of the indexes and the design of the query I can run this and get 59534 total rows in 0.3524 sec.

When designing indexes for your tables it is best to understand what sets of data make up unique rows in your table, and build an index for them, in the order that you think they will most likely appear in the where clause. If you have a table with 3 columns (col1, col2, col3) and you index them all in a single index listed as (col3, col2, col1) and then write your queries using where col1=x and col2=y and col3=z then your index will never get used. So it's always best to profile your queries with Explain.

I hope this makes sence and was of some help to you.

[LordRogaine]

I'm a little fuzzy on some things, but this is going to help me immensely. Thank you very much. I'll let you know if I have anymore questions. Thanks again. I appreciate it.