size?
My Created_tmp_disk_tables is considerably lower than my Created_tmp_files
value. So it seems the Created_tmp_files cannott include the values from the
Created_tmp_disk_tables.
Kevin
- Original Message
From: Visolve DB Team <[EMAIL PROTECTED]>
To: Kevin Fries <[EMAIL
According to the mysql reference manual, the definition of this field is:
"How many temporary files mysqld has created. "
Can someone elaborate on this? What causes mysql to create a temporary file? I
see something indicating it may be associated with replication. In our
environment (which has
The IF function works pretty much like Oracle's decode. But I recommend
using CASE..WHEN for compatibility between the two.
Check out:
http://www.mysql.com/doc/en/Control_flow_functions.html
> -Original Message-
> From: Jonathan Rosenberg [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, No
Michael,
Thanks for informing me on that one. I was not aware of that problem
with multi-row inserts. For the sake of consistency with other DBMS's,
I don't use those, and now I'm quite glad. In fact, this attribute of
mySQL also applies to UPDATE statements, which allow the same columns to
be
That's not how it works. The following demonstrates the feature I think
you want.
>mysql test
test> create table testnull ( X varchar(10) not null );
Query OK, 0 rows affected (0.11 sec)
test> insert into testnull values ( null );
ERROR 1048: Column 'X' cannot be null
test>
> -Original Mess
Then I think you want
SELECT url, COUNT(DISTINCT ip_address)
FROM tablename
GROUP BY url;
> -Original Message-
> From: John Kelly [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, October 21, 2003 3:45 PM
> To: [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED]
> Subject: Re: Need help constructing query
Randy,
For "general" SQL questions, you might find something in a newsgroup,
such as comp.databases.*, but there are so many peculiarities, even
among the most compliant datbases, that it's hard to really ask a
"general" question.
As for your unique values...
> -Original Message-
> From:
Try the following:
SELECT a.*
FROM artifacts a left join artifacts higher
on a.product_id = higher.product_id
and higher.plan_submission_number > a.plan_submission_number
WHERE a.product_id = '1'
AND higher.product_id is null;
For anyone following this, I'm using some sample code,
I think the difference between mysql's precedence operation is
significant to point out.
Looking at the behavior of other databases, SQL Server and Oracle both
respond to the query you gave with
Recordss about users with names != 'Bob'. Jane returned.
Significantly, this is inversion does not incl
nd
> no C record will
That shouldn't be true. The join was (A left-join B), then that result
set joined to C.
And the comparison was A.A_ID = C.C_ID. If the resultset's A.A_ID has
data, the C comparison
will succeed regardless of B.B_ID being null.
Kevin Fries
> -Original Me
You're on the right track with LEFT JOIN. Just continue the thought...
Try:
SELECT A_data, B_data, C_data
FROM A LEFT JOIN B ON A.A_ID = B.B_ID LEFT JOIN C ON A.A_ID = C.C_ID
WHERE A.A_ID = 4;
> -Original Message-
> From: sean peters [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, October
ng/returning so many rows that
> it's going to take a long time to create the needed temporary
> table (how long have you it go?). To start with, show us the
> EXPLAIN output for the problem SELECT, along with the SELECT.
> Also the size of the involved tables (rows and MB).
>
>
So I
then assume that the 'problem' is happening before the file is created?
Does that sound right?
If someone has a better reference for tracking down the source of a
problem like this, other than
http://www.mysql.com/doc/en/MySQL_Optimisation.html, I'd be very
appreciative.
thanks,
Kevin Fries
Can we 'unanalyze' a table? I'd like to remove the statistical
information from my analyzed table, for testing purposes.
Thanks
Kevin
I agree it's unfortunate that the dates get stored. But some do seem to
prefer it this way.
To quote the manual at the bottom of:
http://www.mysql.com/doc/en/Using_DATE.html
If the date cannot be converted to any reasonable value, a 0 is
stored in the DATE field, which will be retrieved
ALTER TABLE tmp DROP COLUMN col_1, DROP COLUMN col_2, DROP COLUMN col_3,
DROP COLUMN col_4;
Or, for short,
ALTER TABLE tmp DROP col_1, DROP col_2, DROP col_3, DROP col_4;
> -Original Message-
> From: Adam Fortuno [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, August 12, 2003 6:22 PM
> To: My
I agree it's probably not possible with one query, unless you have 4.1
for subqueries.
One thought is to use variables:
SELECT @m:=max(ID) from A;
SELECT @m1:=max(ID) from A WHERE ID < @m;
SELECT fields FROM A
LEFT JOIN B ON B.aID = A.ID WHERE A.ID in ( @m, @m1) ORDER BY A.ID
DESC;
> -Or
> Sometimes, movies have more than one director, so the
> association between movies and directors needs to be in its
> own table, instead of the movies table. (Otherwise, you're
> limited to some fixed number of directors per
> film.)
>
> Directors may not be the best example. Think about prod
Try changing "JOIN list_states " to "INNER JOIN list_states "
Looks like the earlier version didn't like your (synonymous) wording.
> -Original Message-
> From: Jack Dare [mailto:[EMAIL PROTECTED]
> Sent: Thursday, August 14, 2003 8:35 AM
> To: [EMAIL PROTECTED]
> Subject: Join syntax di
I don't know of any way to sort before the grouping. That would be
peculiar, since the GROUP will be eliminating potentially many of those
rows.
In your case, a simple solution should be:
select jobnum, min(milestone), min(shipdate) from jobs
group by jobnum
order by 2, 3;
I've always found it
So you want to group by the customer, but only show those gorupings with
a count > 5.
That means you want to apply your restriction after the GROUP BY. Thus,
the clause goes into the HAVING area.
Try:
Select count(*), cust.id
from cust
where cust.time > UNIX_TIMESTAMP(DATE_SUB(NOW(),interval 1 HO
Not surprising, the dbms has no built-in support for ordering rows by
the filters in the where clause.
But with a bit of programming you should be able to construct a useful
ORDER BY clause to do what you want...
SELECT * FROM company WHERE id='3' OR id='1' OR id='7'
ORDER BY CASE id when '3' then
Easy:
SELECT id FROM sales
GROUP BY id
HAVING SUM( totalsales) <= 2 ;
That will perform the grouping, by ID, then filter and display only
calculated rows with a sum less than 2.
> -Original Message-
> From: Yoed Anis [mailto:[EMAIL PROTECTED]
> Sent: Monday, July 28, 2003 10:22
To perform a self-join you just have to alias one or both tables.
In your case, you want to join on ( I guess) the SLM + CUST + ITEM,
restricting the tables to, respectively, 2003 and 2002. Further, you
want to filter out any rows where there's a match in 2002.
I believe the following does it:
s
Yes. Use an exclusive outer join (my term, not official.)
SELECT username from tableA left join tableB on tableA.id = tableB.id
WHERE tableB.id IS NULL;
This will first form an outer join, building a set of rows from tableA,
regardless of having a matching record in tableB. Then the WHERE claus
If you are looking for ways to retrieve and store hierarchical data
(employees and managers, for instance)
I'll also recommend looking at the Nested Set hierarchy. Search news
groups for it, and look into Joe Celko's book _SQL For Smarties_, which
describes the technique. You can store a complete
The standard solution is to use binding.
JDBC and Perl DBI both support it, and I imagine most other environments
do as well.
In such case, your query will turn from:
"INSERT INTO someTable values ('foo\'')"
Into:
"INSERT INTO someTable values (?)"
You're use a prepared statement, and before
I think you can get those rows that have multiple colors, if you know
the list of colors to check for...
Maybe this can help...
SELECT itemID
from theTable
Where colorID in (1,2,3,4,...N)
Group by itemID
Having count(*) = N;
This will only return the itemID if it appears in all of the colors
lis
In InnoDB you *must* put an index on foreign keys. It doesn't do this
for you automatically.
You can do it in one statement:
create table bar (
bar_idint unsigned auto_increment,
foo_idint unsigned,
bar_value int,
constraint bar_pk primary key (bar_id),
index (foo_id),
e the LFT/RGT values to make
room for a new node can be done in one update statement, even in mysql.
So it's tricky to write, but very simple to operate.
Kevin
-Original Message-
From: Grégoire Dubois [mailto:[EMAIL PROTECTED]
Sent: Thursday, April 03, 2003 6:28 AM
To: Kevin Fries
Cc: [EMAI
My advice is to consider an alternate storage approach.
Instead of storing parent id's, store the trees using the Nested Set
model.
http://www.dbmsmag.com/9603d06.html
About halfway down the article he presents a model where you never store
the parent id, but you do store two columns (LEFT and RI
To find duplicates, use something like:
SELECT address, count(*)
>From Customer
GROUP BY address
HAVING count(*) > 1;
-Original Message-
From: Bob Sawyer [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 01, 2003 12:04 PM
To: MySQL List
Subject: Opposite of DISTINCT()
I know that using SE
One of our servers (of many running the same software and mysql version) is
frequently getting
"ERROR 1030: Got error 127 from table handler"
on two of its tables.
mysql version is 3.23.49.
We can not recover with myisamchk -r, but instead have to use "myisamchk -o"
which ends up wipin
Chris,
sounds like you're looking for an exclusive left outer join.
You want to see records from monitorhosts, where there is no corresponding
record
in monitorhostgroupdetails, right?
Use:
Select *
from monitorhosts left join monitorhostgroupdetails on
monitorhosts.HostID = monitorhostgroup
nt,
> >i.name
> > FROM member_interests AS mi left join outer interests AS i on
> > mi.interest_id = i.id
> > GROUP BY mi.interest_id
> > ORDER BY i.name
>
> =C=
>
> *
> * Cal Evans
> * Journeyman Programmer
> * Techno-Mage
> * http://ww
I have a reply for both Cal and Javier,
Cal,
I have a hunch the JOIN is backward for you.
> SELECT DISTINCT(mi.interest_id), COUNT(*) AS count,
>i.name
> FROM member_interests AS mi left join outer interests AS i on
> mi.interest_id = i.id
> GROUP BY mi.interest_id
> ORDER BY i.name
sing (saving the need for temporary tables).
>
> you could also plug the 10..11 20..21 etc statements into a
> perl foreach
> loop (if you were using perl) to automatically create the
> inner numbers.
>
> Ric
>
> p.s. maybe there is a way of tricking it into using a having
>
--+
Note here that there's a gap between 9 and 12, between 19 and 22, and
between 22 and 24.
There's also a gap before 1, and one after 30, but this just tells us where
the range ends.
nulled Outer joins are very handy.
Kevin Fries
> -Original Message-
> From: mos [m
fit about any hierarchy
you've got.
http://searchdatabase.techtarget.com/tip/1,289483,sid13_gci537290,00.html?Fr
omTaxonomy=%2Fpr%2F282457
Kevin Fries
> -Original Message-
> From: David yahoo [mailto:[EMAIL PROTECTED]]
> Sent: Monday, March 25, 2002 3:39 PM
> To:
les optimized away |
+--+
1 row in set (0.00 sec)
mysql> SELECT min(col4), max(col4) from sometable;
+---+---+
| min(col4) | max(col4) |
+---+---+
| NULL | 5 |
+---+---+
1 row in set (0.00 sec)
--
Kevin Fries
lumn) FROM
SomeTable
I just want to make sure we're recommending a good version.
If stability/known-bugs can be found in a faq somewhere to answer this
question, please
let me know!
thanks!
Kevin Fries
-
Before posting, please
41 matches
Mail list logo
