Re: why aren't my PRIMARY KEYs being used?

On Oct 29, 2004, at 6:26 AM, [EMAIL PROTECTED] wrote: I think it may be because of your mixed left and right joins. There are several bugs listed that show that the optimizer mishandles certain combinations of left and right joins. SELECT activelayers.id,activelayers.lid,activelayers.rsrcc,

why aren't my PRIMARY KEYs being used?

I've created two temporary tables: CREATE TEMPORARY TABLE `activenodes` ( `id` smallint(6) unsigned NOT NULL default '0', `name` varchar(50) NOT NULL default '', `rsrcc` bigint(21) NOT NULL default '0', PRIMARY KEY (`id`) ); CREATE TEMPORARY TABLE `activelayers` ( `id` int(10) unsigned

slow query (JOIN JOIN LEFT JOIN ... HAVING)

When I'm posting a question here I usually post a simplified abstraction. This time I have to post the full problem - and my problem is that my query takes far too long to complete. The query is quite speedy with very small data sets, but it slows down unacceptably with data sets of any decent si

Re: Monkeys and Banannas (UPDATE .. JOIN .. SET .. GROUP)

Quoting [EMAIL PROTECTED]: > Good, you recognize the need to perform two separate aggregates (GROUP > BYs) and compare the separate results... In fact, you may need two temp > tables so that you can (INNER / LEFT) JOIN one to the other. (Yes, I can > think of a query that works without the second

Re: Monkeys and Banannas (UPDATE .. JOIN .. SET .. GROUP)

Quoting [EMAIL PROTECTED]: > > Why would you want to do that? bananacount is something you can > > calculate with a LEFT JOIN and a GROUP BY, so storing it in the DB > > would break normalization. > > This would also the first step in creating your own OLAP cube. For each > statistic, you save yo

Re: Monkeys and Banannas (UPDATE .. JOIN .. SET .. GROUP)

Quoting Michael Stassen <[EMAIL PROTECTED]>: > According to the manual , the > multi-table UPDATE syntax is > >UPDATE [LOW_PRIORITY] [IGNORE] tbl_name [, tbl_name ...] > SET col_name1=expr1 [, col_name2=expr2 ...] > [WHERE where_definiti

Monkeys and Banannas (UPDATE .. JOIN .. SET .. GROUP)

I'm trying to create a single UPDATE query to deal with the following problem: == -- I've got two tables: CREATE TABLE `banannas` ( `owner` varchar(15) NOT NULL default '' ); CREATE TABLE `monkeys` ( `name` varchar(15) default

matching people with projects via resources

I'm having difficulty constructing a query. I've got two kinds of information: a table of resources that various people have, and a table of resources that various projects need. === CREATE TABLE `people` ( `name` varchar(11) default NULL, `

Re: client ip address

Quoting [EMAIL PROTECTED]: > You might look at the USER() and CURRENT_USER() functions. > > http://dev.mysql.com/doc/mysql/en/Information_functions.html Aha! I think this is what I was looking for: "the client host from which [I] connected". I'll have to do some in-query string processing to ex

Re: client ip address

Quoting Mazhar Bilen <[EMAIL PROTECTED]>: > Use this variable: > > $REMOTE_ADDR. > > Such that: > > $client_IP = $REMOTE_ADDR; > UPDATE clients SET ipaddr= $client_IP WHERE [EMAIL PROTECTED]; This doesn't appear to work: I don't think the MySQL server recognizes $REMOTE_ADDR as anything meaningfu

client ip address

Is there a variable or function in MySQL which resolves to the connecting client's IP address? I have SQL clients which will be connecting through a NAT firewall. I want to keep a record of IP of the client that connected to it. It would look something like this: UPDATE clients SET ipaddr=INET_

Re: MIN(foo) as bar WHERE bar>50

> What version of MySQL are you running? Version 4.1.x and 5.0.x offer many > more potential solutions than the earlier versions, which don't support > subqueries. Sorry, should have specified. I am using 4.0.20, but I am under no obligation to stick with this version. (I'm developing an applica

MIN(foo) as bar WHERE bar>50

I'm trying to construct a query and running into either a limitation of the SQL language or (more probably) a limitation in my *comprehension* of the SQL language. Here's a simplified version of my data set: === CREATE TABLE people ( name varc