Hi,
I'm running MySQL 5.0.51a-24+lenny3-log on a Debian Lenny box with
2Gb of RAM and a pair of dual core 2.6Ghz CPUs. I'm using a pair of
40Gb disks mirrored using MD (Linux software RAID) for both the OS and
the storage of the mysql DBs.
My problem child is doing this simple query:
mysql> select distinct Call_No from Newspaper_Issues
mysql> WHERE BIB_ID = 464;
+----------+
| Call_No |
+----------+
| News |
| NewsD CT |
+----------+
2 rows in set (2.98 sec)
The Newspaper_Issues table has 1.3 million rows, and has a structure
like this:
mysql> describe Newspaper_Issues;
+----------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+----------------+
| Record_No | int(11) | NO | PRI | NULL | auto_increment |
| BIB_ID | varchar(38) | NO | MUL | NULL | |
| Issue_Date | date | NO | MUL | NULL | |
| Type_Code | char(1) | NO | | r | |
| Condition_Code | char(1) | NO | | o | |
| Format_Code | char(1) | NO | | p | |
| Edition_Code | char(1) | NO | | n | |
| Date_Type_Code | char(1) | NO | | n | |
| Ed_Type | tinyint(1) | NO | | 1 | |
| RS_Code | char(1) | NO | | c | |
| Call_No | varchar(36) | YES | MUL | NULL | |
| Printed_Date | varchar(10) | YES | | NULL | |
| Update_Date | date | NO | | NULL | |
+----------------+-------------+------+-----+---------+----------------+
13 rows in set (0.00 sec)
I've tried adding various indexes, and reading up on howto optimize
DISTINCT or GROUP BY queries, but I'm hitting a wall here. My current indexes
are:
mysql> show index from Newspaper_Issues;
+------------------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index |
Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type |
Comment |
+------------------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Newspaper_Issues | 0 | PRIMARY | 1 |
Record_No | A | 1333298 | NULL | NULL | | BTREE |
|
| Newspaper_Issues | 1 | BIB_ID | 1 | BIB_ID
| A | 14980 | NULL | NULL | | BTREE |
|
| Newspaper_Issues | 1 | Call_No | 1 | Call_No
| A | 927 | NULL | NULL | YES | BTREE |
|
| Newspaper_Issues | 1 | Issue_Date | 1 |
Issue_Date | A | 49381 | NULL | NULL | | BTREE |
|
| Newspaper_Issues | 1 | BIB_ID_Issue_Date | 1 | BIB_ID
| A | 14980 | NULL | NULL | | BTREE |
|
| Newspaper_Issues | 1 | BIB_ID_Issue_Date | 2 |
Issue_Date | A | 1333298 | NULL | NULL | | BTREE |
|
| Newspaper_Issues | 1 | call_no_short | 1 | Call_No
| A | 30 | 6 | NULL | YES | BTREE |
|
| Newspaper_Issues | 1 | BIB_ID_call_no_short | 1 | BIB_ID
| A | 14980 | NULL | NULL | | BTREE |
|
| Newspaper_Issues | 1 | BIB_ID_call_no_short | 2 | Call_No
| A | 15503 | NULL | NULL | YES | BTREE |
|
| Newspaper_Issues | 1 | call_no_bib_id | 1 | Call_No
| A | 927 | NULL | NULL | YES | BTREE |
|
| Newspaper_Issues | 1 | call_no_bib_id | 2 | BIB_ID
| A | 15503 | NULL | NULL | | BTREE |
|
+------------------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
11 rows in set (0.00 sec)
So now when I do an explain on my query I get:
mysql> explain select distinct(Call_No) from Newspaper_Issues WHERE BIB_ID
= 464;
+----+-------------+------------------+-------+-----------------------------------------------+----------------+---------+------+---------+--------------------------+
| id | select_type | table | type | possible_keys
| key | key_len | ref | rows | Extra
|
+----+-------------+------------------+-------+-----------------------------------------------+----------------+---------+------+---------+--------------------------+
| 1 | SIMPLE | Newspaper_Issues | index |
BIB_ID,BIB_ID_Issue_Date,BIB_ID_call_no_short | call_no_bib_id | 227 | NULL
| 1333298 | Using where; Using index |
+----+-------------+------------------+-------+-----------------------------------------------+----------------+---------+------+---------+--------------------------+
1 row in set (0.00 sec)
Interestingly enough, I get much better performance if I just drop the WHERE
clause, but that doesn't help me get what I want though. *grin*
mysql> explain select distinct(Call_No) from Newspaper_Issues;
+----+-------------+------------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+----+-------------+------------------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | Newspaper_Issues | range | NULL | Call_No |
111 | NULL | 928 | Using index for group-by |
+----+-------------+------------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
Would it make sense to split the Call_No data off into it's own table, and put
in a proper numeric ID, instead of the current VARCHAR(36) it uses? So in
Newspaper_Issues I'd just have a Call_No_ID and a new Call_No table with:
Call_No_ID INT
Call_No Char(36)
which would hopefully index better? I only have 928 distinct Call_No strings,
so I'm not afraid of wasting space or anything.
Thanks,
John
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[email protected]
