Re: Using Joins/Unions
On Tue, August 4, 2015 16:05, Ryan Coleman wrote: No but there should be. If there's not my task is useless. Secondly yes. Unique name on it too. -- Ryan Coleman Publisher, d3photography.com ryan.cole...@cwis.biz m. 651.373.5015 o. 612.568.2749 On Aug 4, 2015, at 17:33, Wm Mussatto mussa...@csz.com wrote: On Tue, August 4, 2015 11:19, Ryan Coleman wrote: I have been a MySQL user and supporter for over a decade (since 2001) and I am almost ashamed to admit that I haven’t the faintest idea on how to do joins and unions. I have a specific query I would love to run… I have two tables, one with Unique data (“images”) and one with corresponding paths but many replicated records (“files”). I want to run a query that takes the results from /images/ and also searches /images.file/ as a LIKE statement from /files.path/, sort by /files.filesize/ in descending order returning just the first record (largest file size). There may be up to 750 records from /images/ and thusly could be 3000+ from /files/. How on earth do I do this? — Ryan First question, will there always be at least one record in the files table for every record in the images table? That controls the kind of join you will use. I don't think that a union is a player. Also, is there a unique record ID in each of the table? Sorry, been swamped. If you can ignore the cases where there are not any entry in the 'Files' table then a simple join will work. Otherwise you would need a LEFT JOIN Assume a structure images.id - unique record ID images.commonName - this will be the common reference name in both ... files.id - unique record ID files.commmonName - this will be the common reference name in both files.location -where this record is on the disk/system files.filesize - numeric field in whatever units you want (say bytes) select images.commonName, files.location, MAX(files.filesize) FROM images, files WHERE images.commonName = files.commonName GROUP BY files.commonName ORDER BY images.commonName Here is my test structure. No doubt someone else can get it optimized. This seems to use a temp table -- -- Table structure for table `files` -- CREATE TABLE `files` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `CommonName` varchar(255) NOT NULL, `Location` varchar(255) NOT NULL, `filesize` int(11) NOT NULL, PRIMARY KEY (`ID`), KEY `CommonName` (`CommonName`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Dumping data for table `files` -- INSERT INTO `files` (`ID`, `CommonName`, `Location`, `filesize`) VALUES (1, 'Image1', 'FileLoc/1/image1.jpg', 1000), (2, 'Image1', 'FileLoc/2/image1.jpg', 5), (3, 'Image2', 'FileLoc/1/image2.jpg', 25000), (4, 'Image2', 'FileLoc/2/image2.jpg', 5000); -- -- -- Table structure for table `images` -- CREATE TABLE `images` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `CommonName` varchar(255) NOT NULL, PRIMARY KEY (`ID`), UNIQUE KEY `CommonName` (`CommonName`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- -- Dumping data for table `images` -- INSERT INTO `images` (`ID`, `CommonName`) VALUES (1, 'Image1'), (2, 'Image2'); Hope this helps a bit. --- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext 101 Direct: 909-962-8547 __ CONFIDENTIALITY NOTICE: This communication, including attachments, is for the exclusive use of the person or entity to which it is addressed and may contain confidential, proprietary and/or privileged information. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this by mistake, please contact the sender immediately. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Using Joins/Unions
No but there should be. If there's not my task is useless. Secondly yes. Unique name on it too. -- Ryan Coleman Publisher, d3photography.com ryan.cole...@cwis.biz m. 651.373.5015 o. 612.568.2749 On Aug 4, 2015, at 17:33, Wm Mussatto mussa...@csz.com wrote: On Tue, August 4, 2015 11:19, Ryan Coleman wrote: I have been a MySQL user and supporter for over a decade (since 2001) and I am almost ashamed to admit that I haven’t the faintest idea on how to do joins and unions. I have a specific query I would love to run… I have two tables, one with Unique data (“images”) and one with corresponding paths but many replicated records (“files”). I want to run a query that takes the results from /images/ and also searches /images.file/ as a LIKE statement from /files.path/, sort by /files.filesize/ in descending order returning just the first record (largest file size). There may be up to 750 records from /images/ and thusly could be 3000+ from /files/. How on earth do I do this? — Ryan First question, will there always be at least one record in the files table for every record in the images table? That controls the kind of join you will use. I don't think that a union is a player. Also, is there a unique record ID in each of the table? -- 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/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Using Joins/Unions
I have been a MySQL user and supporter for over a decade (since 2001) and I am almost ashamed to admit that I haven’t the faintest idea on how to do joins and unions. I have a specific query I would love to run… I have two tables, one with Unique data (“images”) and one with corresponding paths but many replicated records (“files”). I want to run a query that takes the results from /images/ and also searches /images.file/ as a LIKE statement from /files.path/, sort by /files.filesize/ in descending order returning just the first record (largest file size). There may be up to 750 records from /images/ and thusly could be 3000+ from /files/. How on earth do I do this? — Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Using Joins/Unions
On Tue, August 4, 2015 11:19, Ryan Coleman wrote: I have been a MySQL user and supporter for over a decade (since 2001) and I am almost ashamed to admit that I haven’t the faintest idea on how to do joins and unions. I have a specific query I would love to run… I have two tables, one with Unique data (“images”) and one with corresponding paths but many replicated records (“files”). I want to run a query that takes the results from /images/ and also searches /images.file/ as a LIKE statement from /files.path/, sort by /files.filesize/ in descending order returning just the first record (largest file size). There may be up to 750 records from /images/ and thusly could be 3000+ from /files/. How on earth do I do this? — Ryan First question, will there always be at least one record in the files table for every record in the images table? That controls the kind of join you will use. I don't think that a union is a player. Also, is there a unique record ID in each of the table? -- 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/mysql
NEW Server Joins Tables from Multiple Servers with Unprecedented Speed
Parallel Universe* now features Parallel Network Query (Distributed Query) which joins tables from multiple servers in the network with unprecedented speed. Parallel Network Query may also be used to speed up slow server by distributing tables of the query to multiple servers for processing which effectively aggregates server resources (such as disk I/O bandwidth and CPU/cores). -- *Parallel Universe is the industry's only SQL server to feature Parallel Query and Parallel Network Query (Distributed Query) with unprecedented speed. Parallel Universe is created by extending MySQL server architecture and supports inter and intra server parallelism. Parallel Query incorporate intra server parallelism by processing tables in parallel with multiple threads (utilizing multiple CPU/cores). Parallel Network Query additionally incorporate inter server parallelism by distributing query processing to multiple servers in the network. Parallel Network Query may also be used to speed up slow server by distributing tables of the query to multiple servers for processing which effectively aggregates server resources (such as disk I/O bandwidth and CPU/cores). Parallel Query and Parallel Network Query make Parallel Universe ideal for data warehouse, business intelligence/analytics and big data application. With Parallel Universe, you'll also be able to deploy less costly server hardware for the same query load/task. Parallel Universe is released under the GPL license and fully compatible with MySQL and Percona servers. Also available as part of Linux OS images at Amazon Web Services and www.GoGrid.com . Info and benchmarks: http://www.paralleluniverse-inc.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Composite Index Usage in Joins
trade_time IN ('2012-07-01', '2012-07-02');
represents two distinct points in a continuum of a DATETIME. Perhaps you
should change to DATE. Even then, it would probably be treated as IN, not
RANGE. Perhaps you really want BETWEEN (a range) instead of IN (a set).
What version are you using? Older version have essentially no optimizations
for IN().
Shrinking the table size may help -- Do you really need BIGINT (8 bytes)
instead of INT UNSIGNED (4 bytes)? DATETIME (8) vs DATE(3). Etc.
If these are stock prices, how can `price` be INT?
-Original Message-
From: Jeffrey Grollo [mailto:grol...@gmail.com]
Sent: Tuesday, July 10, 2012 2:50 PM
To: mysql@lists.mysql.com
Subject: Composite Index Usage in Joins
Hi,
I'm attempting to optimize a join and am having a difficult time using
multiple columns from a composite index. The second column of the
composite key is being used when tested for equality, but not for IN or
BETWEEN criteria.
As an example, say that I'm searching two tables: portfolio and trades.
The portfolio table contains a list of security IDs. The trades table
keeps tracks of the price and time when I've traded securities in my
portfolio.
Tables are:
CREATE TABLE portfolio (
sec_id bigint(20) NOT NULL AUTO_INCREMENT,
name char(10) NOT NULL,
PRIMARY KEY (sec_id)
) ENGINE=InnoDB ;
CREATE TABLE trades (
tx_id bigint(20) NOT NULL AUTO_INCREMENT,
sec_id bigint(20) NOT NULL,
trade_time datetime NOT NULL,
price int NOT NULL,
PRIMARY KEY (tx_id),
KEY sec_time (sec_id, trade_time)
) ENGINE=InnoDB ;
If I query the trades table directly both columns of the composite
index sec_time will be used when I'm using a range criteria on the
trade_time
column:
mysql explain select price from trades force index(sec_time)
- where sec_id IN (1, 2) and trade_time BETWEEN '2012-07-01' AND
'2012-07-04';
++-++---+---+--+---
--+--+--+-+
| id | select_type | table | type | possible_keys | key |
key_len |
ref | rows | Extra |
++-++---+---+--+---
--+--+--+-+
| 1 | SIMPLE | trades | range | sec_time | sec_time | 16
|
NULL |2 | Using where |
++-++---+---+--+---
--+--+--+-+
If I introduce a join to retrieve all trades for my portfolio, the
entire index will continue to be used if I make trade_time a constant:
mysql explain select price from portfolio p inner join trades t force
index(sec_time) on p.sec_id = t.sec_id
- where trade_time = '2012-07-01';
++-+---+---+---+--+
-++--+-+
| id | select_type | table | type | possible_keys | key | key_len
|
ref| rows | Extra |
++-+---+---+---+--+
-++--+-+
| 1 | SIMPLE | p | index | PRIMARY | PRIMARY | 8
|
NULL |1 | Using index |
| 1 | SIMPLE | t | ref | sec_time | sec_time | 16
|
vantage.p.sec_id,const |1 | |
++-+---+---+---+--+
-++--+-+
However, if I expand the trade_time search (either using IN or
BETWEEN), only the sec_id column of the composite query is used:
mysql explain select price from portfolio p inner join trades t force
index(sec_time) on p.sec_id = t.sec_id
- where trade_time IN ('2012-07-01', '2012-07-02');
++-+---+---+---+--+
-+--+--+-+
| id | select_type | table | type | possible_keys | key | key_len
|
ref | rows | Extra |
++-+---+---+---+--+
-+--+--+-+
| 1 | SIMPLE | p | index | PRIMARY | PRIMARY | 8
|
NULL |1 | Using index |
| 1 | SIMPLE | t | ref | sec_time | sec_time | 8
|
vantage.p.sec_id |1 | Using where |
++-+---+---+---+--+
-+--+--+-+
My expectation is that MySQL would be able to use both columns of the
sec_time index, but I've been unable to find either confirmation of
refutation of that assumption. If MySQL cannot optimize a join in this
case, is there another approach to optimizing this query that I should
pursue? Essentially, my trades table may contain many historical
records and pulling the entire history of trades for each security
would produce a much larger result set than would be retrieved if the
trade_time criteria was applied in the index
Re: Composite Index Usage in Joins
Thanks for the guidance and references, Shawn. On Wed, Jul 11, 2012 at 3:37 PM, Shawn Green shawn.l.gr...@oracle.com wrote: Ranged scans only happen for the last portion of an index being used. OR queries (or those using IN) can also only be applied to the last part of an index search. This means that if you are using IN (or OR) against the first part of an index, that's where the usage of the index stops. Basically, this is what I was expecting. However, in the second table in the join, the secondary column wasn't being used for the range or IN. I was expecting, given the nested-join loop algorithm (as I don't believe that BNL would be used http://dev.mysql.com/doc/refman/5.5/en/nested-loop-joins.html), that the first column in the key would be treated as const allowing the second column to be used in a range or IN criteria. Also, trying to force index usage may be creating more work for your disks than necessary. I should have indicated in the original post that I was forcing the index simply to make the example work without data. Regards, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Composite Index Usage in Joins
On Wed, Jul 11, 2012 at 5:30 PM, Sergei Petrunia pser...@askmonty.org wrote:
I can provide a refutation. Ability to make a combined index access of
1. Equality with a non-constant: t.sec_id= p.sec_id
2. non-equality comparison with constants, trade_time IN ('2012-07-01',
'2012-07-02')
has been discussed a number of times by query optimizer developer, but as far
as public knowlege goes, nobody has ever implemented it, either at Oracle, or
at MariaDB, or elsewhere.
As mentioned in my reply to Shawn, given the nested-loop join
processing, I had expected #1 to be treated like a constant for
purposes of index access, but sounds like this is either a
misunderstanding or just a limitation of how the optimizer builds an
execution plan.
My takeaway then is that composite indexes built for supporting join
and where criteria in a query should be constructed to speed join key
lookup first then to satisfy equality conditions in additional
criteria.
MariaDB 5.3+ and MySQL 5.6+ have Index Condition Pushdown feature
A very nice feature indeed.
Basically, ref access can be constructed from equality conditions. If you have
data for many years, and the range you're scanning fits within one particular
year, this could help:
alter table trades add trade_year int, add index(sec_id, trade_year)
update trades set trade_year=year(trade_date); -- will need to be done for
new data, too
Appreciate the suggestion. Given the variability in date ranges this
search is over, finding the right granularity for supporting an
equality condition is an interesting problem. Presumably, the same
technique could be applied monthly, for example, with a second
composite index to support:
alter table trades add trade_month int, add index(sec_id, trade_month)
It seems that partition pruning might be another way to deal with
reducing the data scanned. With yearly partitions, I could then rely
on the storage engine only scanning relevant partitions and then use
equality at the month level.
Thank you for the insight,
Jeff
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Composite Index Usage in Joins
On 7/10/2012 5:50 PM, Jeffrey Grollo wrote:
Hi,
I’m attempting to optimize a join and am having a difficult time using
multiple columns from a composite index. The second column of the composite
key is being used when tested for equality, but not for IN or BETWEEN
criteria.
As an example, say that I’m searching two tables: portfolio and trades. The
portfolio table contains a list of security IDs. The trades table keeps
tracks of the price and time when I’ve traded securities in my portfolio.
Tables are:
CREATE TABLE portfolio (
sec_id bigint(20) NOT NULL AUTO_INCREMENT,
name char(10) NOT NULL,
PRIMARY KEY (sec_id)
) ENGINE=InnoDB ;
CREATE TABLE trades (
tx_id bigint(20) NOT NULL AUTO_INCREMENT,
sec_id bigint(20) NOT NULL,
trade_time datetime NOT NULL,
price int NOT NULL,
PRIMARY KEY (tx_id),
KEY sec_time (sec_id, trade_time)
) ENGINE=InnoDB ;
If I query the trades table directly both columns of the composite index
sec_time will be used when I'm using a range criteria on the trade_time
column:
mysql explain select price from trades force index(sec_time)
- where sec_id IN (1, 2) and trade_time BETWEEN '2012-07-01' AND
'2012-07-04';
++-++---+---+--+-+--+--+-+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
++-++---+---+--+-+--+--+-+
| 1 | SIMPLE | trades | range | sec_time | sec_time | 16 |
NULL |2 | Using where |
++-++---+---+--+-+--+--+-+
If I introduce a join to retrieve all trades for my portfolio, the entire
index will continue to be used if I make trade_time a constant:
mysql explain select price from portfolio p inner join trades t force
index(sec_time) on p.sec_id = t.sec_id
- where trade_time = '2012-07-01';
++-+---+---+---+--+-++--+-+
| id | select_type | table | type | possible_keys | key | key_len |
ref| rows | Extra |
++-+---+---+---+--+-++--+-+
| 1 | SIMPLE | p | index | PRIMARY | PRIMARY | 8 |
NULL |1 | Using index |
| 1 | SIMPLE | t | ref | sec_time | sec_time | 16 |
vantage.p.sec_id,const |1 | |
++-+---+---+---+--+-++--+-+
However, if I expand the trade_time search (either using IN or BETWEEN),
only the sec_id column of the composite query is used:
mysql explain select price from portfolio p inner join trades t force
index(sec_time) on p.sec_id = t.sec_id
- where trade_time IN ('2012-07-01', '2012-07-02');
++-+---+---+---+--+-+--+--+-+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
++-+---+---+---+--+-+--+--+-+
| 1 | SIMPLE | p | index | PRIMARY | PRIMARY | 8 |
NULL |1 | Using index |
| 1 | SIMPLE | t | ref | sec_time | sec_time | 8 |
vantage.p.sec_id |1 | Using where |
++-+---+---+---+--+-+--+--+-+
My expectation is that MySQL would be able to use both columns of the
sec_time index, but I've been unable to find either confirmation of
refutation of that assumption. If MySQL cannot optimize a join in this
case, is there another approach to optimizing this query that I should
pursue? Essentially, my trades table may contain many historical records
and pulling the entire history of trades for each security would produce a
much larger result set than would be retrieved if the trade_time criteria
was applied in the index reference.
I'm using MySQL 5.5.11.
Thanks for any guidance,
Jeff
Indexes are stored as b-TREE structures. For InnoDB tables, the leaf
nodes of the tree will either contain the PRIMARY KEY of the table or a
6-byte hidden value that acts as a row identifier.
The tree is structured so that the key values are parsed together and a
balanced binary tree is built that represents the range of values on the
table. For example, the index entry for one of your rows could be
1_2012-07-01. At each level above the leaves, you have a node that
lists the first and last elements of the range of leaves beneath it. For
example an intermediate node may have 1_2009-01-01 and 1_2009-10-15
to represent that those are the values within that branch of the tree.
Ranged
Re: Composite Index Usage in Joins
On Tue, Jul 10, 2012 at 05:50:07PM -0400, Jeffrey Grollo wrote:
Hi,
I’m attempting to optimize a join and am having a difficult time using
multiple columns from a composite index. The second column of the composite
key is being used when tested for equality, but not for IN or BETWEEN
criteria.
As an example, say that I’m searching two tables: portfolio and trades. The
portfolio table contains a list of security IDs. The trades table keeps
tracks of the price and time when I’ve traded securities in my portfolio.
Tables are:
CREATE TABLE portfolio (
sec_id bigint(20) NOT NULL AUTO_INCREMENT,
name char(10) NOT NULL,
PRIMARY KEY (sec_id)
) ENGINE=InnoDB ;
CREATE TABLE trades (
tx_id bigint(20) NOT NULL AUTO_INCREMENT,
sec_id bigint(20) NOT NULL,
trade_time datetime NOT NULL,
price int NOT NULL,
PRIMARY KEY (tx_id),
KEY sec_time (sec_id, trade_time)
) ENGINE=InnoDB ;
If I query the trades table directly both columns of the composite index
sec_time will be used when I'm using a range criteria on the trade_time
column:
mysql explain select price from trades force index(sec_time)
- where sec_id IN (1, 2) and trade_time BETWEEN '2012-07-01' AND
'2012-07-04';
++-++---+---+--+-+--+--+-+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
++-++---+---+--+-+--+--+-+
| 1 | SIMPLE | trades | range | sec_time | sec_time | 16 |
NULL |2 | Using where |
++-++---+---+--+-+--+--+-+
If I introduce a join to retrieve all trades for my portfolio, the entire
index will continue to be used if I make trade_time a constant:
mysql explain select price from portfolio p inner join trades t force
index(sec_time) on p.sec_id = t.sec_id
- where trade_time = '2012-07-01';
++-+---+---+---+--+-++--+-+
| id | select_type | table | type | possible_keys | key | key_len |
ref| rows | Extra |
++-+---+---+---+--+-++--+-+
| 1 | SIMPLE | p | index | PRIMARY | PRIMARY | 8 |
NULL |1 | Using index |
| 1 | SIMPLE | t | ref | sec_time | sec_time | 16 |
vantage.p.sec_id,const |1 | |
++-+---+---+---+--+-++--+-+
However, if I expand the trade_time search (either using IN or BETWEEN),
only the sec_id column of the composite query is used:
mysql explain select price from portfolio p inner join trades t force
index(sec_time) on p.sec_id = t.sec_id
- where trade_time IN ('2012-07-01', '2012-07-02');
++-+---+---+---+--+-+--+--+-+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
++-+---+---+---+--+-+--+--+-+
| 1 | SIMPLE | p | index | PRIMARY | PRIMARY | 8 |
NULL |1 | Using index |
| 1 | SIMPLE | t | ref | sec_time | sec_time | 8 |
vantage.p.sec_id |1 | Using where |
++-+---+---+---+--+-+--+--+-+
My expectation is that MySQL would be able to use both columns of the
sec_time index, but I've been unable to find either confirmation of
refutation of that assumption.
I can provide a refutation. Ability to make a combined index access of
1. Equality with a non-constant: t.sec_id= p.sec_id
2. non-equality comparison with constants, trade_time IN ('2012-07-01',
'2012-07-02')
has been discussed a number of times by query optimizer developer, but as far
as public knowlege goes, nobody has ever implemented it, either at Oracle, or
at MariaDB, or elsewhere.
MariaDB 5.3+ and MySQL 5.6+ have Index Condition Pushdown feature which will
have the optimizer to check the condition
trade_time IN ('2012-07-01', '2012-07-02')
before reading the fill tables.
If MySQL cannot optimize a join in this
case, is there another approach to optimizing this query that I should
pursue? Essentially, my trades table may contain many historical records
and pulling the entire history of trades for each security would produce a
much larger result set than would be retrieved if the trade_time criteria
was applied in the index reference.
Basically, ref access can be constructed from equality conditions. If
Composite Index Usage in Joins
Hi,
I’m attempting to optimize a join and am having a difficult time using
multiple columns from a composite index. The second column of the composite
key is being used when tested for equality, but not for IN or BETWEEN
criteria.
As an example, say that I’m searching two tables: portfolio and trades. The
portfolio table contains a list of security IDs. The trades table keeps
tracks of the price and time when I’ve traded securities in my portfolio.
Tables are:
CREATE TABLE portfolio (
sec_id bigint(20) NOT NULL AUTO_INCREMENT,
name char(10) NOT NULL,
PRIMARY KEY (sec_id)
) ENGINE=InnoDB ;
CREATE TABLE trades (
tx_id bigint(20) NOT NULL AUTO_INCREMENT,
sec_id bigint(20) NOT NULL,
trade_time datetime NOT NULL,
price int NOT NULL,
PRIMARY KEY (tx_id),
KEY sec_time (sec_id, trade_time)
) ENGINE=InnoDB ;
If I query the trades table directly both columns of the composite index
sec_time will be used when I'm using a range criteria on the trade_time
column:
mysql explain select price from trades force index(sec_time)
- where sec_id IN (1, 2) and trade_time BETWEEN '2012-07-01' AND
'2012-07-04';
++-++---+---+--+-+--+--+-+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
++-++---+---+--+-+--+--+-+
| 1 | SIMPLE | trades | range | sec_time | sec_time | 16 |
NULL |2 | Using where |
++-++---+---+--+-+--+--+-+
If I introduce a join to retrieve all trades for my portfolio, the entire
index will continue to be used if I make trade_time a constant:
mysql explain select price from portfolio p inner join trades t force
index(sec_time) on p.sec_id = t.sec_id
- where trade_time = '2012-07-01';
++-+---+---+---+--+-++--+-+
| id | select_type | table | type | possible_keys | key | key_len |
ref| rows | Extra |
++-+---+---+---+--+-++--+-+
| 1 | SIMPLE | p | index | PRIMARY | PRIMARY | 8 |
NULL |1 | Using index |
| 1 | SIMPLE | t | ref | sec_time | sec_time | 16 |
vantage.p.sec_id,const |1 | |
++-+---+---+---+--+-++--+-+
However, if I expand the trade_time search (either using IN or BETWEEN),
only the sec_id column of the composite query is used:
mysql explain select price from portfolio p inner join trades t force
index(sec_time) on p.sec_id = t.sec_id
- where trade_time IN ('2012-07-01', '2012-07-02');
++-+---+---+---+--+-+--+--+-+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
++-+---+---+---+--+-+--+--+-+
| 1 | SIMPLE | p | index | PRIMARY | PRIMARY | 8 |
NULL |1 | Using index |
| 1 | SIMPLE | t | ref | sec_time | sec_time | 8 |
vantage.p.sec_id |1 | Using where |
++-+---+---+---+--+-+--+--+-+
My expectation is that MySQL would be able to use both columns of the
sec_time index, but I've been unable to find either confirmation of
refutation of that assumption. If MySQL cannot optimize a join in this
case, is there another approach to optimizing this query that I should
pursue? Essentially, my trades table may contain many historical records
and pulling the entire history of trades for each security would produce a
much larger result set than would be retrieved if the trade_time criteria
was applied in the index reference.
I'm using MySQL 5.5.11.
Thanks for any guidance,
Jeff
RE: forcing mysql to use batched key access (BKA) optimization for joins
INDEX(o_orderdate, o_cust_key, o_orderkey) Would probably help a bunch. I assume you have indexes (PKs?) on c_custkey, l_order_key, n_nationkey. Please provide SHOW CREATE TABLE and SHOW STATUS TABLE. -Original Message- From: Hal?sz S?ndor [mailto:h...@tbbs.net] Sent: Tuesday, April 10, 2012 5:20 PM To: mysql@lists.mysql.com Subject: Re: forcing mysql to use batched key access (BKA) optimization for joins 2012/04/10 15:58 -0400, Stephen Tu select c_custkey, c_name, sum(l_extendedprice * (100 - l_discount)) as revenue, c_acctbal, n_name, c_address, c_phone, c_comment from CUSTOMER_INT, ORDERS_INT, LINEITEM_INT, NATION_INT where c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate = date '1994-08-01' and o_orderdate date '1994-08-01' + interval '3' month and l_returnflag = 'R' and c_nationkey = n_nationkey group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue desc limit 20 I understand that MySQL works better if inner joining explicitly is stated, not implicitly as you have it. What are your keys, indices? Going by that which I have read heard, you want every field named after this query s 'where' an index, if not key--and, of course, your every field named '...key' is a key, right? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: forcing mysql to use batched key access (BKA) optimization,for joins
Hi Stephen, 2012/04/10 15:58 -0400, Stephen Tu | id | select_type | table| type | possible_keys | key | key_len | ref | rows| Extra | ++-+--++---+---+-+-+-+-+ | 1 | SIMPLE | CUSTOMER_INT | ALL| PRIMARY | NULL | NULL| NULL| 1501528 | Using temporary; Using filesort | | 1 | SIMPLE | NATION_INT | eq_ref | PRIMARY | PRIMARY | 4 | tpch-10.00.CUSTOMER_INT.C_NATIONKEY | 1 | NULL| | 1 | SIMPLE | ORDERS_INT | ref| PRIMARY,O_CUSTKEY | O_CUSTKEY | 4 | tpch-10.00.CUSTOMER_INT.C_CUSTKEY | 7 | Using where; Using join buffer (Batched Key Access) | | 1 | SIMPLE | LINEITEM_INT | ref| PRIMARY | PRIMARY | 4 | tpch-10.00.ORDERS_INT.O_ORDERKEY| 1 | Using where | ++-+--++---+---+-+-+-+-+ 4 rows in set (0.00 sec) I'm wondering why, in this particular query, mysql doesn't use the BKA to join the LINEITEM_INT table, but uses it for the ORDERS_INT table? It seems like it should also use BKA to batch key fetches from the LINEITEM_INT table (this I believe is the reason for the slow performance). The basis for Batched Key Access (BKA) is the Disk-Sweep Multi-Range Read (DS-MRR) strategy. The basic idea of DS-MRR is to accumulate primary keys from a batch of secondary index look-ups and access the rows in the base table in primary key order. In other words, DS-MRR (and BKA) does not apply for look-ups by primary key. Hence, since the ref access into lineitem is by primary key, BKA will not be used. Maybe you will get a more optimal plan if you add more indexes. In my case, where I have an index on orders(o_orderdate), the join will start with a range scan on the orders tables. If I enable DS-MRR for this range scan, query time is reduced from 455 seconds (without DS-MRR) to 90 seconds on a scale 1 database. Hope this helps, -- Øystein -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: forcing mysql to use batched key access (BKA) optimization for joins
2012/04/10 15:58 -0400, Stephen Tu select c_custkey, c_name, sum(l_extendedprice * (100 - l_discount)) as revenue, c_acctbal, n_name, c_address, c_phone, c_comment from CUSTOMER_INT, ORDERS_INT, LINEITEM_INT, NATION_INT where c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate = date '1994-08-01' and o_orderdate date '1994-08-01' + interval '3' month and l_returnflag = 'R' and c_nationkey = n_nationkey group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue desc limit 20 I understand that MySQL works better if inner joining explicitly is stated, not implicitly as you have it. What are your keys, indices? Going by that which I have read heard, you want every field named after this query s 'where' an index, if not key--and, of course, your every field named '...key' is a key, right? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Multiple joins from same table?
Terry Van de Velde wrote: Good Day, I am attempting to do something new (to me) with MySQL. I am looking to have my query return with the value in the visitor and home columns replaced with the corresponding team name from the teams table. schedule.visitor and schedule.home are essentially foreign keys to teams.team_no (though I have not defined them as such yet). What I have been trying is the select statement below which is fine when joining using one team (say the home team), but as soon as I attempt to add in the visitor team, things fall apart. I am basically looking to have the following outcome: Oct. 30 - Titans vs. Hawks (7:30 PM) Nov. 1 - Red Jackets vs. Boomerangs (8:30 PM) I would like this handled by MySQL instead of PHP if possible. Schedule table 'id' int, 'date_time' datetime, 'visitor' tinyint 'home' tinyint teams table 'id' int 'team_no' smallint, 'team_name' varchar (20) SELECT schedule.date_time, teams.team_name FROM schedule, sojhlteams WHERE schedule.visitor = teams.team_no Any help is appreciated. The trick to using the same table two or more times in the same query is through something called aliases when you alias a column or table you give it a different name and will make it easier to manage. Something like this is what you are after SELECT s.date_time , th.team_name home_team , ta.team_name away_team FROM schedule s INNER JOIN teams th ON th.team_no = s.home INNER JOIN teams ta ON ta.team_no = s.visitor Here you can see that I aliased the `teams` table twice. Once to handle the home team information (th) and once for the away team info (ta). I also aliased the team_name columns to make them less confusing labeling one as home_team and other as away_team. I think that once you get a grip on how to use aliases, all of this multiple-table stuff will start to become much easier. -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Multiple joins from same table?
Shawn, Thanks for the info, it does help indeed. I had also replied back to Gary to thank him as well, but I don't think that it made it to the list... so to Gary, thanks as well. Regards, Terry Terry Van de Velde Email: bya...@rogers.com Phone: (519) 685-0295 Cell: (519) 619-0987 -Original Message- From: shawn.gr...@sun.com [mailto:shawn.gr...@sun.com] Sent: December 12, 2009 4:39 PM To: Terry Van de Velde Cc: mysql@lists.mysql.com Subject: Re: Multiple joins from same table? Terry Van de Velde wrote: Good Day, I am attempting to do something new (to me) with MySQL. I am looking to have my query return with the value in the visitor and home columns replaced with the corresponding team name from the teams table. schedule.visitor and schedule.home are essentially foreign keys to teams.team_no (though I have not defined them as such yet). What I have been trying is the select statement below which is fine when joining using one team (say the home team), but as soon as I attempt to add in the visitor team, things fall apart. I am basically looking to have the following outcome: Oct. 30 - Titans vs. Hawks (7:30 PM) Nov. 1 - Red Jackets vs. Boomerangs (8:30 PM) I would like this handled by MySQL instead of PHP if possible. Schedule table 'id' int, 'date_time' datetime, 'visitor' tinyint 'home' tinyint teams table 'id' int 'team_no' smallint, 'team_name' varchar (20) SELECT schedule.date_time, teams.team_name FROM schedule, sojhlteams WHERE schedule.visitor = teams.team_no Any help is appreciated. The trick to using the same table two or more times in the same query is through something called aliases when you alias a column or table you give it a different name and will make it easier to manage. Something like this is what you are after SELECT s.date_time , th.team_name home_team , ta.team_name away_team FROM schedule s INNER JOIN teams th ON th.team_no = s.home INNER JOIN teams ta ON ta.team_no = s.visitor Here you can see that I aliased the `teams` table twice. Once to handle the home team information (th) and once for the away team info (ta). I also aliased the team_name columns to make them less confusing labeling one as home_team and other as away_team. I think that once you get a grip on how to use aliases, all of this multiple-table stuff will start to become much easier. -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=bya...@rogers.com No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.716 / Virus Database: 270.14.105/2561 - Release Date: 12/12/09 14:39:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Multiple joins from same table?
Good Day, I am attempting to do something new (to me) with MySQL. I am looking to have my query return with the value in the visitor and home columns replaced with the corresponding team name from the teams table. schedule.visitor and schedule.home are essentially foreign keys to teams.team_no (though I have not defined them as such yet). What I have been trying is the select statement below which is fine when joining using one team (say the home team), but as soon as I attempt to add in the visitor team, things fall apart. I am basically looking to have the following outcome: Oct. 30 - Titans vs. Hawks (7:30 PM) Nov. 1 - Red Jackets vs. Boomerangs (8:30 PM) I would like this handled by MySQL instead of PHP if possible. - Schedule table 'id' int, 'date_time' datetime, 'visitor' tinyint 'home' tinyint - teams table 'id' int 'team_no' smallint, 'team_name' varchar (20) - SELECT schedule.date_time, teams.team_name FROM schedule, sojhlteams WHERE schedule.visitor = teams.team_no - Any help is appreciated. Best Regards, Terry
Re: Multiple joins from same table?
Terry Van de Velde wrote: Good Day, I am attempting to do something new (to me) with MySQL. I am looking to have my query return with the value in the visitor and home columns replaced with the corresponding team name from the teams table. schedule.visitor and schedule.home are essentially foreign keys to teams.team_no (though I have not defined them as such yet). What I have been trying is the select statement below which is fine when joining using one team (say the home team), but as soon as I attempt to add in the visitor team, things fall apart. select * from table as t1,table as t2,table as t3 where t1.column1='blah' and t2.column2='blah' and t3.column3='blah' does the same thing as select * from table where column1='blah' and column2='blah' and column3='blah' Does that do what you're after? Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Is a view efficient in joins?
Hello: I have a database with over 60 tables with thousands to millions or rows in each. I want to develop a summary of the data joined across all the tables. I can do this with a view, but I am concerned it will take a lot of resources to perform all the joins required by the view. Is a view efficient at making joins? Are the joins executed every time the view is used or is the data cached somehow? The other approach is for me to create a table to hold the summary data and write application code that periodically updates it. Which alternative would be best? Thanks, Neil -- Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net Host your MySQL driven app on a CentOS VPS for $25/mo Unmetered bandwidth = no overage charges, 7 day free trial -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Is a view efficient in joins?
A view is no more or less efficient that the queries that make it up. Each time you invoke the view, you repeat all the joins. A join could be more efficient only if you go to a lot of effort to ensure it forms the most efficient join(s) of the underlying tables. Your solution of the summary table is actually a good one in many instances, especially if it's not vital that it contain the most up-to-date data. On Sun, Nov 29, 2009 at 6:16 PM, Neil Aggarwal n...@jammconsulting.comwrote: Hello: I have a database with over 60 tables with thousands to millions or rows in each. I want to develop a summary of the data joined across all the tables. I can do this with a view, but I am concerned it will take a lot of resources to perform all the joins required by the view. Is a view efficient at making joins? Are the joins executed every time the view is used or is the data cached somehow? The other approach is for me to create a table to hold the summary data and write application code that periodically updates it. Which alternative would be best? Thanks, Neil -- Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net Host your MySQL driven app on a CentOS VPS for $25/mo Unmetered bandwidth = no overage charges, 7 day free trial -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jlyons4...@gmail.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
RE: Is a view efficient in joins?
Jim: A view is no more or less efficient that the queries that make it up. Each time you invoke the view, you repeat all the joins. That is what I was afraid of. With the large number of tables I have, the joins are going to take a lot of cycles to run. Your solution of the summary table is actually a good one in many instances, especially if it's not vital that it contain the most up-to-date data. I think I will go that direction. It will be OK for the summary data to be delayed by a short interval. Thanks, Neil -- Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net Host your MySQL driven app on a CentOS VPS for $25/mo Unmetered bandwidth = no overage charges, 7 day free trial -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: self-joins in hierarchical queries: optimization problem
Dear Michail and Sergey, Thank you very much for your responses and kind suggestions! On 29.10.2009, at 16:53, Sergey Petrunya wrote: this makes it clear that index on O1.tsn will not be useful. You need indexes on parent_tsn column. mysql alter table taxonomic_units1 add index (parent_tsn); Query OK, 483305 rows affected (7.76 sec) Records: 483305 Duplicates: 0 Warnings: 0 And it is solved! It works like a charm! mysql CREATE TABLE flatfile - SELECT O1.name AS tclass, O2.name AS torder, O4.name AS tfamily, O5.name AS tgenus, O6.name AS tspecies - FROM taxonomic_units1 AS O1 - LEFT OUTER JOIN - taxonomic_units1 AS O2 - ON O1.tsn = O2.parent_tsn - LEFT OUTER JOIN - taxonomic_units1 AS O3 - ON O2.tsn = O3.parent_tsn - LEFT OUTER JOIN - taxonomic_units1 AS O4 - ON O3.tsn = O4.parent_tsn - LEFT OUTER JOIN - taxonomic_units1 AS O5 - ON O4.tsn = O5.parent_tsn - LEFT OUTER JOIN - taxonomic_units1 AS O6 - ON O5.tsn = O6.parent_tsn; Query OK, 2051444 rows affected (2 min 10.96 sec) Records: 2051444 Duplicates: 0 Warnings: 0 My next task here is to match tspecies with another list of species from a different table and display only those which appear in both tables. It can be done: mysql select flatfile.tclass, flatfile.torder, flatfile.tfamily, flatfile.tgenus, flatfile.tspecies from flatfile, marinespecies where tspecies=speciesmarine; I wonder if it is not a better idea to incorporate this query into the first one, perhaps in a form of subquery? Thanks again, Olga -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
self-joins in hierarchical queries: optimization problem
Dear all, I have a table which contains taxonomic data (species, genera, family, order, class) and it is organized as adjacency list model. mysql select* from taxonomic_units1 limit 5; +-+---+-+ | tsn | name | parent_tsn | rank_id | +-+--+--+-+ | 50 | Bacteria |0 | 10 | | 51 | Schizomycetes 202421 | 60 | | 52 | Archangiaceae | 51 |140 | | 53 | Pseudomonadale | 51 | 100 | | 54 | Rhodobacteriineae | 53 | 110 | +-+-++-+ I am trying to flatten it, so that it can be used in further analysis (e.g. in R) I have been trying to run the following query, and it does what I want it to do, but it takes really long time to get it done. As a matter of fact I was not patient enough to get the whole output and instead set LIMIT 10. SELECT O1.name AS tclass, O2.name AS torder, O4.name AS tfamily, O5.name AS tgenus, O6.name AS tspecies FROM taxonomic_units1 AS O1 LEFT OUTER JOIN taxonomic_units1 AS O2 ON O1.tsn = O2.parent_tsn LEFT OUTER JOIN taxonomic_units1 AS O3 ON O2.tsn = O3.parent_tsn LEFT OUTER JOIN taxonomic_units1 AS O4 ON O3.tsn = O4.parent_tsn LEFT OUTER JOIN taxonomic_units1 AS O5 ON O4.tsn = O5.parent_tsn LEFT OUTER JOIN taxonomic_units1 AS O6 ON O5.tsn = O6.parent_tsn LIMIT 10; +---+-+--+-- +---+ | tclass| torder | tfamily | tgenus | tspecies| +---+-+--+-- +---+ | Bacteria | NULL | NULL | NULL| NULL | | Schizomycetes | Archangiaceae| NULL | NULL| NULL | | Schizomycetes | Pseudomonadales | NULL | NULL | NULL| | Schizomycetes | Pseudomonadales | Nitrobacteraceae | Nitrobacter | Nitrobacteragilis | | Schizomycetes | Pseudomonadales | Nitrobacteraceae | Nitrobacter | Nitrobacterflavus| | Schizomycetes | Pseudomonadales | Nitrobacteraceae | Nitrobacter | Nitrobacteroligotrophis | | Schizomycetes | Pseudomonadales | Nitrobacteraceae | Nitrobacter | Nitrobacterpolytrophus | | Schizomycetes | Pseudomonadales | Nitrobacteraceae | Nitrobacter | Nitrobacterpunctata | I have checked this query with EXPLAIN, and it is not using any indices, even though column tsn is set as index in original table. ++-+---+--+---+--+- +--++---+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+--+- +--++---+ | 1 | SIMPLE | O1| ALL | NULL | NULL | NULL| NULL | 483305 | | | 1 | SIMPLE | O2| ALL | NULL | NULL | NULL| NULL | 483305 | | | 1 | SIMPLE | O3| ALL | NULL | NULL | NULL| NULL | 483305 | | | 1 | SIMPLE | O4| ALL | NULL | NULL | NULL| NULL | 483305 | | | 1 | SIMPLE | O5| ALL | NULL | NULL | NULL| NULL | 483305 | | | 1 | SIMPLE | O6| ALL | NULL | NULL | NULL| NULL | 483305 | | ++-+---+--+---+--+- +--++---+ 6 rows in set (0.00 sec) What is wrong with this query? Or is it a problem of all adjacency list models? Is there a way to get columns indexed using self-joins? Thanks, Olga -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: self-joins in hierarchical queries: optimization problem
Not sure if this is the exact problem you're trying to solve, but this helped me in a similar situation. http://dev.mysql.com/tech-resources/articles/hierarchical-data.html kabel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: self-joins in hierarchical queries: optimization problem
Thanks Kabel, Not sure if this is the exact problem you're trying to solve, but this helped me in a similar situation. http://dev.mysql.com/tech-resources/articles/hierarchical-data.html Yes, I have seen this article before, and it is really nice. However they do not discuss any optimization issues. Now I was reading Joe Celko's book. Probably my query is simply not the best solution for this type of problem. Thanks, Olga -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: self-joins in hierarchical queries: optimization problem
Hi Olga, On Thu, Oct 29, 2009 at 03:29:58PM +, Olga Lyashevska wrote: I have a table which contains taxonomic data (species, genera, family, order, class) and it is organized as adjacency list model. mysql select* from taxonomic_units1 limit 5; +-+---+-+ | tsn | name | parent_tsn | rank_id | +-+--+--+-+ | 50 | Bacteria |0 | 10 | | 51 | Schizomycetes 202421 | 60 | | 52 | Archangiaceae | 51 |140 | | 53 | Pseudomonadale | 51 | 100 | | 54 | Rhodobacteriineae | 53 | 110 | +-+-++-+ I am trying to flatten it, so that it can be used in further analysis (e.g. in R) I have been trying to run the following query, and it does what I want it to do, but it takes really long time to get it done. As a matter of fact I was not patient enough to get the whole output and instead set LIMIT 10. SELECT O1.name AS tclass, O2.name AS torder, O4.name AS tfamily, O5.name AS tgenus, O6.name AS tspecies FROM taxonomic_units1 AS O1 LEFT OUTER JOIN taxonomic_units1 AS O2 ON O1.tsn = O2.parent_tsn LEFT OUTER JOIN taxonomic_units1 AS O3 ON O2.tsn = O3.parent_tsn LEFT OUTER JOIN taxonomic_units1 AS O4 ON O3.tsn = O4.parent_tsn LEFT OUTER JOIN taxonomic_units1 AS O5 ON O4.tsn = O5.parent_tsn LEFT OUTER JOIN taxonomic_units1 AS O6 ON O5.tsn = O6.parent_tsn LIMIT 10; +---+-+--+-- +---+ | tclass| torder | tfamily | tgenus | tspecies | +---+-+--+-- +---+ | Bacteria | NULL | NULL | NULL| NULL | | Schizomycetes | Archangiaceae| NULL | NULL| NULL | | Schizomycetes | Pseudomonadales | NULL | NULL | NULL| | Schizomycetes | Pseudomonadales | Nitrobacteraceae | Nitrobacter| Nitrobacteragilis | | Schizomycetes | Pseudomonadales | Nitrobacteraceae | Nitrobacter| Nitrobacterflavus| | Schizomycetes | Pseudomonadales | Nitrobacteraceae | Nitrobacter| Nitrobacteroligotrophis | | Schizomycetes | Pseudomonadales | Nitrobacteraceae | Nitrobacter| Nitrobacterpolytrophus | | Schizomycetes | Pseudomonadales | Nitrobacteraceae | Nitrobacter| Nitrobacterpunctata | I have checked this query with EXPLAIN, and it is not using any indices, even though column tsn is set as index in original table. ++-+---+--+---+--+- +--++---+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+--+- +--++---+ | 1 | SIMPLE | O1| ALL | NULL | NULL | NULL| NULL | 483305 | | | 1 | SIMPLE | O2| ALL | NULL | NULL | NULL| NULL | 483305 | | | 1 | SIMPLE | O3| ALL | NULL | NULL | NULL| NULL | 483305 | | | 1 | SIMPLE | O4| ALL | NULL | NULL | NULL| NULL | 483305 | | | 1 | SIMPLE | O5| ALL | NULL | NULL | NULL| NULL | 483305 | | | 1 | SIMPLE | O6| ALL | NULL | NULL | NULL| NULL | 483305 | | ++-+---+--+---+--+- +--++---+ 6 rows in set (0.00 sec) What is wrong with this query? Or is it a problem of all adjacency list models? Is there a way to get columns indexed using self-joins? For an outer join like ... taxonomic_units1 AS O1 LEFT OUTER JOIN taxonomic_units1 AS O2 ON O1.tsn = O2.parent_tsn current optimizer has only one option(*): use Nested-Loops Join algorthm, with the outer table being the first one. That is, it will execute these loops: for each record from O1 for each record in O2 such that O1.tsn = O2.parent_tsn ... this makes it clear that index on O1.tsn will not be useful. You need indexes on parent_tsn column. (*) certain kinds of WHERE clause may open other opportinites. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog -- MySQL General Mailing List For list archives: http
Re: self-joins in hierarchical queries: optimization problem
On Thu, Oct 29, 2009 at 07:53:25PM +0300, Sergey Petrunya wrote: ... taxonomic_units1 AS O1 LEFT OUTER JOIN taxonomic_units1 AS O2 ON O1.tsn = O2.parent_tsn current optimizer has only one option(*): use Nested-Loops Join algorthm, with the outer table being the first one. That is, it will execute these loops: for each record from O1 for each record in O2 such that O1.tsn = O2.parent_tsn ... this makes it clear that index on O1.tsn will not be useful. You need indexes on parent_tsn column. I meant, one index over taxonomic_units1.parent_tsn . BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: self-joins in hierarchical queries: optimization problem
Olga, Would you post SHOW CREATE TABLE taxonomic_units1\G;? It should give us more info on the table you are dealing with Regards, Mikhail Berman Olga Lyashevska wrote: Dear all, I have a table which contains taxonomic data (species, genera, family, order, class) and it is organized as adjacency list model. mysql select* from taxonomic_units1 limit 5; +-+---+-+ | tsn | name | parent_tsn | rank_id | +-+--+--+-+ | 50 | Bacteria | 0 | 10 | | 51 | Schizomycetes 202421 | 60 | | 52 | Archangiaceae | 51 | 140 | | 53 | Pseudomonadale | 51 | 100 | | 54 | Rhodobacteriineae | 53 | 110 | +-+-++-+ I am trying to flatten it, so that it can be used in further analysis (e.g. in R) I have been trying to run the following query, and it does what I want it to do, but it takes really long time to get it done. As a matter of fact I was not patient enough to get the whole output and instead set LIMIT 10. SELECT O1.name AS tclass, O2.name AS torder, O4.name AS tfamily, O5.name AS tgenus, O6.name AS tspecies FROM taxonomic_units1 AS O1 LEFT OUTER JOIN taxonomic_units1 AS O2 ON O1.tsn = O2.parent_tsn LEFT OUTER JOIN taxonomic_units1 AS O3 ON O2.tsn = O3.parent_tsn LEFT OUTER JOIN taxonomic_units1 AS O4 ON O3.tsn = O4.parent_tsn LEFT OUTER JOIN taxonomic_units1 AS O5 ON O4.tsn = O5.parent_tsn LEFT OUTER JOIN taxonomic_units1 AS O6 ON O5.tsn = O6.parent_tsn LIMIT 10; +---+-+--+--+---+ | tclass | torder | tfamily | tgenus | tspecies | +---+-+--+--+---+ | Bacteria | NULL | NULL | NULL | NULL | | Schizomycetes | Archangiaceae | NULL | NULL | NULL | | Schizomycetes | Pseudomonadales | NULL | NULL | NULL | | Schizomycetes | Pseudomonadales | Nitrobacteraceae | Nitrobacter | Nitrobacteragilis | | Schizomycetes | Pseudomonadales | Nitrobacteraceae | Nitrobacter | Nitrobacterflavus | | Schizomycetes | Pseudomonadales | Nitrobacteraceae | Nitrobacter | Nitrobacteroligotrophis | | Schizomycetes | Pseudomonadales | Nitrobacteraceae | Nitrobacter | Nitrobacterpolytrophus | | Schizomycetes | Pseudomonadales | Nitrobacteraceae | Nitrobacter | Nitrobacterpunctata | I have checked this query with EXPLAIN, and it is not using any indices, even though column tsn is set as index in original table. ++-+---+--+---+--+-+--++---+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+--+-+--++---+ | 1 | SIMPLE | O1 | ALL | NULL | NULL | NULL | NULL | 483305 | | | 1 | SIMPLE | O2 | ALL | NULL | NULL | NULL | NULL | 483305 | | | 1 | SIMPLE | O3 | ALL | NULL | NULL | NULL | NULL | 483305 | | | 1 | SIMPLE | O4 | ALL | NULL | NULL | NULL | NULL | 483305 | | | 1 | SIMPLE | O5 | ALL | NULL | NULL | NULL | NULL | 483305 | | | 1 | SIMPLE | O6 | ALL | NULL | NULL | NULL | NULL | 483305 | | ++-+---+--+---+--+-+--++---+ 6 rows in set (0.00 sec) What is wrong with this query? Or is it a problem of all adjacency list models? Is there a way to get columns indexed using self-joins? Thanks, Olga -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Nested Joins
Thanks! Got it! I was missing the word from...
select * FROM %s c ...
Thanks again ;)
V
On Thu, Oct 1, 2009 at 5:08 PM, Gavin Towey gto...@ffn.com wrote:
Victor,
Just noticed, the join condition from client to productions should be
changed:
select * from ben_franklin_planners c join products p on c.Item=p.ID join
categories cat on p.Category=cat.ID
If you’re still getting syntax errors you need to check your variables.
Try assigning the query you’re building to a string, then printing it out so
you know **exactly** what you’re sending to mysql.
Regards,
Gavin Towey
*From:* Victor Subervi [mailto:victorsube...@gmail.com]
*Sent:* Thursday, October 01, 2009 3:04 PM
*To:* Gavin Towey; mysql@lists.mysql.com
*Subject:* Re: Nested Joins
Well, your syntax is *exactly* what I had (with a few cosmetic changes).
I've been over the MySQL manual on joins with no luck. I'll read over your
resources tonight. Any other ideas would be appreciated.
Thanks,
V
On Thu, Oct 1, 2009 at 4:49 PM, Gavin Towey gto...@ffn.com wrote:
Victor,
Thank you for the information, that was helpful.
At least part of the problem is the variables you are replacing in that
string, which we can’t see.
The statement should be something like:
select * from ben_franklin_planners c join products p on c.Item=p.Item join
categories cat on p.Category=cat.ID
Make your code produce the above, and you should be fine. I suspect you
don’t need LEFT JOIN there, an inner join will suffice.
For more info on joins:
http://hashmysql.org/index.php?title=Introduction_to_Joins
For more indepth info:
http://dev.mysql.com/tech-resources/articles/mysql-db-design-ch5.pdf
http://dev.mysql.com/doc/refman/5.1/en/join.html
Regards,
Gavin Towey
*From:* Victor Subervi [mailto:victorsube...@gmail.com]
*Sent:* Thursday, October 01, 2009 2:25 PM
*To:* Gavin Towey; mysql@lists.mysql.com
*Subject:* Re: Nested Joins
On Thu, Oct 1, 2009 at 4:03 PM, Gavin Towey gto...@ffn.com wrote:
Joins aren't nested like that, unless you use a subquery. I think you just
need to remove the parens around the second join.
I tried that and no go :(
For better help:
1. show the real SQL -- echo the statement. Most people here don't like
looking at app code because your variables could contain anything.
ProgrammingError: (1064, You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right syntax to
use near ') left join categories on products.Category=categories.ID)' at
line 1), referer: http://13gems.com/global_solutions/spreadsheet_edit.py
2. Give the exact error message
Isn't that the same thing?
3. If there's no error, explain what you expect and what you're getting
4. Include table schema
DESCRIBE `ben_franklin_planners`
ID int(4) unsigned NULL
Item int(4) unsigned NULL
Discount int(2) unsigned NULL
DESCRIBE categories
ID int(3) primary key not NULL auto_increment
Category varchar(20) unique NULL
describe products
ID int(4) primary key not NULL
Category int(3) NULL
Item varchar(20) UNIQUE NULL
Description varchar(255) NULL
UOM varchar(20) NULL
Price float(7,2) NULL
5. Explain what you're trying to accomplish.
cursor.execute('select * from %s left join products on
%s.Item=products.Item left join categories on
products.Category=categories.ID;' % (client, client))
The client in this case is ben_franklin_planners
ben_franklin_planners has an item # that is the same as the item # in
products, where all the information about the products is to be found,
EXCEPT the name of the category. For that, we have to go to the categories
table.
Hope that makes it clear.
TIA,
V
--
The information contained in this transmission may contain privileged and
confidential information. It is intended only for the use of the person(s)
named above. If you are not the intended recipient, you are hereby notified
that any review, dissemination, distribution or duplication of this
communication is strictly prohibited. If you are not the intended recipient,
please contact the sender by reply email and destroy all copies of the
original message.
--
The information contained in this transmission may contain privileged and
confidential information. It is intended only for the use of the person(s)
named above. If you are not the intended recipient, you are hereby notified
that any review, dissemination, distribution or duplication of this
communication is strictly prohibited. If you are not the intended recipient,
please contact the sender by reply email and destroy all copies of the
original message.
Nested Joins
Hi;
I'm new to join statements. Here's my python syntax:
cursor.execute('select * from %s left join products on
%s.Item=products.Item (left join categories on
products.Category=categories.ID);' % (client, client))
I believe it's clear how I want to nest, but I don't know how to repair my
syntax.
TIA,
V
RE: Nested Joins
Joins aren't nested like that, unless you use a subquery. I think you just
need to remove the parens around the second join.
For better help:
1. show the real SQL -- echo the statement. Most people here don't like
looking at app code because your variables could contain anything.
2. Give the exact error message
3. If there's no error, explain what you expect and what you're getting
4. Include table schema
5. Explain what you're trying to accomplish.
Regards,
Gavin Towey
-Original Message-
From: Victor Subervi [mailto:victorsube...@gmail.com]
Sent: Thursday, October 01, 2009 1:34 PM
To: mysql@lists.mysql.com
Subject: Nested Joins
Hi;
I'm new to join statements. Here's my python syntax:
cursor.execute('select * from %s left join products on
%s.Item=products.Item (left join categories on
products.Category=categories.ID);' % (client, client))
I believe it's clear how I want to nest, but I don't know how to repair my
syntax.
TIA,
V
The information contained in this transmission may contain privileged and
confidential information. It is intended only for the use of the person(s)
named above. If you are not the intended recipient, you are hereby notified
that any review, dissemination, distribution or duplication of this
communication is strictly prohibited. If you are not the intended recipient,
please contact the sender by reply email and destroy all copies of the original
message.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Nested Joins
On Thu, Oct 1, 2009 at 4:03 PM, Gavin Towey gto...@ffn.com wrote:
Joins aren't nested like that, unless you use a subquery. I think you just
need to remove the parens around the second join.
I tried that and no go :(
For better help:
1. show the real SQL -- echo the statement. Most people here don't like
looking at app code because your variables could contain anything.
ProgrammingError: (1064, You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right syntax to
use near ') left join categories on products.Category=categories.ID)' at
line 1), referer: http://13gems.com/global_solutions/spreadsheet_edit.py
2. Give the exact error message
Isn't that the same thing?
3. If there's no error, explain what you expect and what you're getting
4. Include table schema
DESCRIBE `ben_franklin_planners`
ID int(4) unsigned NULL
Item int(4) unsigned NULL
Discount int(2) unsigned NULL
DESCRIBE categories
ID int(3) primary key not NULL auto_increment
Category varchar(20) unique NULL
describe products
ID int(4) primary key not NULL
Category int(3) NULL
Item varchar(20) UNIQUE NULL
Description varchar(255) NULL
UOM varchar(20) NULL
Price float(7,2) NULL
5. Explain what you're trying to accomplish.
cursor.execute('select * from %s left join products on
%s.Item=products.Item left join categories on
products.Category=categories.ID;' % (client, client))
The client in this case is ben_franklin_planners
ben_franklin_planners has an item # that is the same as the item # in
products, where all the information about the products is to be found,
EXCEPT the name of the category. For that, we have to go to the categories
table.
Hope that makes it clear.
TIA,
V
RE: Nested Joins
Victor,
Thank you for the information, that was helpful.
At least part of the problem is the variables you are replacing in that string,
which we can't see.
The statement should be something like:
select * from ben_franklin_planners c join products p on c.Item=p.Item join
categories cat on p.Category=cat.ID
Make your code produce the above, and you should be fine. I suspect you don't
need LEFT JOIN there, an inner join will suffice.
For more info on joins:
http://hashmysql.org/index.php?title=Introduction_to_Joins
For more indepth info:
http://dev.mysql.com/tech-resources/articles/mysql-db-design-ch5.pdf
http://dev.mysql.com/doc/refman/5.1/en/join.html
Regards,
Gavin Towey
From: Victor Subervi [mailto:victorsube...@gmail.com]
Sent: Thursday, October 01, 2009 2:25 PM
To: Gavin Towey; mysql@lists.mysql.com
Subject: Re: Nested Joins
On Thu, Oct 1, 2009 at 4:03 PM, Gavin Towey
gto...@ffn.commailto:gto...@ffn.com wrote:
Joins aren't nested like that, unless you use a subquery. I think you just
need to remove the parens around the second join.
I tried that and no go :(
For better help:
1. show the real SQL -- echo the statement. Most people here don't like
looking at app code because your variables could contain anything.
ProgrammingError: (1064, You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right syntax to
use near ') left join categories on products.Category=categories.ID)' at line
1), referer: http://13gems.com/global_solutions/spreadsheet_edit.py
2. Give the exact error message
Isn't that the same thing?
3. If there's no error, explain what you expect and what you're getting
4. Include table schema
DESCRIBE `ben_franklin_planners`
ID int(4) unsigned NULL
Item int(4) unsigned NULL
Discount int(2) unsigned NULL
DESCRIBE categories
ID int(3) primary key not NULL auto_increment
Category varchar(20) unique NULL
describe products
ID int(4) primary key not NULL
Category int(3) NULL
Item varchar(20) UNIQUE NULL
Description varchar(255) NULL
UOM varchar(20) NULL
Price float(7,2) NULL
5. Explain what you're trying to accomplish.
cursor.execute('select * from %s left join products on %s.Item=products.Item
left join categories on products.Category=categories.ID;' % (client, client))
The client in this case is ben_franklin_planners
ben_franklin_planners has an item # that is the same as the item # in products,
where all the information about the products is to be found, EXCEPT the name of
the category. For that, we have to go to the categories table.
Hope that makes it clear.
TIA,
V
The information contained in this transmission may contain privileged and
confidential information. It is intended only for the use of the person(s)
named above. If you are not the intended recipient, you are hereby notified
that any review, dissemination, distribution or duplication of this
communication is strictly prohibited. If you are not the intended recipient,
please contact the sender by reply email and destroy all copies of the original
message.
Re: Nested Joins
Well, your syntax is *exactly* what I had (with a few cosmetic changes).
I've been over the MySQL manual on joins with no luck. I'll read over your
resources tonight. Any other ideas would be appreciated.
Thanks,
V
On Thu, Oct 1, 2009 at 4:49 PM, Gavin Towey gto...@ffn.com wrote:
Victor,
Thank you for the information, that was helpful.
At least part of the problem is the variables you are replacing in that
string, which we can’t see.
The statement should be something like:
select * from ben_franklin_planners c join products p on c.Item=p.Item join
categories cat on p.Category=cat.ID
Make your code produce the above, and you should be fine. I suspect you
don’t need LEFT JOIN there, an inner join will suffice.
For more info on joins:
http://hashmysql.org/index.php?title=Introduction_to_Joins
For more indepth info:
http://dev.mysql.com/tech-resources/articles/mysql-db-design-ch5.pdf
http://dev.mysql.com/doc/refman/5.1/en/join.html
Regards,
Gavin Towey
*From:* Victor Subervi [mailto:victorsube...@gmail.com]
*Sent:* Thursday, October 01, 2009 2:25 PM
*To:* Gavin Towey; mysql@lists.mysql.com
*Subject:* Re: Nested Joins
On Thu, Oct 1, 2009 at 4:03 PM, Gavin Towey gto...@ffn.com wrote:
Joins aren't nested like that, unless you use a subquery. I think you just
need to remove the parens around the second join.
I tried that and no go :(
For better help:
1. show the real SQL -- echo the statement. Most people here don't like
looking at app code because your variables could contain anything.
ProgrammingError: (1064, You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right syntax to
use near ') left join categories on products.Category=categories.ID)' at
line 1), referer: http://13gems.com/global_solutions/spreadsheet_edit.py
2. Give the exact error message
Isn't that the same thing?
3. If there's no error, explain what you expect and what you're getting
4. Include table schema
DESCRIBE `ben_franklin_planners`
ID int(4) unsigned NULL
Item int(4) unsigned NULL
Discount int(2) unsigned NULL
DESCRIBE categories
ID int(3) primary key not NULL auto_increment
Category varchar(20) unique NULL
describe products
ID int(4) primary key not NULL
Category int(3) NULL
Item varchar(20) UNIQUE NULL
Description varchar(255) NULL
UOM varchar(20) NULL
Price float(7,2) NULL
5. Explain what you're trying to accomplish.
cursor.execute('select * from %s left join products on
%s.Item=products.Item left join categories on
products.Category=categories.ID;' % (client, client))
The client in this case is ben_franklin_planners
ben_franklin_planners has an item # that is the same as the item # in
products, where all the information about the products is to be found,
EXCEPT the name of the category. For that, we have to go to the categories
table.
Hope that makes it clear.
TIA,
V
--
The information contained in this transmission may contain privileged and
confidential information. It is intended only for the use of the person(s)
named above. If you are not the intended recipient, you are hereby notified
that any review, dissemination, distribution or duplication of this
communication is strictly prohibited. If you are not the intended recipient,
please contact the sender by reply email and destroy all copies of the
original message.
RE: Nested Joins
Victor,
Just noticed, the join condition from client to productions should be changed:
select * from ben_franklin_planners c join products p on c.Item=p.ID join
categories cat on p.Category=cat.ID
If you're still getting syntax errors you need to check your variables. Try
assigning the query you're building to a string, then printing it out so you
know *exactly* what you're sending to mysql.
Regards,
Gavin Towey
From: Victor Subervi [mailto:victorsube...@gmail.com]
Sent: Thursday, October 01, 2009 3:04 PM
To: Gavin Towey; mysql@lists.mysql.com
Subject: Re: Nested Joins
Well, your syntax is *exactly* what I had (with a few cosmetic changes). I've
been over the MySQL manual on joins with no luck. I'll read over your resources
tonight. Any other ideas would be appreciated.
Thanks,
V
On Thu, Oct 1, 2009 at 4:49 PM, Gavin Towey
gto...@ffn.commailto:gto...@ffn.com wrote:
Victor,
Thank you for the information, that was helpful.
At least part of the problem is the variables you are replacing in that string,
which we can't see.
The statement should be something like:
select * from ben_franklin_planners c join products p on c.Item=p.Item join
categories cat on p.Category=cat.ID
Make your code produce the above, and you should be fine. I suspect you don't
need LEFT JOIN there, an inner join will suffice.
For more info on joins:
http://hashmysql.org/index.php?title=Introduction_to_Joins
For more indepth info:
http://dev.mysql.com/tech-resources/articles/mysql-db-design-ch5.pdf
http://dev.mysql.com/doc/refman/5.1/en/join.html
Regards,
Gavin Towey
From: Victor Subervi
[mailto:victorsube...@gmail.commailto:victorsube...@gmail.com]
Sent: Thursday, October 01, 2009 2:25 PM
To: Gavin Towey; mysql@lists.mysql.commailto:mysql@lists.mysql.com
Subject: Re: Nested Joins
On Thu, Oct 1, 2009 at 4:03 PM, Gavin Towey
gto...@ffn.commailto:gto...@ffn.com wrote:
Joins aren't nested like that, unless you use a subquery. I think you just
need to remove the parens around the second join.
I tried that and no go :(
For better help:
1. show the real SQL -- echo the statement. Most people here don't like
looking at app code because your variables could contain anything.
ProgrammingError: (1064, You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right syntax to
use near ') left join categories on products.Category=categories.ID)' at line
1), referer: http://13gems.com/global_solutions/spreadsheet_edit.py
2. Give the exact error message
Isn't that the same thing?
3. If there's no error, explain what you expect and what you're getting
4. Include table schema
DESCRIBE `ben_franklin_planners`
ID int(4) unsigned NULL
Item int(4) unsigned NULL
Discount int(2) unsigned NULL
DESCRIBE categories
ID int(3) primary key not NULL auto_increment
Category varchar(20) unique NULL
describe products
ID int(4) primary key not NULL
Category int(3) NULL
Item varchar(20) UNIQUE NULL
Description varchar(255) NULL
UOM varchar(20) NULL
Price float(7,2) NULL
5. Explain what you're trying to accomplish.
cursor.execute('select * from %s left join products on %s.Item=products.Item
left join categories on products.Category=categories.ID;' % (client, client))
The client in this case is ben_franklin_planners
ben_franklin_planners has an item # that is the same as the item # in products,
where all the information about the products is to be found, EXCEPT the name of
the category. For that, we have to go to the categories table.
Hope that makes it clear.
TIA,
V
The information contained in this transmission may contain privileged and
confidential information. It is intended only for the use of the person(s)
named above. If you are not the intended recipient, you are hereby notified
that any review, dissemination, distribution or duplication of this
communication is strictly prohibited. If you are not the intended recipient,
please contact the sender by reply email and destroy all copies of the original
message.
The information contained in this transmission may contain privileged and
confidential information. It is intended only for the use of the person(s)
named above. If you are not the intended recipient, you are hereby notified
that any review, dissemination, distribution or duplication of this
communication is strictly prohibited. If you are not the intended recipient,
please contact the sender by reply email and destroy all copies of the original
message.
Re: Newbie --- JOINS
Hello Bob, BobSharp wrote: Picture does not seem to have been carried in the message, posts with attachment did not seem to get through either. So hope the link works. Below is the ER diagram in an exercise I am trying to do. http://www.probowluk.co.uk/images/er_ECA_001.jpg It's been going well so far, and several ColdFusion pages written already. Need to do one for a Purchase Order Report for ... - given SupplierCode - given StartDate and EndDate of Orders My problem is in the CFquery - understanding what JOINS to use and in what order to use them. Which kind of join to write all depends on how inclusive you want your results to be. On a sheet of paper draw two overlapping circles (yes this is a Venn diagram). In the left circle, write left, in the right circle write right, and where they overlap write inner. Above each circle put the name of a table. From your diagram and your descriptoin is sounds like you wanted to link Suppliers and PurchaseOrders. Put Suppliers over the left circle and PurchaseOrders over the right. If I assume that `Suppliers`.`SupplierCode` and `PurchaseOrders`.`SupplierCodefk` are the same numbers then here is how to get your results. Remember: a JOIN is a form of cross-product between two tables. If you have one row in one table and it matches to 3 rows in the other table, then the first table's data will be listed 3 times. This correct and proper behavior for a database. To get the list of all rows in Suppliers and any matching rows from PurchaseOrders, use this form: SELECT ... FROM `Suppliers` LEFT JOIN `PurchaseOrders` ON `Suppliers`.`SupplierCode` = `PurchaseOrders`.`SupplierCodefk` WHERE ... To get all of the rows from PurchaseOrders and only those matching rows from Suppliers, use: SELECT ... FROM `Suppliers` RIGHT JOIN `PurchaseOrders` ON `Suppliers`.`SupplierCode` = `PurchaseOrders`.`SupplierCodefk` WHERE ... And if you only want to list rows from either table if they have at least one matching row in the other, use: SELECT ... FROM `Suppliers` INNER JOIN `PurchaseOrders` ON `Suppliers`.`SupplierCode` = `PurchaseOrders`.`SupplierCodefk` WHERE ... The WHERE clause is optional. Of course, you will have to either supply a list of columns or use the * operator for the SELECT clause but this is the general shape of the command you will want to use. -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Newbie --- JOINS
Picture does not seem to have been carried in the message, posts with attachment did not seem to get through either. So hope the link works. Below is the ER diagram in an exercise I am trying to do. http://www.probowluk.co.uk/images/er_ECA_001.jpg It's been going well so far, and several ColdFusion pages written already. Need to do one for a Purchase Order Report for ... - given SupplierCode - given StartDate and EndDate of Orders My problem is in the CFquery - understanding what JOINS to use and in what order to use them. (it is a MyISAM database --- no constraints) -- I am using the free version of SPAMfighter. We are a community of 6 million users fighting spam. SPAMfighter has removed 12908 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len The Professional version does not have this message
Newbie and JOINS
Below is the ER diagram in an exercise I am trying to do. It's been going well so far, and several ColdFusion pages written already. Need to do one for a Purchase Order Report for ... - given SupplierCode - given StartDate and EndDate of Orders My problem is in the CFquery - understanding what JOINS to use and in what order to use them. (it is a MyISAM database = no constraints) -- I am using the free version of SPAMfighter. We are a community of 6 million users fighting spam. SPAMfighter has removed 12908 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len The Professional version does not have this message
Re: Are equi-joins faster than left joins?
I have 3 tables that are 1:1 and will always have a row for a given product,_code date. If I want to join them together, is it going to be faster to use an equi join or a left join, or does it matter? Who cares, they are -different- things symantically, use the correct type of join for your data and resultset. Speed is an implementation issue, a physical property. With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database 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/mysql?unsub=arch...@jab.org
Re: Are equi-joins faster than left joins?
IIRC it does not matter. But you can double-check my opinion with EXPLAIN. A. On Thu, Mar 12, 2009 at 11:41 AM, mos mo...@fastmail.fm wrote: I have 3 tables that are 1:1 and will always have a row for a given product,_code date. If I want to join them together, is it going to be faster to use an equi join or a left join, or does it matter? TIA Mike
Re: Are equi-joins faster than left joins?
On Thu, Mar 12, 2009 at 8:41 AM, mos mo...@fastmail.fm wrote: I have 3 tables that are 1:1 and will always have a row for a given product,_code date. If I want to join them together, is it going to be faster to use an equi join or a left join, or does it matter? IIRC:With an inner join the optimizer has more latitude to reorganize the join order and can short circuit the process if there is not a corresponding row. This is completely secondary to getting the right result set for your query. If there is the possibility of a row in the parent table not joining and needing to be part of the result then you must use left. -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Are equi-joins faster than left joins?
I have 3 tables that are 1:1 and will always have a row for a given product,_code date. If I want to join them together, is it going to be faster to use an equi join or a left join, or does it matter? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
SELECT with JOINs
This links to an ERD diagram that illustrates a MySQL database schema. www.ProBowlUK.co.uk\images\ERD_001.jpg They are MyISAM tables, with no Foreign Key contraints (the fk suffix is not used in the actual database). I need to provide MySQLSELECT statements, with various combinations of JOINs, to give the following output ... 1) Who is assigned what assets? 2) What maintenance has been carried out on each asset? 3) Which assets have not undergone any maintenance? 4) Who hasn't been assigned any assets? 5) Which assets have not been scheduled for maintenance? 6) Which employees have been assigned assets for which no maintenance has been scheduled? I have managed to complete the first 2. Making no headway at all with the others. Can anyone give me pointers/examples ? Also, in the first SELECT, is there a way of not repeating an employee's name in the output ? -- I am using the free version of SPAMfighter. We are a community of 6 million users fighting spam. SPAMfighter has removed 12716 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len The Professional version does not have this message
Re: left joins concat
Hi Phil,
you seem quite a bit confused!
I would not step to use joins before understanding the 'simple' logic
behind,
otherwise you will be always confused by the syntax.
There are many, many, many resources (thanks to Tim!)
I will try to give you a simple overview of joins but please get more
confortable with them!
In relational databases the information is(should!) logically separated into
entities which are no more no less represented by tables,
yes the tables you well know(mathematically speaking they are known as
[relations] the R in RDBMS).
So in some application,like yours, you could have the need to deal with
[books], [authors], [publishers].
These, as said, are the so called entities when we speak from an
abstract-logical point of view,
but eventually turn to be the tables in your database.
So you have a table with all the books, a table with all the authors, and a
table with the publishers.
How could we relate each book with his author?
One way is to have extra information on each book (1 book=1 record/row in
the [books] table),
if an author wrote 100 books you would have 100 times the same information
on each of his books.
another way(better!) is to add the extra information as well, but just an
identifier of the author,
an handle, a key, a UNIQUE value (Social Security Number?) , so that you
have only one place
with the author information (the [author] table) which is also great for
maintenance!
Imagine updating an information about the author on 100 rows of the [books]
table,
and update the same information just in one row of the [authors] table.
I think you can imagine also that the UNIQUE value you add to each book
which identifies the book author,
will be present in the [authors] table to be able to identify the author.
Until now we are just speaking about logic and you could do an excercise
with pen and paper,
drawing a line from the AUTHOR UNIQUE ID from the [books] table to the
AUTHOR UNIQUE ID from the [authors] table.
So you could easily find the author of each book by following the line the
links the two rows/records,
on the left you have the books and on the right you have the authors.
Reading from left to right, for instance, you would be able now to read
consequently the book title and the book author name.
Sit back, the JOIN is the line you have just draw.
It is the connection between two tables to be able to have on just one row
all the information that are split into two(or more) parts/table.
The ON clause that you find in the JOIN syntax is the place where you
specify
ON [books].AUTHOR UNIQUE ID = [authors].AUTHOR UNIQUE ID
( by the way the ID that points to the table with all the informations is
also knows as FOREIGN KEY, in this case the left operand)
The resulting table is a table that have each row like a concatenation of
two rows related from the two different tables.
The WHERE clause is used to FILTER, not to connect the two tables!!
After you connect(join) the two tables you could want to see only certain
rows, ok now you use the WHERE.
Forget about CONCAT/CONCAT_WS this is a string function, and is not related
to JOINS.
Please, let me know if this was useful to you.
Claudio Nanni
2009/2/22 PJ af.gour...@videotron.ca
I have been searching and searching for a clear and logical explanation
of JOINs and have found nothing that can be reasonably understood.
Perhaps I am dense or from another planet, but nothing seems to fall
into place.
I need to display all the books (with their respective authors and
publishers) -
the tables are book, author, publisher and book_author, book_publisher
for linking many to many books-authors and books-publishers.
Here is what I have (and it gives me rather confusing results:
SELECT b.title, b.sub_title, b.descr, b.comment, b.bk_cover,
b.copyright, b.ISBN, b.sellers, c.publisher,
CONCAT_WS(' ', first_name, last_name) AS Author
FROM book AS b, publishers AS c
NATURAL JOIN book_author AS ab
NATURAL JOIN author AS a
NATURAL JOIN book_publisher AS d
WHERE d.bookID = b.id
ORDER BY title ASC
First, I see what CONCAT_WS does (more or less) but I cannot grasp the
logic related to the author and book_author tables.
Second, I don't understand the logic of the linking tables since the
relationships seem to have to be stated outside the tables... (i.e. the
foreign key reference is in the table but seems to be necessary in a
WHERE clause as well ???
And lastly, I don't understand what conditions (ON or WHERE clauses)
need to be included to get the books matched up to their respective
authors and publishers.
One link that I have been trying to fathom is
http://www.java2s.com/Tutorial/MySQL/0100__Table-Join/Catalog0100__Table-Join.htm
but it is not very helpful in any explanations...
I sure would like to hear some clear explanations...
TIA
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
--
MySQL General Mailing List
For list archives
Re: left joins concat
Claudio Nanni wrote:
Hi Phil,
you seem quite a bit confused!
I would not step to use joins before understanding the 'simple' logic
behind,
otherwise you will be always confused by the syntax.
There are many, many, many resources (thanks to Tim!)
I will try to give you a simple overview of joins but please get more
confortable with them!
In relational databases the information is(should!) logically
separated into entities which are no more no less represented by tables,
yes the tables you well know(mathematically speaking they are known as
[relations] the R in RDBMS).
So in some application,like yours, you could have the need to deal
with [books], [authors], [publishers].
These, as said, are the so called entities when we speak from an
abstract-logical point of view,
but eventually turn to be the tables in your database.
So you have a table with all the books, a table with all the authors,
and a table with the publishers.
How could we relate each book with his author?
One way is to have extra information on each book (1 book=1 record/row
in the [books] table),
if an author wrote 100 books you would have 100 times the same
information on each of his books.
another way(better!) is to add the extra information as well, but just
an identifier of the author,
an handle, a key, a UNIQUE value (Social Security Number?) , so that
you have only one place
with the author information (the [author] table) which is also great
for maintenance!
Imagine updating an information about the author on 100 rows of the
[books] table,
and update the same information just in one row of the [authors] table.
I think you can imagine also that the UNIQUE value you add to each
book which identifies the book author,
will be present in the [authors] table to be able to identify the author.
Until now we are just speaking about logic and you could do an
excercise with pen and paper,
drawing a line from the AUTHOR UNIQUE ID from the [books] table to
the AUTHOR UNIQUE ID from the [authors] table.
So you could easily find the author of each book by following the line
the links the two rows/records,
on the left you have the books and on the right you have the authors.
Reading from left to right, for instance, you would be able now to
read consequently the book title and the book author name.
Sit back, the JOIN is the line you have just draw.
It is the connection between two tables to be able to have on just one
row all the information that are split into two(or more) parts/table.
The ON clause that you find in the JOIN syntax is the place where you
specify
ON [books].AUTHOR UNIQUE ID = [authors].AUTHOR UNIQUE ID
( by the way the ID that points to the table with all the informations
is also knows as FOREIGN KEY, in this case the left operand)
The resulting table is a table that have each row like a concatenation
of two rows related from the two different tables.
The WHERE clause is used to FILTER, not to connect the two tables!!
After you connect(join) the two tables you could want to see only
certain rows, ok now you use the WHERE.
Forget about CONCAT/CONCAT_WS this is a string function, and is not
related to JOINS.
Please, let me know if this was useful to you.
Claudio Nanni
2009/2/22 PJ af.gour...@videotron.ca mailto:af.gour...@videotron.ca
I have been searching and searching for a clear and logical
explanation
of JOINs and have found nothing that can be reasonably understood.
Perhaps I am dense or from another planet, but nothing seems to fall
into place.
I need to display all the books (with their respective authors and
publishers) -
the tables are book, author, publisher and book_author, book_publisher
for linking many to many books-authors and books-publishers.
Here is what I have (and it gives me rather confusing results:
SELECT b.title, b.sub_title, b.descr, b.comment, b.bk_cover,
b.copyright, b.ISBN, b.sellers, c.publisher,
CONCAT_WS(' ', first_name, last_name) AS Author
FROM book AS b, publishers AS c
NATURAL JOIN book_author AS ab
NATURAL JOIN author AS a
NATURAL JOIN book_publisher AS d
WHERE d.bookID = b.id http://b.id
ORDER BY title ASC
First, I see what CONCAT_WS does (more or less) but I cannot grasp the
logic related to the author and book_author tables.
Second, I don't understand the logic of the linking tables since the
relationships seem to have to be stated outside the tables...
(i.e. the
foreign key reference is in the table but seems to be necessary in a
WHERE clause as well ???
And lastly, I don't understand what conditions (ON or WHERE clauses)
need to be included to get the books matched up to their respective
authors and publishers.
One link that I have been trying to fathom is
http://www.java2s.com/Tutorial/MySQL/0100__Table-Join/Catalog0100__Table-Join.htm
Re: left joins concat
Gentlemen,
and all those who care,
THE PROBLEM SEEMS TO BE SOLVED (for now, NEXT is HOW TO QUERY(SELECT)
MULTIPLE AUTHORS AND DISPLAY THAT and then HOW TO DEAL WITH MULTIPLE
CATEGORIES (heh... heh... heh ;-) :
SELECT b.title, b.sub_title, b.descr, b.comment, b.bk_cover,
b.copyright, b.ISBN, c.publisher,
CONCAT_WS(' ', first_name, last_name) AS Author
FROM book AS b
LEFT JOIN book_author AS ab ON b.id = ab.bookID
LEFT JOIN author AS a ON ab.authID=a.id
LEFT JOIN book_publisher AS abc ON b.id = abc.bookID
LEFT JOIN publishers AS c ON abc.publishers_id = c.id
ORDER BY title ASC
This works except I don't fully understand how the CONCAT AS Author
works, nor the AS ab and AS abc.
Does the order of ab and abc matter? Are they related... I think I got
this working by pure experimentation in trying all possible
combinations... kind of primitive, but it seems to work... long hours
and loss of sleep... etc...
And then there is the warning message at the bottom of the displayed web
page:
*Warning*: Unknown: Your script possibly relies on a session side-effect
which existed until PHP 4.2.3. Please be advised that the session
extension does not consider global variables as a source of data, unless
register_globals is enabled. You can disable this functionality and this
warning by setting session.bug_compat_42 or session.bug_compat_warn to
off, respectively. in *Unknown* on line *0
*I guess I'll have to research it on the web... :-)*
*
Claudio Nanni wrote:
Hi Phil,
you seem quite a bit confused!
I would not step to use joins before understanding the 'simple' logic
behind,
otherwise you will be always confused by the syntax.
There are many, many, many resources (thanks to Tim!)
I will try to give you a simple overview of joins but please get more
confortable with them!
In relational databases the information is(should!) logically
separated into entities which are no more no less represented by tables,
yes the tables you well know(mathematically speaking they are known as
[relations] the R in RDBMS).
So in some application,like yours, you could have the need to deal
with [books], [authors], [publishers].
These, as said, are the so called entities when we speak from an
abstract-logical point of view,
but eventually turn to be the tables in your database.
So you have a table with all the books, a table with all the authors,
and a table with the publishers.
How could we relate each book with his author?
One way is to have extra information on each book (1 book=1 record/row
in the [books] table),
if an author wrote 100 books you would have 100 times the same
information on each of his books.
another way(better!) is to add the extra information as well, but just
an identifier of the author,
an handle, a key, a UNIQUE value (Social Security Number?) , so that
you have only one place
with the author information (the [author] table) which is also great
for maintenance!
Imagine updating an information about the author on 100 rows of the
[books] table,
and update the same information just in one row of the [authors] table.
I think you can imagine also that the UNIQUE value you add to each
book which identifies the book author,
will be present in the [authors] table to be able to identify the author.
Until now we are just speaking about logic and you could do an
excercise with pen and paper,
drawing a line from the AUTHOR UNIQUE ID from the [books] table to
the AUTHOR UNIQUE ID from the [authors] table.
So you could easily find the author of each book by following the line
the links the two rows/records,
on the left you have the books and on the right you have the authors.
Reading from left to right, for instance, you would be able now to
read consequently the book title and the book author name.
Sit back, the JOIN is the line you have just draw.
It is the connection between two tables to be able to have on just one
row all the information that are split into two(or more) parts/table.
The ON clause that you find in the JOIN syntax is the place where you
specify
ON [books].AUTHOR UNIQUE ID = [authors].AUTHOR UNIQUE ID
( by the way the ID that points to the table with all the informations
is also knows as FOREIGN KEY, in this case the left operand)
The resulting table is a table that have each row like a concatenation
of two rows related from the two different tables.
The WHERE clause is used to FILTER, not to connect the two tables!!
After you connect(join) the two tables you could want to see only
certain rows, ok now you use the WHERE.
Forget about CONCAT/CONCAT_WS this is a string function, and is not
related to JOINS.
Please, let me know if this was useful to you.
Claudio Nanni
2009/2/22 PJ af.gour...@videotron.ca mailto:af.gour...@videotron.ca
I have been searching and searching for a clear and logical
explanation
of JOINs and have found nothing that can be reasonably understood.
Perhaps I am dense or from another
Re: left joins concat
The 'AS' keyword for tables is just to give a table an alias by which
you can then use it in the rest of the query. In your case, when you
say 'book as b' in your query, it means that you can use b in places
where you need to refer to the book table. eg. 'b.id' refers to the
'id' field of the 'book' table. It is good practice to always use an
alias for a table and refer to the table by that alias anywhere in the
query. It makes your query easier to read and understand.
As a side-effect (which you should probably not focus on just yet ;) )
it means you can use this construction to join a table on itself and
use aliasses in the rest of the query to uniquely identify the table
you are referring to.
The same idea goes for the as keyword on field names. You can use
those aliasses to refer to the column in the gorup by, order by and
having clauses of your query. It is mostly used to display a proper
column name though. In your example, the concat_ws expresion has an
alias so in the queries result this name will be used instead of the
expression used to select.
A propos: The problem with your first query was that you were using
natural joins. Natural joins match up all columns that have the same
name in both tables automatically. I consider it bad practice to use
it as it might have unexpected results, especially when you alter
tables later on.
I recommend you to read up on your SQL knowledge though, as these are
very basic problems and it is imperative to have a good understanding
of SQL to be able to work with (m)any relational databases. I haven't
looked at their specific SQL course, but w3schools generally has very
good basic introductions: http://www.w3schools.com/sql/
Have fun!
Walter
OlinData: Professional services for MySQL
Support * Consulting * Administration
http://www.olindata.com
On Sun, Feb 22, 2009 at 4:15 PM, PJ af.gour...@videotron.ca wrote:
Gentlemen,
and all those who care,
THE PROBLEM SEEMS TO BE SOLVED (for now, NEXT is HOW TO QUERY(SELECT)
MULTIPLE AUTHORS AND DISPLAY THAT and then HOW TO DEAL WITH MULTIPLE
CATEGORIES (heh... heh... heh ;-) :
SELECT b.title, b.sub_title, b.descr, b.comment, b.bk_cover,
b.copyright, b.ISBN, c.publisher,
CONCAT_WS(' ', first_name, last_name) AS Author
FROM book AS b
LEFT JOIN book_author AS ab ON b.id = ab.bookID
LEFT JOIN author AS a ON ab.authID=a.id
LEFT JOIN book_publisher AS abc ON b.id = abc.bookID
LEFT JOIN publishers AS c ON abc.publishers_id = c.id
ORDER BY title ASC
This works except I don't fully understand how the CONCAT AS Author
works, nor the AS ab and AS abc.
Does the order of ab and abc matter? Are they related... I think I got
this working by pure experimentation in trying all possible
combinations... kind of primitive, but it seems to work... long hours
and loss of sleep... etc...
And then there is the warning message at the bottom of the displayed web
page:
*Warning*: Unknown: Your script possibly relies on a session side-effect
which existed until PHP 4.2.3. Please be advised that the session
extension does not consider global variables as a source of data, unless
register_globals is enabled. You can disable this functionality and this
warning by setting session.bug_compat_42 or session.bug_compat_warn to
off, respectively. in *Unknown* on line *0
*I guess I'll have to research it on the web... :-)*
*
Claudio Nanni wrote:
Hi Phil,
you seem quite a bit confused!
I would not step to use joins before understanding the 'simple' logic
behind,
otherwise you will be always confused by the syntax.
There are many, many, many resources (thanks to Tim!)
I will try to give you a simple overview of joins but please get more
confortable with them!
In relational databases the information is(should!) logically
separated into entities which are no more no less represented by tables,
yes the tables you well know(mathematically speaking they are known as
[relations] the R in RDBMS).
So in some application,like yours, you could have the need to deal
with [books], [authors], [publishers].
These, as said, are the so called entities when we speak from an
abstract-logical point of view,
but eventually turn to be the tables in your database.
So you have a table with all the books, a table with all the authors,
and a table with the publishers.
How could we relate each book with his author?
One way is to have extra information on each book (1 book=1 record/row
in the [books] table),
if an author wrote 100 books you would have 100 times the same
information on each of his books.
another way(better!) is to add the extra information as well, but just
an identifier of the author,
an handle, a key, a UNIQUE value (Social Security Number?) , so that
you have only one place
with the author information (the [author] table) which is also great
for maintenance!
Imagine updating an information about the author on 100 rows of the
[books] table,
and update the same information just in one
Re: left joins concat
Thank you gentlemen.
Questions /or comments annotated below:
Walter Heck wrote:
The 'AS' keyword for tables is just to give a table an alias by which
you can then use it in the rest of the query. In your case, when you
say 'book as b' in your query, it means that you can use b in places
where you need to refer to the book table. eg. 'b.id' refers to the
'id' field of the 'book' table. It is good practice to always use an
alias for a table and refer to the table by that alias anywhere in the
query. It makes your query easier to read and understand.
As a side-effect (which you should probably not focus on just yet ;) )
it means you can use this construction to join a table on itself and
use aliasses in the rest of the query to uniquely identify the table
you are referring to.
I do want to know, so I'll look more into it. :)
The same idea goes for the as keyword on field names. You can use
those aliasses to refer to the column in the gorup by, order by and
having clauses of your query. It is mostly used to display a proper
column name though. In your example, the concat_ws expresion has an
alias so in the queries result this name will be used instead of the
expression used to select.
A propos: The problem with your first query was that you were using
natural joins. Natural joins match up all columns that have the same
name in both tables automatically. I consider it bad practice to use
it as it might have unexpected results, especially when you alter
tables later on.
When I had my flash of enlightenment came when I noticed that in my many
concatenations I had used and posted the NATURAL join (which = NATURAL
LEFT join); with a little tweaking I got it to work.
But I still don't understand if aliasing AS a is any different from
aliasing AS ab (or, for that matter, if I substituted a number for the
table. I haven't experimented yet, but is there a difference between
using a or ab, cd, abc and just plain t1, t2, t3 or is this the same:
one two three. It may seem naive to be asking such questions, but they
are not readily answered in the tutorials.
I recommend you to read up on your SQL knowledge though, as these are
very basic problems and it is imperative to have a good understanding
of SQL to be able to work with (m)any relational databases. I haven't
looked at their specific SQL course, but w3schools generally has very
good basic introductions: http://www.w3schools.com/sql/
I have consulted w3shools and do find that they are not clear on a lot
of details... I may be picky about the small details but it is they that
make the whole possible.
Have fun!
Walter
OlinData: Professional services for MySQL
Support * Consulting * Administration
http://www.olindata.com
On Sun, Feb 22, 2009 at 4:15 PM, PJ af.gour...@videotron.ca wrote:
Gentlemen,
and all those who care,
THE PROBLEM SEEMS TO BE SOLVED (for now, NEXT is HOW TO QUERY(SELECT)
MULTIPLE AUTHORS AND DISPLAY THAT and then HOW TO DEAL WITH MULTIPLE
CATEGORIES (heh... heh... heh ;-) :
SELECT b.title, b.sub_title, b.descr, b.comment, b.bk_cover,
b.copyright, b.ISBN, c.publisher,
CONCAT_WS(' ', first_name, last_name) AS Author
FROM book AS b
LEFT JOIN book_author AS ab ON b.id = ab.bookID
LEFT JOIN author AS a ON ab.authID=a.id
LEFT JOIN book_publisher AS abc ON b.id = abc.bookID
LEFT JOIN publishers AS c ON abc.publishers_id = c.id
ORDER BY title ASC
This works except I don't fully understand how the CONCAT AS Author
works, nor the AS ab and AS abc.
Does the order of ab and abc matter? Are they related... I think I got
this working by pure experimentation in trying all possible
combinations... kind of primitive, but it seems to work... long hours
and loss of sleep... etc...
And then there is the warning message at the bottom of the displayed web
page:
*Warning*: Unknown: Your script possibly relies on a session side-effect
which existed until PHP 4.2.3. Please be advised that the session
extension does not consider global variables as a source of data, unless
register_globals is enabled. You can disable this functionality and this
warning by setting session.bug_compat_42 or session.bug_compat_warn to
off, respectively. in *Unknown* on line *0
*I guess I'll have to research it on the web... :-)*
*
Claudio Nanni wrote:
Hi Phil,
you seem quite a bit confused!
I would not step to use joins before understanding the 'simple' logic
behind,
otherwise you will be always confused by the syntax.
There are many, many, many resources (thanks to Tim!)
I will try to give you a simple overview of joins but please get more
confortable with them!
In relational databases the information is(should!) logically
separated into entities which are no more no less represented by tables,
yes the tables you well know(mathematically speaking they are known as
[relations] the R in RDBMS).
So in some application,like yours, you could have the need to deal
with [books
left joins concat
I have been searching and searching for a clear and logical explanation
of JOINs and have found nothing that can be reasonably understood.
Perhaps I am dense or from another planet, but nothing seems to fall
into place.
I need to display all the books (with their respective authors and
publishers) -
the tables are book, author, publisher and book_author, book_publisher
for linking many to many books-authors and books-publishers.
Here is what I have (and it gives me rather confusing results:
SELECT b.title, b.sub_title, b.descr, b.comment, b.bk_cover,
b.copyright, b.ISBN, b.sellers, c.publisher,
CONCAT_WS(' ', first_name, last_name) AS Author
FROM book AS b, publishers AS c
NATURAL JOIN book_author AS ab
NATURAL JOIN author AS a
NATURAL JOIN book_publisher AS d
WHERE d.bookID = b.id
ORDER BY title ASC
First, I see what CONCAT_WS does (more or less) but I cannot grasp the
logic related to the author and book_author tables.
Second, I don't understand the logic of the linking tables since the
relationships seem to have to be stated outside the tables... (i.e. the
foreign key reference is in the table but seems to be necessary in a
WHERE clause as well ???
And lastly, I don't understand what conditions (ON or WHERE clauses)
need to be included to get the books matched up to their respective
authors and publishers.
One link that I have been trying to fathom is
http://www.java2s.com/Tutorial/MySQL/0100__Table-Join/Catalog0100__Table-Join.htm
but it is not very helpful in any explanations...
I sure would like to hear some clear explanations...
TIA
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Conditional Joins
Let's say I have the following tables: Plates table +++-+ | id | Name | Description | +++-+ | 1 | Paper | Blah| | 2 | Plastic| Blah| | 3 | China | Blah| | 4 | Glass | Blah| +++-+ Cups table +++-+ | id | Type | Description | +++-+ | 1 | Paper | Blah| | 2 | Mug| Blah| | 3 | Coffee | Blah| | 4 | Glass | Blah| +++-+ Flatware table +++-+ | id | Form | Description | +++-+ | 1 | Spork | Blah| | 2 | Plastic| Blah| | 3 | Antique| Blah| | 4 | Tin| Blah| +++-+ Inventory table ++++---+ | id | ItemType | ItemId | Owned | ++++---+ | 1 | PLATES | 2 | 17| | 2 | CUPS | 4 | 3 | | 3 | FLATWARE | 3 | 6 | | 4 | CUPS | 3 | 9 | | 5 | CUPS | 1 | 7 | | 6 | FLATWARE | 4 | 12| | 7 | PLATES | 1 | 1 | ++++---+ Is there a way to construct a query so that only the appropriate tables are included as a join? I'm trying to do a conditional (and more elegant) version of the following query: SELECT Inventory.id, CASE Inventory.ItemType WHEN 'PLATES' THEN Plates.Name WHEN 'CUPS' THEN Cups.Type WHEN 'FLATWARE' THEN Flatware.Form END as ItemName Inventory.ItemType, Inventory.ItemId, Inventory.Owned FROM Inventory LEFT OUTER JOIN Plates ON Inventory.ItemType = 'Plates' AND Plates.Id = Inventory.ItemId LEFT OUTER JOIN Cups ON Inventory.ItemType = 'Cups' AND Cups.Id = Inventory.ItemId LEFT OUTER JOIN Flatware ON Inventory.ItemType = 'Flatware' AND Flatware.Id = Inventory.ItemId WHERE Inventory.id IN (2, 4, 5) In the query above, the joins on both the Plates and Flatware table are superfluous because those rows are never selected. I'm not sure I can get out of specifying each possible case in column list part of the query but it seems to me like it should be possible to only join those tables that are relevant based on the conditions set in the WHERE clause. Is something like this even possible? thnx, Christoph -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Why are joins between tables in dif db so slow?
I have two indexed MyISAM tables, each in a separate database. If I do a left join on the two tables, it takes 2 minutes to return the 5,000 rows. The same join on similar tables in the same database would take 5-10 seconds. Both databases are on the same drive. So why is it 10x slower when the other table is in another database? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why are joins between tables in dif db so slow?
Both times seem a bit long, even if you database has millions of rows. Can you post and explain of your query? That they are in different databases should have minimal effect on your query. Brent On Oct 3, 2008, at 12:14 PM, mos wrote: I have two indexed MyISAM tables, each in a separate database. If I do a left join on the two tables, it takes 2 minutes to return the 5,000 rows. The same join on similar tables in the same database would take 5-10 seconds. Both databases are on the same drive. So why is it 10x slower when the other table is in another database? TIA Mike -- 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: [PHP] joins issues again
On Tue, Apr 8, 2008 at 7:28 AM, Steven Macintyre
[EMAIL PROTECTED] wrote:
Hi all,
I have the following SQL statement;
SELECT count( salesID ) AS count, branch_name, company_name, branch.branchID
FROM sales
LEFT JOIN IGuser ON sales.IGuid = IGuser.IGuid
LEFT JOIN branch ON IGuser.branchID = branch.branchID
LEFT JOIN company ON branch.companyID = '{$companyID}'
WHERE maincompanyid = '{$mcid}'
GROUP BY branch.branchID
ORDER BY branch_name ASC
However, i do not want those join records to be appended, only to return the
count of records from sales.
Can someone assist me with this? I have tried differance variants of joins
and none of the results are correct.
Sales tbl doesnt have the companyID, nor does IGuser
Steven,
Since this isn't a PHP-specific question, you'll probably receive
better responses on either the MySQL list (you didn't mention which
database system you're using, but I'll blindly and ignorantly assume
that's it), or at least the PHP-DB list. I'm CC'ing both of those for
you.
--
/Daniel P. Brown
Ask me about:
Dedicated servers starting @ $59.99/mo., VPS starting @ $19.99/mo.,
and shared hosting starting @ $2.50/mo.
Unmanaged, managed, and fully-managed!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: performance of heterogeneous joins
Cool it's good to know thank you. On 25/01/2008, Jay Pipes [EMAIL PROTECTED] wrote: Nope, no difference, AFAIK. Alex K wrote: Any ideas pertaining this newbie question? Thank you so much, Hi Guys, Is there a performance hit when joining across multiple databases as opposed to joining multiples tables in one database? Suppose the same tables are available across all databases. Thank you, Alex -- 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 of heterogeneous joins
Any ideas pertaining this newbie question? Thank you so much, Hi Guys, Is there a performance hit when joining across multiple databases as opposed to joining multiples tables in one database? Suppose the same tables are available across all databases. Thank you, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: performance of heterogeneous joins
Nope, no difference, AFAIK. Alex K wrote: Any ideas pertaining this newbie question? Thank you so much, Hi Guys, Is there a performance hit when joining across multiple databases as opposed to joining multiples tables in one database? Suppose the same tables are available across all databases. Thank you, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with MySQL Query (2 Outer joins)
Hi, I need some urgent for sql query.. It will be great if someone could help me.. I have ARTICLE, FAVORITE_ARTICLES, RATING Tables apart from other table USER, CHANNEL, CATEGORY etc ARTICLE table stores a user's article, FAVORITE_ARTICLES will store a user's favorite articles, and rating table ratings on a article.. I have a requirement to fetch All article by descending order of their ratings and I have to find favorite articles count for each articles as well. My SQL works fine if I put outer join with Article and Favorite Articles table or between Article and Ratings table.. but it doesn't work if put Outer with both Favorite Articles and Rating Table How can resolve this issue? How can I join use Outer Join with Two Tables in a SQL... If it is not possible then What is the alternate ways? I tried with below SQL SELECT a.CHANNEL_ID, ch.CHANNEL_NAME, a.CATEGORY_ID, ca.CATEGORY_NAME, a.LAST_UPDATED_DATE, a.ARTICLE_ID, a.USER_ID, u.USER_NAME as WRITER_NAME, a.TITLE, a.DEDICATED, a.ARTICLE_LENGTH, a.ARTICLE_DESC, a.READ_COUNT, count(fa.ARTICLE_ID) as myFavArticlesCnt, avg(rating), COUNT(r.ARTICLE_ID) AS RATINGS FROM article a left join favorite_articles fa using (article_id), ARTICLE b left join RATING r using (article_id), USER u, CHANNEL ch,CATEGORY ca WHERE a.CHANNEL_ID = '1' AND a.CATEGORY_ID = '1' AND a.CHANNEL_ID = ch.CHANNEL_ID AND a.CATEGORY_ID = ca.CATEGORY_ID and a.USER_ID = u.USER_ID AND a.PUBLISH_FLAG = '1' AND a.IS_DELETE = '0' AND A.last_updated_date = Date_SUB(Now(), INTERVAL 30 DAY) GROUP BY a.ARTICLE_ID ORDER BY RATINGS DESC Thanks Raghu
performance of heterogeneous joins
Hi Guys, Is there a performance hit when joining across multiple databases as opposed to joining multiples tables in one database? Suppose the same tables are available across all databases. Thank you, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Assistance with query and joins
Hi all, I have a query that is not quite producing what I expected: select n.nid, n.title, DATE_FORMAT(FROM_UNIXTIME(n.created), '%c/%e/%Y') as created, c.field_product_price_value as price, d.name, t.tid, v.value AS vote_average from node n left join node_revisions r on r.vid = n.vid left join content_type_galleria_product c on c.nid = n.nid left join term_node t on t.nid = n.nid left join term_data d on d.tid = t.tid right join votingapi_cache v on v.content_id = n.nid WHERE n.type = 'galleria_product' AND v.function = 'average' AND t.tid = 22; This query produces one result: nid 1391 title The Omnivore's Dilemma created 12/12/2007 price 21.99 nameBooks, DVDs tid 22 vote_average80 However, I was expecting two rows to be returned. The issue is that the second record does not have an entry in the votingapi_cache table. I used a right join on that table, expecting to get the record back with a NULL in the vote_average column, but that didn't happen. How can I get both records to be found? -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Cross database joins
Hi, I found one thread on this that included some people's opinions, but I haven't been able to find anyone who has actually done some performance testing to see if there is a cost and what that cost is to doing cross database joins. I do tend to want to keep everything in one DB, but it gets hard when you have databases that do cross over at times, but rarely. Of course I am being somewhat lazy in doing this post, but only because I think someone here *must* have already done some testing between cross db joins and inside db joins. Another point of interest is if DBI actually opens another connection or not. I saw one mention of a worry about that, but as I understand it, you can refer to any table in any database from any mysql connection that has permission to access that DB and table, so you should be able to access any DB from any initial connection. Thanks, Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Left outer joins, where clause and table_names
Hi, I have the following sql request: SELECT projects.`id` AS t0_r0, projects.`name` AS t0_r1, projects.`abbreviated_name` AS t0_r2, projects.`producer` AS t0_r3, projects.`tel_1` AS t0_r4, projects.`tel_2` AS t0_r5, projects.`recital` AS t0_r6, projects.`completed_flag` AS t0_r7, projects.`completed_at` AS t0_r8, projects.`created_at` AS t0_r9, projects.`update_at` AS t0_r10, materials.`id` AS t1_r0, materials.`created_at` AS t1_r1, materials.`work_id` AS t1_r2, materials.`work_type` AS t1_r3, materials.`comment` AS t1_r4, materials.`user_name` AS t1_r5, materials.`user_id` AS t1_r6, materials.`material_file_id` AS t1_r7, materials.`tag_id` AS t1_r8, tags.`id` AS t2_r0, tags.`name` AS t2_r1, tags.`project_id` AS t2_r2, uploaded_files.`id` AS t3_r0, uploaded_files.`size` AS t3_r1, uploaded_files.`content_type` AS t3_r2, uploaded_files.`filename` AS t3_r3, uploaded_files.`height` AS t3_r4, uploaded_files.`width` AS t3_r5, uploaded_files.`parent_id` AS t3_r6, uploaded_files.`thumbnail` AS t3_r7, forums.`id` AS t4_r0, forums.`name` AS t4_r1, forums.`description` AS t4_r2, forums.`topics_count` AS t4_r3, forums.`posts_count` AS t4_r4, forums.`position` AS t4_r5, forums.`description_html` AS t4_r6, forums.`work_id` AS t4_r7, forums.`work_type` AS t4_r8, posts.`id` AS t5_r0, posts.`user_id` AS t5_r1, posts.`topic_id` AS t5_r2, posts.`body` AS t5_r3, posts.`created_at` AS t5_r4, posts.`updated_at` AS t5_r5, posts.`forum_id` AS t5_r6, posts.`body_html` AS t5_r7, posts.`material_file_id` AS t5_r8, topics.`id` AS t6_r0, topics.`forum_id` AS t6_r1, topics.`user_id` AS t6_r2, topics.`subject` AS t6_r3, topics.`created_at` AS t6_r4, topics.`updated_at` AS t6_r5, topics.`hits` AS t6_r6, topics.`sticky` AS t6_r7, topics.`posts_count` AS t6_r8, topics.`replied_at` AS t6_r9, topics.`replied_by` AS t6_r10, topics.`last_post_id` AS t6_r11, topics.`tag_id` AS t6_r12, tags_topics.`id` AS t7_r0, tags_topics.`name` AS t7_r1, tags_topics.`project_id` AS t7_r2, readerships.`id` AS t8_r0, readerships.`user_id` AS t8_r1, readerships.`topic_id` AS t8_r2, readerships.`read` AS t8_r3, roles.`id` AS t9_r0, roles.`name` AS t9_r1, roles.`authorizable_type` AS t9_r2, roles.`authorizable_id` AS t9_r3, roles.`created_at` AS t9_r4, roles.`updated_at` AS t9_r5, users.`id` AS t10_r0, users.`login` AS t10_r1, users.`crypted_password` AS t10_r2, users.`salt` AS t10_r3, users.`family_name` AS t10_r4, users.`first_name` AS t10_r5, users.`affiliation` AS t10_r6, users.`tel` AS t10_r7, users.`email` AS t10_r8, users.`note` AS t10_r9, users.`active` AS t10_r10, users.`days_display_unit` AS t10_r11, users.`user_icon_id` AS t10_r12, users.`remember_token` AS t10_r13, users.`remember_token_expires_at` AS t10_r14, users.`position` AS t10_r15, users.`posts_count` AS t10_r16, users.`last_seen_at` AS t10_r17, users.`created_at` AS t10_r18, users.`updated_at` AS t10_r19, titles.`id` AS t11_r0, titles.`project_id` AS t11_r1, titles.`name` AS t11_r2, titles.`oa_date` AS t11_r3, titles.`oa_hour` AS t11_r4, titles.`oa_minute` AS t11_r5, titles.`slip_number` AS t11_r6, titles.`note` AS t11_r7, titles.`director` AS t11_r8, titles.`director_tel_1` AS t11_r9, titles.`director_tel_2` AS t11_r10, titles.`in_charge` AS t11_r11, titles.`in_charge_tel_1` AS t11_r12, titles.`in_charge_tel_2` AS t11_r13, titles.`recital` AS t11_r14, titles.`completed_flag` AS t11_r15, titles.`completed_at` AS t11_r16, titles.`position` AS t11_r17, titles.`created_at` AS t11_r18, titles.`updated_at` AS t11_r19, materials_titles.`id` AS t12_r0, materials_titles.`created_at` AS t12_r1, materials_titles.`work_id` AS t12_r2, materials_titles.`work_type` AS t12_r3, materials_titles.`comment` AS t12_r4, materials_titles.`user_name` AS t12_r5, materials_titles.`user_id` AS t12_r6, materials_titles.`material_file_id` AS t12_r7, materials_titles.`tag_id` AS t12_r8, tags_materials.`id` AS t13_r0, tags_materials.`name` AS t13_r1, tags_materials.`project_id` AS t13_r2, material_files_materials.`id` AS t14_r0, material_files_materials.`size` AS t14_r1, material_files_materials.`content_type` AS t14_r2, material_files_materials.`filename` AS t14_r3, material_files_materials.`height` AS t14_r4, material_files_materials.`width` AS t14_r5, material_files_materials.`parent_id` AS t14_r6, material_files_materials.`thumbnail` AS t14_r7, forums_titles.`id` AS t15_r0, forums_titles.`name` AS t15_r1, forums_titles.`description` AS t15_r2, forums_titles.`topics_count` AS t15_r3, forums_titles.`posts_count` AS t15_r4, forums_titles.`position` AS t15_r5, forums_titles.`description_html` AS t15_r6, forums_titles.`work_id` AS t15_r7, forums_titles.`work_type` AS t15_r8, posts_forums.`id` AS t16_r0, posts_forums.`user_id` AS t16_r1, posts_forums.`topic_id` AS t16_r2, posts_forums.`body` AS t16_r3, posts_forums.`created_at` AS t16_r4, posts_forums.`updated_at` AS t16_r5, posts_forums.`forum_id` AS t16_r6, posts_forums.`body_html` AS t16_r7, posts_forums.`material_file_id` AS t16_r8, topics_posts.`id` AS t17_r0,
Re: Left outer joins, where clause and table_names
tom wang wrote: Hi, I have the following sql request: [snipped, for the sake of the children] As you can see I have two left outerjoins involving the readerships table: LEFT OUTER JOIN readerships ON readerships.topic_id = topics.id and LEFT OUTER JOIN readerships readerships_topics ON readerships_topics.topic_id = topics_posts.id I'll take your word for it! and I have a condition on both of those tables in my where clause: WHERE (readerships.read != '1' OR readerships_topics.read != '1') Not that I'm going to pretend to understand the goal of the query, but couldn't you do: LEFT OUTER JOIN readerships ON readerships.topic_id = topics.id AND readerships.read != '1' AND LEFT OUTER JOIN readerships readerships_topics ON readerships_topics.topic_id = topics_posts.id AND readerships_topics.read != '1' Now what I was wondering is: is there a way to write something like : WHERE *.read != '1' Which would match all tables with a read column? No, you cannott use the asterisk selector in a WHERE condition. If not is there a way to match all readerships table in my where clause? so for example WHERE readerships.read != '1' where readerships.read would match both readerships and readerships_topic? readerships.read matches readerships.read, nothing more. I can't just the left outer join part as it's autogenerated but I can change the WHERE clause Autogenerated by what, exactly? Does it run at all the way it is? That is quite a monster SELECT statement. In fact, if it doesn't run now, perhaps all it requires is a good jolt of electricity. I'm not good a sql syntax, but I would love to learn Are you reasonably certain that your SELECT requires all that? Could your application maybe be adjusted so that you could break that up a little? brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
OT: K.I.S.S.? Re: Left outer joins, where clause and table_names
tom wang schrieb: Hi, I have the following sql request: SELECT projects.`id` AS t0_r0, projects.`name` AS [..endless sql..] Hi Tom, did you understand that query (in lets say 3 months) if you need to fix a bug? If not it maybe better to simplify that. regards -ralf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE : Re: Left outer joins, where clause and table_names
Hi,
First, sorry, I kind of messed of with copy and
pasting (it's been a long day) and forgot to strip all
the useless part (for the sake of explaining my
problem) between select and from...
SELECT * FROM projects LEFT OUTER JOIN forums ON
forums.work_id = projects.id AND forums.work_type =
'Project' LEFT OUTER JOIN posts ON posts.forum_id =
forums.id LEFT OUTER JOIN topics ON topics.id =
posts.topic_id LEFT OUTER JOIN readerships ON
readerships.topic_id = topics.id LEFT OUTER JOIN
titles ON titles.project_id = projects.id LEFT OUTER
JOIN forums forums_titles ON forums_titles.work_id =
titles.id AND forums_titles.work_type = 'Title' LEFT
OUTER JOIN posts posts_forums ON posts_forums.forum_id
= forums_titles.id LEFT OUTER JOIN topics topics_posts
ON topics_posts.id = posts_forums.topic_id LEFT OUTER
JOIN readerships readerships_topics ON
readerships_topics.topic_id = topics_posts.id LEFT
OUTER JOIN forums forums_projects ON
forums_projects.work_id = projects.id AND
forums_projects.work_type = 'Project' LEFT OUTER JOIN
titles titles_projects ON titles_projects.project_id =
projects.id WHERE ((readerships.read != '1' OR
readerships_topics.read != '1'))
I guess I should have given more background
I can't just the left outer join part as it's
autogenerated but I can change the WHERE clause
Autogenerated by what, exactly? Does it run at all
the way it is? That
is quite a monster SELECT statement. In fact, if it
doesn't run now,
perhaps all it requires is a good jolt of
electricity.
I'm using ActiveRecord with ruby on rails, which
generated this query with from:
Project.find(:all,{:conditions=[(readerships.read !=
'1' OR readerships_topics.read != '1')], :order=nil,
:include={:forum={:posts={:topic=:readerships}}},
{:titles={:forum={:posts={:topic=:readerships}
I'm not good a sql syntax, but I would love to
learn
Are you reasonably certain that your SELECT requires
all that? Could
your application maybe be adjusted so that you could
break that up a little?
The problem I have is that I have a projects that is
linked to a forum table (itself linked with posts and
topics) and a titles table that is also linked to a
forum table (etc...)
I need to know which titles (don't ask me for the
name, that's the what happen when a manager designs
the database schema based on what the customer wants
:-( ) and which projects have posts that have been
read by the user.
Thanks
Thomas
_
Ne gardez plus qu'une seule adresse mail ! Copiez vos mails vers Yahoo! Mail
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RE : Re: Left outer joins, where clause and table_names
tom wang wrote:
Hi,
First, sorry, I kind of messed of with copy and
pasting (it's been a long day) and forgot to strip all
the useless part (for the sake of explaining my
problem) between select and from...
SELECT * FROM projects LEFT OUTER JOIN forums ON
forums.work_id = projects.id AND forums.work_type =
'Project' LEFT OUTER JOIN posts ON posts.forum_id =
forums.id LEFT OUTER JOIN topics ON topics.id =
posts.topic_id LEFT OUTER JOIN readerships ON
readerships.topic_id = topics.id LEFT OUTER JOIN
titles ON titles.project_id = projects.id LEFT OUTER
JOIN forums forums_titles ON forums_titles.work_id =
titles.id AND forums_titles.work_type = 'Title' LEFT
OUTER JOIN posts posts_forums ON posts_forums.forum_id
= forums_titles.id LEFT OUTER JOIN topics topics_posts
ON topics_posts.id = posts_forums.topic_id LEFT OUTER
JOIN readerships readerships_topics ON
readerships_topics.topic_id = topics_posts.id LEFT
OUTER JOIN forums forums_projects ON
forums_projects.work_id = projects.id AND
forums_projects.work_type = 'Project' LEFT OUTER JOIN
titles titles_projects ON titles_projects.project_id =
projects.id WHERE ((readerships.read != '1' OR
readerships_topics.read != '1'))
That's still, um ... a bit difficult to follow. First rule of SQL: line
breaks are allowed. Especially when posting queries in an email.
I'm using ActiveRecord with ruby on rails, which
generated this query with from:
Project.find(:all,{:conditions=[(readerships.read !=
'1' OR readerships_topics.read != '1')], :order=nil,
:include={:forum={:posts={:topic=:readerships}}},
{:titles={:forum={:posts={:topic=:readerships}
I've been waiting for a sign to show me that giving RoR a pass was the
correct thing to do. Now, i think i know ;-)
The problem I have is that I have a projects that is
linked to a forum table (itself linked with posts and
topics) and a titles table that is also linked to a
forum table (etc...)
I need to know which titles (don't ask me for the
name, that's the what happen when a manager designs
the database schema based on what the customer wants
:-( ) and which projects have posts that have been
read by the user.
The first thing that (i think) i see is that you're not selecting
anything at all from titles:
SELECT * FROM projects
I'm not sure i understand your schema enough to help. Are you sure you
need a separate titles table, for instance? And you shouldn't be able to
use the alias readerships_topics in the WHERE clause. Come to think of
it, your WHERE clause makes no sense at all because you're selecting
from projects. If you want to test readerships.read it should go in the
ON clause of that particular join:
LEFT OUTER JOIN readerships
ON readerships.topic_id = topics.id
AND readerships.read != '1'
Though i'm unsure whether or not the test on topics.id would work here,
either.
brian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE : Re: RE : Re: Left outer joins, where clause and table_names
--- [EMAIL PROTECTED] a écrit :
tom wang wrote:
Hi,
First, sorry, I kind of messed of with copy and
pasting (it's been a long day) and forgot to strip
all
the useless part (for the sake of explaining my
problem) between select and from...
SELECT * FROM projects LEFT OUTER JOIN forums ON
forums.work_id = projects.id AND forums.work_type
=
'Project' LEFT OUTER JOIN posts ON posts.forum_id
=
forums.id LEFT OUTER JOIN topics ON topics.id =
posts.topic_id LEFT OUTER JOIN readerships ON
readerships.topic_id = topics.id LEFT OUTER JOIN
titles ON titles.project_id = projects.id LEFT
OUTER
JOIN forums forums_titles ON forums_titles.work_id
=
titles.id AND forums_titles.work_type = 'Title'
LEFT
OUTER JOIN posts posts_forums ON
posts_forums.forum_id
= forums_titles.id LEFT OUTER JOIN topics
topics_posts
ON topics_posts.id = posts_forums.topic_id LEFT
OUTER
JOIN readerships readerships_topics ON
readerships_topics.topic_id = topics_posts.id LEFT
OUTER JOIN forums forums_projects ON
forums_projects.work_id = projects.id AND
forums_projects.work_type = 'Project' LEFT OUTER
JOIN
titles titles_projects ON
titles_projects.project_id =
projects.id WHERE ((readerships.read != '1' OR
readerships_topics.read != '1'))
That's still, um ... a bit difficult to follow.
First rule of SQL: line
breaks are allowed. Especially when posting queries
in an email.
Sorry, I should have thought of that
I'm using ActiveRecord with ruby on rails, which
generated this query with from:
Project.find(:all,{:conditions=[(readerships.read
!=
'1' OR readerships_topics.read != '1')],
:order=nil,
:include={:forum={:posts={:topic=:readerships}}},
{:titles={:forum={:posts={:topic=:readerships}
I've been waiting for a sign to show me that giving
RoR a pass was the
correct thing to do. Now, i think i know ;-)
I guess someone better than me with mysql and rails
would have done something much cleaner though But
rails make simple things simpler and things out of the
ordinary harder.
The problem I have is that I have a projects that
is
linked to a forum table (itself linked with posts
and
topics) and a titles table that is also linked to
a
forum table (etc...)
I need to know which titles (don't ask me for the
name, that's the what happen when a manager
designs
the database schema based on what the customer
wants
:-( ) and which projects have posts that have
been
read by the user.
The first thing that (i think) i see is that you're
not selecting
anything at all from titles:
SELECT * FROM projects
I'm not sure i understand your schema enough to
help. Are you sure you
need a separate titles table, for instance?
Well the name is rather misleading... the database
schema was created by my japanese coworker and is not
at all used for titles but used to store different
parts of the project (when I asked why it was
named title, the answer was that it's client
requirement ;-) )
it seems that the titles table is pulled out through
this in the LEFT OUTER JOIN
titles titles_projects ON titles_projects.project_id
= projects.id
but I guess it would be more correct to put it inside
the from clause...
And you
shouldn't be able to
use the alias readerships_topics in the WHERE
clause. Come to think of
it, your WHERE clause makes no sense at all because
you're selecting
from projects. If you want to test readerships.read
it should go in the
ON clause of that particular join:
LEFT OUTER JOIN readerships
ON readerships.topic_id = topics.id
AND readerships.read != '1'
Though i'm unsure whether or not the test on
topics.id would work here,
either.
I guess I need to find a way to ask rails to change my
left outer join
I also have another question more or less related
How could I check that I either have
readerships.read = '0' AND readerships.user_id = '5'
or
no record with readerships.user_id = '5'
?
Thanks for your help
Thomas
_
Ne gardez plus qu'une seule adresse mail ! Copiez vos mails vers Yahoo! Mail
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multi Lookup Table Joins
I often find that I have more than one column in a tale that is an integer ID used to join to a lookup table. If there is only one Join to do it is to do something like this SELECT t.data, l.group FROM table t JOIN lookuptable l USING (groupID) WHERE whatever however if I need to join more than one that syntax wont work because the second join will be trying to join to the first lookup table no the main table. Is there a way around this or do I need to just do joins using this syntax SELECT x, y, z FROM table t, lookupA la, lookupB lb WHERE t.aID = a.aID AND t.bID = b.bID -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi Lookup Table Joins
Chris W wrote: I often find that I have more than one column in a tale that is an integer ID used to join to a lookup table. If there is only one Join to do it is to do something like this SELECT t.data, l.group FROM table t JOIN lookuptable l USING (groupID) WHERE whatever however if I need to join more than one that syntax wont work because the second join will be trying to join to the first lookup table no the main table. Is there a way around this or do I need to just do joins using this syntax SELECT x, y, z FROM table t, lookupA la, lookupB lb WHERE t.aID = a.aID AND t.bID = b.bID You can use ON clauses and explicitly qualify which table the columns are in. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi Lookup Table Joins
Perhaps you want the ON syntax SELECT x, y, z FROM table t, lookupA a, lookupB b WHERE t.aID = a.aID AND t.bID = b.bID is equivalent to: SELECT x, y, z FROM table t INNER JOIN lookupA a ON t.aID = a.aID INNER JOIN lookupB b ON t.bID = b.bID If you were to do: SELECT x, y, z FROM table t INNER JOIN lookupA a USING(aID) INNER JOIN lookupB b USING(bID) that would be equivalent to SELECT x, y, z FROM table t INNER JOIN lookupA a ON t.aID = a.aID INNER JOIN lookupB b ON a.bID = b.bID On 9/29/07, Chris W [EMAIL PROTECTED] wrote: I often find that I have more than one column in a tale that is an integer ID used to join to a lookup table. If there is only one Join to do it is to do something like this SELECT t.data, l.group FROM table t JOIN lookuptable l USING (groupID) WHERE whatever however if I need to join more than one that syntax wont work because the second join will be trying to join to the first lookup table no the main table. Is there a way around this or do I need to just do joins using this syntax SELECT x, y, z FROM table t, lookupA la, lookupB lb WHERE t.aID = a.aID AND t.bID = b.bID -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Rob Wultsch (480)223-2566 [EMAIL PROTECTED] (email/google im) wultsch (aim) [EMAIL PROTECTED] (msn) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Performance Problems With JOINS - Tunnng required or upgrade hardware?
Hi all, First sorry my bad english :) I having a problem with a large join with 10 tables with 70Gb of text data, some joins executed by index but some others not. I´m work with HP SERVER (Proliant NL-150) a 2 Xeon 2 Duo with 3Gb Ram and RAID 0. When executed to a client with small datasets the retrive is fastest, but when i try with a large dataset client the database down or left a 10 min to execute a query. This is my.cnf [client] port= 3306 socket= /var/lib/mysql/mysql.sock [mysqld] port= 3306 socket= /var/lib/mysql/mysql.sock skip-locking tmp_table_size =256M key_buffer_size = 750M max_allowed_packet = 10M max_connections=400 table_cache = 4000 sort_buffer_size = 100M read_buffer_size = 100M read_rnd_buffer_size = 50M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_type=1 query_cache_size = 256M query_cache_limit=25M join_buffer_size=128M thread_concurrency = 16 log-bin=mysql-bin server-id= 1 innodb_buffer_pool_size = 1512M innodb_additional_mem_pool_size = 100M innodb_thread_concurrency=16 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout and that is a trouble SQL ### SQL 1 ## SELECT NAC.id, NAC.nome assunto, NAC.ordem FROM Noticias N INNER JOIN ( SELECT NC.noticiaId, A.id, A.nome, AC.ordem FROM NoticiasClientes NC INNER JOIN (AssuntosClientes AC, Assuntos A) ON (NC.clienteId = '.$clienteId.' AND NC.clienteId = AC.clienteId AND NC.assuntoId = AC.assuntoId AND AC.assuntoId = A.id) )NAC ON (N.dataInsercao = '.$clippingDate.') SQL 2 ### SELECT NT.id, NT.titulo as tituloNoticia, NC.tipo tipoNoticia, NI.id Imagem, VI.nome AS veiculoNome, VI.id veiculoId, NC.impactoId FROM (SELECT NCL.* FROM NoticiasClientes NCL WHERE NCL.assuntoId = '.$filter.' AND NCL.clienteId='.$clienteId.') NC INNER JOIN (Noticias NT, Veiculos VI, (SELECT VCL.veiculoId, VCL.clienteId, VCL.ordem FROM VeiculosClientes VCL WHERE VCL.clienteId='.$clienteId.' ORDER BY VCL.ordem) VC) ON (NT.id = NC.noticiaId AND NT.dataInsercao = '.$clippingDate.' AND VI.tipoVeiculoIdIN (.$tiposVeiculos.) AND VI.id = NT.veiculoId ) LEFT JOIN (ImagemNoticia NI) ON (NI.noticiaId = NC.noticiaId) GROUP BY NC.noticiaId SQL 3 ### SELECT N.id, N.titulo,VCT.id veiculoId, VCT.veiculo, VCT.tipoVeiculo, VCT.ordemVeiculo, NAC.assuntoId, NAC.impactoId, NAC.assunto, NAC.ordemAssunto, IMN.id as imgId FROM (Noticias N INNER JOIN ((SELECT NC.noticiaId, I.id as impactoId, A.nome as assunto, AC.ordem as ordemAssunto, AC.assuntoId FROM NoticiasClientes NC INNER JOIN (AssuntosClientes AC, Assuntos A, Impactos I) ON ( NC.clienteId = '.$clienteId.' AND NC.clienteId = AC.clienteId AND NC.assuntoId = AC.assuntoId AND AC.assuntoId = A.id AND NC.impactoId = I.id)) NAC, (SELECT V.id, V.nome as Veiculo, VC.ordem as ordemVeiculo, TV.nome as tipoVeiculo FROM Veiculos V INNER JOIN (VeiculosClientes VC, TiposVeiculos TV) ON ( VC.clienteId = '.$clienteId.' AND ( TV.id IN (.$tiposVeiculos.)) AND V.id = VC.veiculoId AND V.tipoVeiculoId = TV.id)) VCT) ON (N.id = NAC.noticiaId AND N.veiculoId = VCT.id)) LEFT JOIN
Re: Performance Problems With JOINS - Tunnng required or upgrade hardware?
Hi, Your English is fine :) Your queries don't look too bad. It could be there are no good indexes. Have you tried running EXPLAIN on them? What version of MySQL are you using? You can also try profiling the queries (by hand with SHOW STATUS, or more easily with MySQL Query Profiler) to see what they are doing. I don't know what your operating system is, but if it's Linux or UNIX you can also run vmstat -n 5 while the query runs so you can see what it is doing. (For example, it may be swapping, which is bad). If you need help understanding EXPLAIN, please write back and include the output of SHOW CREATE TABLE for the tables, your query, and the result of EXPLAIN for the query. [ Triadbrasil ] Filipe Tomita wrote: Hi all, First sorry my bad english :) I having a problem with a large join with 10 tables with 70Gb of text data, some joins executed by index but some others not. I´m work with HP SERVER (Proliant NL-150) a 2 Xeon 2 Duo with 3Gb Ram and RAID 0. When executed to a client with small datasets the retrive is fastest, but when i try with a large dataset client the database down or left a 10 min to execute a query. This is my.cnf [client] port= 3306 socket= /var/lib/mysql/mysql.sock [mysqld] port= 3306 socket= /var/lib/mysql/mysql.sock skip-locking tmp_table_size =256M key_buffer_size = 750M max_allowed_packet = 10M max_connections=400 table_cache = 4000 sort_buffer_size = 100M read_buffer_size = 100M read_rnd_buffer_size = 50M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_type=1 query_cache_size = 256M query_cache_limit=25M join_buffer_size=128M thread_concurrency = 16 log-bin=mysql-bin server-id= 1 innodb_buffer_pool_size = 1512M innodb_additional_mem_pool_size = 100M innodb_thread_concurrency=16 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout and that is a trouble SQL ### SQL 1 ## SELECT NAC.id, NAC.nome assunto, NAC.ordem FROM Noticias N INNER JOIN ( SELECT NC.noticiaId, A.id, A.nome, AC.ordem FROM NoticiasClientes NC INNER JOIN (AssuntosClientes AC, Assuntos A) ON (NC.clienteId = '.$clienteId.' AND NC.clienteId = AC.clienteId AND NC.assuntoId = AC.assuntoId AND AC.assuntoId = A.id) )NAC ON (N.dataInsercao = '.$clippingDate.') SQL 2 ### SELECT NT.id, NT.titulo as tituloNoticia, NC.tipo tipoNoticia, NI.id Imagem, VI.nome AS veiculoNome, VI.id veiculoId, NC.impactoId FROM (SELECT NCL.* FROM NoticiasClientes NCL WHERE NCL.assuntoId = '.$filter.' AND NCL.clienteId='.$clienteId.') NC INNER JOIN (Noticias NT, Veiculos VI, (SELECT VCL.veiculoId, VCL.clienteId, VCL.ordem FROM VeiculosClientes VCL WHERE VCL.clienteId='.$clienteId.' ORDER BY VCL.ordem) VC) ON (NT.id = NC.noticiaId AND NT.dataInsercao = '.$clippingDate.' AND VI.tipoVeiculoIdIN (.$tiposVeiculos.) AND VI.id = NT.veiculoId ) LEFT JOIN (ImagemNoticia NI) ON (NI.noticiaId = NC.noticiaId) GROUP BY NC.noticiaId SQL 3 ### SELECT N.id, N.titulo,VCT.id veiculoId, VCT.veiculo, VCT.tipoVeiculo, VCT.ordemVeiculo, NAC.assuntoId, NAC.impactoId, NAC.assunto, NAC.ordemAssunto, IMN.id as imgId FROM (Noticias N INNER JOIN ((SELECT NC.noticiaId, I.id as impactoId, A.nome as assunto, AC.ordem as ordemAssunto, AC.assuntoId FROM NoticiasClientes NC INNER JOIN (AssuntosClientes AC, Assuntos A, Impactos I) ON ( NC.clienteId = '.$clienteId.' AND NC.clienteId = AC.clienteId AND NC.assuntoId = AC.assuntoId AND AC.assuntoId = A.id AND NC.impactoId = I.id)) NAC, (SELECT V.id, V.nome as Veiculo, VC.ordem
How to rewrite SQL query for new MySQL LEFT JOINS
I hope someone can clue me in what a syntax of query that produces the same would look like for MySQL 5.0.12 Old query meant to list most recent message from each thread, e.g. select * from messages left join messages as messages_ on messages.thread = messages_.thread and messages.created messages_.created where messages_.id is null It worked in 4.x but does not work in the new syntax. How should I rewrite it to get the same result? TIA, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to rewrite SQL query for new MySQL LEFT JOINS
It worked in 4.x but does not work in the new syntax. How should I rewrite it to get the same result? OK, that was a lie. It works in 5.x as well. I should learn to describe my problem more accurately as well as RTFM :-( The correct description of the query in question would have been: select * from messages, someothertable left join messages as messages_ on messages.thread = messages_.thread and messages.created messages_.created where messages_.id is null Since there was a comma (inner) join there, the left join applied only to 'someothertable' and not to message table as intended. As I learned, in 5.0.12+, parentheses matter! Duh! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LEFT/RIGHT Joins not working
Many thanks for your input. Looks like the older version of mysql, preceding
5.0 has some problems. I've to upgrade mysql now. :-(
Thanks again.
Mogens Melander [EMAIL PROTECTED] wrote:
Well, trying your example gives me the expected result.
select cust.ssn, disposition.disposition, source.source
from cust LEFT JOIN source ON (cust.source=source.id)
LEFT JOIN disposition ON (cust.disposition=disposition.id);
'123456789', 'dispo2', 'source1'
'123456780', 'dispo1', 'source2'
'123456781', null, null
On Wed, April 4, 2007 00:03, murthy gandikota wrote:
Hi Mike
Thanks for your input. I read the page before and it caused more
confusion.
May be an example would clarify this:
create table `cust` (`ssn` varchar(10), `source` int(3) , `disposition`
int(3));
insert into cust (ssn, source, disposition) values ('123456789', 1, 2);
insert into cust (ssn, source, disposition) values ('123456780', 2, 1);
insert into cust (ssn, source, disposition) values ('123456781', NULL,
NULL);
create table `source` (`id` int(3), `source` varchar(10));
insert into source(id, source) values (1, 'source1');
insert into source(id,source) values (2, 'source2');
create table `disposition` (`id` int(3), `disposition` varchar(10));
insert into disposition (id, disposition) values (1, 'dispo1');
insert into disposition(id,disposition) values (2, 'dispo2');
Now I run the sql:
select cust.ssn, disposition.disposition, source.source from cust LEFT
JOIN source ON (cust.source=source.id) LEFT JOIN disposition ON
(cust.disposition=disposition.id)
+---+-+-+
| ssn | disposition | source |
+---+-+-+
| 123456789 | dispo2 | source1 |
| 123456789 | dispo2 | source1 |
| 123456780 | dispo1 | source2 |
| 123456780 | dispo1 | source2 |
| 123456781 | NULL | NULL |
+---+-+-+
I don't want this cos the ssn 123456780 is shown twice
select cust.ssn, disposition.disposition, source.source from cust RIGHT
JOIN source ON (cust.source=source.id) RIGHT JOIN disposition ON
(cust.disposition=disposition.id)
+---+-+-+
| ssn | disposition | source |
+---+-+-+
| 123456789 | dispo2 | source1 |
| 123456789 | dispo2 | source1 |
| 123456780 | dispo1 | source2 |
| 123456780 | dispo1 | source2 |
+---+-+-+
This has the same problem.
All I want is
+---+-+-+
| ssn | disposition | source |
+---+-+-+
| 123456789 | dispo2 | source1 |
| 123456780 | dispo1 | source2 |
| 123456781 | NULL | NULL |
+---+-+-+
I'd appreciate your help.
Thanks
Murthy
Michael Dykman wrote:
a left join and a right join are 2 very distinct things... It is not
clear from your text what it is you exactly are going for here but I
doubt that applying either LEFT or RIGHT to ALL of your (many) joins
is going to give it to you. You need to stop and examine the
relationships between the tables in this query and determine which
class of JOIN you will need (and there are more than just these 2).
The description you gave of your results using RIGHT and LEFT
universally are consistent with what I would expect from those types
of joins. I suggest that you read this page very carefully before you
continue:
http://dev.mysql.com/doc/refman/5.1/en/join.html
- michael
On 4/3/07, murthy gandikota wrote:
I tried the following 2 SQL's and the results are less than
satisfactory. The RIGHT join does not show where disposition is NULL.
The LEFT join shows dispositions as NULL where they shouldn't be. Also
the LEFT join generates more dupes. Any way to fix this?
select cust.first as FIRST, cust.last as LAST, cust.address AS ADDRESS,
cust.city as CITY, cust.state as STATE, cust.zip as ZIP, cust.ssn as
SSN, disposition.disposition as DISPOSITION, leadSource.source as
SOURCE, cust.loanBalance AS LOANBALANCE, cust.currentPayment as
CURRENTPAYMENT, cust.newPayment as NEWPAYMENT from cust RIGHT JOIN
disposition on (cust.disposition=disposition.id) RIGHT JOIN leadSource
on (cust.source=leadSource.id) where agentCode=11 and newPayment 0 and
cust.disposition 0 order by SOURCE, DISPOSITION
select cust.first as FIRST, cust.last as LAST, cust.address AS ADDRESS,
cust.city as CITY, cust.state as STATE, cust.zip as ZIP, cust.ssn as
SSN, disposition.disposition as DISPOSITION, leadSource.source as
SOURCE, cust.loanBalance AS LOANBALANCE, cust.currentPayment as
CURRENTPAYMENT, cust.newPayment as NEWPAYMENT from cust LEFT JOIN
disposition on (cust.disposition=disposition.id) LEFT JOIN leadSource on
(cust.source=leadSource.id) where agentCode=11 and newPayment 0 and
cust.disposition 0 order by SOURCE, DISPOSITION
The MYSQL has the following version
mysql Ver 14.7 Distrib 5.0.2-alpha, for pc-linux (i686)
Thanks for your help
Murthy
-
Don't get
RE: Joins versus Grouping/Indexing: Normalization Excessive?
Do you really mean 1:1 relationships? A song might have more than one artist, album, or genre. Could a song have no album at all, such as something that was released in MP3 format? Also, you might have more than one version of a song. You might need a separate table for recordings, with each song having one or more recordings. That would be a good place to keep the release information, rather than storing it with the song. The biggest problem would be to figure out how to index the songs, themselves. I don't have a good suggestion for that off the top of my head. That all being said, there's no reason I can think of not to normalize the data. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Daniel Cousineau [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 03, 2007 12:15 AM To: mysql@lists.mysql.com Subject: Joins versus Grouping/Indexing: Normalization Excessive? So I'm currently designing a database for a web site and intra net for my campuses student radio. Since I'm not getting paid for this and I'm doing this in my free time I kinda want to take my time and have the system setup as perfectly as any one college student can. I'm currently debating on how I want to store all the songs in the system. Namely I'm not quite sure which way will eek the most performance out of MySQL. My plan so far is quite normalized, a songs table with 1:1 relationships with an Albums, Artists, and Genres table. The big benefits I see from this is when I'm building the intra net application I'll want to pull just all of the artists or all of the albums, etc. However I feel like I'm encountering issues with where to store the year field of an mp3 (do I want it on the album, song, or both) along with issues like printing everything out at once. The only other way I can think of thats relatively efficient is to have the singular songs table and have indexes on albums, artists, and genres. My question, more out of curiosity than necessity, is which of these would be more efficient (given that I'll be using the InnoDB storage engine)? Other relevant facts include it'll be using the latest, stable release of MySQL 5 and I'll be developing in PHP5 (through CakePHP's database abstraction layer). -- Thanks and Gig 'Em! Daniel Cousineau http://www.terminalfuture.com/ http://www.linkedin.com/in/dcousineau [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LEFT/RIGHT Joins not working
I tried the following 2 SQL's and the results are less than satisfactory. The RIGHT join does not show where disposition is NULL. The LEFT join shows dispositions as NULL where they shouldn't be. Also the LEFT join generates more dupes. Any way to fix this? select cust.first as FIRST, cust.last as LAST, cust.address AS ADDRESS, cust.city as CITY, cust.state as STATE, cust.zip as ZIP, cust.ssn as SSN, disposition.disposition as DISPOSITION, leadSource.source as SOURCE, cust.loanBalance AS LOANBALANCE, cust.currentPayment as CURRENTPAYMENT, cust.newPayment as NEWPAYMENT from cust RIGHT JOIN disposition on (cust.disposition=disposition.id) RIGHT JOIN leadSource on (cust.source=leadSource.id) where agentCode=11 and newPayment 0 and cust.disposition 0 order by SOURCE, DISPOSITION select cust.first as FIRST, cust.last as LAST, cust.address AS ADDRESS, cust.city as CITY, cust.state as STATE, cust.zip as ZIP, cust.ssn as SSN, disposition.disposition as DISPOSITION, leadSource.source as SOURCE, cust.loanBalance AS LOANBALANCE, cust.currentPayment as CURRENTPAYMENT, cust.newPayment as NEWPAYMENT from cust LEFT JOIN disposition on (cust.disposition=disposition.id) LEFT JOIN leadSource on (cust.source=leadSource.id) where agentCode=11 and newPayment 0 and cust.disposition 0 order by SOURCE, DISPOSITION The MYSQL has the following version mysql Ver 14.7 Distrib 5.0.2-alpha, for pc-linux (i686) Thanks for your help Murthy - Don't get soaked. Take a quick peek at the forecast with theYahoo! Search weather shortcut.
Re: LEFT/RIGHT Joins not working
a left join and a right join are 2 very distinct things... It is not clear from your text what it is you exactly are going for here but I doubt that applying either LEFT or RIGHT to ALL of your (many) joins is going to give it to you. You need to stop and examine the relationships between the tables in this query and determine which class of JOIN you will need (and there are more than just these 2). The description you gave of your results using RIGHT and LEFT universally are consistent with what I would expect from those types of joins. I suggest that you read this page very carefully before you continue: http://dev.mysql.com/doc/refman/5.1/en/join.html - michael On 4/3/07, murthy gandikota [EMAIL PROTECTED] wrote: I tried the following 2 SQL's and the results are less than satisfactory. The RIGHT join does not show where disposition is NULL. The LEFT join shows dispositions as NULL where they shouldn't be. Also the LEFT join generates more dupes. Any way to fix this? select cust.first as FIRST, cust.last as LAST, cust.address AS ADDRESS, cust.city as CITY, cust.state as STATE, cust.zip as ZIP, cust.ssn as SSN, disposition.disposition as DISPOSITION, leadSource.source as SOURCE, cust.loanBalance AS LOANBALANCE, cust.currentPayment as CURRENTPAYMENT, cust.newPayment as NEWPAYMENT from cust RIGHT JOIN disposition on (cust.disposition=disposition.id) RIGHT JOIN leadSource on (cust.source=leadSource.id) where agentCode=11 and newPayment 0 and cust.disposition 0 order by SOURCE, DISPOSITION select cust.first as FIRST, cust.last as LAST, cust.address AS ADDRESS, cust.city as CITY, cust.state as STATE, cust.zip as ZIP, cust.ssn as SSN, disposition.disposition as DISPOSITION, leadSource.source as SOURCE, cust.loanBalance AS LOANBALANCE, cust.currentPayment as CURRENTPAYMENT, cust.newPayment as NEWPAYMENT from cust LEFT JOIN disposition on (cust.disposition=disposition.id) LEFT JOIN leadSource on (cust.source=leadSource.id) where agentCode=11 and newPayment 0 and cust.disposition 0 order by SOURCE, DISPOSITION The MYSQL has the following version mysql Ver 14.7 Distrib 5.0.2-alpha, for pc-linux (i686) Thanks for your help Murthy - Don't get soaked. Take a quick peek at the forecast with theYahoo! Search weather shortcut. -- - 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: LEFT/RIGHT Joins not working
Hi Mike
Thanks for your input. I read the page before and it caused more confusion.
May be an example would clarify this:
create table `cust` (`ssn` varchar(10), `source` int(3) , `disposition`
int(3));
insert into cust (ssn, source, disposition) values ('123456789', 1, 2);
insert into cust (ssn, source, disposition) values ('123456780', 2, 1);
insert into cust (ssn, source, disposition) values ('123456781', NULL, NULL);
create table `source` (`id` int(3), `source` varchar(10));
insert into source(id, source) values (1, 'source1');
insert into source(id,source) values (2, 'source2');
create table `disposition` (`id` int(3), `disposition` varchar(10));
insert into disposition (id, disposition) values (1, 'dispo1');
insert into disposition(id,disposition) values (2, 'dispo2');
Now I run the sql:
select cust.ssn, disposition.disposition, source.source from cust LEFT JOIN
source ON (cust.source=source.id) LEFT JOIN disposition ON
(cust.disposition=disposition.id)
+---+-+-+
| ssn | disposition | source |
+---+-+-+
| 123456789 | dispo2 | source1 |
| 123456789 | dispo2 | source1 |
| 123456780 | dispo1 | source2 |
| 123456780 | dispo1 | source2 |
| 123456781 | NULL| NULL|
+---+-+-+
I don't want this cos the ssn 123456780 is shown twice
select cust.ssn, disposition.disposition, source.source from cust RIGHT JOIN
source ON (cust.source=source.id) RIGHT JOIN disposition ON
(cust.disposition=disposition.id)
+---+-+-+
| ssn | disposition | source |
+---+-+-+
| 123456789 | dispo2 | source1 |
| 123456789 | dispo2 | source1 |
| 123456780 | dispo1 | source2 |
| 123456780 | dispo1 | source2 |
+---+-+-+
This has the same problem.
All I want is
+---+-+-+
| ssn | disposition | source |
+---+-+-+
| 123456789 | dispo2 | source1 |
| 123456780 | dispo1 | source2 |
| 123456781 | NULL| NULL|
+---+-+-+
I'd appreciate your help.
Thanks
Murthy
Michael Dykman [EMAIL PROTECTED] wrote:
a left join and a right join are 2 very distinct things... It is not
clear from your text what it is you exactly are going for here but I
doubt that applying either LEFT or RIGHT to ALL of your (many) joins
is going to give it to you. You need to stop and examine the
relationships between the tables in this query and determine which
class of JOIN you will need (and there are more than just these 2).
The description you gave of your results using RIGHT and LEFT
universally are consistent with what I would expect from those types
of joins. I suggest that you read this page very carefully before you
continue:
http://dev.mysql.com/doc/refman/5.1/en/join.html
- michael
On 4/3/07, murthy gandikota wrote:
I tried the following 2 SQL's and the results are less than satisfactory. The
RIGHT join does not show where disposition is NULL. The LEFT join shows
dispositions as NULL where they shouldn't be. Also the LEFT join generates
more dupes. Any way to fix this?
select cust.first as FIRST, cust.last as LAST, cust.address AS ADDRESS,
cust.city as CITY, cust.state as STATE, cust.zip as ZIP, cust.ssn as SSN,
disposition.disposition as DISPOSITION, leadSource.source as SOURCE,
cust.loanBalance AS LOANBALANCE, cust.currentPayment as CURRENTPAYMENT,
cust.newPayment as NEWPAYMENT from cust RIGHT JOIN disposition on
(cust.disposition=disposition.id) RIGHT JOIN leadSource on
(cust.source=leadSource.id) where agentCode=11 and newPayment 0 and
cust.disposition 0 order by SOURCE, DISPOSITION
select cust.first as FIRST, cust.last as LAST, cust.address AS ADDRESS,
cust.city as CITY, cust.state as STATE, cust.zip as ZIP, cust.ssn as SSN,
disposition.disposition as DISPOSITION, leadSource.source as SOURCE,
cust.loanBalance AS LOANBALANCE, cust.currentPayment as CURRENTPAYMENT,
cust.newPayment as NEWPAYMENT from cust LEFT JOIN disposition on
(cust.disposition=disposition.id) LEFT JOIN leadSource on
(cust.source=leadSource.id) where agentCode=11 and newPayment 0 and
cust.disposition 0 order by SOURCE, DISPOSITION
The MYSQL has the following version
mysql Ver 14.7 Distrib 5.0.2-alpha, for pc-linux (i686)
Thanks for your help
Murthy
-
Don't get soaked. Take a quick peek at the forecast
with theYahoo! Search weather shortcut.
--
- michael dykman
- [EMAIL PROTECTED]
- All models are wrong. Some models are useful.
-
8:00? 8:25? 8:40? Find a flick in no time
with theYahoo! Search movie showtime shortcut.
Re: LEFT/RIGHT Joins not working
Well, trying your example gives me the expected result.
select cust.ssn, disposition.disposition, source.source
from cust LEFT JOIN source ON (cust.source=source.id)
LEFT JOIN disposition ON (cust.disposition=disposition.id);
'123456789', 'dispo2', 'source1'
'123456780', 'dispo1', 'source2'
'123456781', null, null
On Wed, April 4, 2007 00:03, murthy gandikota wrote:
Hi Mike
Thanks for your input. I read the page before and it caused more
confusion.
May be an example would clarify this:
create table `cust` (`ssn` varchar(10), `source` int(3) , `disposition`
int(3));
insert into cust (ssn, source, disposition) values ('123456789', 1, 2);
insert into cust (ssn, source, disposition) values ('123456780', 2, 1);
insert into cust (ssn, source, disposition) values ('123456781', NULL,
NULL);
create table `source` (`id` int(3), `source` varchar(10));
insert into source(id, source) values (1, 'source1');
insert into source(id,source) values (2, 'source2');
create table `disposition` (`id` int(3), `disposition` varchar(10));
insert into disposition (id, disposition) values (1, 'dispo1');
insert into disposition(id,disposition) values (2, 'dispo2');
Now I run the sql:
select cust.ssn, disposition.disposition, source.source from cust LEFT
JOIN source ON (cust.source=source.id) LEFT JOIN disposition ON
(cust.disposition=disposition.id)
+---+-+-+
| ssn | disposition | source |
+---+-+-+
| 123456789 | dispo2 | source1 |
| 123456789 | dispo2 | source1 |
| 123456780 | dispo1 | source2 |
| 123456780 | dispo1 | source2 |
| 123456781 | NULL| NULL|
+---+-+-+
I don't want this cos the ssn 123456780 is shown twice
select cust.ssn, disposition.disposition, source.source from cust RIGHT
JOIN source ON (cust.source=source.id) RIGHT JOIN disposition ON
(cust.disposition=disposition.id)
+---+-+-+
| ssn | disposition | source |
+---+-+-+
| 123456789 | dispo2 | source1 |
| 123456789 | dispo2 | source1 |
| 123456780 | dispo1 | source2 |
| 123456780 | dispo1 | source2 |
+---+-+-+
This has the same problem.
All I want is
+---+-+-+
| ssn | disposition | source |
+---+-+-+
| 123456789 | dispo2 | source1 |
| 123456780 | dispo1 | source2 |
| 123456781 | NULL| NULL|
+---+-+-+
I'd appreciate your help.
Thanks
Murthy
Michael Dykman [EMAIL PROTECTED] wrote:
a left join and a right join are 2 very distinct things... It is not
clear from your text what it is you exactly are going for here but I
doubt that applying either LEFT or RIGHT to ALL of your (many) joins
is going to give it to you. You need to stop and examine the
relationships between the tables in this query and determine which
class of JOIN you will need (and there are more than just these 2).
The description you gave of your results using RIGHT and LEFT
universally are consistent with what I would expect from those types
of joins. I suggest that you read this page very carefully before you
continue:
http://dev.mysql.com/doc/refman/5.1/en/join.html
- michael
On 4/3/07, murthy gandikota wrote:
I tried the following 2 SQL's and the results are less than
satisfactory. The RIGHT join does not show where disposition is NULL.
The LEFT join shows dispositions as NULL where they shouldn't be. Also
the LEFT join generates more dupes. Any way to fix this?
select cust.first as FIRST, cust.last as LAST, cust.address AS ADDRESS,
cust.city as CITY, cust.state as STATE, cust.zip as ZIP, cust.ssn as
SSN, disposition.disposition as DISPOSITION, leadSource.source as
SOURCE, cust.loanBalance AS LOANBALANCE, cust.currentPayment as
CURRENTPAYMENT, cust.newPayment as NEWPAYMENT from cust RIGHT JOIN
disposition on (cust.disposition=disposition.id) RIGHT JOIN leadSource
on (cust.source=leadSource.id) where agentCode=11 and newPayment 0 and
cust.disposition 0 order by SOURCE, DISPOSITION
select cust.first as FIRST, cust.last as LAST, cust.address AS ADDRESS,
cust.city as CITY, cust.state as STATE, cust.zip as ZIP, cust.ssn as
SSN, disposition.disposition as DISPOSITION, leadSource.source as
SOURCE, cust.loanBalance AS LOANBALANCE, cust.currentPayment as
CURRENTPAYMENT, cust.newPayment as NEWPAYMENT from cust LEFT JOIN
disposition on (cust.disposition=disposition.id) LEFT JOIN leadSource on
(cust.source=leadSource.id) where agentCode=11 and newPayment 0 and
cust.disposition 0 order by SOURCE, DISPOSITION
The MYSQL has the following version
mysql Ver 14.7 Distrib 5.0.2-alpha, for pc-linux (i686)
Thanks for your help
Murthy
-
Don't get soaked. Take a quick peek
Joins versus Grouping/Indexing: Normalization Excessive?
So I'm currently designing a database for a web site and intra net for my campuses student radio. Since I'm not getting paid for this and I'm doing this in my free time I kinda want to take my time and have the system setup as perfectly as any one college student can. I'm currently debating on how I want to store all the songs in the system. Namely I'm not quite sure which way will eek the most performance out of MySQL. My plan so far is quite normalized, a songs table with 1:1 relationships with an Albums, Artists, and Genres table. The big benefits I see from this is when I'm building the intra net application I'll want to pull just all of the artists or all of the albums, etc. However I feel like I'm encountering issues with where to store the year field of an mp3 (do I want it on the album, song, or both) along with issues like printing everything out at once. The only other way I can think of thats relatively efficient is to have the singular songs table and have indexes on albums, artists, and genres. My question, more out of curiosity than necessity, is which of these would be more efficient (given that I'll be using the InnoDB storage engine)? Other relevant facts include it'll be using the latest, stable release of MySQL 5 and I'll be developing in PHP5 (through CakePHP's database abstraction layer). -- Thanks and Gig 'Em! Daniel Cousineau http://www.terminalfuture.com/ http://www.linkedin.com/in/dcousineau [EMAIL PROTECTED]
Re: Joins versus Grouping/Indexing: Normalization Excessive?
I think you're approaching this from the wrong angle. You'll want to put the data at the highest level at which it changes. i.e. If every song on an album is always the same year, put it at the album level, however, if it changes from song to song on a particular album, then you want it at the song level. Year wouldn't ever apply to artist I don't think, unless they're truly a one hit wonder. :) -Micah On 04/02/2007 09:14 PM, Daniel Cousineau wrote: So I'm currently designing a database for a web site and intra net for my campuses student radio. Since I'm not getting paid for this and I'm doing this in my free time I kinda want to take my time and have the system setup as perfectly as any one college student can. I'm currently debating on how I want to store all the songs in the system. Namely I'm not quite sure which way will eek the most performance out of MySQL. My plan so far is quite normalized, a songs table with 1:1 relationships with an Albums, Artists, and Genres table. The big benefits I see from this is when I'm building the intra net application I'll want to pull just all of the artists or all of the albums, etc. However I feel like I'm encountering issues with where to store the year field of an mp3 (do I want it on the album, song, or both) along with issues like printing everything out at once. The only other way I can think of thats relatively efficient is to have the singular songs table and have indexes on albums, artists, and genres. My question, more out of curiosity than necessity, is which of these would be more efficient (given that I'll be using the InnoDB storage engine)? Other relevant facts include it'll be using the latest, stable release of MySQL 5 and I'll be developing in PHP5 (through CakePHP's database abstraction layer). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql 4.11, left joins, multiple tables
Hi -
I'm having an issue with left joins and multiple tables. I have a table
which is a fairly simple name-value pair table:
create table
{
acnt char(20),
item char(40),
value char (60)
}
I want to pull out all of a subset of the values for a particular account.
It works for
two of the values, but as soon as I add the third (not to mention the 4th
and 5th),
The query returns nothing, and quietly. No errors.
My SQL is:
select A.acnt,
A.item, A.value,
B.item, B.value,
C.item, C.value
from (client_profile A LEFT JOIN client_profile B USING(acnt))
LEFT JOIN client_profile C USING(acnt)
where A.acnt = 'AR87923'
and A.item = 'ctt_minute_usage'
and B.item = 'ctt_promo_used'
and C.item = 'ctt_testdrive_expire'
I have tried it with and without the parens around the first left join. Is
there a
limit in mySQL 4.11 on the number of tables that can be used in a left join?
Thanks,
Andy Wallace
CISData Systems.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Joins with sums
I'm missing something really silly in a query. I want to produce a list of accounts, the invoices and receipts and the balance against them. Simply, there is an accounts table, invoices and receipts. I want to show the account details, sum(invoices), sum(receipts) and the balance. There is a situation where there maybe receipts and no invoices, and visa-versa, hence the if statement. SELECT account.accountId, account.name, if(count(invoices.invoiceId)=0,0,sum(invoices.amount)) as debits, if(count(receipts.receiptId)=0,0,sum(receipts.amount)) as credits, if(count(invoices.invoiceId)=0,0,sum(invoices.amount))-if(count(receipts.rec eiptId)=0,0,sum(receipts.amount)) as balance FROM family LEFT JOIN invoices ON invoices.accountId=account.accountId LEFT JOIN receipts ON receipts.accountId=account.accountId WHERE invoices.invoiceDate=CURDATE() GROUP BY account.accountId For some reason, I'm getting results with multiple invoices etc against them - normally 3. Can anyone point out where I've gone wrong? Cheers Nunners -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joins with sums
You're left joining invoices, then left joining receipts. A left join will replicate rows on the left side to match the number of rows it found in the join, or just leave 1 row with NULL values (as you probably know). This is where your problem is. You were correct to try to use left joins because there may or may not be invoices/receipts. Your invoice values are being replicated to match the number of receipts matched in the join. You need to summarize the invoices and receipts separately since there is no relation between the two in this case. You also shouldn't be doing conditional sums. That means breaking it up into essentially 2 queries and then summarizing the result of those two queries. It can still be one query, just a little complicated. You're going to be querying and summarizing the result of the union of two other queries. Not sure if I got everything correct in this query, but this type of query does work in mysql 4. SELECT account.accountId, account.name,sum(debits) as debits, sum(credits) as credits FROM ( SELECT account.accountId, account.name, sum(if(invoices.amount IS NULL,0,invoices.amount)) as debits, 0 as credits FROM family LEFT JOIN invoices ON invoices.accountId=account.accountId WHERE invoices.invoiceDate=CURDATE() GROUP BY account.accountId UNION SELECT account.accountId, account.name, 0 as debits, sum(if(receipts.amount IS NULL, 0, receipts.amount)) as credits, FROM family JOIN receipts ON receipts.accountId=account.accountId GROUP BY account.accountId ) AS combined GROUP BY account.acountId - Original Message - From: webmaster [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, January 30, 2007 6:28 AM Subject: Joins with sums I'm missing something really silly in a query. I want to produce a list of accounts, the invoices and receipts and the balance against them. Simply, there is an accounts table, invoices and receipts. I want to show the account details, sum(invoices), sum(receipts) and the balance. There is a situation where there maybe receipts and no invoices, and visa-versa, hence the if statement. SELECT account.accountId, account.name, if(count(invoices.invoiceId)=0,0,sum(invoices.amount)) as debits, if(count(receipts.receiptId)=0,0,sum(receipts.amount)) as credits, if(count(invoices.invoiceId)=0,0,sum(invoices.amount))-if(count(receipts.rec eiptId)=0,0,sum(receipts.amount)) as balance FROM family LEFT JOIN invoices ON invoices.accountId=account.accountId LEFT JOIN receipts ON receipts.accountId=account.accountId WHERE invoices.invoiceDate=CURDATE() GROUP BY account.accountId For some reason, I'm getting results with multiple invoices etc against them - normally 3. Can anyone point out where I've gone wrong? Cheers Nunners -- 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]
A simple way to make table joins 2x faster
I thought I'd pass this on for those of you that have slow table joins. I'm sure a few of you have already figured this out, but if not, here it is. I have some slow table joins, namely a 6 table join using a primary integer index field which takes 15 seconds to pull in 18k rows from each of the 35 million row tables. Unfortunately I have to do this 3000 times so the time adds up. I was able to speed this up by 2x with small or large number of table joins. Here's how I did it. 1) Created 6 memory tables, one for each of the tables I'm joining. I extract the 18k rows from each of the disk based tables, 2) Then build an index on the join column of each memory table. 3) Join the memory tables together. The time for all the steps is 50% of using MyISAM tables on disk. Example: create temporary table mem_table1 type=memory select col1,col2,..coln from table1 where date='2006-01-01'; alter table mem_table1 add index ix_Primary (Rcd_Id); -- Can omit index from 1st table ... Repeat for remaining 5 tables now join the memory tables together. Here I'm using a left join: select * from mem_table1 T1 left join mem_table2 T2 on T1.KeyField=T2.KeyField ... I figure this is faster because random disk access needed to join a disk based table is considerably slower than when using a RAM based table. The overhead of creating the Memory table, extracting the data and building the index isn't that slow because the data is accessed sequentially as opposed to the slower random disk access needed for the join. If anyone wants to try it out, let me know if you see an improvement. Mike -- 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]
Re: Complex SQL for multiple joins
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) users_usergroups (a join table that identifies which users belong to which groups) users_permissions (another join table that identifies which users have which permissions) usergroups_permissions (the final join table that identifies which usergroups have which permissions) Each of the _permissions join tables has an additional type column specifying whether the link allows or denies that particular action. What I've managed to get so far is queries returning which users belong to which groups, which users have which permissions, and which usergroups have which permissions. However, I need to go one step further and retrieve the permissions belonging to the usergroups which a specified user is a member of. Ideally I'd like to retrieve the individual users permissions at the same time. So what I need is a query that returns permissions belonging to a specific user, and permissions belonging to the usergroups that the specified user is a member of. Is this something I can do without too much hassle? Thanks in advance! -- 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
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)
users_usergroups (a join table that identifies which users belong to which
groups)
users_permissions (another join table that identifies which users have which
permissions)
usergroups_permissions (the final join table that identifies which
usergroups have which permissions)
Each of the _permissions join tables has an additional type column
specifying whether the link allows or denies that particular action.
What I've managed to get so far is queries returning which users belong to
which groups, which users have which permissions, and which usergroups have
which permissions. However, I need to go one step further and retrieve the
permissions belonging to the usergroups which a specified user is a member
of. Ideally I'd like to retrieve the individual users permissions at the
same time.
So what I need is a query that returns permissions belonging to a specific
user, and permissions belonging to the usergroups
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
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) users_usergroups (a join table that identifies which users belong to which groups) users_permissions (another join table that identifies which users have which permissions) usergroups_permissions (the final join table that identifies which usergroups have which permissions) Each of the _permissions join tables has an additional type column specifying whether the link allows or denies that particular action. What I've managed to get so far is queries returning which users belong to which groups, which users have which permissions, and which usergroups have which permissions. However, I need to go one step further and retrieve the permissions belonging to the usergroups which a specified user is a member of. Ideally I'd like to retrieve the individual users permissions at the same time. So what I need is a query that returns permissions belonging to a specific user, and permissions belonging to the usergroups that the specified user is a member of. Is this something I can do without too much hassle? Thanks in advance!
Re: Should Joins always be using an index? (where possible?)
On 8/20/06, Ow Mun Heng [EMAIL PROTECTED] wrote: I'm have a query like so select A, index_A from tableA join tableB on tableB.indexA = tableA.indexA select A, index_A from tableA join tableB on tableB.A = tableA.A whcih would be more efficient? using the where clause which uses the index or the one which isn't index? If you join using the field that is indexed, this will use an index. If you join using the field that isn't indexed, it can't use an index. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Should Joins always be using an index? (where possible?)
On Sun, 2006-08-20 at 19:59 +1000, chris smith wrote: On 8/20/06, Ow Mun Heng [EMAIL PROTECTED] wrote: I'm have a query like so select A, index_A from tableA join tableB on tableB.indexA = tableA.indexA select A, index_A from tableA join tableB on tableB.A = tableA.A whcih would be more efficient? using the where clause which uses the index or the one which isn't index? If you join using the field that is indexed, this will use an index. If you join using the field that isn't indexed, it can't use an index. I take it that that means yes, it's more efficient performance wise to always use an index. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Should Joins always be using an index? (where possible?)
Ow Mun Heng wrote: On Sun, 2006-08-20 at 19:59 +1000, chris smith wrote: On 8/20/06, Ow Mun Heng [EMAIL PROTECTED] wrote: I'm have a query like so select A, index_A from tableA join tableB on tableB.indexA = tableA.indexA select A, index_A from tableA join tableB on tableB.A = tableA.A whcih would be more efficient? using the where clause which uses the index or the one which isn't index? If you join using the field that is indexed, this will use an index. If you join using the field that isn't indexed, it can't use an index. I take it that that means yes, it's more efficient performance wise to always use an index. Of course, same as any query ;) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[SOLVED] Re: Should Joins always be using an index? (where possible?)
On Mon, 2006-08-21 at 07:39 +1000, Chris wrote: Ow Mun Heng wrote: On Sun, 2006-08-20 at 19:59 +1000, chris smith wrote: On 8/20/06, Ow Mun Heng [EMAIL PROTECTED] wrote: I'm have a query like so select A, index_A from tableA join tableB on tableB.indexA = tableA.indexA select A, index_A from tableA join tableB on tableB.A = tableA.A whcih would be more efficient? using the where clause which uses the index or the one which isn't index? If you join using the field that is indexed, this will use an index. If you join using the field that isn't indexed, it can't use an index. I take it that that means yes, it's more efficient performance wise to always use an index. Of course, same as any query ;) Thanks!! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Should Joins always be using an index? (where possible?)
I'm have a query like so select A, index_A from tableA join tableB on tableB.indexA = tableA.indexA select A, index_A from tableA join tableB on tableB.A = tableA.A whcih would be more efficient? using the where clause which uses the index or the one which isn't index? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: inner outer joins
Brian E Boothe wrote: hi all Can someone provide a small project using inner and outter joins with querys thanks alot create table t1 (id int); create table t2 (id int); This will find everything that has an entry in both tables: select * from t1 inner join t2; This will find records that are in t1 but not t2: select * from t1 left outer join t2 where t2.id is null; Read the manual for more information. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
inner outer joins
hi all Can someone provide a small project using inner and outter joins with querys thanks alot -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multiple joins
What am I missing here? select m.*, d.discussion, d.discussion_id, u.user_id, t.topic_id from forums_messages left join forums_members m, forums_discussions d, users u, forums_topics t on m.topic_id = t.topic_id, t.discussion_id = d.discussion_id where m.topic_id = 1; Is it legal to do multiple joins like this? Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | F A X 6 0 2 . 9 7 1 . 1 6 9 4 Steffan A. Cline [EMAIL PROTECTED] Phoenix, Az http://www.ExecuChoice.net USA AIM : SteffanC ICQ : 57234309 Lasso Partner Alliance Member --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple joins
Steffan A. Cline wrote: What am I missing here? select m.*, d.discussion, d.discussion_id, u.user_id, t.topic_id from forums_messages left join forums_members m, forums_discussions d, users u, forums_topics t on m.topic_id = t.topic_id, t.discussion_id = d.discussion_id where m.topic_id = 1; Is it legal to do multiple joins like this? Thanks Steffan forum_messages does not take part in any selection or any where clause. You have 5 tables listed, with only 4 of them appearing in 2 disjointed, and improperly formed joins. From the comma separated table list after a LEFT JOIN, I am not sure whether you intend them to be LEFT JOINs or INNER JOINs. Try: SELECT fields FROM file1 LEFT JOIN file2 ON something INNER JOIN file3 ON something INNER JOIN file4 ON something INNER JOIN file5 ON something WHERE somecondition -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple joins
This is what I finally settled on. It seems to work well. select m.*, d.discussion, d.discussion_id, u.user_id, u.icon, u.nick_name from forums_messages m left join forums_topics t on m.topic_id = t.topic_id left join forums_discussions d on t.discussion_id = d.discussion_id left join users u on m.user_id = u.user_id where m.topic_id = 1; Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | F A X 6 0 2 . 9 7 1 . 1 6 9 4 Steffan A. Cline [EMAIL PROTECTED] Phoenix, Az http://www.ExecuChoice.net USA AIM : SteffanC ICQ : 57234309 Lasso Partner Alliance Member --- From: Gerald L. Clark [EMAIL PROTECTED] Date: Fri, 30 Jun 2006 09:54:14 -0500 To: Steffan A. Cline [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: Multiple joins Steffan A. Cline wrote: What am I missing here? select m.*, d.discussion, d.discussion_id, u.user_id, t.topic_id from forums_messages left join forums_members m, forums_discussions d, users u, forums_topics t on m.topic_id = t.topic_id, t.discussion_id = d.discussion_id where m.topic_id = 1; Is it legal to do multiple joins like this? Thanks Steffan forum_messages does not take part in any selection or any where clause. You have 5 tables listed, with only 4 of them appearing in 2 disjointed, and improperly formed joins. From the comma separated table list after a LEFT JOIN, I am not sure whether you intend them to be LEFT JOINs or INNER JOINs. Try: SELECT fields FROM file1 LEFT JOIN file2 ON something INNER JOIN file3 ON something INNER JOIN file4 ON something INNER JOIN file5 ON something WHERE somecondition -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
