I am looking into indexing my MySQL table.

Should I index each column that i pick out in my script using the WHERE statement?

I ran this command to index 'picconame':

alter table picco add index(picconame)

Should this be done against all columns in the table access regularly?

Picco

www.crmpicco.co.uk

__________________
_______________________
Ayrshire Minis - a Mini E-Community
http://www.ayrshireminis.com
http://www.crmpicco.co.uk

+---------+------+---------------+-------------+---------+--------+------+------------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+---------+------+---------------+-------------+---------+--------+------+------------------------------+
| picco | ref | picconame | picconame | 101 | const | 2119 | Using where; Using temporary |
+---------+------+---------------+-------------+---------+--------+------+------------------------------+

That is the result I get when I ran the above query with:
EXPLAIN SELECT DISTINCT piccoa FROM `picco` WHERE picconame = 'RFC'

www.crmpicco.co.uk

Hi Picco,

To determine what fields should be indexed, can you please supply:

1) A "describe [tablename]" output for the table
2) Three to five commonly run queries that include the table (especially include queries that join this table to other tables)

Thanks!

Jon Emerson
Adobe Systems, Inc.
http://www.jonemerson.net/

+---------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+--------------+------+-----+---------+-------+
| piccocode | char(3) | YES | | | |
| stadiumname | varchar(100) | YES | | | |
| citycode | char(3) | YES | | | |
| cityname | varchar(100) | YES | | | |
| countrycode | char(2) | YES | | [NULL] | |
| provincecode | char(2) | YES | | [NULL] | |
| provincename | varchar(100) | YES | | | |
| provincecountrycode | char(2) | YES | | [NULL] | |
| picconame | varchar(100) | YES | MUL | | |
| Geog_loc | varchar(100) | YES | | [NULL] | |
| area | varchar(5) | YES | | [NULL] | |
| nationality | varchar(100) | YES | | [NULL] | |
| la | char(1) | YES | | [NULL] | |
| la1 | tinyint(10) | YES | | [NULL] | |
| la2 | tinyint(10) | YES | | [NULL] | |
| la3 | tinyint(10) | YES | | [NULL] | |
| lo | char(1) | YES | | [NULL] | |
| lo1 | tinyint(10) | YES | | [NULL] | |
| lo2 | tinyint(10) | YES | | [NULL] | |
| lo3 | tinyint(10) | YES | | [NULL] | |
+---------------------+--------------+------+-----+---------+-------+


www.crmpicco.co.uk

that is the result of:
some typical queries are
I have already indexed countryname, and this is shown with the MUL (Multiple?) Key above?
Also, in MySQL CC there is a green diamond beside the column.


www.crmpicco.co.uk

Also, there are no joins (inner or outer) run on this table

www.crmpicco.co.uk