After I ran some database updates last night, it seems to be working OK again today. Not sure what happened! Thanks anyway.
JS.
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]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
