Indexes are your friend. Perhaps your best friend (as far as databases go).... --------------------------- To give light on your question... you can index any field... if the entries are distinct, it's called a 'unique index' which are the best kind to use. Otherwise you have a 'non-unique index', which can also be handy....
Think of indexes, like... well... book indexes... to prevent you from looking all over the book for an answer, you can find the exact page the topic is on (unique index), or if it's covered in multiple pages, you reduce your searching to just that list of pages (non-unique index).
Then there's compound indexes, which is an index on the value of two seperate fields (which can be unique, or non-unique).
As a rule of thumb, you want indexes on your unique key (primary key) (call record id) Most db's make indexes automatically on keys you say are the primary key. Also on your most commonly searched fields (usually non-unique) such as foreign keys (call record id in your record notes table for example) --------------------------
Ok, I've found the optimization chapter in the manual and I'm still reading it.
Here's what I've done so far:
mysql> alter table traf_oper add index (tel), add index (telefb);
mysql> explain select tel, telefb, rutaentran, rutasalien, sum(minutos) as minutos from traf_oper group by 1, 2, 3, 4;
+----+-------------+-----------+------+---------------+------+---------+------+---------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+---------+---------------------------------+
| 1 | SIMPLE | traf_oper | ALL | NULL | NULL | NULL | NULL | 5014313 | Using temporary; Using filesort |
+----+-------------+-----------+------+---------------+------+---------+------+---------+---------------------------------+
1 row in set (0.50 sec)
mysql> describe traf_oper; +------------+----------+-------------------+------+-----+---------+-------+ | Field | Type | Collation | Null | Key | Default | Extra | +------------+----------+-------------------+------+-----+---------+-------+ | tel | char(8) | latin1_swedish_ci | YES | MUL | NULL | | | fecha | char(8) | latin1_swedish_ci | YES | | NULL | | | hora | char(6) | latin1_swedish_ci | YES | | NULL | | | telefb | char(14) | latin1_swedish_ci | YES | MUL | NULL | | | tiempotasa | char(6) | latin1_swedish_ci | YES | | NULL | | | rutasalien | char(7) | latin1_swedish_ci | YES | | NULL | | | rutaentran | char(7) | latin1_swedish_ci | YES | | NULL | | | serie | char(3) | latin1_swedish_ci | YES | | NULL | | | tipotraf | int(1) | binary | YES | | NULL | | | minutos | int(4) | binary | YES | | NULL | | +------------+----------+-------------------+------+-----+---------+-------+ 10 rows in set (0.00 sec)
So, why isn't SELECT using indexes (key = NULL, key_len = NULL)? Where else do I need to add indexes? Thanks for your help!
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
