hi.
ok, first actual question to the list, hope someone can help:
the following query -
select
pn.number,
c.ID,
c.cost,
c.days_valid,
c.description
from
pinnumbers pn,
cardtype c
where
pn.cardtypeID = c.ID and
pn.tmaster = 0
order by c.ID, pn.number
returns: 55033 rows in set (4 min 10.11 sec)
pinnumbers has only about 80000 rows, and cardtype has 2 rows.
an explain shows this:
+-------+------+---------------+--------+---------+-------+-------+---------
------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+-------+------+---------------+--------+---------+-------+-------+---------
------------------------+
| pn | ref | tmster | tmster | 5 | const | 67580 | Using
temporary; Using filesort |
| c | ALL | PRIMARY | NULL | NULL | NULL | 2 | where
used |
+-------+------+---------------+--------+---------+-------+-------+---------
------------------------+
if i replace the first part of the query with "select count(pn.number)
from...", i get:
+------------------+
| count(pn.number) |
+------------------+
| 55033 |
+------------------+
1 row in set (0.81 sec)
pretty fast.
what's making it take so long? the join? as stated before, there are only 2
rows in cardtype.
output from show index as follows:
mysql> show index from pinnumbers;
+------------+------------+----------+--------------+-------------+---------
--+-------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Comment |
+------------+------------+----------+--------------+-------------+---------
--+-------------+----------+--------+---------+
| pinnumbers | 1 | tmster | 1 | tmaster | A
| NULL | NULL | NULL | NULL |
| pinnumbers | 0 | PRIMARY | 1 | number | A
| 80000 | NULL | NULL | NULL |
+------------+------------+----------+--------------+-------------+---------
--+-------------+----------+--------+---------+
2 rows in set (0.00 sec)
mysql> show index from cardtype;
+----------+------------+----------+--------------+-------------+-----------
+-------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation
| Cardinality | Sub_part | Packed | Comment |
+----------+------------+----------+--------------+-------------+-----------
+-------------+----------+--------+---------+
| cardtype | 0 | PRIMARY | 1 | ID | A
| 2 | NULL | NULL | NULL |
+----------+------------+----------+--------------+-------------+-----------
+-------------+----------+--------+---------+
1 row in set (0.00 sec)
any input would be gratefully appreciated, if the table descriptions would
help, i'll post those.
this seems like an easy join, but i'm confused as to what to do to speed it
up.
-ravi.
