Hello,
I'm experincing a problem with my database locking up on some queries.
Any explanation or solutions anyone can provide will be much appreicated.
----------------------------------------------------------------------------
-----
##Scenario: 3 tables: company, co_type_assoc, co_type
##with data pertaining to [co_id in (0,1) as follows]
mysql> select * from co_type;
+------------+------------------+------+----------------------------------+
| co_type_id | co_super_type_id | code | description |
+------------+------------------+------+----------------------------------+
| 1 | 4 | AC | Associate Consultant |
| 2 | 4 | AL | Associate Life Member |
| 3 | 4 | AM | Associate Media |
+------------+------------------+------+----------------------------------+
3 rows in set (0.00 sec)
mysql> select * from company where co_id = 0;
Empty set (0.00 sec)
mysql> select * from company where co_id = 1;
+-------+-------+-------+-----------+-------+---------+----------------+
| co_id | name1 | name2 | sort_name | email | website | pri_co_type_id |
+-------+-------+-------+-----------+-------+---------+----------------+
| 1 | SHOPA | | | | | 1 |
+-------+-------+-------+-----------+-------+---------+----------------+
1 row in set (0.00 sec)
mysql> select * from co_type_assoc where co_id in (0,1);
Empty set (0.00 sec)
----------------------------------------------------------------------------
-----
##When I run the following query, there is no problem:
mysql> Select cta.co_type_id , ct.description
> from company c, co_type_assoc cta, co_type ct
> where c.co_id = cta.co_id
> and ct.co_type_id = cta.co_type_id
> and c.co_id = 1;
----------------------------------------------------------------------------
-----
##But if I run this query, the entire database locks up:
mysql> Select cta.co_type_id , ct.description
-> from company c, co_type_assoc cta, co_type ct
-> where c.co_id = cta.co_id
-> and ct.co_type_id = cta.co_type_id
-> and c.co_id = 0;
ERROR 1015: Can't lock file (errno: -30989)
----------------------------------------------------------------------------
-----
On examing the queries using EXPLAIN, I get the following:
----------------------------------------------------------------------------
-----
mysql> explain
-> Select cta.co_type_id , ct.description
-> from company c, co_type_assoc cta, co_type ct
-> where c.co_id = cta.co_id
-> and ct.co_type_id = cta.co_type_id
-> and c.co_id = 0;
+-----------------------------------------------------+
| Comment |
+-----------------------------------------------------+
| Impossible WHERE noticed after reading const tables |
+-----------------------------------------------------+
----------------------------------------------------------------------------
-----
mysql> explain
-> Select cta.co_type_id , ct.description
-> from company c, co_type_assoc cta, co_type ct
-> where c.co_id = cta.co_id
-> and ct.co_type_id = cta.co_type_id
-> and c.co_id = 1;
+-------+--------+---------------+---------+---------+----------------+-----
-+-------------------------+
| table | type | possible_keys | key | key_len | ref | rows
| Extra |
+-------+--------+---------------+---------+---------+----------------+-----
-+-------------------------+
| c | const | PRIMARY | PRIMARY | 4 | const | 1
| |
| cta | ref | PRIMARY | PRIMARY | 4 | const | 1
| where used; Using index |
| ct | eq_ref | PRIMARY | PRIMARY | 1 | cta.co_type_id | 1
| |
+-------+--------+---------------+---------+---------+----------------+-----
-+-------------------------+
3 rows in set (0.00 sec)
----------------------------------------------------------------------------
-----
So my question is
1) Why does the database lock up ?
2) Is this intentional, i.e. the optimizer is smarter than I want.
3) How can I overcome this problem ?
Any help anyone can provide will be very much appreciated.
Thank you.
Kalok
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <mysql-unsubscribe-##L=##[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
