SET @tz=@@session.time_zone ;
SET SESSION time_zone = '+0:00' ;
SELECT DATE_FORMAT(
FROM_UNIXTIME(1409304102.153) /*your epoch column here*/
,'%Y-%m-%d %a %H:%i:%s.%f GMT');
SET SESSION time_zone = @tz ;
2014-08-29 Fri 09:21:42.153000 GMT
(or)
SELECT DATE_FORMAT(
No, not unique to PostgreSQL. Microsoft SQL Server has the OUTPUT
Clause. The major difference is MS has an extra feature that allows the
OUTPUT or some form of the output to be reused in yet another INSERT.
This would allow you to DELETE a block rows from table and insert them
into a work table
It looks like LIKE is only slightly faster(on my XP), hardly worth
mentioning. Go with what is easier for you to read or for portability if
you need it. IMHO
set @a='gfdueruie baz hdhrh';select BENCHMARK(500, (select 1 from
dual WHERE @a LIKE '%foo%' OR @a LIKE '%bar%' OR @a LIKE '%baz%')) as
I think what you want is CREATE VIEW test.v AS SELECT * FROM t;
http://dev.mysql.com/doc/refman/5.0/en/create-view.html
Ed
-Original Message-
From: Martijn Tonies [mailto:[EMAIL PROTECTED]
Sent: Friday, August 01, 2008 6:34 AM
To: mysql@lists.mysql.com
Subject: Re: Table aliasing
I'm not aware of MySQL supporting this feature. Microsoft does and calls
it common table expression (CTE). The UNION is necessary as this the
part that links the anchor query, Part1 of the UNION to the recursive
query, Part2. Part2 of the UNION must reference the produced temporary
table called
To change the value of the AUTO_INCREMENT counter to be used for new
rows, do this:
ALTER TABLE t2 AUTO_INCREMENT = value;
You cannot reset the counter to a value less than or equal to any that
have already been used. For MyISAM, if the value is less than or equal
to the maximum value
Yes, for a MyIsam type table.
Ed
-Original Message-
From: Jason Pruim [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 29, 2007 11:53 AM
To: emierzwa
Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com
Subject: Re: Reset a auto increment field?
If I understand you correctly, if my table
You might try:
INSERT INTO table_track select OLD.*;
-Original Message-
From: Olaf Stein [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 09, 2007 2:59 PM
To: MySql
Subject: Trigger OLD question
Hi All,
I have a table with 205 columns. When an update statement updates a row
in
this
Perhaps you can add the sql command version of myisamchk to the server
init file to run at startup. Where a line in this file was: myisamchk
tbl_name
[mysqld]
init_file=/path/to/data-directory/mysqld_init.sql
Ed
-Original Message-
From: Payne [mailto:[EMAIL PROTECTED]
Sent: Tuesday,
I agree, you should just update it since the standard operation for
MYSQL is
to only apply updates if the value is changing.
http://dev.mysql.com/doc/refman/5.0/en/update.html
If you set a column to the value it currently has,
MySQL notices this and does not update it.
Ed
-Original
It is expected behavior, you can make the unique key a primary key
instead. This should prevent this situation.
Ed
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Monday, December 11, 2006 7:42 AM
To: mysql@lists.mysql.com
Subject: UNIQUE KEY vs NULLs
Hi,
I
Try this...
select a.title, group_concat(c.name SEPARATOR ' ') as name
from album a
join albumartist b on b.albumid=a.albumid
join artist c on c.artistid=b.artistid
group by a.title;
Ed
-Original Message-
From: James Eaton [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November
What are your wait_timeout and/or interactive_timeout values set to?
Does the server crash and auto restart? Check server's up time.
Do both servers have the exact table schema? Same column datatypes and
indexes to be specific.
Although your data volumn may be similar, can the actual data be
detection of invalid constant expressions. MySQL quickly detects
that some SELECT statements are impossible and returns no rows.
Ed
-Original Message-
From: Tripp Bishop [mailto:[EMAIL PROTECTED]
Sent: Wednesday, January 04, 2006 11:01 AM
To: emierzwa; mysql@lists.mysql.com
Subject: RE
An error is only thown during an INSERT or UPDATE statement, otherwise
you just get a warning.
- ERROR_FOR_DIVISION_BY_ZERO
Produce an error in strict mode (otherwise a warning) when we encounter
a division by zero (or MOD(X,0)) during an INSERT or UPDATE. If this
mode is not given, MySQL
The document says it evaluates the 'expr', so I'm guessing it is only
function calls and arithmetic evaluation. Although I have tried selects
without any luck. It could probably be documented a little more clearly,
or event better yet...a supported feature.
Ed
-Original Message-
From:
I believe the (16) is the size of offset pointer used to locate the text
data elsewhere on the disk. They are all (16 bits)...Sybase does this as
well and is an odd (ok it's stupid) notation. This is one of those
things you need to adjust by hand when convert from one db to another.
Ed
This is the old syntax for OUTER JOIN. Not very ANSI...
select a.userID
from USers a
left join UserGroups b on b.UserGroupID=a.UserGroupID;
-Original Message-
From: Rajesh Mehrotra [mailto:[EMAIL PROTECTED]
Sent: Monday, August 22, 2005 7:50 AM
To: John c;
I beleive the below solution will not detect rows missing from the very
top of the table,
try this...
select ifnull((select max(a.test_id) +1
from tests a
where a.test_idb.test_id),1) as 'from'
,b.test_id -1 as 'to'
from tests b
left outer join
I believe the conflict here is the OR. Try this...
select * from table
where field1 = 'VALUE1' and field2 like 'VALUE2%'
union
select * from table
where field3 = 'VALUE1' and field2 like 'VALUE2%'
Currently, MySql can't utilize two indexes on the same table at
the same time but it is on
You could leave your separate tables by region and create a MERGE
table over them. Your application only needs to reference the MERGE
table name, eliminating the need for your recompiles and MySql will
Handle the underlying tables. You can add or remove tables from the
MERGE at any time. I did
If you're concerned about subtle data type differences you can also
run one show create table myMergeTable
statement for each source table and diff them.
Ed
-Original Message-
From: mos [mailto:[EMAIL PROTECTED]
Sent: Friday, June 10, 2005 8:35 AM
To: mySQL list
Subject: Re: MERGE
Not every DBMS...
MSSQL:
Create Unique Index
Microsoft(r) SQL Server(tm) checks for duplicate values when the index
is created (if data already exists) and checks each time data is added
with an INSERT or UPDATE statement. If duplicate key values exist, the
CREATE INDEX statement is canceled and
Martin, Shawn, you are correct. An oversight on my part...this is why I
still follow this list, I am always able to learn something and never
cease to be humbled.
Ed
-Original Message-
From: Martijn Tonies [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 26, 2005 8:01 AM
To: emierzwa
What you describe makes sense and would certainly work, I don't know
that I would call it a temporal solution. The ENUM (I, U, D) seams a bit
redundant with time. This model resembles a traditional application log
or trace file, which is highly desirable for a records keeping system,
like for a
There was a magazine Database Programming Design, now defunct...too
much detail for the average IT Manager so it didn't sell well...that did
a nice multi-part series on the subject. Including the SQL3 connection.
I could only locate a couple of fragments online...it used to be all
online if you
You could do something like this, not sure what your intent is if among
the top total_amt is a single exact amount that occurred 30 or 40
times...are you implying the top 10 items or the top 10 distinct items?
select *
from table_z a
where 10=(select count(*)
from table_z b
You didn't mention you release number, but on 4.1 you can use LOAD
INDEX INTO CACHE. You can also use options like --init-file and enter
your startup sql there for your warmup.
Ed
-Original Message-
From: Andy Eastham [mailto:[EMAIL PROTECTED]
Sent: Friday, September 10, 2004 7:36 AM
Maybe MyISAM is still a better choice for this use...?
For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary
column (or three columns in your case) in a multiple-column index. In
this case, the generated value for the AUTO_INCREMENT column is
calculated as
The general rule of thumb (in Sybase not sure for MySQL), is if using an
index hits more than 10% of the tables total volume it is
faster/cheaper/less evasive on the drive to just table scan the table
opposed to read an index row, get the representing table row pointer and
seek the table for each
You might save some space if you compress() before storing. Depending on
file content I'm seeing 0-50% savings?
select length(load_file('c:/temp/SomeFile.pdf')) as old_size
,length(compress(load_file('c:/temp/SomeFile.pdf'))) as new_size
Ed
-Original Message-
From: Michael Stassen
Found this at http://dev.mysql.com/doc/mysql/en/BLOB.html, basically
it's not stored in the table row along with any other columns in the
same row.
12.4.2 The BLOB and TEXT Types
Each BLOB or TEXT value is represented internally by a separately
allocated object. This is in contrast to all other
: Thursday, June 17, 2004 8:36 AM
To: emierzwa
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: Where are BLOBs / TEXTs stored?
I am curious about this, too. However, I don't think that you answer the
original question.
Are BLOBs stored as separate files, one file per object? Are they
combined
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 17, 2004 9:00 AM
To: [EMAIL PROTECTED]
Cc: emierzwa; [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: Where are BLOBs / TEXTs stored
Here's one more quote, it is more relative to Alec's comment/concern
about access speed.
It is unlikely that the 'field and offset are on different pages'
unless the record contains a large BLOB.
Ed
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
Yes, you can save using multiple statements. We send
hundreds...thousands of statements in a single batch in our machine
automation application. The communication cost is relatively fixed (with
in reason), where the actual work to be done (mostly inserts) was very
fast for us in our table schemas.
Assuming both scenarios only had one connection open for all the
statements,
you probably will see a savings. You will need to benchmark your
situation.
You will need MySQL 4.1 or greater and your ODBC driver will need to
support
this version as well.
Ed
-Original Message-
From: Paul
Since your on 4.1, give this a try...
select *
from tbl as a
where a.recdate=(select max(b.recdate)
from tbl as b
where b.id=a.id and b.mount=a.mount)
Ed
-Original Message-
From: Duncan Hill [mailto:[EMAIL PROTECTED]
Subject: Re: Query help with
You can't do efficient ranging on a HASH indexed column without a table
scan...
Select * from heap_table where indx_col between 247 and 258
This would table scan your 200meg table even if indx_col is a PRIMARY
KEY, using the default hash key. Hash key was the only choice prior to
4.1, but now
MERGE tables have a table_name.MRG that contains pathed names to the
original table location. You can carefuly hand edit these or run an
ALTER command to fix them.
ALTER TABLE merge_table_name UNION=(table_1,table_2...)
Ed
-Original Message-
From: Ed Reed [mailto:[EMAIL PROTECTED]
I beleive this is the built-in word list file you were looking for. And the
word beyond was in the list. It would probably be helpful if there were
a SHOW command that listed the active built-in stopwords.
Ed
-
#include ftdefs.h
ulong ft_min_word_len=4;
ulong
My guess is because 200400 is an illegal time. If you try a
legal time, January 1, midnight (2004010100) it should work.
You can also convert the time to integer and then compare values.
select last from users where last 2004010100
select last from users where cast(last as
Silently ignore duplicate index inserts...
insert IGNORE into PERSON ( NAME ) values ( 'Test')
Ed
-Original Message-
From: Gerhard Gaupp [mailto:[EMAIL PROTECTED]
Hello
I'm looking for something like
if not exists( select 1 from PERSON where NAME = 'Test' ) then
insert into PERSON
James, I replied to you back in January. I tried it with an 854kb WKT
block with out any problems. You had sent me a couple WKT samples
offline, each of which had errors in them. After I corrected them they
worked for me. If you would like to try once more, send me a file,
offline, of your table
This will do it...
SELECT id, group_concat(distinct buddy_id)
FROM buddies
GROUP BY id
Ed
-Original Message-
Hi,
When using aggregate functions, I know you can retrieve the MAX, MIN,
SUM, etc from all the values in your specific group from the GROUP BY.
Is there any function to
Try this...works on 4.1.2
UPDATE ControlTable SET NextID= 1 + (select @prevval := NextID)
Ed
-Original Message-
From: Sasha Pachev [mailto:[EMAIL PROTECTED]
Sent: Monday, February 23, 2004 11:19 AM
To: Matt Fagan
Cc: [EMAIL PROTECTED]
Subject: Re: Setting variables on update
Matt
Sure, try this. I'm using 4.1.2 in case it matters.
set @n=0;
UPDATE Ranks_table
SET Rank= (select @n:[EMAIL PROTECTED])
ORDER BY Score DESC;
Ed
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Monday, February 09, 2004 6:37 PM
To: [EMAIL PROTECTED]
Subject:
You could try the following:
1) Perform normal backup.
2) Run sql command flush status. --Resets most status
variables to zero.
3) Next week prior to backup, run sql commands:
show status like 'Handler_delete'
show status like 'Handler_update'
show status like
One more consideration, if your 5mil of records contains duplicates you
won't get the opportunity to load the table using REPLACE or INSERT
IGNORE without the UNIQUE index already in place. You would have to
distinct the rows first or manually remove the duplicates before
creating the index after
Paul wrote:
Is there such a thing as an index on the first 8 characters
of a DATETIME column?
No. Indexes on partial column values are available only for string
types.
Won't PACK_KEYS=1 help in a situation like this?
From the Documentation:
6.5.3 CREATE TABLE Syntax
If you don't use
You could just use the benchmark function?
select BENCHMARK(1000, 'dfsfsdfs' like 'F%' ) /* 0.45 sec. */
select BENCHMARK(1000, 'dfsfsdfs' between 'F' and 'Fzzz' ) /* 0.55
sec. */
select BENCHMARK(1000, left('dfsfsdfs',1)='F' ) /* 0.79 sec. */
The times go up a little if the
I'm on WinXP, 2.6ghz. mysqld-nt Alpha 4.1.2
Either way, I was surprised to see the like to be in the top
performers and left() to be last.
Ed
---
* ed aka emierzwa at micron.com
You could just use the benchmark function?
select BENCHMARK(1000, 'dfsfsdfs' like 'F
I tried it with an 854kb WKT block with out any problems. A total of 64k
points in fact. Two things, did you correctly close the polygon (first
and last points must be the same). Did you use the astext() function to
display the polygon in your select statement?
CREATE TABLE big (
g geometry NOT
Using correlated subqueries on 4.1 you could do it all in sql:
SELECT ProductId, MarketId, Type, Price
FROM Products a
WHERE 3=(SELECT count(*) --finds highest prices
FROM Products b
where b.ProductId=a.ProductId
AND b.MarketId=a.MarketId
AND
Using your exact dataset listed at the bottom, I tried using derived
tables to combine your implementation under Alpha 4.1.1 as a single
statement. I expected a message saying it was to complex or out of some
resource but the server goes to 100 percent cpu and I have to kill it. I
even got it to
55 matches
Mail list logo
