Personally I feel that I have a pretty good understanding on MySQL locking,
however I am confused by the output I've received from SHOW PROCESSLIST at
seemingly random times.
At the end of this message is the output from SHOW FULL PROCESSLIST. ALL
queries from output are shown. ALL WHERE clauses
The MySQL documentation says:
To ensure that the update log/binary log can be used to re-create the
original tables, MySQL will not allow concurrent inserts during INSERT
SELECT
Does this mean:
A.
An INSERT...SELECT cannot run if any other SELECT is running on the table
OR
B.
An
What could be a possible cause for DB connections that do not close? I have
a Apache-PHP-mysql setup and the apache/php thread appears to exit, but
show processlist report connections that stay open and sleep
Is there a way to determine what was run on that connection that may have
caused it to
with an empty
result set.
Maybe I didn't understand your objection completely. But what I see
seems perfectly reasonable to me.
Bye,
Benjamin.
"Michael Griffith" [EMAIL PROTECTED] wrote:
EXPLAIN LEFT JOIN fails when joining on an empty table with constant
in
WH
I appreciate your explanation. You may be correct about Standard SQL,
however I do not believe that this can be explained as a simple
cross-product. If a LEFT JOIN were implemented starting with a cross product
then NO LEFT JOIN would EVER return NULL in the right table. A pure cross
product
EXPLAIN LEFT JOIN fails when joining on an empty table with constant in
WHERE clause
CREATE TABLE idx (
id mediumint,
KEY id (id),
) TYPE=MyISAM
CREATE TABLE hist (
id mediumint,
link mediumint,
KEY id (id,link)
) TYPE=MyISAM
Table idx is populated with thousands or records. Consider
hist.id will never be NULL and 5 at the same time. Your clauses conflict
with each other.
You need to re-write your query.
This is ture, except the query suceeds if there is records in 'hist'
Even if it is not a bug it is at least inconsistent behaviour of MySQL
"Michael Gri
Can anyone explain why LIKE queries do not use an index when the LIKE string contains
a forward slash?
Example:
CREATE TABLE words ( word varchar(250) PRIMARY KEY );
Populate table with 200,000+ rows
EXPLAIN SELECT * FROM words WHERE word LIKE 'abcdef';
This query uses the Primary key to
Can anyone explain why LIKE queries do not use an index when the
LIKE string contains a forward slash?
Follow-up to my own post:
The problem is with the '_' wildcard, but I still don't understand. My
previous examples were simplified, but here is the exact query:
SELECT * FROM words WHERE word
When using mySQL I've learned to avoid OR in any queries as much as possible
. Almost always this causes a major speed decrease. Consider this table:
CREATE TABLE test(
userID int, # (non-unique)
testID int, # (non-unique)
PRIMARY key(testid,userid)
);
Suppose this table is
Shouldn't the first query have parenthesis? As in:
DELETE FROM test WHERE userID=X AND (testID20 OR
testID80);
Even if it works the other way, parenthesis make it more clear what
you're trying to accomplish.
-Angela
Actually, yes. Sorry about the poor example. My point is that
Using SHOW PROCESSLIST or mysqladmin proc
Every time a table reports status of 'copying to tmp table' all other UPDATE queries
are locked, even in unrelated tables.
For example:
Query #1: SELECT * FROM a JOIN B WHERE Status: copying to tmp table
Query #2: UPDATE C SET x=x+1 WHERE..
|
+--++
Michael Griffith writes:
Using SHOW PROCESSLIST or mysqladmin proc
Every time a table reports status of 'copying to tmp table' all other
UPDATE queries are locked, even in unrelated tables.
For example:
Query #1: SELECT * FROM a JOIN B WHERE Status: copying to tmp
Earlier I posted a message about SHOW PROCESSLIST reporting queries "locked" whenever
another thread is "Copying to tmp table"
After many more hours of diagnosis, the actual problem is somewhat different:
MySQL does report other processes as locked. But they do not wait until the temp table
14 matches
Mail list logo
