Why isn't the key being used in the "c" (certificate) table?
SELECT DISTINCT a.uid, a.company, a.firstname, a.lastname
FROM master_info a, logins lsl, logins lc, certificate c WHERE
a.uid = lsl.uid AND lc.parent = lsl.uid AND lc.uid = c.uid AND
c.void <> 1 AND c.status IN
('CP', 'MC', 'AIC', 'RP', 'VQ', 'NQ', 'LQ', 'RQ', 'NG', 'EP', 'AA', 'AD' )
AND lsl.active = "1" AND lsl.void = "0"
ORDER BY company, uid
EXPLAIN:
+-------+--------+--------------------+---------+---------+-----------+-------+---------------------------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+-------+--------+--------------------+---------+---------+-----------+-------+---------------------------------------------+
| c | ALL | uid,status | NULL | NULL | NULL | 11552 | where
used; Using temporary; Using filesort |
| lc | eq_ref | PRIMARY,uid,parent | PRIMARY | 10 | c.uid | 1 | |
| lsl | eq_ref | PRIMARY,uid | PRIMARY | 10 | lc.parent | 1 | where
used |
| a | eq_ref | PRIMARY | PRIMARY | 10 | lsl.uid | 1 | |
+-------+--------+--------------------+---------+---------+-----------+-------+---------------------------------------------+
mysql> show index from certificate;
+-------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Cardinality | Sub_part | Packed | Comment |
+-------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+---------+
+-------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+---------+
| certificate | 0 | PRIMARY | 1 | invoice_num | A| 11552
| NULL | NULL | |
| certificate | 0 | cert_num | 1 | cert_num | A| NULL
| NULL | NULL | |
| certificate | 0 | cert_num | 2 | cert_order | A| 11552
| NULL | NULL | |
| certificate | 1 | uid | 1 | uid | A| NULL
| NULL | NULL | |
| certificate | 1 | status | 1 | status | A| NULL
| NULL | NULL | |
| certificate | 1 | invoice_num | 1 | invoice_num | A| NULL
| NULL | NULL | |
| certificate | 1 | invoice_num | 2 | status | A| NULL
| NULL | NULL | |
| certificate | 1 | x1 | 1 | uid | A| NULL
| NULL | NULL | |
| certificate | 1 | x1 | 2 | status | A| NULL
| NULL | NULL | |
| certificate | 1 | x1 | 3 | void | A| NULL
| NULL | NULL | |
+-------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+---------+
10 rows in set (0.00 sec)
mysql> show index from logins;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Cardinality | Sub_part | Packed | Comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| logins | 0 | PRIMARY | 1 | uid | A | 9091 |
NULL | NULL | |
| logins | 1 | uid | 1 | uid | A | NULL |
NULL | NULL | |
| logins | 1 | parent | 1 | parent | A | NULL |
NULL | NULL | |
| logins | 1 | type | 1 | type | A | NULL |
NULL | NULL | |
| logins | 1 | level | 1 | level | A | NULL |
NULL | NULL | |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
5 rows in set (0.01 sec)
mysql> show index from master_info;
+-------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation|
Cardinality | Sub_part | Packed | Comment |
+-------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| master_info | 0 | PRIMARY | 1 | uid | A| 9078 |
NULL | NULL | |
| master_info | 1 | slbco_idx | 1 | slbco_id | A| NULL |
NULL | NULL | |
+-------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+---------+
2 rows in set (0.00 sec)
The query above is taking much longer than it should. And I cannot
for the life of me figure out why the keys on the certificate table are
not being used.
Any tips as to why would be greatly appreciated!
thnx,
Chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
