Hi,
I have this query which used to work really fast but now it just seems to lock up the tables. I tried running it with EXPLAIN but it just hangs at the command prompt. I also tried adding the USE INDEX directives but still no luck. I've run myisamchk -m on both tables involved and no errors were reported so I'm really at a loss to why this has gone wrong. Can anybody help me out please?
Thanks, JS.
mysql> SELECT DISTINCT uv.urlid FROM url_visit uv USE INDEX(url_server_ID), url_servers us
-> use index(ID) WHERE us.server LIKE "%java%" AND uv.url_server_ID=us.ID;
mysql> desc url_servers; +--------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+------------------+------+-----+---------+----------------+ | ID | int(10) unsigned | | MUL | NULL | auto_increment | | server | varchar(255) | | PRI | | | +--------+------------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec)
mysql> show indexes from url_servers;
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| url_servers | 0 | PRIMARY | 1 | server | A | 279599 | NULL | NULL | | BTREE | |
| url_servers | 1 | ID | 1 | ID | A | 279599 | NULL | NULL | | BTREE | |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)
mysql> desc url_visit;
+-----------------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+----------------------+------+-----+---------+----------------+
| urlid | int(10) unsigned | | PRI | NULL | auto_increment |
| url_scheme_ID | tinyint(3) unsigned | | | 0 | |
| url_server_ID | int(10) unsigned | | MUL | 0 | |
| url_path_ID | int(10) unsigned | | | 0 | |
| url_query_ID | int(10) unsigned | | | 0 | |
| url_category_ID | smallint(5) unsigned | | | 0 | |
+-----------------+----------------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)
mysql> show indexes from url_visit;
+-----------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| url_visit | 0 | PRIMARY | 1 | urlid | A | 25881342 | NULL | NULL | | BTREE | |
| url_visit | 0 | url_server_ID | 1 | url_server_ID | A | 278294 | NULL | NULL | | BTREE | |
| url_visit | 0 | url_server_ID | 2 | url_path_ID | A | 12940671 | NULL | NULL | | BTREE | |
| url_visit | 0 | url_server_ID | 3 | url_query_ID | A | 25881342 | NULL | NULL | | BTREE | |
| url_visit | 0 | url_server_ID | 4 | url_scheme_ID | A | 25881342 | NULL | NULL | | BTREE | |
+-----------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in set (0.00 sec)
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
