getGeneratedKeys
I have a stored procedure that inserts a record that I call from a PreparedStatement. When I call #getGeneratedKeys it always returns a null result set. Is that expected behavior? Robert DiFalco | Chief Technology Officer (Products) Direct: 503.276.7564 Mobile: 503.890.4994 Charlotte Caswell | Executive Assistant Direct: 503.276.7665 TRIPWIRE | The Leader in Configuration Audit Control Check out the latest Tripwire news! http://www.tripwire.com/press/index.cfm
Indices in InnoDB/MySQL
I've been told that an index always contains the primary key. So if I have a LONG ID that is the primary key of table and create on index on LONG VALUE, the index on LONG VALUE will actually work as a typical compound index on ID,VALUE. My question is this, if I don't know that about MySQL and create my indices on purpose (i.e. not taking into account implementation details about MySQL) I would create the index explicitly on ID,VALUE. If I did that would I effectively have the same index as one on just VALUE or would there some how be duplicate data in the ID,VALUE index (i.e. ID, ID, VALUE)? R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Indices in InnoDB/MySQL
There is already a primary key index on ID. -Original Message- From: Wm Mussatto [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 01, 2008 10:50 AM To: mysql@lists.mysql.com Subject: Re: Indices in InnoDB/MySQL On Tue, April 1, 2008 10:01, Robert DiFalco wrote: I've been told that an index always contains the primary key. So if I have a LONG ID that is the primary key of table and create on index on LONG VALUE, the index on LONG VALUE will actually work as a typical compound index on ID,VALUE. My question is this, if I don't know that about MySQL and create my indices on purpose (i.e. not taking into account implementation details about MySQL) I would create the index explicitly on ID,VALUE. If I did that would I effectively have the same index as one on just VALUE or would there some how be duplicate data in the ID,VALUE index (i.e. ID, ID, VALUE)? R. Close but not quite there... You should always have AN index which is the primary key. You can create other indexes which don't. If you are searching for 'Value' then that should be the index. If you do ID,VALUE it can't use the index AFAIK because it won't know the ID. -- William R. Mussatto Systems Engineer http://www.csz.com 909-920-9154 -- 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]
RE: Indices in InnoDB/MySQL
Right, the proof is that if I have an PKEY on ID and an index just on VALUE in MySQL then a query that would use both ID and VALUE works fine with just the index on VALUE. For Oracle, I need an explicit compound index (in addition to the PKEY) on (ID,VALUE). The results on MySQL get a little blurry when the PKEY is compound. -Original Message- From: Olexandr Melnyk [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 01, 2008 11:08 AM To: mysql@lists.mysql.com Subject: Indices in InnoDB/MySQL On 4/1/08, Paul DuBois [EMAIL PROTECTED] wrote: At 10:01 AM -0700 4/1/08, Robert DiFalco wrote: I've been told that an index always contains the primary key. By who? Ask for proof. I guess he was referring to the fact that InnoDB stores the primary key values alongside the indexed columns value, as a way of referencing the associated row. -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
useCursorFetch
A while back there was a general consensus that useCursorFetch (with useServerPrepStmts) was somehow flakey? Is this still the case? I had heard from someone that MySQL will not even provide support for customers using these options in the JDBC driver. Is that true? TIA, Robert -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Rollback on a Transaction with No Updates
Well, assume a higher level abstraction that does not give clients to
that abstraction access to the raw connection. It only has methods like
update, search, commit, or rollback. What the connection is doing is a
kind of implementation detail.
-Original Message-
From: Michael Dykman [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 18, 2007 10:00 AM
To: Robert DiFalco
Cc: Baron Schwartz; mysql@lists.mysql.com
Subject: Re: Rollback on a Transaction with No Updates
I realize that wasn't the question, but it does seem like a lot of
trouble to get the equivalent of setAutoCommit(true);
On 9/17/07, Robert DiFalco [EMAIL PROTECTED] wrote:
Sure, but that wasn't really the question.
-Original Message-
From: Michael Dykman [mailto:[EMAIL PROTECTED]
Sent: Monday, September 17, 2007 2:56 PM
To: Robert DiFalco
Cc: Baron Schwartz; mysql@lists.mysql.com
Subject: Re: Rollback on a Transaction with No Updates
If your transaction are only 1 query deep, why use them at all? An
individual query is already atomic, regardless of table type/server
mode.
- michael dkyman
On 9/17/07, Robert DiFalco [EMAIL PROTECTED] wrote:
While it is functionally equivalent I wonder if it the code paths
taken are the same. I suppose for both commit and rollback mysql
would
have to look for any pending work, if there were none both would do
nothing.
That's what makes me think that there is probably no performance
difference between the two. I ask this because my programmers like
to do
this:
con = ...
try
{
queryOnlyWith( con );
}
finally
{
con.rollback();
}
And I wanted to make sure that this would perform the same and act
the
same as issuing a commit (unless there was an exception but I'm not
analyzing that case).
-Original Message-
From: Baron Schwartz [mailto:[EMAIL PROTECTED]
Sent: Monday, September 17, 2007 2:36 PM
To: Robert DiFalco
Cc: mysql@lists.mysql.com
Subject: Re: Rollback on a Transaction with No Updates
Robert DiFalco wrote:
Is there any difference between calling rollback or commit on a
transaction that did not alter data? For example, not a read-only
transaction but a transaction that only performed read-only
selects.
Any difference in performance between calling rollback or commit?
I know they are functionally the same at the high level.
The only thing I could think of was possibly rollback would leave
open
transaction and its read view if you are running in REPEATABLE READ
isolation mode, whereas commit begins a new transaction and discards
the read view. But I just tested that, and both commands start a
new transaction and discard the read view.
That's a long way of saying they are functionally equivalent as far
as
I know, as long as there are no changes to discard.
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
- michael dykman
- [EMAIL PROTECTED]
- All models are wrong. Some models are useful.
--
- michael dykman
- [EMAIL PROTECTED]
- All models are wrong. Some models are useful.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Rollback on a Transaction with No Updates
While it is functionally equivalent I wonder if it the code paths taken
are the same. I suppose for both commit and rollback mysql would have to
look for any pending work, if there were none both would do nothing.
That's what makes me think that there is probably no performance
difference between the two. I ask this because my programmers like to do
this:
con = ...
try
{
queryOnlyWith( con );
}
finally
{
con.rollback();
}
And I wanted to make sure that this would perform the same and act the
same as issuing a commit (unless there was an exception but I'm not
analyzing that case).
-Original Message-
From: Baron Schwartz [mailto:[EMAIL PROTECTED]
Sent: Monday, September 17, 2007 2:36 PM
To: Robert DiFalco
Cc: mysql@lists.mysql.com
Subject: Re: Rollback on a Transaction with No Updates
Robert DiFalco wrote:
Is there any difference between calling rollback or commit on a
transaction that did not alter data? For example, not a read-only
transaction but a transaction that only performed read-only selects.
Any difference in performance between calling rollback or commit? I
know they are functionally the same at the high level.
The only thing I could think of was possibly rollback would leave open
transaction and its read view if you are running in REPEATABLE READ
isolation mode, whereas commit begins a new transaction and discards the
read view. But I just tested that, and both commands start a new
transaction and discard the read view.
That's a long way of saying they are functionally equivalent as far as I
know, as long as there are no changes to discard.
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Rollback on a Transaction with No Updates
Is there any difference between calling rollback or commit on a transaction that did not alter data? For example, not a read-only transaction but a transaction that only performed read-only selects. Any difference in performance between calling rollback or commit? I know they are functionally the same at the high level. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Rollback on a Transaction with No Updates
Sure, but that wasn't really the question.
-Original Message-
From: Michael Dykman [mailto:[EMAIL PROTECTED]
Sent: Monday, September 17, 2007 2:56 PM
To: Robert DiFalco
Cc: Baron Schwartz; mysql@lists.mysql.com
Subject: Re: Rollback on a Transaction with No Updates
If your transaction are only 1 query deep, why use them at all? An
individual query is already atomic, regardless of table type/server
mode.
- michael dkyman
On 9/17/07, Robert DiFalco [EMAIL PROTECTED] wrote:
While it is functionally equivalent I wonder if it the code paths
taken are the same. I suppose for both commit and rollback mysql would
have to look for any pending work, if there were none both would do
nothing.
That's what makes me think that there is probably no performance
difference between the two. I ask this because my programmers like to
do
this:
con = ...
try
{
queryOnlyWith( con );
}
finally
{
con.rollback();
}
And I wanted to make sure that this would perform the same and act the
same as issuing a commit (unless there was an exception but I'm not
analyzing that case).
-Original Message-
From: Baron Schwartz [mailto:[EMAIL PROTECTED]
Sent: Monday, September 17, 2007 2:36 PM
To: Robert DiFalco
Cc: mysql@lists.mysql.com
Subject: Re: Rollback on a Transaction with No Updates
Robert DiFalco wrote:
Is there any difference between calling rollback or commit on a
transaction that did not alter data? For example, not a read-only
transaction but a transaction that only performed read-only selects.
Any difference in performance between calling rollback or commit? I
know they are functionally the same at the high level.
The only thing I could think of was possibly rollback would leave open
transaction and its read view if you are running in REPEATABLE READ
isolation mode, whereas commit begins a new transaction and discards
the read view. But I just tested that, and both commands start a new
transaction and discard the read view.
That's a long way of saying they are functionally equivalent as far as
I know, as long as there are no changes to discard.
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
- michael dykman
- [EMAIL PROTECTED]
- All models are wrong. Some models are useful.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Slow query examining 10 Million Rows, please help !!!
Could also be the DISTINCT processing depending on the number of dups and the fields in the result set that must be sorted to perform the distinct operation. Normally if there were a lot of dupes I would suggest a sub-query but that is not a great option for MySQL. -Original Message- From: Kishore Jalleda [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 20, 2007 10:23 AM To: Brent Baisley Cc: mysql@lists.mysql.com Subject: Re: Slow query examining 10 Million Rows, please help !!! Yes I already did try adding an index on tag, but as you said it didn't work as its using the primary key from the freetags table for the join , anyway I will try adding an index on object_type, and see if that helps ... Thanks Kishore Jalleda http://kjalleda.googlepages.com On 6/20/07, Brent Baisley [EMAIL PROTECTED] wrote: As Dan mentioned, you're searching on the 'tag' field which has no index. But since that field is in the table you're joining on, adding an index on it might not help. You actually searching on the tag_id in the join field, not the 'tag'. Add an index on 'object_type' in the freetagged_objects table since you're searching on object_type=1. You're doing a full table scan on that table as indicated my the explain. On Jun 19, 2007, at 6:20 PM, Kishore Jalleda wrote: Hi everybody, we have this super slow query which is going through more than 10 million rows to retrieve results, here is the query and other information, I tried a few things to make this faster , but failed , so any help from you guys in making this faster is greatly appreciated # Query_time: 10 Lock_time: 0 Rows_sent: 1 Rows_examined: 11863498 SELECT DISTINCT object_id FROM freetagged_objects INNER JOIN freetags ON (tag_id = id) WHERE tag = 'shot' AND object_type = 1 ORDER BY object_id ASC LIMIT 0, 10 explain gives the following output ++-+++--- +-+-+-- +-+--+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra| ++-+++--- +-+-+-- +-+--+ | 1 | SIMPLE | freetagged_objects | ALL| PRIMARY | NULL |NULL | NULL | 9079381 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | freetags | eq_ref | PRIMARY | PRIMARY | 4 | osCommerce.freetagged_objects.tag_id | 1 | Using where; Distinct| ++-+++--- +-+-+-- +-+--+ mysql show create table freetagged_objects; | freetagged_objects | CREATE TABLE `freetagged_objects` ( `tag_id` int(11) unsigned NOT NULL default '0', `tagger_id` int(11) unsigned NOT NULL default '0', `object_id` int(11) unsigned NOT NULL default '0', `tagged_on` datetime NOT NULL default '-00-00 00:00:00', `object_type` int(11) NOT NULL default '0', PRIMARY KEY (`tag_id`,`tagger_id`,`object_id`), KEY `tagger_id_index` (`tagger_id`), KEY `object_id_tagger_id_index` (`object_id`,`tagger_id`), KEY `object_id_tag_id_index` (`object_id`,`tag_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | mysql show create table freetags; | freetags | CREATE TABLE `freetags` ( `id` int(11) unsigned NOT NULL auto_increment, `tag` varchar(30) NOT NULL default '', `raw_tag` varchar(50) NOT NULL default '', `suppress` tinyint(1) NOT NULL default '0', PRIMARY KEY (`id`), KEY `raw_tag` (`raw_tag`(10)) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | Freetags table has like a million rows in it MySQL version 4.1.11 , server has 16GB RAM .. Kishore Jalleda http://kjalleda.googlepages.com/mysqlprojects -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INNER versus OUTER
Any thoughts? -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Monday, June 18, 2007 10:10 AM To: mysql@lists.mysql.com Subject: INNER versus OUTER I'm using the latest MySQL with InnoDB and something is happening I don't understand. I am going to try this first by paraphrasing my queries since they are complex and have some proprietary info in them. It seems that when a LEFT OUTER or an INNER join will produce the same result and other joins in the query are LEFT OUTER that I get a better query plan if I make the JOIN that could go either way a LEFT OUTER instead of an INNER. For example consider the following query (everything is indexed)... SELECT Field_List FROM Header JOIN ON HeaderNames ON Header.nameID = HeaderNames.ID LEFT OUTER JOIN Items LastItem ON Header.lastItemID = LastItem.ID WHERE Header.nodeID = 20 ORDER BY Header.ruleID; This seems to run much slower than the following: SELECT Field_List FROM Header LEFT OUTER JOIN ON HeaderNames ON Header.nameID = HeaderNames.ID LEFT OUTER JOIN Items LastItem ON Header.lastItemID = LastItem.ID WHERE Header.nodeID = 20 ORDER BY Header.ruleID; Looking at the query plan, the first query puts the HeaderNames table first and Uses a temporary and a filesort. The second query examines a few more rows but it puts the Header file first and uses a Where and index for everything. Could someone explain to me why this is? Does it have something to do with the ORDER BY? R. -- 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]
INNER versus OUTER
I'm using the latest MySQL with InnoDB and something is happening I don't understand. I am going to try this first by paraphrasing my queries since they are complex and have some proprietary info in them. It seems that when a LEFT OUTER or an INNER join will produce the same result and other joins in the query are LEFT OUTER that I get a better query plan if I make the JOIN that could go either way a LEFT OUTER instead of an INNER. For example consider the following query (everything is indexed)... SELECT Field_List FROM Header JOIN ON HeaderNames ON Header.nameID = HeaderNames.ID LEFT OUTER JOIN Items LastItem ON Header.lastItemID = LastItem.ID WHERE Header.nodeID = 20 ORDER BY Header.ruleID; This seems to run much slower than the following: SELECT Field_List FROM Header LEFT OUTER JOIN ON HeaderNames ON Header.nameID = HeaderNames.ID LEFT OUTER JOIN Items LastItem ON Header.lastItemID = LastItem.ID WHERE Header.nodeID = 20 ORDER BY Header.ruleID; Looking at the query plan, the first query puts the HeaderNames table first and Uses a temporary and a filesort. The second query examines a few more rows but it puts the Header file first and uses a Where and index for everything. Could someone explain to me why this is? Does it have something to do with the ORDER BY? R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need confirmation: Subselects are broken with regards to index usage?
Lets hope something is done about it soon, not all sub queries SHOULD be re-written as joins. Generally joins that result in the need to use DISTINCT are better off re-written as IN OR EXISTS sub queries. In any event, I have a lot of very complex queries that work great for our other database backends but will need to be rewritten for MySQL. Does anyone know of a tool that will just take a standard SQL select query string and rewrite it (with the option of rewriting subqueries to joins). I know such a tool can't be perfect but it could help out with the heavy lifting leaving only some tuning and debugging. -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Thursday, May 24, 2007 2:35 PM To: Robert DiFalco Cc: mysql@lists.mysql.com Subject: Re: Need confirmation: Subselects are broken with regards to index usage? Hi Robert, The way non-correlated subqueries are sometimes optimized into correlated ones and then executed for each row in the outer table is a well-known MySQL deficiency, yes. I would not really look for it to be fixed soon, though it's been in progress for a while. The version in which it gets fixed is still likely a long way from GA. But maybe a MySQL employee can give better info on that. In the meantime, we are all forced to find alternative ways to write such queries :-) Baron Robert DiFalco wrote: Ok, so I guess it is more complicated than that. This query which has 5M records that match its criteria returns instantly: SELECT ELEMS.id FROM ELEMS WHERE (( ELEMS.nodeID IN ( SELECT link.childID FROM link JOIN path ON link.parentID=path.decendantId WHERE (path.ancestorId = 1 LIMIT 0,100; Now if I change the ancestorId criteria to a node group that does not exist the query takes a very long time. Btw, it also looks like this is being optimized into a less efficient EXISTS query. Anyway, the join version doesn't have the same problem, it is fast if it is searching for a conditions that has results or one that has none. Note that the JOIN version requires a SELECT in case a node has multiple ancestors. SELECT DISTINCT ELEMS.id FROM ELEMS JOIN link ON ELEMS.nodeID = link.childID JOIN path ON link.parentID=path.decendantId WHERE (path.ancestorId = 1) LIMIT 0,100; Anyone have any ideas why this is the case? -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Thursday, May 24, 2007 1:11 PM To: mysql@lists.mysql.com Subject: Need confirmation: Subselects are broken with regards to index usage? I think I'm discovering that sub-selects in MySQL are broken. Is that true? It seems like you cannot have a sub-select without doing a table scan -- even for a constant IN expression -- this because it gets re-written as an EXISTS that executes for each row. Is that true? Forcing an index doesn't even seem to help. R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need confirmation: Subselects are broken with regards to index usage?
I think I'm discovering that sub-selects in MySQL are broken. Is that true? It seems like you cannot have a sub-select without doing a table scan -- even for a constant IN expression -- this because it gets re-written as an EXISTS that executes for each row. Is that true? Forcing an index doesn't even seem to help. R.
RE: Need confirmation: Subselects are broken with regards to index usage?
Ok, so I guess it is more complicated than that. This query which has 5M records that match its criteria returns instantly: SELECT ELEMS.id FROM ELEMS WHERE (( ELEMS.nodeID IN ( SELECT link.childID FROM link JOIN path ON link.parentID=path.decendantId WHERE (path.ancestorId = 1 LIMIT 0,100; Now if I change the ancestorId criteria to a node group that does not exist the query takes a very long time. Btw, it also looks like this is being optimized into a less efficient EXISTS query. Anyway, the join version doesn't have the same problem, it is fast if it is searching for a conditions that has results or one that has none. Note that the JOIN version requires a SELECT in case a node has multiple ancestors. SELECT DISTINCT ELEMS.id FROM ELEMS JOIN link ON ELEMS.nodeID = link.childID JOIN path ON link.parentID=path.decendantId WHERE (path.ancestorId = 1) LIMIT 0,100; Anyone have any ideas why this is the case? -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Thursday, May 24, 2007 1:11 PM To: mysql@lists.mysql.com Subject: Need confirmation: Subselects are broken with regards to index usage? I think I'm discovering that sub-selects in MySQL are broken. Is that true? It seems like you cannot have a sub-select without doing a table scan -- even for a constant IN expression -- this because it gets re-written as an EXISTS that executes for each row. Is that true? Forcing an index doesn't even seem to help. R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
When are subselects faster than Joins?
Are there any hard and fast rules for this? If someone has already compiled a list I'd love to see it. For example: * When a subselect will eliminate duplicates a join might introduce. Change: SELECT DISTINCT Acl.* FROM Acl JOIN Link ON Link.childID = Acl.ID JOIN Paths ON Link.parentID = Path.descendantID WHERE Path.ancestorID = '12345'; To: SELECT Acl.* FROM Acl WHERE Acl.ID IN ( SELECT Link.childID FROM Link JOIN Paths ON Link.parentID = Path.descendantID WHERE Path.ancestorID = '12345' ); When table Acl might have many duplicates and VARCHAR fields. Also, lets take something simple. For example I have a one to many table of some kind of Objects to Strings. Lets call the tables Food and Category. Would the subselect version below be faster than the JOIN version or just the same? SELECT Food.* FROM Food JOIN FoodCategory ON Food.categoryID = FoodCategory.ID WHERE FoodCategory.name LIKE '%rui%'; Or: SELECT Food.* FROM Food WHERE Food.categoryID IN ( SELECT FoodCategory.ID FROM FoodCategory WHERE FoodCategory.name LIKE '%rui%' ); Or in this case will the JOIN as a rule be faster? R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Analysis Tool
I'm looking for a tool that could parse a boat load of various queries using complex joins and subqueries, analyze each, and print out the optimal covering indices that could be used on each table for each query. It would have to take into consideration stuff like a WHERE expression that could not use an index even if the column was indexable. I suppose it would not be difficult to build one but I was hoping that someone knew of a tool out there that already did such a thing? The problem with EXPLAIN is that it will print out how the query will be executed based on existing indices, not the optimal indices that may or may not exist yet. TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query Analysis Tool
Ah well, wishful thinking I guess. I guess I thought that at least the recommendations would be deterministic. Fwiw, there would still be human judgment in the end to decide which covering/index recommendations to actually create. -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Friday, November 03, 2006 1:40 PM To: Robert DiFalco Cc: mysql@lists.mysql.com Subject: Re: Query Analysis Tool H. I actually think this would be somewhat difficult to write, Robert. Parsing the queries would be complex enough given the different ways one can construct SQL. Also it would have to examine the cardinality of the data in each column to determine if indexing would be worthwhile vs. a table scan... and then there's the human judgment that needs to be made as far as which queries actually need optimizing vs those that don't, or that need an index less at any rate. The ones that get run several times a second vs once an hour or once a day ... My two cents' worth anyway. Dan On 11/3/06, Robert DiFalco [EMAIL PROTECTED] wrote: I'm looking for a tool that could parse a boat load of various queries using complex joins and subqueries, analyze each, and print out the optimal covering indices that could be used on each table for each query. It would have to take into consideration stuff like a WHERE expression that could not use an index even if the column was indexable. I suppose it would not be difficult to build one but I was hoping that someone knew of a tool out there that already did such a thing? The problem with EXPLAIN is that it will print out how the query will be executed based on existing indices, not the optimal indices that may or may not exist yet. TIA, R. -- 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]
Prefix Indices
I have some long VARCHAR fields that a user will sometimes sort on. Does a prefix index in any way help with sorting or just for lookups? Will it speed up a filesort? I couldn't find this information in How MySQL uses indices. R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: References on Optimizing File Sort
Btw, this is using the InnoDB engine. -Original Message- From: Robert DiFalco Sent: Tuesday, October 17, 2006 9:26 AM To: mysql@lists.mysql.com Subject: References on Optimizing File Sort I have an unavoidable filesort in a very large query. Can someone point me to references for optimizing filesort? I'm assuming this is going to be changes to my.ini or the hardware. TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
References on Optimizing File Sort
I have an unavoidable filesort in a very large query. Can someone point me to references for optimizing filesort? I'm assuming this is going to be changes to my.ini or the hardware. TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Innodb Locks
I think what is strange to me is that InnoDB is locking on the subquery table at all. Here's another example: DELETE FROM Vers WHERE ( Vers.elementID IN ( SELECT Elems.ID FROM Elems WHERE (Elems.nodeID = ?))) Disregarding whether performance would be better or worse with a JOIN, what I find odd is that this DELETE statement on Vers seems to be putting locks on Elems. Might this be a bug in InnoDB? Innotop has this to say: Locks Held and Waited For Txn What Mode DB Tbl Index Heap Special Ins Intent 1 waits_for Xte elems PRIMARY2 rec but not gap 0 Not that Txn 1 is an UPDATE on a single row of the ELEMS table and it is waiting for the LOCK from the above DELETE FROM Vers to be released. I'm not sure why the DELETE statement is locking the subquery table ELEMS which is simply being queried. Do I *really* need to change all of these to write the subquery to a temporary table in order to gain better concurrency? R. -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 1:39 PM To: Rick James Cc: Robert DiFalco; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Re: Innodb Locks There is a detailed write-up on how locking works in the manual: http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html If you are not doing replication, you might check out innodb_locks_unsafe_for_binlog as mentioned in http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html. Peter Z also wrote an article on this: http://www.mysqlperformanceblog.com/category/replication/ You may get better performance from using a JOIN instead of an IN() subquery. You will have to test. Sometimes it is much better, sometimes worse. Usually better in my experience. Making the long-running query as short as possible is probably a good idea. Maybe you can break it up into several queries so it doesn't try to lock so many rows at once. There could be many other approaches too, it just depends on your needs and data. Without altering how locks are handled with startup options, the temporary table approach will avoid the locks only if you COMMIT after the CREATE... SELECT. The other subquery approach will not avoid them. I'm not sure if I should be replying to both the 'internals' and 'lists' mailing lists, since this was cross-posted. Feel free to give me guidance :-) Baron Rick James wrote: Can't answer your question directly. But I wonder if this would trick it into avoiding the lock: UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM SomeTable); And the real workaround would be CREATE TEMPORARY TABLE t SELECT id ...; UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM t); -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 9:26 AM To: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Innodb Locks Any thoughts on this? Should SomeTable be locked when performing the UPDATE on AnotherTable? --- Is there a detailed source for when innodb creates row or table locks? I have a situation where one thread is performing this in one transaction: UPDATE SomeTable SET WHERE SomeTable.id = N; This is invoked after another thread has kicked off this long running query in another transaction: UPDATE AnotherTable SET ... WHERE EXISTS( SELECT null FROM SomeTable WHERE SomeTable.id = AnotherTable.id ); Would this create a conflicting lock? I am getting Lock wait timeout exceeded on SomeTable fro the UPDATE to SomeTable. TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL Internals Mailing List For list archives: http://lists.mysql.com/internals To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Baron Schwartz http://www.xaprb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Innodb Locks
We'll do some testing with innodb_locks_unsafe_for_binlog but if this fixes the problem then it is a pretty safe assumption that the problem also exists with subqueries in DELETE and UPDATE and not just for that one case of INSERT as the article points out. -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 1:39 PM To: Rick James Cc: Robert DiFalco; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Re: Innodb Locks There is a detailed write-up on how locking works in the manual: http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html If you are not doing replication, you might check out innodb_locks_unsafe_for_binlog as mentioned in http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html. Peter Z also wrote an article on this: http://www.mysqlperformanceblog.com/category/replication/ You may get better performance from using a JOIN instead of an IN() subquery. You will have to test. Sometimes it is much better, sometimes worse. Usually better in my experience. Making the long-running query as short as possible is probably a good idea. Maybe you can break it up into several queries so it doesn't try to lock so many rows at once. There could be many other approaches too, it just depends on your needs and data. Without altering how locks are handled with startup options, the temporary table approach will avoid the locks only if you COMMIT after the CREATE... SELECT. The other subquery approach will not avoid them. I'm not sure if I should be replying to both the 'internals' and 'lists' mailing lists, since this was cross-posted. Feel free to give me guidance :-) Baron Rick James wrote: Can't answer your question directly. But I wonder if this would trick it into avoiding the lock: UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM SomeTable); And the real workaround would be CREATE TEMPORARY TABLE t SELECT id ...; UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM t); -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 9:26 AM To: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Innodb Locks Any thoughts on this? Should SomeTable be locked when performing the UPDATE on AnotherTable? --- Is there a detailed source for when innodb creates row or table locks? I have a situation where one thread is performing this in one transaction: UPDATE SomeTable SET WHERE SomeTable.id = N; This is invoked after another thread has kicked off this long running query in another transaction: UPDATE AnotherTable SET ... WHERE EXISTS( SELECT null FROM SomeTable WHERE SomeTable.id = AnotherTable.id ); Would this create a conflicting lock? I am getting Lock wait timeout exceeded on SomeTable fro the UPDATE to SomeTable. TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL Internals Mailing List For list archives: http://lists.mysql.com/internals To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Baron Schwartz http://www.xaprb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Innodb Locks
Yup, innodb_locks_unsafe_for_binlog=1 fixes the problem and so does your suggestion of using a JOIN instead of a subselect. -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 10, 2006 8:54 AM To: Baron Schwartz; Rick James Cc: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Innodb Locks We'll do some testing with innodb_locks_unsafe_for_binlog but if this fixes the problem then it is a pretty safe assumption that the problem also exists with subqueries in DELETE and UPDATE and not just for that one case of INSERT as the article points out. -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 1:39 PM To: Rick James Cc: Robert DiFalco; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Re: Innodb Locks There is a detailed write-up on how locking works in the manual: http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html If you are not doing replication, you might check out innodb_locks_unsafe_for_binlog as mentioned in http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html. Peter Z also wrote an article on this: http://www.mysqlperformanceblog.com/category/replication/ You may get better performance from using a JOIN instead of an IN() subquery. You will have to test. Sometimes it is much better, sometimes worse. Usually better in my experience. Making the long-running query as short as possible is probably a good idea. Maybe you can break it up into several queries so it doesn't try to lock so many rows at once. There could be many other approaches too, it just depends on your needs and data. Without altering how locks are handled with startup options, the temporary table approach will avoid the locks only if you COMMIT after the CREATE... SELECT. The other subquery approach will not avoid them. I'm not sure if I should be replying to both the 'internals' and 'lists' mailing lists, since this was cross-posted. Feel free to give me guidance :-) Baron Rick James wrote: Can't answer your question directly. But I wonder if this would trick it into avoiding the lock: UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM SomeTable); And the real workaround would be CREATE TEMPORARY TABLE t SELECT id ...; UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM t); -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 9:26 AM To: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Innodb Locks Any thoughts on this? Should SomeTable be locked when performing the UPDATE on AnotherTable? --- Is there a detailed source for when innodb creates row or table locks? I have a situation where one thread is performing this in one transaction: UPDATE SomeTable SET WHERE SomeTable.id = N; This is invoked after another thread has kicked off this long running query in another transaction: UPDATE AnotherTable SET ... WHERE EXISTS( SELECT null FROM SomeTable WHERE SomeTable.id = AnotherTable.id ); Would this create a conflicting lock? I am getting Lock wait timeout exceeded on SomeTable fro the UPDATE to SomeTable. TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL Internals Mailing List For list archives: http://lists.mysql.com/internals To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Baron Schwartz http://www.xaprb.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]
RE: Innodb Locks
Then I guess I am not understanding why re-writing the statement as a JOIN alleviates that need. -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 10, 2006 11:35 AM To: Robert DiFalco Cc: Rick James; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Re: Innodb Locks It's not a bug in InnoDB. There are far more knowledgeable people than I on this list, but it should get a share-mode lock on anything it selects from, otherwise there might be inconsistencies as it tries to serialize different transactions into the binary log for replication. If the statement reads different values from Elems on the master and slave, there'll be different results. So locking is necessary for replication to work right. If I'm glossing over the finer points too much, it's because I don't know them. Someone else can surely correct me :-) Baron Robert DiFalco wrote: I think what is strange to me is that InnoDB is locking on the subquery table at all. Here's another example: DELETE FROM Vers WHERE ( Vers.elementID IN ( SELECT Elems.ID FROM Elems WHERE (Elems.nodeID = ?))) Disregarding whether performance would be better or worse with a JOIN, what I find odd is that this DELETE statement on Vers seems to be putting locks on Elems. Might this be a bug in InnoDB? Innotop has this to say: Locks Held and Waited For Txn What Mode DB Tbl Index Heap Special Ins Intent 1 waits_for Xte elems PRIMARY2 rec but not gap 0 Not that Txn 1 is an UPDATE on a single row of the ELEMS table and it is waiting for the LOCK from the above DELETE FROM Vers to be released. I'm not sure why the DELETE statement is locking the subquery table ELEMS which is simply being queried. Do I *really* need to change all of these to write the subquery to a temporary table in order to gain better concurrency? R. -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 1:39 PM To: Rick James Cc: Robert DiFalco; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Re: Innodb Locks There is a detailed write-up on how locking works in the manual: http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html If you are not doing replication, you might check out innodb_locks_unsafe_for_binlog as mentioned in http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html. Peter Z also wrote an article on this: http://www.mysqlperformanceblog.com/category/replication/ You may get better performance from using a JOIN instead of an IN() subquery. You will have to test. Sometimes it is much better, sometimes worse. Usually better in my experience. Making the long-running query as short as possible is probably a good idea. Maybe you can break it up into several queries so it doesn't try to lock so many rows at once. There could be many other approaches too, it just depends on your needs and data. Without altering how locks are handled with startup options, the temporary table approach will avoid the locks only if you COMMIT after the CREATE... SELECT. The other subquery approach will not avoid them. I'm not sure if I should be replying to both the 'internals' and 'lists' mailing lists, since this was cross-posted. Feel free to give me guidance :-) Baron Rick James wrote: Can't answer your question directly. But I wonder if this would trick it into avoiding the lock: UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM SomeTable); And the real workaround would be CREATE TEMPORARY TABLE t SELECT id ...; UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM t); -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 9:26 AM To: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Innodb Locks Any thoughts on this? Should SomeTable be locked when performing the UPDATE on AnotherTable? --- Is there a detailed source for when innodb creates row or table locks? I have a situation where one thread is performing this in one transaction: UPDATE SomeTable SET WHERE SomeTable.id = N; This is invoked after another thread has kicked off this long running query in another transaction: UPDATE AnotherTable SET ... WHERE EXISTS( SELECT null FROM SomeTable WHERE SomeTable.id = AnotherTable.id ); Would this create a conflicting lock? I am getting Lock wait timeout exceeded on SomeTable fro the UPDATE to SomeTable. TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL Internals Mailing List For list archives: http://lists.mysql.com/internals
RE: Innodb Locks
Right, as I understand it the query optimizer in 5.2 will simply rewrite these sub selects as joins when possible. -Original Message- From: Jerry Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 10, 2006 11:45 AM To: Robert DiFalco; 'Baron Schwartz' Cc: 'Rick James'; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Innodb Locks It probably uses a single lock to handle a JOIN, and two locks to handle a sub-SELECT. I doubt that it helps, but if I'm right it will change what you see when you poking around. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 10, 2006 2:42 PM To: Baron Schwartz Cc: Rick James; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Innodb Locks Then I guess I am not understanding why re-writing the statement as a JOIN alleviates that need. -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 10, 2006 11:35 AM To: Robert DiFalco Cc: Rick James; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Re: Innodb Locks It's not a bug in InnoDB. There are far more knowledgeable people than I on this list, but it should get a share-mode lock on anything it selects from, otherwise there might be inconsistencies as it tries to serialize different transactions into the binary log for replication. If the statement reads different values from Elems on the master and slave, there'll be different results. So locking is necessary for replication to work right. If I'm glossing over the finer points too much, it's because I don't know them. Someone else can surely correct me :-) Baron Robert DiFalco wrote: I think what is strange to me is that InnoDB is locking on the subquery table at all. Here's another example: DELETE FROM Vers WHERE ( Vers.elementID IN ( SELECT Elems.ID FROM Elems WHERE (Elems.nodeID = ?))) Disregarding whether performance would be better or worse with a JOIN, what I find odd is that this DELETE statement on Vers seems to be putting locks on Elems. Might this be a bug in InnoDB? Innotop has this to say: Locks Held and Waited For Txn What Mode DB Tbl Index Heap Special Ins Intent 1 waits_for Xte elems PRIMARY2 rec but not gap 0 Not that Txn 1 is an UPDATE on a single row of the ELEMS table and it is waiting for the LOCK from the above DELETE FROM Vers to be released. I'm not sure why the DELETE statement is locking the subquery table ELEMS which is simply being queried. Do I *really* need to change all of these to write the subquery to a temporary table in order to gain better concurrency? R. -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 1:39 PM To: Rick James Cc: Robert DiFalco; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Re: Innodb Locks There is a detailed write-up on how locking works in the manual: http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html If you are not doing replication, you might check out innodb_locks_unsafe_for_binlog as mentioned in http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html. Peter Z also wrote an article on this: http://www.mysqlperformanceblog.com/category/replication/ You may get better performance from using a JOIN instead of an IN() subquery. You will have to test. Sometimes it is much better, sometimes worse. Usually better in my experience. Making the long-running query as short as possible is probably a good idea. Maybe you can break it up into several queries so it doesn't try to lock so many rows at once. There could be many other approaches too, it just depends on your needs and data. Without altering how locks are handled with startup options, the temporary table approach will avoid the locks only if you COMMIT after the CREATE... SELECT. The other subquery approach will not avoid them. I'm not sure if I should be replying to both the 'internals' and 'lists' mailing lists, since this was cross-posted. Feel free to give me guidance :-) Baron Rick James wrote: Can't answer your question directly. But I wonder if this would trick it into avoiding the lock: UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM SomeTable); And the real workaround would be CREATE TEMPORARY TABLE t SELECT id ...; UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM t); -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 9:26 AM To: mysql@lists.mysql.com; [EMAIL PROTECTED
RE: Glitch in Query Optimizer
Anyone here know enough about how the optimizer works to explain why it is use the less optimal index in this case? -Original Message- From: Christian Hammers [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 2:54 PM To: Robert DiFalco Cc: mysql@lists.mysql.com Subject: Re: Glitch in Query Optimizer On Tue, Oct 03, 2006 at 01:35:01PM -0700, Robert DiFalco wrote: Scratch that, the only way to have the optimizer choose the correct index is to remove all compound indices that start with NodeID or move NodeID so that it is not the first column specified in the compound index. Ugh. Any ideas? Search the docs for FORCE INDEX () and IGNORE INDEX () if you don't want to rely on MySQL's cleverness. bye, -christian- -- Christian Hammers WESTEND GmbH | Internet-Business-Provider Technik CISCO Systems Partner - Authorized Reseller Lütticher Straße 10 Tel 0241/701333-11 [EMAIL PROTECTED]D-52064 Aachen Fax 0241/911879 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Innodb Locks
Any thoughts on this? Should SomeTable be locked when performing the UPDATE on AnotherTable? --- Is there a detailed source for when innodb creates row or table locks? I have a situation where one thread is performing this in one transaction: UPDATE SomeTable SET WHERE SomeTable.id = N; This is invoked after another thread has kicked off this long running query in another transaction: UPDATE AnotherTable SET ... WHERE EXISTS( SELECT null FROM SomeTable WHERE SomeTable.id = AnotherTable.id ); Would this create a conflicting lock? I am getting Lock wait timeout exceeded on SomeTable fro the UPDATE to SomeTable. TIA, R. -- 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]
RE: Innodb Locks
No foreign key relationships. If I pull it into a temp table or a separate query that I then iterate through for all the updates on AnotherTable, then all works well. Odd. -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 11:09 AM To: mysql@lists.mysql.com Subject: Re: Innodb Locks On 10/2/06, Robert DiFalco wrote: Is there a detailed source for when innodb creates row or table locks? The sourcecode. I have a situation where one thread is performing this in one transaction: UPDATE SomeTable SET WHERE SomeTable.id = N; This is invoked after another thread has kicked off this long running query in another transaction: UPDATE AnotherTable SET ... WHERE EXISTS( SELECT null FROM SomeTable WHERE SomeTable.id = AnotherTable.id ); Would this create a conflicting lock? It shouldn't from what you have described here. But might there be foreign key relation sbetween both tables? Jochem -- 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]
Glitch in Query Optimizer
Here's an odd one. I have a table called Elements and another table called ElementNames. The ElementNames table has a unique ID and a VARCHAR display name. The Elements table has a ElementName.ID, a node ID, a rule ID and some other stuff. I have an index on the NameID, NodeID, and RuleID. I have another index on just the NodeID and RuleID. For the following query, MySQL normally uses the NameID, NodeID, RuleID index. SELECT * FROM Elements JOIN Enames ON Elements.nameID = Enames.ID WHERE Elements.nodeID = ? ORDER BY Enames.displayName LIMIT ?, ?; However, once I have more than about 50K elements, the query switches over to using the index on NodeID and RuleID (which results in a very slow query). I can *force* it to use the correct index by adding this to the query: SELECT * FROM Elements JOIN Enames ON Elements.nameID = Enames.ID WHERE Elements.nodeID = ? AND Elements.nameID 0 ORDER BY Enames.displayName LIMIT ?, ?; It is impossible for nameID to be zero so it doesn't effect the outcome of the query, it just forces it to use the correct index. Any idea why this is happening? Is this a bug in the query optimizer? R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Glitch in Query Optimizer
Scratch that, the only way to have the optimizer choose the correct index is to remove all compound indices that start with NodeID or move NodeID so that it is not the first column specified in the compound index. Ugh. Any ideas? -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 1:00 PM To: mysql@lists.mysql.com Subject: Glitch in Query Optimizer Here's an odd one. I have a table called Elements and another table called ElementNames. The ElementNames table has a unique ID and a VARCHAR display name. The Elements table has a ElementName.ID, a node ID, a rule ID and some other stuff. I have an index on the NameID, NodeID, and RuleID. I have another index on just the NodeID and RuleID. For the following query, MySQL normally uses the NameID, NodeID, RuleID index. SELECT * FROM Elements JOIN Enames ON Elements.nameID = Enames.ID WHERE Elements.nodeID = ? ORDER BY Enames.displayName LIMIT ?, ?; However, once I have more than about 50K elements, the query switches over to using the index on NodeID and RuleID (which results in a very slow query). I can *force* it to use the correct index by adding this to the query: SELECT * FROM Elements JOIN Enames ON Elements.nameID = Enames.ID WHERE Elements.nodeID = ? AND Elements.nameID 0 ORDER BY Enames.displayName LIMIT ?, ?; It is impossible for nameID to be zero so it doesn't effect the outcome of the query, it just forces it to use the correct index. Any idea why this is happening? Is this a bug in the query optimizer? R. -- 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]
RE: RE: Glitch in Query Optimizer
Thanks, I had seen that but I don't have a lot of flexibility for adding database specific extensions on a query by query basis. -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 2:30 PM To: Robert DiFalco Cc: mysql@lists.mysql.com Subject: Re: RE: Glitch in Query Optimizer Have you tried using the USE/IGNORE/FORCE INDEX optimizer hints? http://dev.mysql.com/doc/refman/5.0/en/join.html Sometimes, MySQL's optimize just doesn't make the best choice. Somewhat rare in my experience but it happens. HTH, Dan On 10/3/06, Robert DiFalco [EMAIL PROTECTED] wrote: Scratch that, the only way to have the optimizer choose the correct index is to remove all compound indices that start with NodeID or move NodeID so that it is not the first column specified in the compound index. Ugh. Any ideas? -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 1:00 PM To: mysql@lists.mysql.com Subject: Glitch in Query Optimizer Here's an odd one. I have a table called Elements and another table called ElementNames. The ElementNames table has a unique ID and a VARCHAR display name. The Elements table has a ElementName.ID, a node ID, a rule ID and some other stuff. I have an index on the NameID, NodeID, and RuleID. I have another index on just the NodeID and RuleID. For the following query, MySQL normally uses the NameID, NodeID, RuleID index. SELECT * FROM Elements JOIN Enames ON Elements.nameID = Enames.ID WHERE Elements.nodeID = ? ORDER BY Enames.displayName LIMIT ?, ?; However, once I have more than about 50K elements, the query switches over to using the index on NodeID and RuleID (which results in a very slow query). I can *force* it to use the correct index by adding this to the query: SELECT * FROM Elements JOIN Enames ON Elements.nameID = Enames.ID WHERE Elements.nodeID = ? AND Elements.nameID 0 ORDER BY Enames.displayName LIMIT ?, ?; It is impossible for nameID to be zero so it doesn't effect the outcome of the query, it just forces it to use the correct index. Any idea why this is happening? Is this a bug in the query optimizer? R. -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Innodb Locks
Is there a detailed source for when innodb creates row or table locks? I have a situation where one thread is performing this in one transaction: UPDATE SomeTable SET WHERE SomeTable.id = N; This is invoked after another thread has kicked off this long running query in another transaction: UPDATE AnotherTable SET ... WHERE EXISTS( SELECT null FROM SomeTable WHERE SomeTable.id = AnotherTable.id ); Would this create a conflicting lock? I am getting Lock wait timeout exceeded on SomeTable fro the UPDATE to SomeTable. TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: AW: Count of children
For us the querying of trees is more important than the speed of writing them. So each time we add a child or change a parent or whatever, we trigger a stored procedure that updates a paths table. Then our query for children is pretty simple: SELECT Node.* FROM Node JOIN Paths P ON Node.id = Paths.descendantID WHERE P.ancestorID = pID; So to perform a count I can just do this part without the join: SELECT COUNT(*) FROM Paths WHERE Paths.ancestorID = pID; Our system is structured using a sort of GoF composite parent so some nodes can be Groups and others can only be leaves. If I want to return children nodes that are not leaves I can do this: SELECT Node.* FROM Node JOIN Paths P ON Node.id = Paths.childID WHERE P.ancestorID = pID AND Node.isLeaf = false; The Paths table is pretty simple; just descendantID and ancestorID columns that make up a composite primary key. Some people also add another column called depth as this can make recalculating the tree easier. R. From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 27, 2006 8:20 AM To: André Hänsel Cc: mysql@lists.mysql.com Subject: Re: AW: Count of children André With an edge list, the solution entails recursion, so you need either an sproc or application proc. With a nested sets model, the count is dead simple. If the id of the target row is N, and the left right node columns are named leftedge and rightedge, the query is SELECT COUNT(t2.id) FROM tbl t1 JOIN tbl t2 ON t2.leftedge t1.leftedge AND t2.leftedge t1.rightedge WHERE t1.id=N; PB - André Hänsel wrote: I will use any model that is suitable. ;) I am somewhat familiar with both tree models but I can't come up with a method to get the count of all sub- and sub-sub-nodes in either of them. -Ursprüngliche Nachricht- Von: Peter Brawley [mailto:[EMAIL PROTECTED] Gesendet: Mittwoch, 27. September 2006 16:49 An: André Hänsel Cc: mysql@lists.mysql.com Betreff: Re: Count of children André, I want the count of all sub-entries for a specific entry. Depends on the model you are using--edge list or nested sets? PB - André Hänsel wrote: I have a table with id and parent_id. I want the count of all sub-entries for a specific entry. I found several documents about working with graphs/trees in MySQL but I could not find a solution for my problem. I can imagine two possibilities, but one is memory intensive and the other one creates load on updates. The first is, that I select all entries and then use a procedural language to determine recursively whether an node is a sub-node of the specific node. The second is, that I store the sub-node count with each node and when I do an insert, I walk the tree upwards and increment the node-counts. Is there a smart solution/best practice for my problem? Now I can't think of another sentence starting with an i. ;-) Best regards, André -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.407 / Virus Database: 268.12.9/457 - Release Date: 9/26/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Updating two fields from an aggregate query
I have two tables that are related: Parent LONG id LONG childCount LONG maxChildAge ... Child LONG parentId LONG age ... There can be thousands of parents and millions of children, that is why I have denormalized childCount and maxChildAge. The values are too expensive to calculate each time the data is viewed so I update these values each time a Child is added, removed, or modified. I currently have to update the Parent table with two queries like so: SELECT MAX( Child.age ), COUNT(*) FROM Child WHERE parentID = x; UPDATE Parent SET maxChildAge = MAX, childCount = COUNT WHERE id = x; Worse yet I might be updating the stats for several hundred Parents at a time, so I have to loop through the above where x is the current Parent.id in the batch. What I would like to do is something like the following (made up syntax): UPDATE Parent SET maxChildAge AND childCount = COUNT WITH ( SELECT MAX( Child.age ), COUNT(*) FROM Child WHERE parentID = Parent.id ) WHERE id IN ( set_of_parents_to_update ); Any suggestions? TIA R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Updating two fields from an aggregate query
Hdo you think this would perform better than simply using the two queries? I wonder if the overhead associated with the ON DUPLICATE KEY exception and handler might not outweigh the benefits of a single query. -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 26, 2006 7:15 AM To: Robert DiFalco Cc: mysql@lists.mysql.com Subject: Re: Updating two fields from an aggregate query Robert, you might give insert ... select ... on duplicate key update a try: http://dev.mysql.com/doc/refman/5.0/en/insert-select.html something like this (untested): INSERT INTO parent (id, maxChildAge, childCount) SELECT parentid, MAX(age) as maxAge, COUNT(*) as ct FROM child WHERE parentid IN (1, 2, 3, 4) ON DUPLICATE KEY UPDATE maxChildAge=maxAge, childCount=ct That seems like it ought to work as long as the id column in the parent table is a unique key. One consideration is that if you are writing this query programmatically and using a long list of parentid values in the IN clause, the SQL could get pretty long, perhaps too long for the default value of MAX_ALLOWED_PACKET. You can adjust that up pretty easily, but bear in mind you need to adjust it for both client and server. http://dev.mysql.com/doc/refman/5.0/en/packet-too-large.html HTH, Dan On 9/26/06, Robert DiFalco [EMAIL PROTECTED] wrote: I have two tables that are related: Parent LONG id LONG childCount LONG maxChildAge ... Child LONG parentId LONG age ... There can be thousands of parents and millions of children, that is why I have denormalized childCount and maxChildAge. The values are too expensive to calculate each time the data is viewed so I update these values each time a Child is added, removed, or modified. I currently have to update the Parent table with two queries like so: SELECT MAX( Child.age ), COUNT(*) FROM Child WHERE parentID = x; UPDATE Parent SET maxChildAge = MAX, childCount = COUNT WHERE id = x; Worse yet I might be updating the stats for several hundred Parents at a time, so I have to loop through the above where x is the current Parent.id in the batch. What I would like to do is something like the following (made up syntax): UPDATE Parent SET maxChildAge AND childCount = COUNT WITH ( SELECT MAX( Child.age ), COUNT(*) FROM Child WHERE parentID = Parent.id ) WHERE id IN ( set_of_parents_to_update ); Any suggestions? TIA R. -- 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]
RE: Complex SQL for multiple joins
Something else you may or may not want to consider. You may want to have
both users and user-groups be principles. Something like the following:
Principle (ID, NAME, PERMS)
User (P_ID, PASSWORD, ... )
UserGroup (P_ID, ... )
PrincipleLink (PID, CID) - Many to Many (parent, child)
Also consider a row in the Links table for each user refering to itself.
Then you can do stuff like the following pretty easily:
// search for a user permission (or the groups they belong to)
EXISTS(
SELECT null
FROM Principle P
JOIN PrincipleLink L ON P.ID = L.PID
WHERE
( P.PERMS (1 + 2) = (1 + 2) )
AND L.CID = userID )
You can then simply have User and UserGroup store information that is
not directly related to permissions.
R.
-Original Message-
From: Jay Pipes [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 31, 2006 7:59 AM
To: Brent Baisley
Cc: Stephen Orr; mysql@lists.mysql.com
Subject: Re: Complex SQL for multiple joins
Also, depending on the number of permissions you are tracking, you could
use a single INT field and do bitwise ORing in your application to
determine permission checks...
Though I usually don't recommend denormalizing the schema, this is one
scenario that would potentially make life a bit easier. Instead of
having six tables, you would have 3:
Users
UserGroups
User2Group (stores many-to-many relationship)
Both the fact tables (Users and UserGroups) would have an INT UNSIGNED
field called, say, permission_flags which could contain up to 32 flag
values for various permissions.
This is a very compact and efficienct way of *storing* permissions.
Retrieving sets of users/groups based on a single flag would be easy,
though an index would not be used.
For instance, imagine you have set bit 1 to mean has read access.
To find all users with read access, you would do:
SELECT * FROM Users WHERE permissions 1;
Let's say you have another permission for write access at the second bit
and you want to see all users with both read and write permission, you'd
do:
SELECT * FROM Users WHERE permissions (1 + 2) = (1 + 2);
the third bit would be 2^3 or 4, etc...:
SELECT * FROM Users WHERE permissions (1 + 2 + 4) = (1 + 2 + 4);
Additionally, what is nice about this type of organization is that you
can store the user's permissions in session memory and reference the
permissions without having to go to the database by using the same
bitwise operations in your application code.
For instance, in PHP you would write something like:
?php
define('CAN_READ', 1 0);
define('CAN_WRITE', 1 1);
define('CAN_DO_OTHER', 1 2);
$permissions = $my_session_vars['permissions']; $can_read = $permissions
CAN_READ; $can_write = $permissions CAN_WRITE;
...
?
Some more notes:
* Use a 64-bit BIGINT datatype if you need 33-64 flags for permissions
* You can use a SET datatype as well in this manner
* Make sure you understand bitwise operations
Cheers,
Jay
On Thu, 2006-08-31 at 09:39 -0400, Brent Baisley wrote:
You should be able to do it 1 query, I can think of two ways. I'm not
sure which one will run faster. I use table aliasing to keep the lines
shorter.
The first possibility would have user permissions in one column and
group permissions in another:
SELECT users.userid,perm_u.permissions,perm_g.permissions
FROM users
LEFT JOIN users_permissions AS u_p ON users.userid=u_p.userid LEFT
JOIN permissions AS perm_u ON u_p.permid=perm_u.permid LEFT JOIN
users_usergroups AS u_ug ON users.userid=u_ug.userid LEFT JOIN
usergroups_permissions AS ug_p ON u_ug.groupid=ug_p.groupid LEFT JOIN
permissions AS perm_g ON ug_p.permid=perm_g.permid WHERE
users.userid=#
But if you want to just have one column of permissions, you'll need to
use UNIONs. I think this is more readable.
SELECT users.userid AS userid,permissions AS permissions FROM users
LEFT JOIN users_permissions AS u_p ON users.userid=u_p.userid LEFT
JOIN permissions ON u_p.permid=permissions.permid WHERE
users.userid=# UNION SELECT users.userid AS userid,permissions FROM
users LEFT JOIN users_usergroups AS u_ug ON users.userid=u_ug.userid
LEFT JOIN usergroups_permissions AS ug_p ON u_ug.groupid=ug_p.groupid
LEFT JOIN permissions AS perm_g ON ug_p.permid=perm_g.permid WHERE
users.userid=#
Either of those should work. I don't know your table structure, so I
can't get too specific with it.
- Original Message -
From: Stephen Orr [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Wednesday, August 30, 2006 7:27 PM
Subject: Complex SQL for multiple joins
Hi,
For my current website project I'm developing a fine-grained access
control system.
I have the schema mostly worked out, although it will be having
additional data added to parts of it later.
For the record, I have 6 tables:
users (contains all my individual users) usergroups (contains all
the groups users can belong to) permissions (contains all the things
that the system allows)
Performance: LIMIT 1 with UPDATE
Does using LIMIT 1 with UPDATE provide a performance improvement when the WHERE condition is on a unique index or primary key? R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Performance: LIMIT 1 with UPDATE
FWIW, my preliminary testing shows no performance increase for adding LIMIT 1 to the following construct: [ SELECT | UPDATE ] WHERE Table.UniqueID = N In fact, there seems to be a slight performance edge to NOT appending LIMIT 1 to the statement. R. -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 29, 2006 9:06 AM To: mysql@lists.mysql.com Subject: Performance: LIMIT 1 with UPDATE Does using LIMIT 1 with UPDATE provide a performance improvement when the WHERE condition is on a unique index or primary key? R. -- 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]
RE: Performance: LIMIT 1 with UPDATE
Yeah, seems like for a uniquely index column expression that the LIMIT 1 provides no benefit (which seems like a logical conclusion). The performance on a large data set with both approaches is close enough to call equivalent. -Original Message- From: Rick James [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 29, 2006 11:51 AM To: Robert DiFalco; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Performance: LIMIT 1 with UPDATE Maybe it is the tiny extra time to parse the unnecessary LIMIT 1? -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 29, 2006 10:09 AM To: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Performance: LIMIT 1 with UPDATE FWIW, my preliminary testing shows no performance increase for adding LIMIT 1 to the following construct: [ SELECT | UPDATE ] WHERE Table.UniqueID = N In fact, there seems to be a slight performance edge to NOT appending LIMIT 1 to the statement. R. -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 29, 2006 9:06 AM To: mysql@lists.mysql.com Subject: Performance: LIMIT 1 with UPDATE Does using LIMIT 1 with UPDATE provide a performance improvement when the WHERE condition is on a unique index or primary key? R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL Internals Mailing List For list archives: http://lists.mysql.com/internals 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]
RE: Problem with INNODB transactions
What connection pool code are you using? My guess is that the problem is in your code somewhere. Either transactions are not being closed (i.e. because of a connection pool flaw maybe?) or you have two threads trying to update the same row at the same time (in which case this would be expected behavior). R. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 23, 2006 9:31 AM To: mysql@lists.mysql.com Subject: Problem with INNODB transactions Hi, I am facing a strange problem with INNODB. My application communicates with mysql server using JDBC. I am using mysql 5.1 version. Even after issuing connection.commit() / connection.rollback() commands, still on the sql side the transactions are not getting closed properly. In our application we never try to acquire locks on the same row from different threads. A request is sent only when the previous transaction is closed by issuing commit or rollback. But still the following error is seen : Lock wait timeout exceeded; try restarting transaction. The transaction isolation level used is READ-COMMITTED. We maintain a database connection pool and try to reuse the connections from the pool instead of trying to close and create the connections everytime. In our application there is a continuous database updates happening at a very high rate. Could you please suggest what could be going wrong. Thanks Prasad The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: More query help for user-defined values
No takers? -Original Message- From: Robert DiFalco Sent: Thursday, August 17, 2006 3:03 PM To: Robert DiFalco; mysql@lists.mysql.com Subject: RE: More query help for user-defined values One simple solution is to denormalize a little and take the refTypeID column out of StringValue and add it to the Links table. Then I could have: SELECT P.* FROM Person P LEFT JOIN StringLinks L1 ON P.ID = L1.parentID AND L1.refTypeID = 1 LEFT JOIN StringValue SV1 ON L1.valueID = SV1.ID LEFT JOIN StringType ST1 ON ST1.ID = 1 LEFT JOIN StringLinks L2 ON P.ID = L2.parentID AND L2.refTypeID = 2 LEFT JOIN StringValue SV2 ON L2.valueID = SV2.ID LEFT JOIN StringType ST2 ON ST2.ID = 2 WHERE ( SV1.f_val LIKE 'foo' OR ( SV1.val IS NULL AND ST1.defaultVal LIKE 'foo' ) ) AND ( SV2.f_val LIKE 'bar' OR ( SV2.val IS NULL AND ST2.defaultVal LIKE 'bar' ) ) -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Thursday, August 17, 2006 8:43 AM To: mysql@lists.mysql.com Subject: More query help for user-defined values To support user defined properties I have the following tables: TABLE StringType LONG ID PKEY VARCHAR name ... VARCHAR defaultValue TABLE StringValue LONG parentID LONG typeID VARCHAR val Assume the correct indices are in place. Different string value types can be defined by giving them a new row in StringType. The StringValue.parentID can be a row from any table that has a LONG identity column. If a row in the parent table does not have a corresponding row in StringValue, we want to assume an implicit default value. So to return all the Person rows that have either an explicit StringValue of ( type 1 and value foo ) or of ( type 2 and value bar ) I can just execute the following query. SELECT P.* FROM Person P LEFT JOIN StringValue SV1 ON P.ID = SV1.parentID AND SV1.refTypeID = 1 LEFT JOIN StringType ST1 ON ST1.ID = 1 LEFT JOIN StringValue SV2 ON P.ID = SV2.parentID AND SV2.refTypeID = 2 LEFT JOIN StringType ST2 ON ST2.ID = 2 WHERE ( SV1.f_val LIKE 'foo' OR ( SV1.val IS NULL AND ST1.defaultVal LIKE 'foo' ) ) AND ( SV2.f_val LIKE 'bar' OR ( SV2.val IS NULL AND ST2.defaultVal LIKE 'bar' ) ) The above seems to work fine. I can also get rid of the constant joins on the type table and just do a subquery for the default value, either approach works. Now I want to change things to introduce a ValueLink table so that if 100 rows have the same string value that I don't have to write the string 100 times. So my table structure changes like this: TABLE StringType LONG ID PKEY VARCHAR name ... VARCHAR defaultValue TABLE StringValue LONG ID PKEY // the value unique ID LONG typeID VARCHAR val TABLE StringLinks LONG parentID PKEY LONG valueID PKEY My naive approach to the query was this: SELECT P.* FROM Person P LEFT JOIN StringLinks L ON P.ID = L.parentID LEFT JOIN StringValue SV1 ON L.ID = SV1.ID AND SV1.refTypeID = 1 LEFT JOIN StringType ST1 ON ST1.ID = 1 LEFT JOIN StringValue SV2 ON L.ID = SV2.ID AND SV2.refTypeID = 2 LEFT JOIN StringType ST2 ON ST2.ID = 2 WHERE ( SV1.f_val LIKE 'foo' OR ( SV1.val IS NULL AND ST1.defaultVal LIKE 'foo' ) ) AND ( SV2.f_val LIKE 'bar' OR ( SV2.val IS NULL AND ST2.defaultVal LIKE 'bar' ) ) As you can probably tell this wont work because the two values will now show up in two different rows so the AND of the search terms will always fail. Can anyone think of a clean way to do this or do I have to get rid of the joins altogether and do a subquery for each StringValue search term? TIA, R. -- 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]
RE: More query help for user-defined values
Given the three tables (Type, Value, and Links), here is another (crazy looking) way to structure the query for the Person table. SELECT P.* FROM Person P WHERE ( EXISTS ( SELECT null FROM StringLinks L JOIN StringValue SV ON L.ID = SV.ID AND SV.refTypeID = 1 WHERE P.ID = L.parentID AND SV.f_val LIKE 'foo' ) OR ( NOT EXISTS ( SELECT null FROM StringLinks L JOIN StringValue SV2 ON L.ID = SV.ID AND SV.refTypeID = 1 WHERE P.ID = L.parentID AND SV.f_val LIKE 'foo' ) AND EXISTS ( SELECT null FROM StringType ST WHERE ST.ID = 1 AND ST.defaultVal LIKE 'foo' ) ) ) AND ( EXISTS ( SELECT null FROM StringLinks L JOIN StringValue SV ON L.ID = SV.ID AND SV.refTypeID = 1 WHERE P.ID = L.parentID AND SV.f_val LIKE 'bar' ) OR ( NOT EXISTS ( SELECT null FROM StringLinks L JOIN StringValue SV2 ON L.ID = SV.ID AND SV.refTypeID = 1 WHERE P.ID = L.parentID AND SV.f_val LIKE 'bar' ) AND EXISTS ( SELECT null FROM StringType ST WHERE ST.ID = 1 AND ST.defaultVal LIKE 'bar' ) ) ) -Original Message- From: Robert DiFalco Sent: Friday, August 18, 2006 12:37 PM To: Robert DiFalco; 'mysql@lists.mysql.com' Subject: RE: More query help for user-defined values No takers? -Original Message- From: Robert DiFalco Sent: Thursday, August 17, 2006 3:03 PM To: Robert DiFalco; mysql@lists.mysql.com Subject: RE: More query help for user-defined values One simple solution is to denormalize a little and take the refTypeID column out of StringValue and add it to the Links table. Then I could have: SELECT P.* FROM Person P LEFT JOIN StringLinks L1 ON P.ID = L1.parentID AND L1.refTypeID = 1 LEFT JOIN StringValue SV1 ON L1.valueID = SV1.ID LEFT JOIN StringType ST1 ON ST1.ID = 1 LEFT JOIN StringLinks L2 ON P.ID = L2.parentID AND L2.refTypeID = 2 LEFT JOIN StringValue SV2 ON L2.valueID = SV2.ID LEFT JOIN StringType ST2 ON ST2.ID = 2 WHERE ( SV1.f_val LIKE 'foo' OR ( SV1.val IS NULL AND ST1.defaultVal LIKE 'foo' ) ) AND ( SV2.f_val LIKE 'bar' OR ( SV2.val IS NULL AND ST2.defaultVal LIKE 'bar' ) ) -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Thursday, August 17, 2006 8:43 AM To: mysql@lists.mysql.com Subject: More query help for user-defined values To support user defined properties I have the following tables: TABLE StringType LONG ID PKEY VARCHAR name ... VARCHAR defaultValue TABLE StringValue LONG parentID LONG typeID VARCHAR val Assume the correct indices are in place. Different string value types can be defined by giving them a new row in StringType. The StringValue.parentID can be a row from any table that has a LONG identity column. If a row in the parent table does not have a corresponding row in StringValue, we want to assume an implicit default value. So to return all the Person rows that have either an explicit StringValue of ( type 1 and value foo ) or of ( type 2 and value bar ) I can just execute the following query. SELECT P.* FROM Person P LEFT JOIN StringValue SV1 ON P.ID = SV1.parentID AND SV1.refTypeID = 1 LEFT JOIN StringType ST1 ON ST1.ID = 1 LEFT JOIN StringValue SV2 ON P.ID = SV2.parentID AND SV2.refTypeID = 2 LEFT JOIN StringType ST2 ON ST2.ID = 2 WHERE ( SV1.f_val LIKE 'foo' OR ( SV1.val IS NULL AND ST1.defaultVal LIKE 'foo' ) ) AND ( SV2.f_val LIKE 'bar' OR ( SV2.val IS NULL AND ST2.defaultVal LIKE 'bar' ) ) The above seems to work fine. I can also get rid of the constant joins on the type table and just do a subquery for the default value, either approach works. Now I want to change things to introduce a ValueLink table so that if 100 rows have the same string value that I don't have to write the string 100 times. So my table structure changes like this: TABLE StringType LONG ID PKEY VARCHAR name ... VARCHAR defaultValue TABLE StringValue LONG ID PKEY // the value unique ID LONG typeID VARCHAR val TABLE StringLinks LONG parentID PKEY LONG valueID PKEY My naive approach to the query was this: SELECT P.* FROM Person P LEFT JOIN StringLinks L ON P.ID = L.parentID LEFT JOIN StringValue SV1 ON L.ID = SV1.ID AND SV1.refTypeID = 1 LEFT JOIN StringType ST1 ON ST1.ID = 1 LEFT JOIN StringValue SV2 ON L.ID = SV2.ID AND SV2.refTypeID = 2 LEFT JOIN StringType ST2 ON ST2.ID = 2 WHERE ( SV1.f_val LIKE 'foo' OR ( SV1.val IS NULL AND ST1.defaultVal LIKE 'foo' ) ) AND ( SV2
More query help for user-defined values
To support user defined properties I have the following tables: TABLE StringType LONG ID PKEY VARCHAR name ... VARCHAR defaultValue TABLE StringValue LONG parentID LONG typeID VARCHAR val Assume the correct indices are in place. Different string value types can be defined by giving them a new row in StringType. The StringValue.parentID can be a row from any table that has a LONG identity column. If a row in the parent table does not have a corresponding row in StringValue, we want to assume an implicit default value. So to return all the Person rows that have either an explicit StringValue of ( type 1 and value foo ) or of ( type 2 and value bar ) I can just execute the following query. SELECT P.* FROM Person P LEFT JOIN StringValue SV1 ON P.ID = SV1.parentID AND SV1.refTypeID = 1 LEFT JOIN StringType ST1 ON ST1.ID = 1 LEFT JOIN StringValue SV2 ON P.ID = SV2.parentID AND SV2.refTypeID = 2 LEFT JOIN StringType ST2 ON ST2.ID = 2 WHERE ( SV1.f_val LIKE 'foo' OR ( SV1.val IS NULL AND ST1.defaultVal LIKE 'foo' ) ) AND ( SV2.f_val LIKE 'bar' OR ( SV2.val IS NULL AND ST2.defaultVal LIKE 'bar' ) ) The above seems to work fine. I can also get rid of the constant joins on the type table and just do a subquery for the default value, either approach works. Now I want to change things to introduce a ValueLink table so that if 100 rows have the same string value that I don't have to write the string 100 times. So my table structure changes like this: TABLE StringType LONG ID PKEY VARCHAR name ... VARCHAR defaultValue TABLE StringValue LONG ID PKEY // the value unique ID LONG typeID VARCHAR val TABLE StringLinks LONG parentID PKEY LONG valueID PKEY My naive approach to the query was this: SELECT P.* FROM Person P LEFT JOIN StringLinks L ON P.ID = L.parentID LEFT JOIN StringValue SV1 ON L.ID = SV1.ID AND SV1.refTypeID = 1 LEFT JOIN StringType ST1 ON ST1.ID = 1 LEFT JOIN StringValue SV2 ON L.ID = SV2.ID AND SV2.refTypeID = 2 LEFT JOIN StringType ST2 ON ST2.ID = 2 WHERE ( SV1.f_val LIKE 'foo' OR ( SV1.val IS NULL AND ST1.defaultVal LIKE 'foo' ) ) AND ( SV2.f_val LIKE 'bar' OR ( SV2.val IS NULL AND ST2.defaultVal LIKE 'bar' ) ) As you can probably tell this wont work because the two values will now show up in two different rows so the AND of the search terms will always fail. Can anyone think of a clean way to do this or do I have to get rid of the joins altogether and do a subquery for each StringValue search term? TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: More query help for user-defined values
One simple solution is to denormalize a little and take the refTypeID column out of StringValue and add it to the Links table. Then I could have: SELECT P.* FROM Person P LEFT JOIN StringLinks L1 ON P.ID = L1.parentID AND L1.refTypeID = 1 LEFT JOIN StringValue SV1 ON L1.valueID = SV1.ID LEFT JOIN StringType ST1 ON ST1.ID = 1 LEFT JOIN StringLinks L2 ON P.ID = L2.parentID AND L2.refTypeID = 2 LEFT JOIN StringValue SV2 ON L2.valueID = SV2.ID LEFT JOIN StringType ST2 ON ST2.ID = 2 WHERE ( SV1.f_val LIKE 'foo' OR ( SV1.val IS NULL AND ST1.defaultVal LIKE 'foo' ) ) AND ( SV2.f_val LIKE 'bar' OR ( SV2.val IS NULL AND ST2.defaultVal LIKE 'bar' ) ) -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Thursday, August 17, 2006 8:43 AM To: mysql@lists.mysql.com Subject: More query help for user-defined values To support user defined properties I have the following tables: TABLE StringType LONG ID PKEY VARCHAR name ... VARCHAR defaultValue TABLE StringValue LONG parentID LONG typeID VARCHAR val Assume the correct indices are in place. Different string value types can be defined by giving them a new row in StringType. The StringValue.parentID can be a row from any table that has a LONG identity column. If a row in the parent table does not have a corresponding row in StringValue, we want to assume an implicit default value. So to return all the Person rows that have either an explicit StringValue of ( type 1 and value foo ) or of ( type 2 and value bar ) I can just execute the following query. SELECT P.* FROM Person P LEFT JOIN StringValue SV1 ON P.ID = SV1.parentID AND SV1.refTypeID = 1 LEFT JOIN StringType ST1 ON ST1.ID = 1 LEFT JOIN StringValue SV2 ON P.ID = SV2.parentID AND SV2.refTypeID = 2 LEFT JOIN StringType ST2 ON ST2.ID = 2 WHERE ( SV1.f_val LIKE 'foo' OR ( SV1.val IS NULL AND ST1.defaultVal LIKE 'foo' ) ) AND ( SV2.f_val LIKE 'bar' OR ( SV2.val IS NULL AND ST2.defaultVal LIKE 'bar' ) ) The above seems to work fine. I can also get rid of the constant joins on the type table and just do a subquery for the default value, either approach works. Now I want to change things to introduce a ValueLink table so that if 100 rows have the same string value that I don't have to write the string 100 times. So my table structure changes like this: TABLE StringType LONG ID PKEY VARCHAR name ... VARCHAR defaultValue TABLE StringValue LONG ID PKEY // the value unique ID LONG typeID VARCHAR val TABLE StringLinks LONG parentID PKEY LONG valueID PKEY My naive approach to the query was this: SELECT P.* FROM Person P LEFT JOIN StringLinks L ON P.ID = L.parentID LEFT JOIN StringValue SV1 ON L.ID = SV1.ID AND SV1.refTypeID = 1 LEFT JOIN StringType ST1 ON ST1.ID = 1 LEFT JOIN StringValue SV2 ON L.ID = SV2.ID AND SV2.refTypeID = 2 LEFT JOIN StringType ST2 ON ST2.ID = 2 WHERE ( SV1.f_val LIKE 'foo' OR ( SV1.val IS NULL AND ST1.defaultVal LIKE 'foo' ) ) AND ( SV2.f_val LIKE 'bar' OR ( SV2.val IS NULL AND ST2.defaultVal LIKE 'bar' ) ) As you can probably tell this wont work because the two values will now show up in two different rows so the AND of the search terms will always fail. Can anyone think of a clean way to do this or do I have to get rid of the joins altogether and do a subquery for each StringValue search term? TIA, R. -- 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]
RE: Query Help for Loosely Couple Properties
So the question is what if I have multiple conditions say COLOR LIKE 'Blue' [AND|OR] NAME LIKE 'Fred'. Will I have to create a JOIN for each? SELECT * FROM T LEFT JOIN StringVal CSV ON T.ID = CSV.REF_ID INNER JOIN StringType CST ON CSV.TYPE_ID = CST.ID AND CST.ID = COLOR LEFT JOIN StringVal NSV ON T.ID = NSV.REF_ID INNER JOIN StringType NST ON NSV.TYPE_ID = NST.ID AND NST.ID = NAME WHERE ( CSV.VAL = 'Blue' OR (CST.VAL = 'Blue' AND CSV.REF_ID IS NULL) ) AND ( NSV.VAL = 'Fred' OR (NST.VAL = 'Fred' AND NSV.REF_ID IS NULL) ) How do I generally simplify this? R. -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Friday, August 04, 2006 4:12 PM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: RE: Query Help for Loosely Couple Properties I think this only works if I know that Orange is the default value. For example if Orange is the default and I am searching for Blue I will have to use a different query. Since I don't know the default (its in the type table) I would have to do two queries. How about something like this? SELECT * FROM T LEFT JOIN StringVal SV ON T.ID = SV.REF_ID INNER JOIN StringType ST ON SV.TYPE_ID = ST.ID AND ST.ID = COLOR WHERE SV.VAL = Blue OR (ST.VAL = Blue AND SV.REF_ID IS NULL); -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 02, 2006 9:37 AM To: Robert DiFalco Cc: mysql@lists.mysql.com Subject: Re: Query Help for Loosely Couple Properties On Tue, 2006-08-01 at 16:23 -0700, Robert DiFalco wrote: The question is, how do I query this? Say I want all records from table T whose COLOR property value is ORANGE. The only thing I can come up with (and I'm no SQL expert and this looks wrong to me) is the following: SELECT * FROM T WHERE ( T.ID NOT IN ( SELECT StringVal.REF_ID FROM StringVal WHERE StringValue.TYPE_ID = COLOR ) AND EXISTS ( SELECT * FROM StringType WHERE StringType.DEF_VAL LIKE Orange AND StringType.ID = COLOR ) ) OR ( T.ID IN ( SELECT StringVal.REF_ID FROM StringVal WHERE StringVal.VAL LIKE Orange AND StringVal.TYPE_ID = COLOR ) ) SELECT * FROM T LEFT JOIN StringVal V ON T.ID = V.REF_ID INNER JOIN StringType ST ON V.TYPE_ID = ST.ID AND ST.ID = COLOR WHERE V.REF_ID IS NULL OR V.VAL = Orange; -- 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]
RE: Query Help for Loosely Couple Properties
I think this only works if I know that Orange is the default value. For example if Orange is the default and I am searching for Blue I will have to use a different query. Since I don't know the default (its in the type table) I would have to do two queries. How about something like this? SELECT * FROM T LEFT JOIN StringVal SV ON T.ID = SV.REF_ID INNER JOIN StringType ST ON SV.TYPE_ID = ST.ID AND ST.ID = COLOR WHERE SV.VAL = Blue OR (ST.VAL = Blue AND SV.REF_ID IS NULL); -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 02, 2006 9:37 AM To: Robert DiFalco Cc: mysql@lists.mysql.com Subject: Re: Query Help for Loosely Couple Properties On Tue, 2006-08-01 at 16:23 -0700, Robert DiFalco wrote: The question is, how do I query this? Say I want all records from table T whose COLOR property value is ORANGE. The only thing I can come up with (and I'm no SQL expert and this looks wrong to me) is the following: SELECT * FROM T WHERE ( T.ID NOT IN ( SELECT StringVal.REF_ID FROM StringVal WHERE StringValue.TYPE_ID = COLOR ) AND EXISTS ( SELECT * FROM StringType WHERE StringType.DEF_VAL LIKE Orange AND StringType.ID = COLOR ) ) OR ( T.ID IN ( SELECT StringVal.REF_ID FROM StringVal WHERE StringVal.VAL LIKE Orange AND StringVal.TYPE_ID = COLOR ) ) SELECT * FROM T LEFT JOIN StringVal V ON T.ID = V.REF_ID INNER JOIN StringType ST ON V.TYPE_ID = ST.ID AND ST.ID = COLOR WHERE V.REF_ID IS NULL OR V.VAL = Orange; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query Help for Loosely Couple Properties
They are user defined properties. -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 01, 2006 8:11 PM To: Robert DiFalco Cc: mysql@lists.mysql.com Subject: Re: Query Help for Loosely Couple Properties On Tue, 2006-08-01 at 16:23 -0700, Robert DiFalco wrote: I have a table that contains properties that can be associated with any table whose primary key is a LONG. Lets say that there is just one kind of property. The table looks something like this: TABLE StringVal REF_ID BIGINT// row to associate property with TYPE_ID BIGINT// type of string property VAL VARCHAR // property value P_KEY( REF_ID, TYPE_ID ) There is another table to represent a specific StringVal type along with its default value: TABLE StringType ID BIGINT // The TYPE ID NAMEVARCHAR // The unique name of this property DEF_VAL VARCHAR // The default value of this property Actually, the rub is that you are not using specific columns for specific entity attributes, and are pretty much storing everything in one gigantic table. Any particular reason for this? For instance, why not have a column called color, instead of overcomplicating things? -jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Help for Loosely Couple Properties
I have a table that contains properties that can be associated with any table whose primary key is a LONG. Lets say that there is just one kind of property. The table looks something like this: TABLE StringVal REF_ID BIGINT// row to associate property with TYPE_ID BIGINT// type of string property VAL VARCHAR // property value P_KEY( REF_ID, TYPE_ID ) There is another table to represent a specific StringVal type along with its default value: TABLE StringType ID BIGINT // The TYPE ID NAMEVARCHAR // The unique name of this property DEF_VAL VARCHAR // The default value of this property The rub is that the target table could have millions of records and I only want a record in StringVal if the associated property is going to be a value other than the default. So consider that StringType has a record that defines a property named COLOR with a default value of ORANGE. For some table T, a T record will only have a corresponding row in StringVal if it has a COLOR property whose value has been explicitly set. It *could* be ORANGE but in most cases it will be something else. Each row implicitly gets a COLOR value of ORANGE. The question is, how do I query this? Say I want all records from table T whose COLOR property value is ORANGE. The only thing I can come up with (and I'm no SQL expert and this looks wrong to me) is the following: SELECT * FROM T WHERE ( T.ID NOT IN ( SELECT StringVal.REF_ID FROM StringVal WHERE StringValue.TYPE_ID = COLOR ) AND EXISTS ( SELECT * FROM StringType WHERE StringType.DEF_VAL LIKE Orange AND StringType.ID = COLOR ) ) OR ( T.ID IN ( SELECT StringVal.REF_ID FROM StringVal WHERE StringVal.VAL LIKE Orange AND StringVal.TYPE_ID = COLOR ) ) Any suggestions on how to simplify this (besides writing a row for each T that has the default value)? Should I lay out the tables differently or keep the DDL the same and just clean up the query? R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: max size of TEXT columns
FWIW, IMO LOB handling is really where MySQL lags behind all other enterprise database solutions. Firebird, DB2, Oracle, et al all are able to stream LOB data to and from disk so that it does not all need to be loaded in memory (multiple times for a single LOB). I would call this a bug but others would probably prefer to call it a limitation. :) -Original Message- From: schlubediwup [mailto:[EMAIL PROTECTED] Sent: Monday, May 29, 2006 8:07 AM To: mysql@lists.mysql.com Subject: max size of TEXT columns hi listers, 1. environment: [EMAIL PROTECTED] ~ uname -a Linux myhost.mydomain.com 2.6.16-1.2080_2.rhfc5.cubbi_suspend2 #1 Wed Mar 29 12:54:32 CEST 2006 i686 i686 i386 GNU/Linux [EMAIL PROTECTED] ~ localhost.(none) show variables like version%; +-+--+ | Variable_name | Value | +-+--+ | version | 5.0.18-standard | | version_comment | MySQL Community Edition - Standard (GPL) | | version_compile_machine | i686 | | version_compile_os | pc-linux-gnu | +-+--+ 4 rows in set (0.00 sec) localhost.(none) localhost.mydb show variables like max%; ++--+ | Variable_name | Value | ++--+ | max_allowed_packet | 1048576 | localhost.mydb select max(char_length(history)) from my_contacts2; +---+ | max(char_length(history)) | +---+ | 65535 | +---+ 1 row in set (0.01 sec) localhost.mydb 2. problem according do the mysql docu TEXT/BLOB fields depend solely on the db environment and can grow to any length: 11.4.3. The |BLOB| and |TEXT| Types The maximum size of a |BLOB| or |TEXT| object is determined by its type, but the largest value you actually can transmit between the client and server is determined by the amount of available memory and the size of the communications buffers. You can change the message buffer size by changing the value of the |max_allowed_packet| variable, but you must do so for both the server and your client program. For example, both *mysql* and *mysqldump* allow you to change the client-side |max_allowed_packet| value. See Section 7.5.2, Tuning Server Parameters http://www.ayni.com/mysql/optimization.html#server-parameters, Section 8.3, mysql - The MySQL Command-Line Tool http://www.ayni.com/mysql/client-side-scripts.html#mysql, and Section 8.8, mysqldump - A Database Backup Program http://www.ayni.com/mysql/client-side-scripts.html#mysqldump. BUT: The table in consideration contains a column named history containing all message text which has been sent to the mail-address in the row in question. i ment to have observed that this column in reality does not contain all message text from the very beginning. today, i found out that the maximum lenght of the history fields in all tables of this kind is 65535. when an update is ocurring, the column history is recreated using concat(new_text, history) so that the newest text is always at the beginning of the column. but the oldest text at the end of the column apparently is lost. 3. question which parameter do i have to change in order to get this TEXT column really to any length? any hint is very much appreciated, thanks in advance. suomi -- 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]
RE: how to extract common text string from field?
Why not store them in separate columns? You could then have the domain field be a foreign key into another table. -Original Message- From: Ferindo Middleton [mailto:[EMAIL PROTECTED] Sent: Friday, May 12, 2006 2:14 PM To: mysql@lists.mysql.com Subject: how to extract common text string from field? Suppose you have a field in a db table that holds email addresses and all of the address end in domain.com. Is there a MySQL function that can be used to extract the first part of the email address, the username (the part of the email address before the 'domain.com' part of the email address). I was hoping somehow to do this within a SELECT statement so instead of seeing the email address I would see the username without the 'domain.com' part. Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Intermittent deadlock/InnoDB
No problem, just didn't want to jump the gun if it was a known issue or if it were clear from the posted traces that I was doing something wrong. -Original Message- From: Stewart Smith [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 10, 2006 10:54 PM To: Robert DiFalco Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: Intermittent deadlock/InnoDB On Wed, 2006-05-10 at 16:26 -0700, Robert DiFalco wrote: Version 5.0.19. We have no autogenerate keys. We perform a single insert and get a lock timeout. The insert is done with a stored procedure with a single line. The lockup happens VERY rarely and we have no idea how to reproduce it. Probably best to file a bug report in the bugs system. This is the best way so we can track problem reports. -- Stewart Smith, Software Engineer MySQL AB, www.mysql.com Office: +14082136540 Ext: 6616 VoIP: [EMAIL PROTECTED] Mobile: +61 4 3 8844 332 Jumpstart your cluster: http://www.mysql.com/consulting/packaged/cluster.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Intermittent deadlock/InnoDB
Version 5.0.19. We have no autogenerate keys. We perform a single insert and get a lock timeout. The insert is done with a stored procedure with a single line. The lockup happens VERY rarely and we have no idea how to reproduce it. Here's the hostname.err data: Alarm status: Active alarms: 0 Max used alarms: 0 Next alarm time: 0 Thread database.table_name Locked/WaitingLock_type 11 te.node Locked - writeConcurrent insert lock And then here is the SHOW INNODB STATUS output. Notice that thread 11 is the one that is WAITING for the lock. However, above it says that it is locked. TRANSACTIONS Trx id counter 0 2912 Purge done for trx's n:o 0 2834 undo n:o 0 0 History list length 13 Total number of lock structs in row lock hash table 2 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, OS thread id 1928 MySQL thread id 30, query id 14092 172.18.0.102 root SHOW INNODB STATUS ---TRANSACTION 0 0, not started, OS thread id 1176 MySQL thread id 25, query id 13730 172.18.0.102 root ---TRANSACTION 0 0, not started, OS thread id 3176 MySQL thread id 15, query id 12618 rogerrabbit.tripwire.com 10.150.1.60 root ---TRANSACTION 0 2905, not started, OS thread id 1080 MySQL thread id 13, query id 14038 localhost 127.0.0.1 root ---TRANSACTION 0 2911, ACTIVE 1 sec, OS thread id 2312 inserting mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 320, undo log entries 2 MySQL thread id 11, query id 14091 localhost 127.0.0.1 root update INSERT INTO Node(F_MAKE,F_MODEL,F_VER,oid) VALUES (p_F_MAKE,p_F_MODEL,p_F_VER,p_oid) --- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 241 n bits 72 index `PRIMARY` of table `te/node` trx id 0 2911 lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; -- ---TRANSACTION 0 2791, ACTIVE 4064 sec, OS thread id 3096 2 lock struct(s), heap size 320 MySQL thread id 12, query id 13254 localhost 127.0.0.1 root == -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Backups with MySQL/InnoDB
What are people doing for backups on very large MySQL/InnoDB databases? Say for databases greater than 200 GB. Curious about the backup methods, procedures, and frequency.
RE: Backups with MySQL/InnoDB
Fast, incremental, compressed, and no max-size limitations. Must be transaction safe; able to run while transactions are going on without including any started after the backup began; the usual stuff. -Original Message- From: Greg 'groggy' Lehey [mailto:[EMAIL PROTECTED] Sent: Sunday, May 07, 2006 6:14 PM To: Robert DiFalco Cc: mysql@lists.mysql.com Subject: Re: Backups with MySQL/InnoDB On Sunday, 7 May 2006 at 9:27:31 -0700, Robert DiFalco wrote: What are people doing for backups on very large MySQL/InnoDB databases? Say for databases greater than 200 GB. Curious about the backup methods, procedures, and frequency. A second question, but not for the first time: how would you *like* to do backups if you had the choice? We're currently in the final stages of the design of an online backup solution, and in the near future I'll publish the specs. I won't mention them now to avoid influencing you, but now's the time to speak up if you want something specific. Greg -- Greg Lehey, Senior Software Engineer MySQL AB, http://www.mysql.com/ Echunga, South Australia Phone: +61-8-8388-8286 Mobile: +61-418-838-708 Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Streaming LOB Data
Anyone know for sure if the memory needed to insert a LOB is a percentage of the system's available memory or if it is allocated from the innodb_buffer_pool_size? IOW, how should my configuration settings be modified to allow the insertion of larger blobs? :) -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Saturday, April 29, 2006 9:49 AM To: Sergei Golubchik Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: RE: Streaming LOB Data Thanks Sergei, it's nice to know for sure. Do you know if there is any documentation on how memory is used to store LOB data? For example, is it a percentage of the total buffer pool size or is it allocated from available memory un-allocated to the buffer pool size? -Original Message- From: Sergei Golubchik [mailto:[EMAIL PROTECTED] Sent: Saturday, April 29, 2006 9:34 AM To: Robert DiFalco Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: Streaming LOB Data Hi! On Apr 28, Robert DiFalco wrote: It appears (for InnoDB at least) that while INSERTing a LOB that all LOB data must be loaded into memory before it is written to disk. Or is it just the size of the combined log files? Looking at Task Manager, it looks like it DOES try to load the whole thing into memory before streaming it to disk; which just seems wacky. The error I get is this: Out of memory (Needed xxx ... If the server crashes I get this: InnoDB: which exceeds the log group capacity 18870682. InnoDB: If you are using big BLOB or TEXT rows, you must set the InnoDB: combined size of log files at least 10 times bigger than the InnoDB: largest such row. 060427 15:26:53 InnoDB: Error: cannot allocate 539001144 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 52823786 bytes. Operating system errno: 8 InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. InnoDB: Note that in most 32-bit computers the process InnoDB: memory space is limited to 2 GB or 4 GB. How can I make the size of LOBs I insert NOT be memory constrained? No way. Unfortunately, it's the way MySQL works at the moment. Which does not mean it's not going to be changed in the future. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Kerpen, Germany ___/ 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]
RE: Optimizing DISTINCT searches
Well, normally a DISTINCT has to do a type of sort and is slower than non-DISTINCT queries. Each field of the result set is considered in the DISTINCT logic. Can you modify the query so that it does not require the DISTINCT? Can you post the query? R. -Original Message- From: Stephen P. Fracek, Jr. [mailto:[EMAIL PROTECTED] Sent: Monday, May 01, 2006 7:52 AM To: mysql@lists.mysql.com Subject: Optimizing DISTINCT searches Several of my DISTINCT searches are frequently showing up in the slow query log. These queries use multiple table joins. Using EXPLAIN shows that the queries are using the appropriate keys, as far as I know. Are DISTINCT searches using multiple joins slow? TIA. Stephen P. Fracek, Jr. [EMAIL PROTECTED] -- 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]
RE: Optimizing DISTINCT searches
Would you need the DISTINCT if you change the query like so? SELECT Site.Site_ID, Site, Status, Type FROM Site JOIN Project ON Site.Site_ID = Project.Site_ID ORDER BY Site; You may also want to just try your initial query without the distinct to see if that is the issue. Also, do you have an index on the Site column? The issue with this query is that you are pretty much selecting everything from the Project table. R. -Original Message- From: Stephen P. Fracek, Jr. [mailto:[EMAIL PROTECTED] Sent: Monday, May 01, 2006 11:05 AM To: mysql@lists.mysql.com Subject: FW: Optimizing DISTINCT searches On 2006-05-01 11:55 AM, Robert DiFalco [EMAIL PROTECTED] wrote: Well, normally a DISTINCT has to do a type of sort and is slower than non-DISTINCT queries. Each field of the result set is considered in the DISTINCT logic. Can you modify the query so that it does not require the DISTINCT? Can you post the query? Robert - Query: SELECT DISTINCT Project.Site_ID, Site, Status, Type FROM Project, Site WHERE Site.Site_ID = Project.Site_ID ORDER BY Site; Site is the site name, Status and Type contain additional information about the site, and Site_ID is the unique site id. The Project table contains among other things a list of sites where the projects are being done. The results of this query are supposed to be a non-duplicated list of sites that are associated with at least one project. As the number of projects and sites have increased, this query is now frequently in the slow query log. Stephen P. Fracek, Jr. [EMAIL PROTECTED] -- 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]
RE: Optimizing DISTINCT searches
How about something like this? SELECT Site.Site_ID, Site, Status, Type FROM Site WHERE EXISTS( SELECT * FROM Project) ORDER BY Site; I'm assuming Site_ID is unique in the Site table? -Original Message- From: Stephen P. Fracek, Jr. [mailto:[EMAIL PROTECTED] Sent: Monday, May 01, 2006 1:27 PM To: mysql@lists.mysql.com Subject: Re: Optimizing DISTINCT searches On 2006-05-01 1:14 PM, Robert DiFalco [EMAIL PROTECTED] wrote: Would you need the DISTINCT if you change the query like so? SELECT Site.Site_ID, Site, Status, Type FROM Site JOIN Project ON Site.Site_ID = Project.Site_ID ORDER BY Site; You may also want to just try your initial query without the distinct to see if that is the issue. Also, do you have an index on the Site column? The issue with this query is that you are pretty much selecting everything from the Project table. Robert - Your query doesn't work - it finds ALL the rows in Project table and hence repeats the sites.. I do have an index on the Site table, it is the Site_ID. The Project.Site_ID is also indexed. Stephen P. Fracek, Jr. [EMAIL PROTECTED] -- 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]
RE: Streaming LOB Data
Thanks Sergei, it's nice to know for sure. Do you know if there is any documentation on how memory is used to store LOB data? For example, is it a percentage of the total buffer pool size or is it allocated from available memory un-allocated to the buffer pool size? -Original Message- From: Sergei Golubchik [mailto:[EMAIL PROTECTED] Sent: Saturday, April 29, 2006 9:34 AM To: Robert DiFalco Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: Streaming LOB Data Hi! On Apr 28, Robert DiFalco wrote: It appears (for InnoDB at least) that while INSERTing a LOB that all LOB data must be loaded into memory before it is written to disk. Or is it just the size of the combined log files? Looking at Task Manager, it looks like it DOES try to load the whole thing into memory before streaming it to disk; which just seems wacky. The error I get is this: Out of memory (Needed xxx ... If the server crashes I get this: InnoDB: which exceeds the log group capacity 18870682. InnoDB: If you are using big BLOB or TEXT rows, you must set the InnoDB: combined size of log files at least 10 times bigger than the InnoDB: largest such row. 060427 15:26:53 InnoDB: Error: cannot allocate 539001144 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 52823786 bytes. Operating system errno: 8 InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. InnoDB: Note that in most 32-bit computers the process InnoDB: memory space is limited to 2 GB or 4 GB. How can I make the size of LOBs I insert NOT be memory constrained? No way. Unfortunately, it's the way MySQL works at the moment. Which does not mean it's not going to be changed in the future. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Kerpen, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Streaming LOB Data
It appears (for InnoDB at least) that while INSERTing a LOB that all LOB data must be loaded into memory before it is written to disk. Or is it just the size of the combined log files? Looking at Task Manager, it looks like it DOES try to load the whole thing into memory before streaming it to disk; which just seems wacky. The error I get is this: Out of memory (Needed xxx ... If the server crashes I get this: InnoDB: which exceeds the log group capacity 18870682. InnoDB: If you are using big BLOB or TEXT rows, you must set the InnoDB: combined size of log files at least 10 times bigger than the InnoDB: largest such row. 060427 15:26:53 InnoDB: Error: cannot allocate 539001144 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 52823786 bytes. Operating system errno: 8 InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. InnoDB: Note that in most 32-bit computers the process InnoDB: memory space is limited to 2 GB or 4 GB. How can I make the size of LOBs I insert NOT be memory constrained? R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Out of memory (Needed xxx ...
Setting max_allowed_packet does not seem to make a difference on how large the blob to the database can be. The MySQL server still gives the OOM exception after streaming ~600mb to a LOB regardless of how max_allowed_packet is set. R. From: Carlos Proal [mailto:[EMAIL PROTECTED] Sent: Thursday, April 27, 2006 11:31 AM To: Robert DiFalco Cc: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Re: Out of memory (Needed xxx ... As far as i remember you must set that variable in order to send huge blobs, and the error message can vary because it can means that the current memory space for that connection has been filled. Why not do you try to set it up, as mentioned in the manual and check the results. Im forwarding this to the java list, maybe Mark can reserve a liitle time from the Mysql Users Conference and give us some feedback. Carlos On 4/26/06, Robert DiFalco [EMAIL PROTECTED] wrote: It's not set, but I am streaming the LOG to the server, would max packet impact this situation? Also, wouldn't I get a different error, i.e. Packet Too Large? From: Carlos Proal [mailto: [EMAIL PROTECTED] Sent: Wednesday, April 26, 2006 4:59 PM To: Robert DiFalco Cc: mysql@lists.mysql.com Subject: Re: Out of memory (Needed xxx ... And whats the size of your max_allowed_packet variable ? Carlos On 4/26/06, Robert DiFalco [EMAIL PROTECTED] mailto: [EMAIL PROTECTED] wrote: I am, the java heap is set to 2G. But I don't think it is my java process that is running out of memory, I believe it is the MySQL server. -Original Message- From: Carlos Proal [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 26, 2006 4:53 PM Cc: mysql@lists.mysql.com Subject: Re: Out of memory (Needed xxx ... Hi Robert, are you using the extended parameters to increase the jvm heap memory ? ie. java -Xms256m -Xmx512m Carlos On 4/26/06, Robert DiFalco [EMAIL PROTECTED] wrote: Getting this error from JDBC while inserting a VERY large VARBINARY or MEDIUMTEXT field, ~250MB. I'm guessing this is an error from the server? Is there a way to have the server start streaming to disk sooner with a LOB? Is there a property I'm not setting? TIA, R. -- 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]
Out of memory (Needed xxx ...
Getting this error from JDBC while inserting a VERY large VARBINARY or MEDIUMTEXT field, ~250MB. I'm guessing this is an error from the server? Is there a way to have the server start streaming to disk sooner with a LOB? Is there a property I'm not setting? TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Out of memory (Needed xxx ...
I am, the java heap is set to 2G. But I don't think it is my java process that is running out of memory, I believe it is the MySQL server. -Original Message- From: Carlos Proal [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 26, 2006 4:53 PM Cc: mysql@lists.mysql.com Subject: Re: Out of memory (Needed xxx ... Hi Robert, are you using the extended parameters to increase the jvm heap memory ? ie. java -Xms256m -Xmx512m Carlos On 4/26/06, Robert DiFalco [EMAIL PROTECTED] wrote: Getting this error from JDBC while inserting a VERY large VARBINARY or MEDIUMTEXT field, ~250MB. I'm guessing this is an error from the server? Is there a way to have the server start streaming to disk sooner with a LOB? Is there a property I'm not setting? TIA, R. -- 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]
RE: Out of memory (Needed xxx ...
It's not set, but I am streaming the LOG to the server, would max packet impact this situation? Also, wouldn't I get a different error, i.e. Packet Too Large? From: Carlos Proal [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 26, 2006 4:59 PM To: Robert DiFalco Cc: mysql@lists.mysql.com Subject: Re: Out of memory (Needed xxx ... And whats the size of your max_allowed_packet variable ? Carlos On 4/26/06, Robert DiFalco [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: I am, the java heap is set to 2G. But I don't think it is my java process that is running out of memory, I believe it is the MySQL server. -Original Message- From: Carlos Proal [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 26, 2006 4:53 PM Cc: mysql@lists.mysql.com Subject: Re: Out of memory (Needed xxx ... Hi Robert, are you using the extended parameters to increase the jvm heap memory ? ie. java -Xms256m -Xmx512m Carlos On 4/26/06, Robert DiFalco [EMAIL PROTECTED] wrote: Getting this error from JDBC while inserting a VERY large VARBINARY or MEDIUMTEXT field, ~250MB. I'm guessing this is an error from the server? Is there a way to have the server start streaming to disk sooner with a LOB? Is there a property I'm not setting? TIA, R. -- 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]
RE: Making result rows to one row
Can you tell us more about the use case? Why does this need to be in a single row? Maybe instead of figuring out how to get this into a single row we could instead figure out how to solve the problem that requires it to be in a single row. -Original Message- From: Peter Lauri [mailto:[EMAIL PROTECTED] Sent: Monday, April 10, 2006 10:45 AM To: mysql@lists.mysql.com Subject: Making result rows to one row Best group member, Many of you probably do not understand the question; neither would I if someone ask me, so I will explain a little bit more. This is what I have: SELECT tps.strokes FROM tour_player_score tps join tour_scorecard_hole tsh WHERE tour_player_id=175 AND tps.scorecard_hole_id=tsh.id ORDER BY tsh.hole_number; This generates this: +-+ | strokes | +-+ | 6 | | 4 | | 5 | | 3 | | 5 | | 4 | | 4 | | 3 | | 6 | +-+ I would like to retrieve it in one row instead of many rows with one per row. s1 s2 s3 s4 s5 s6 s7 s8 s9 6 4 5 3 5 4 4 3 6 Can this be done? Best regards, Peter Lauri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RIGHT JOIN better than INNER JOIN?
Martjin, Of course one should use the right JOIN for the job. But let me ask you, which join would you use here? You have a table called Descriptors, it has a field called nameID which is a unique key that relates to a Names table made up of a unique identity and a VARCHAR name. I think most people would write a simple query like this: SELECT desc.fields, names.name FROM desc JOIN names ON desc.nameId = names.Id ORDER BY names.name However, it this really correct? Every descriptor has a record in names, so it could be equivalently written as: SELECT desc.fields, names.name FROM desc RIGHT JOIN names ON desc.nameId = names.Id ORDER BY names.name My guess is that most people conventionally write the first query. R. -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 21, 2006 1:01 AM To: mysql@lists.mysql.com Subject: Re: RIGHT JOIN better than INNER JOIN? I apologize if this is a naive question but it appears through my testing that a RIGHT JOIN may out perform an INNER JOIN in those cases where they would produce identical result sets. i.e. there are no keys in the left table that do not exist in the right table. Is this true? If so, it this peculiar to MySQL or would this be true with almost all database servers? i.e. Oracle, DB2, MSSQL, etc. Use the join that is appropriate for your query, do not use a different one. If speed isn't OK, then bug the guys that do the implementation, but don't start changing the query that _might_ return a different result if, for example, someone else starts working at the application and figures hey, this is a right join, so it's optional etc ... Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.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]
RE: RIGHT JOIN better than INNER JOIN?
For me the argument is a little pedantic. The contract of the descriptor table is that it must reference a name; there is code and constraints to enforce this. I am happy to have the query return nulls to indicate a programming error that can be quickly addressed. _If_ (after buffer tuning et al) a RIGHT JOIN still provides a substantial performance improvement over a FULL JOIN in this case, my customers would want me to provide that rather than have me tell them it is an inappropriate join or that I am asking the database server developers to improve their query optimizer. I wasn't really looking to get into a philosophical debate on correctness so let me restate my question a little better. Is there a reason in MySQL/InnoDB why a RIGHT JOIN would substantially out perform a FULL JOIN in those cases where the results would be identical? It is a little difficult to test query performance empirically since performance will change as different indices are swapped in and out of memory buffers and such (although I have turned query caching off), but it appears that for a table with 1-2 million rows a query similar to what I posted here was faster with a RIGHT JOIN. R. -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 21, 2006 9:43 AM To: mysql@lists.mysql.com Subject: Re: RIGHT JOIN better than INNER JOIN? Robert, Of course one should use the right JOIN for the job. But let me ask you, which join would you use here? You have a table called Descriptors, it has a field called nameID which is a unique key that relates to a Names table made up of a unique identity and a VARCHAR name. I think most people would write a simple query like this: SELECT desc.fields, names.name FROM desc JOIN names ON desc.nameId = names.Id ORDER BY names.name However, it this really correct? Every descriptor has a record in names, so it could be equivalently written as: SELECT desc.fields, names.name FROM desc RIGHT JOIN names ON desc.nameId = names.Id ORDER BY names.name My guess is that most people conventionally write the first query. Gee, I wonder why ... This happens to be the query that returns the rows as it should. What happens, if two years from now you didn't document WHY you wrote a right join query instead of an inner join and someone figures out that this could return nulls for a result column? If you start using the wrong joins, you will make things harder on yourself and others. As I said: if performance isn't satisfactory (which sounds a bit strange for this situation), then try to solve that. Either by using different index/buffer/caching strategies or by complaining to the people who created the database system in the first place. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.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]
RIGHT JOIN better than INNER JOIN?
I apologize if this is a naive question but it appears through my testing that a RIGHT JOIN may out perform an INNER JOIN in those cases where they would produce identical result sets. i.e. there are no keys in the left table that do not exist in the right table. Is this true? If so, it this peculiar to MySQL or would this be true with almost all database servers? i.e. Oracle, DB2, MSSQL, etc. TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Insert performance
I need some help with improving INSERT performance. I am using JDBC. I have minimized my indices as much as I can, changed to use batching, and setup a thread pool for each transacted batch. Note that I am only using InnoDB. Now I am wondering what can be tweaked in the server properties. Right now I have two log files, the log file size 512M and my buffer pool is 1.2G. 6 threads seems to work best for a single CPU. As an aside, even though I have minimized index usage as much as I can, I still notice that every subsequent set of inserts I do takes a little longer each time. For example, the fourth set of 1 million inserts takes at least twice as long as the first set of inserts. Do we have the ability to play with the Btree page depth or anything like that? TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query Optimization Question
Shawn, Any performance gains for specifying type 0 than type 0 ? R. From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, March 13, 2006 6:37 AM To: Robert DiFalco Cc: mysql@lists.mysql.com Subject: Re: Query Optimization Question Robert DiFalco [EMAIL PROTECTED] wrote on 03/11/2006 12:43:43 PM: In a previous database engine I was using an IN was more optimal than a . So, for example: SELECT * FROM table WHERE table.type IN (1,2,3); Where the possible values of type are 0-3, was appreciably faster than: SELECT * FROM table WHERE table.type 0; I've been playing with the Query Browser and checking out the optimization documents and haven't been able to make a clear call on whether or not this is also the case with MySQL/InnoDB. TIA, R. YES, YES, YES! This is definitely an optimization. When you say IN or =, you are asking for matching values. Matches can come from indexes. When you say or NOT IN, you are asking for everything BUT matches. In order to evaluate a negative, the database engine (and this is usually true regardless of database server) almost always performs a full table scan to test every row to make sure it is either or NOT IN. At the very best, they have to perform a full index scan which is still less efficient than ranged or values-based lookups. It's when you get into the situation where you are matching against dozens of IN-clause items that you may run into slowdowns again. Until you reach 2 or 3 dozen terms (depending on your hardware) you should be faster with an IN comparison than a or a NOT IN comparison. An optimization to search for BUT a term or two is to create a temporary table of all of your terms and delete the exact ones you want to exclude. Put an index on yoru temp table then JOIN that back into your query again (replacing the huge IN clause). The database will match index to index and things will get fast again. This technique can scale up to some really big queries. Always try to code for the affirmative tests. Your users will thank you. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query Optimization Question
Interesting, that seems like an optimization the query optimizer could do itself when it sees a operator on a indexed numeric. From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, March 13, 2006 8:01 AM To: Robert DiFalco Cc: mysql@lists.mysql.com Subject: RE: Query Optimization Question Yes, a ranged query should respond faster than a negation. In some cases you can seriously improve query performance for a negation query if you split it into two range queries unioned together. Here is a pseudo example: This query should be slow due to the table scan it takes to test the condition: SELECT ... FROM ... WHERE indexfield 16 This query should be noticeably faster due to using ranged queries (partial index ranged matches): (SELECT ... FROM ... WHERE indexfield 16 ) UNION ( SELECT ... FROM ... WHERE indexfield 16 ) Of course, if you have to do a table scan ANYWAY (because a value you have in a constraint is not in an index) then this won't help. This optimization is all about getting the engine to use an index whenever it can instead of performing a table scan. Of course, that is not always possible. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Robert DiFalco [EMAIL PROTECTED] wrote on 03/13/2006 10:48:29 AM: Shawn, Any performance gains for specifying type 0 than type 0 ? R. From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, March 13, 2006 6:37 AM To: Robert DiFalco Cc: mysql@lists.mysql.com Subject: Re: Query Optimization Question Robert DiFalco [EMAIL PROTECTED] wrote on 03/11/2006 12:43:43 PM: In a previous database engine I was using an IN was more optimal than a . So, for example: SELECT * FROM table WHERE table.type IN (1,2,3); Where the possible values of type are 0-3, was appreciably faster than: SELECT * FROM table WHERE table.type 0; I've been playing with the Query Browser and checking out the optimization documents and haven't been able to make a clear call on whether or not this is also the case with MySQL/InnoDB. TIA, R. YES, YES, YES! This is definitely an optimization. When you say IN or =, you are asking for matching values. Matches can come from indexes. When you say or NOT IN, you are asking for everything BUT matches. In order to evaluate a negative, the database engine (and this is usually true regardless of database server) almost always performs a full table scan to test every row to make sure it is either or NOT IN. At the very best, they have to perform a full index scan which is still less efficient than ranged or values-based lookups. It's when you get into the situation where you are matching against dozens of IN-clause items that you may run into slowdowns again. Until you reach 2 or 3 dozen terms (depending on your hardware) you should be faster with an IN comparison than a or a NOT IN comparison. An optimization to search for BUT a term or two is to create a temporary table of all of your terms and delete the exact ones you want to exclude. Put an index on yoru temp table then JOIN that back into your query again (replacing the huge IN clause). The database will match index to index and things will get fast again. This technique can scale up to some really big queries. Always try to code for the affirmative tests. Your users will thank you. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Query Optimization Question
In a previous database engine I was using an IN was more optimal than a . So, for example: SELECT * FROM table WHERE table.type IN (1,2,3); Where the possible values of type are 0-3, was appreciably faster than: SELECT * FROM table WHERE table.type 0; I've been playing with the Query Browser and checking out the optimization documents and haven't been able to make a clear call on whether or not this is also the case with MySQL/InnoDB. TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB Indices
I have some questions regarding InnoDB indices. Say I have a table with millions of records. On of the fields is a type field that has a possible value of 1,2,3, or 4. I sometimes query by the type field and may at other times order on it. Do queries benefit from an index with this low of a selectivity? If through the query browser I find that they do, should I increase the selectivity of the index by making it a compound index and adding my Primary Key to the TYPE index? If I make it a compound index, does it have any positive impact on INSERT, UPDATE, or maybe just DELETE? R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB Indices
++ I can't see it helping with insert, but depending on the where clause on your updates and deletes it could. I guess I was thinking that if an index with otherwise low selectivity added a rightmost column that was completely unique that it would improve key distribution and therefore make deletes faster. But every database engine handles this stuff differently. R -Original Message- From: David Turner [mailto:[EMAIL PROTECTED] Sent: Thursday, March 09, 2006 10:13 AM To: Robert DiFalco; mysql@lists.mysql.com Subject: Re: InnoDB Indices - Original Message From: Robert DiFalco [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, March 9, 2006 9:32:44 AM Subject: InnoDB Indices I have some questions regarding InnoDB indices. Say I have a table with millions of records. On of the fields is a type field that has a possible value of 1,2,3, or 4. I sometimes query by the type field and may at other times order on it. Do queries benefit from an index with this low of a selectivity? ++ For the most part no. I come from Oracle where you can use histograms to help. So, someone feel free to correct me if I'm wrong. If through the query browser I find that they do, should I increase the selectivity of the index by making it a compound index and adding my Primary Key to the TYPE index? ++ If your primary key will be included in the where clause then definitely include it. If I make it a compound index, does it have any positive impact on INSERT, UPDATE, or maybe just DELETE? ++ I can't see it helping with insert, but depending on the where clause on your updates and deletes it could. Dave R. -- 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]
SELECT DISTINCT Optimizations
SELECT DISTINCT can be kind of slow if there are many result values, specifically if those result values include large VARCHARs. Furthermore, some database engines cannot support a SELECT DISTINCT if any LOBs are included in the result values. I'm trying to find a general way to optimize SELECT DISTINCT since for more purposes I rarely need to decide DISTINCT on any more than a single column. For example, consider I have a tree structure where leafs or groups can have more than a single parent group (i.e. they are links). (A) SELECT DISTINCT G.oid, G.name, G.description, G.type [, ...] FROM Groupable G JOIN Link ON G.oid = Link.childId JOIN Path ON Link.parentId = Path.descendantId WHERE (G.type = LEAF AND Path.ancestorId = 12345) Something like this can be slow on a large result set with many large character columns and it does not even express clearly what I mean, because what I really mean to say is: (B) SELECT ( FOR DISTINCT ON G.oid ), G.name, G.description, G.type [, ...] FROM Groupable G JOIN Link ON G.oid = Link.childId JOIN Path ON Link.parentId = Path.descendantId WHERE (G.type = LEAF AND Path.ancestorId = 12345) Of course there is no valid SQL like this. So what I end up doing is the following: (C) SELECT G.oid, G.name, G.description, G.type [, ...] FROM Groupable G WHERE G.oid IN ( SELECT G.oid FROM Groupable G JOIN Link ON G.oid = Link.childId JOIN Path ON Link.parentId = Path.descendantId WHERE (G.type = LEAF AND Path.ancestorId = 12345)) For the majority of cases this will perform better than (A). However, sometimes the expense of the subquery will out weight the sort used for the distinct and it will not be faster. Is there a standard SQL way to avoid sorting on every field for a distinct on a unique key without having to perform a subquery? Maybe something with a group by? TIA for any ideas or thoughts... R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Updating Index Statistics
Is there a command to regenerating the selectivity statistics of indices with MySQL? Or does MySQL not have a cost based optimizer and this would make no difference? R.
Why do these transactions show table locks?
My understanding is that innodb should not be using table locks for insert, update, or delete. However, the following transactions are showing table locks. What's up? R. ---TRANSACTION 0 4573, ACTIVE 1 sec, OS thread id 3112 setting table lock mysql tables in use 1, locked 0 LOCK WAIT 2 lock struct(s), heap size 320, undo log entries 250 MySQL thread id 4, query id 566875 squid.tripwire.com 10.150.1.30 root System lock INSERT INTO ELEMS(oid,E_NID,E_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,E_LCTYP,E_ LCSEV) VALUES(-9223372036854523873,-9223372036854775299,-9223372036854775181,-9 223372036854744697,1,-1,-9223372036854027123,-9223372036854027123,1,0,0) --- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED: TABLE LOCK table `space/elems` trx id 0 4573 lock mode X waiting -- ---TRANSACTION 0 4569, ACTIVE 1 sec, OS thread id 1708 setting table lock mysql tables in use 1, locked 0 LOCK WAIT 2 lock struct(s), heap size 320, undo log entries 250 MySQL thread id 6, query id 565737 squid.tripwire.com 10.150.1.30 root System lock INSERT INTO ELEMS(oid,E_NID,E_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,E_LCTYP,E_ LCSEV) VALUES(-9223372036854557373,-9223372036854775352,-9223372036854775181,-9 223372036854601648,1,-1,-9223372036854027623,-9223372036854027623,1,0,0) --- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED: TABLE LOCK table `space/elems` trx id 0 4569 lock mode X waiting -- ---TRANSACTION 0 4565, ACTIVE 2 sec, OS thread id 4008 setting table lock mysql tables in use 1, locked 0 LOCK WAIT 2 lock struct(s), heap size 320, undo log entries 250 MySQL thread id 5, query id 564870 squid.tripwire.com 10.150.1.30 root System lock INSERT INTO ELEMS(oid,E_NID,E_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,E_LCTYP,E_ LCSEV) VALUES(-9223372036854578873,-9223372036854775355,-9223372036854775181,-9 223372036854744697,1,-1,-9223372036854033123,-9223372036854033123,1,0,0) --- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED: TABLE LOCK table `space/elems` trx id 0 4565 lock mode X waiting -- ---TRANSACTION 0 4561, ACTIVE 2 sec, OS thread id 3148 setting table lock mysql tables in use 1, locked 0 LOCK WAIT 2 lock struct(s), heap size 320, undo log entries 250 MySQL thread id 7, query id 563809 squid.tripwire.com 10.150.1.30 root System lock INSERT INTO ELEMS(oid,E_NID,E_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,E_LCTYP,E_ LCSEV) VALUES(-9223372036854557623,-9223372036854775352,-9223372036854775181,-9 223372036854757305,1,-1,-9223372036854033623,-9223372036854033623,1,0,0) --- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED: TABLE LOCK table `space/elems` trx id 0 4561 lock mode X waiting -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Why do these transactions show table locks?
It might be important to note that I have a delete trigger on the ELEMS table, also, this INSERT call is being made from a stored procedure. The stored procedure only has one line, this INSERT statement. Could this have anything to do with bug# 16229? http://bugs.mysql.com/bug.php?id=16229 -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Sunday, February 26, 2006 9:33 AM To: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Why do these transactions show table locks? My understanding is that innodb should not be using table locks for insert, update, or delete. However, the following transactions are showing table locks. What's up? R. ---TRANSACTION 0 4573, ACTIVE 1 sec, OS thread id 3112 setting table lock mysql tables in use 1, locked 0 LOCK WAIT 2 lock struct(s), heap size 320, undo log entries 250 MySQL thread id 4, query id 566875 squid.tripwire.com 10.150.1.30 root System lock INSERT INTO ELEMS(oid,E_NID,E_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,E_LCTYP,E_ LCSEV) VALUES(-9223372036854523873,-9223372036854775299,-9223372036854775181,-9 223372036854744697,1,-1,-9223372036854027123,-9223372036854027123,1,0,0) --- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED: TABLE LOCK table `space/elems` trx id 0 4573 lock mode X waiting -- ---TRANSACTION 0 4569, ACTIVE 1 sec, OS thread id 1708 setting table lock mysql tables in use 1, locked 0 LOCK WAIT 2 lock struct(s), heap size 320, undo log entries 250 MySQL thread id 6, query id 565737 squid.tripwire.com 10.150.1.30 root System lock INSERT INTO ELEMS(oid,E_NID,E_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,E_LCTYP,E_ LCSEV) VALUES(-9223372036854557373,-9223372036854775352,-9223372036854775181,-9 223372036854601648,1,-1,-9223372036854027623,-9223372036854027623,1,0,0) --- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED: TABLE LOCK table `space/elems` trx id 0 4569 lock mode X waiting -- ---TRANSACTION 0 4565, ACTIVE 2 sec, OS thread id 4008 setting table lock mysql tables in use 1, locked 0 LOCK WAIT 2 lock struct(s), heap size 320, undo log entries 250 MySQL thread id 5, query id 564870 squid.tripwire.com 10.150.1.30 root System lock INSERT INTO ELEMS(oid,E_NID,E_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,E_LCTYP,E_ LCSEV) VALUES(-9223372036854578873,-9223372036854775355,-9223372036854775181,-9 223372036854744697,1,-1,-9223372036854033123,-9223372036854033123,1,0,0) --- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED: TABLE LOCK table `space/elems` trx id 0 4565 lock mode X waiting -- ---TRANSACTION 0 4561, ACTIVE 2 sec, OS thread id 3148 setting table lock mysql tables in use 1, locked 0 LOCK WAIT 2 lock struct(s), heap size 320, undo log entries 250 MySQL thread id 7, query id 563809 squid.tripwire.com 10.150.1.30 root System lock INSERT INTO ELEMS(oid,E_NID,E_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,E_LCTYP,E_ LCSEV) VALUES(-9223372036854557623,-9223372036854775352,-9223372036854775181,-9 223372036854757305,1,-1,-9223372036854033623,-9223372036854033623,1,0,0) --- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED: TABLE LOCK table `space/elems` trx id 0 4561 lock mode X waiting -- -- 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]
RE: dropping a database to reclaim space
If I am testing for performance, I make sure to have one schema for each database server. When I want to tear down the data and restart (possibly with new settings). I first DROP the database I am working on. Then I shutdown the server and in the my_sql/data directory, I delete all the files there. It seems like, for InnoDB at least, that you have to delete these log files if you want to be able to try different log file sizes; once they are created the size cannot be changed. I'm not sure what is left in ibdata after dropping a database, but deleting this file too just causes it to be recreated next time you start MySQL. -Original Message- From: Luke Vanderfluit [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 21, 2006 6:48 PM To: mysql@lists.mysql.com Subject: dropping a database to reclaim space Hi. I have a database that is fairly large and I'm doing some testing with 2 different mysql packages, one 32-bit, one 64bit, for comparison's sake. Now I've finished with one of the dbs and I want to reclaim the disk space. I've tried deleting a few (large) tables from the database, thinking this would free up space, however the mysql/var/ibdata1 file doesn't change in size. How can I get rid of a table or even complete database to reclaim disk space? I don't really want to delete the ibdata1 file, because that will also delete the system database right? I've already tried drop database and that doesn't show reduced disk space either. I'm doing this on a solaris 9 box. Any input appreciated. Thanks. Kind regards. -- Luke -- 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]
Lock wait timeout exceeded during concurrent inserts on an InnoDB table....
Concurrent inserts (there also may be concurrent reads going on) are intermittently causing: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction I noticed that adding innodb_table_locks=0 in my.ini fixes the problem. Looking through the manual however, this should only be a problem when I am using LOCK_TABLE, but there is no where in my code that I use this. Do some queries implicitly add LOCK_TABLE? Is there a way to disable innodb_table_locks when I create my database or in some JDBC property so I don't require users to modify their my.ini files? FWIW, I'm guessing this is a bug; i.e. hat innodb_table_locks controls more than just the locking behavior of an explicit LOCK TABLE. R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Lock wait timeout exceeded during concurrent inserts on an InnoDB table....
Ok, I will do that during my next test run. But in the meantime, when I did it previously, it was oddly enough waiting for a table lock on the table that I was inserting into. R. -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Monday, February 20, 2006 1:18 PM To: mysql@lists.mysql.com Subject: Re: Lock wait timeout exceeded during concurrent inserts on an InnoDB table Robert, please post SHOW INNODB STATUS\G during such lock wait, so that we see what lock it is waiting for. Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php - Original Message - From: Robert DiFalco [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, February 20, 2006 8:00 PM Subject: Lock wait timeout exceeded during concurrent inserts on an InnoDB table Concurrent inserts (there also may be concurrent reads going on) are intermittently causing: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction=20 I noticed that adding innodb_table_locks=3D0 in my.ini fixes the = problem. Looking through the manual however, this should only be a problem when I am using LOCK_TABLE, but there is no where in my code that I use this. Do some queries implicitly add LOCK_TABLE? Is there a way to disable innodb_table_locks when I create my database or in some JDBC property so I don't require users to modify their my.ini files? FWIW, I'm guessing this is a bug; i.e. hat innodb_table_locks controls=20 more than just the locking behavior of an explicit LOCK TABLE. R. -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Lock wait timeout exceeded during concurrent inserts on an InnoDB table....
I'm doing about 200,000 inserts, collecting them into batches of 500, and queuing them into a thread pool with 6 threads. R. -Original Message- From: Ady Wicaksono [mailto:[EMAIL PROTECTED] Sent: Monday, February 20, 2006 8:36 PM To: Robert DiFalco Cc: mysql@lists.mysql.com Subject: Re: Lock wait timeout exceeded during concurrent inserts on an InnoDB table Roberts How many concurreent inserts you've done? What MySQL version you use? Concurrent inserts (there also may be concurrent reads going on) are intermittently causing: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction I noticed that adding innodb_table_locks=0 in my.ini fixes the problem. Looking through the manual however, this should only be a problem when I am using LOCK_TABLE, but there is no where in my code that I use this. Do some queries implicitly add LOCK_TABLE? Is there a way to disable innodb_table_locks when I create my database or in some JDBC property so I don't require users to modify their my.ini files? FWIW, I'm guessing this is a bug; i.e. hat innodb_table_locks controls more than just the locking behavior of an explicit LOCK TABLE. R. -- 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]
Best case-insensitive search for Character Set utf8 COLLATE utf8_bin Columns.
What is the best (most optimal) way to perform a case-insensitive search
for a VARCHAR column with COLLATE utf8_bin?
I'm assuming the answer is not:
SELECT *
FROM MyTable
WHERE UPPER(MyColumn) LIKE Upper('%pattern%');
Tia!
R.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
