Hi.
Uncommon situation. Please tell me, what OS do you use, MySQL version and how was it compiled? Is there any clues at the end of .err file? "J S" <[EMAIL PROTECTED]> wrote: > 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) > > > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
