Hi all,
I've been working on optimizing my query but no success. Concerning the
type of query shown by "explain select", about join type "ALL" in the
manual is written: "This is normally not good if the table is the first
table not marked const, and usually very bad in all other cases." So, I
added indexes on all columns involved in join but "explain select" still
shows the same result. Am I missing something or this is inevitable and
can't be optimized. Below are some snippets.
mysql> describe firm;
+------------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default |
Extra |
+------------+----------------------+------+-----+---------+----------------+
| fikey | smallint(5) unsigned | | MUL | 0 |
auto_increment |
| finame | varchar(60) binary | | MUL |
| |
| ficity | varchar(30) | | MUL |
| |
| fiaddress | varchar(60) | YES | | NULL
| |
| fiphone | varchar(60) | YES | | NULL
| |
| fiuserid | smallint(5) unsigned | | MUL | 0
| |
+------------+----------------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)
mysql> describe product;
+------------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default |
Extra |
+------------+----------------------+------+-----+---------+----------------+
| prkey | smallint(5) unsigned | | MUL | 0 |
auto_increment |
| prfikey | smallint(5) unsigned | | MUL | 0
| |
| pritem | varchar(100) | | MUL |
| |
| prproducer | varchar(80) | YES | | NULL
| |
+------------+----------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> describe users;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default |
Extra |
+----------+----------------------+------+-----+---------+----------------+
| userid | smallint(5) unsigned | | MUL | 0 |
auto_increment |
| username | varchar(20) | YES | | NULL
| |
| userpass | varchar(20) | YES | | NULL
| |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> explain select finame, pritem, username from firm, product, users
where fiuserid=userid and fikey=prfikey;
+---------+------+----------------+------+---------+------+------+----------------------------------------------+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+---------+------+----------------+------+---------+------+------+----------------------------------------------+
| firm | ALL | fikey,fiuserid | NULL | NULL | NULL | 11
| |
| product | ALL | prfikey | NULL | NULL | NULL | 25 | range
checked for each record (index map: 2) |
| users | ALL | userid | NULL | NULL | NULL | 96 | range
checked for each record (index map: 1) |
+---------+------+----------------+------+---------+------+------+----------------------------------------------+
3 rows in set (0.00 sec)
I have the indexes, tried STRAIGHT_JOIN and *all* permutations of tables
in FROM clause
possible, but still the same result. It is said in the manual that the
query will examine 11*25*96 rows to get the result set. I must optimize
this, otherwise when records in these three tables grow, it will take
too much time to execute.
Thanks in advance,
Dimitar
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
