Questions about indexing custom tables in db
 

some of my queries on custom tables tabe a while to load, I know I could definitely create some additional indexes (other than just the primary id) on them to increase performance, but am not really sure which ones. Generally speaking, is it whichever columns are being used in LEFT JOINs or is there something more to it. I was looking for some documentation on mysql.com or here about when it's recommended to create additional indexes but can't seem to find any.

Perfect example, in this thread I had a table which had more than 60,000 records, each having one of 100 different entries. Until Sir Adrian and PaulM brought it to my attention, i had no idea that the zipcode column needed to be indexed as well.

So basically my question (to get to the point) is "when should I know that another column on my table needs to be indexed, and how do I know which one(s)?".

anyone?

Use the SQL explain syntax to view how a query is being run. Generally you need to look at columns you are sorting on (ORDER BY) or columns used in a WHERE, and columns used for matching in JOINS.

Thanks for tips paul, so generally speaking anytime columns are listed in db queries (big ones), they should be indexed?

For instance...
I attached a screenshot of explain view for the big query and I notice in the "extra" column sometimes it says one (or more) of the following:
using where
using temporary
using filesort
using index

What exactly does this tell me? Does it tell me that I need to index the tables which are not "using index" ?