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 clau
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 INSERT
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
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 would
annot tell you how it would optimize the
> query, because the query returns in an early stage with an empty
> result set.
>
> Maybe I didn't understand your objection completely. But what I see
> seems perfectly reasonable to me.
>
> Bye,
>
> Benjamin.
>
> 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
&g
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
>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
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 sear
> Shouldn't the first query have parenthesis? As in:
> DELETE FROM test WHERE userID=X AND (testID<20 OR
> testID>80);
> 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
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 popul
ilivojevic" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Saturday, January 20, 2001 5:27 AM
Subject: Re: temp tables lock unrelated tables
> Michael Griffith writes:
> > Cacheing before writing sounds like exact
Cacheing before writing sounds like exactly the type of thing that would
explain the problem. However, I'm on FreeBSD (Sorry, didn't give too many
details). Anybody know if there is something similar in to bdflush BSD?
I did run iostat to monitor the the activity during the freeze. It appears
th
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
is
| 23360 |
+--+----+
> 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:
> &g
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..
16 matches
Mail list logo
