indexes

[matjaz]

i have never bothered with creating indexes with multiple columns, like this:

index "person"; [name+surname]

instead of that i created 2 indexes
"name" [name]
"surname" [surname]

my question is: is it better to create a "combined" index if i know that i will be doing comarison by name and surname at the same time (same query) or not? when would you create such index?

what is bothering me is the fact that in one project i have 1000 different queries and if i would want to create all possible "combined" indexes, i would probably go crazy.

thanks and a nice day from New Orleans
[going back to Europe soon]

p.s. to make the day more interesting: where are you guys from?

[nemonoman]

MySQL tries to optimize your queries by finding indexed columns.

Example: Suppose you have a 200000 order records that you'll be searching by customer id and order date.

You could index the customer id field. MySQL would optimize its search to find the rows with the required customer id. Presumably there would not be a large number of rows for next part of the search, by date.

Basically, you want to index a column if there are a large number of rows and if you'll be searching by that column value on a regular basis. In the example above, you can see that a customer id search might be a regular event.

Some tables have 'compound keys': For example, suppose you sold stuff to big companies that had lots of different buyers. You might have a 'CompanyBuyer' table where there was a compound key of 'companyID' and BuyerID. If there were enough rows to warrant it (you'd know this if your searches take too long to return values), you'd want to do a compound indiex.

As a rule, you'll index the primary key of tables with a lot of rows, and you'll index columns where these values are 'foreign keys':

Example: I'd index the ID column of my Customer table, and I index the CustomerID column of my Order table. I index the ID column of my INvoice table, and I index the OrderID column of the INnvoice table.