This is the unadulterated material, complete with Hebrew table names!
mysql> describe sochnim;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | mediumint(9) | | PRI | NULL | auto_increment |
| name | char(24) | | MUL | | |
| passkey | char(8) | YES | | NULL | |
| email | char(12) | YES | | NULL | |
+---------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> show index from sochnim;
+---------+------------+----------+--------------+-------------+-----------+
-------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Cardinality | Sub_part | Packed | Comment |
+---------+------------+----------+--------------+-------------+-----------+
-------------+----------+--------+---------+
| sochnim | 0 | PRIMARY | 1 | id | A |
8 | NULL | NULL | |
| sochnim | 1 | i_name | 1 | name | A |
8 | NULL | NULL | |
+---------+------------+----------+--------------+-------------+-----------+
-------------+----------+--------+---------+
2 rows in set (0.01 sec)
mysql> describe status;
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| id | mediumint(9) | | PRI | 0 | |
| name | char(24) | | MUL | | |
| displayorder | tinyint(4) | YES | MUL | NULL | |
+--------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> show index from status;
+--------+------------+-----------+--------------+--------------+-----------
+-------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation
| Cardinality | Sub_part | Packed | Comment |
+--------+------------+-----------+--------------+--------------+-----------
+-------------+----------+--------+---------+
| status | 0 | PRIMARY | 1 | id | A
| 5 | NULL | NULL | |
| status | 1 | i_display | 1 | displayorder | A
| 5 | NULL | NULL | |
| status | 1 | i_name | 1 | name | A
| 5 | NULL | NULL | |
+--------+------------+-----------+--------------+--------------+-----------
+-------------+----------+--------+---------+
3 rows in set (0.00 sec)
mysql> describe projects;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | mediumint(9) | | PRI | NULL | auto_increment |
| name | char(32) | | MUL | | |
| sochen | mediumint(9) | YES | MUL | NULL | |
| schum | bigint(20) | | | 0 | |
| status | tinyint(4) | YES | MUL | NULL | |
| adrichal | mediumint(9) | YES | MUL | NULL | |
| customer | mediumint(9) | YES | MUL | NULL | |
| managers | mediumint(9) | YES | MUL | NULL | |
| openeddate | date | YES | MUL | NULL | |
| targetdate | date | YES | | NULL | |
| matzav | tinyint(4) | YES | MUL | NULL | |
| closeddate | date | YES | MUL | NULL | |
| closedmonth | mediumint(9) | YES | | NULL | |
| reason | tinyint(4) | YES | | NULL | |
| ordered | tinyint(4) | YES | | NULL | |
| sug | tinyint(4) | YES | MUL | NULL | |
| comments | mediumint(9) | YES | | NULL | |
| sicui | tinyint(4) | YES | | NULL | |
| hazadit | char(8) | YES | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
19 rows in set (0.00 sec)
mysql> show index from projects;
+----------+------------+------------+--------------+-------------+---------
--+-------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Comment |
+----------+------------+------------+--------------+-------------+---------
--+-------------+----------+--------+---------+
| projects | 0 | PRIMARY | 1 | id | A
| 1334 | NULL | NULL | |
| projects | 1 | i_matzav | 1 | matzav | A
| 2 | NULL | NULL | |
| projects | 1 | i_sug | 1 | sug | A
| 2 | NULL | NULL | |
| projects | 1 | i_sochen | 1 | sochen | A
| 7 | NULL | NULL | |
| projects | 1 | i_adrichal | 1 | adrichal | A
| 266 | NULL | NULL | |
| projects | 1 | i_customer | 1 | customer | A
| 333 | NULL | NULL | |
| projects | 1 | i_managers | 1 | managers | A
| 133 | NULL | NULL | |
| projects | 1 | i_name | 1 | name | A
| 1334 | NULL | NULL | |
| projects | 1 | i_odate | 1 | openeddate | A
| 190 | NULL | NULL | |
| projects | 1 | i_cdate | 1 | closeddate | A
| 190 | NULL | NULL | |
| projects | 1 | i_status | 1 | status | A
| 4 | NULL | NULL | |
+----------+------------+------------+--------------+-------------+---------
--+-------------+----------+--------+---------+
11 rows in set (0.00 sec)
{ What does the cardinality mean? There should be three values for matzav,
three for sug and five for status. Is this zero based? }
mysql> explain select p.id, p.name, p.schum, s.name, sochnim.name
-> from projects p, status s, sochnim
-> where p.matzav = 0
-> and p.status = s.id
-> and p.sochen = sochnim.id
-> order by p.name;
+---------+--------+----------------------------+----------+---------+------
----+------+---------------------------------+
| table | type | possible_keys | key | key_len | ref
| rows | Extra |
+---------+--------+----------------------------+----------+---------+------
----+------+---------------------------------+
| p | ref | i_matzav,i_sochen,i_status | i_matzav | 2 | const
| 353 | Using temporary; Using filesort |
| s | ALL | PRIMARY | NULL | NULL | NULL
| 5 | where used |
| sochnim | eq_ref | PRIMARY | PRIMARY | 3 |
p.sochen | 1 | |
+---------+--------+----------------------------+----------+---------+------
----+------+---------------------------------+
3 rows in set (0.00 sec)
-----Original Message-----
From: Roger Baklund [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 20, 2004 3:33 PM
To:
Cc: Noamn
Subject: Re: Improving a query
* Noamn
> I have a query which was written like this
> select p.id, p.name, p.money, s.name, agents.name
> from projects p, status s, agents
> where p.stage = 0
> and p.status = s.id
> and p.agent = agents.id
> order by p.name
>
> Despite the fact that p.name is indexed, 'explain' shows in the
> extra column
> 'using temporary; using filesort'. I don't understand why.
Could you show us the output of this EXPLAIN, and also SHOW CREATE TABLE on
projects, status and actors?
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
