Dear Sirs and Ladies,
I tried to put some keys on a large table (more than 100.000.000 in 13G)
with the Statement:
ALTER TABLE mytable
ADD INDEX i1 (COL1(4)),
ADD INDEX i2 (COL2(4)),
ADD INDEX i3 (COL3(12)),
ADD INDEX i4 (COL4(12)),
ADD INDEX i5 (COL5(5)),
ADD INDEX i5 (COL6(11));
After about 7
Traditionally, I'd take the following approach
1) Update - assume it's there
2) If update fails (0 rows) do an insert
Thats assuming that the update case is more prevalent. If the row is
unlikely to be there - insert and do the update if you get a duplicate key.
Rgds
Mike
-Original
Hi All,
I'm using mysql as my database. I have a table which has the following
colums:
idx
productname
value
My question is how do i display and select all the data that has a value of
more than 915.
Hope you can help.
Regards,
Delz
--
MySQL General Mailing List
For list archives:
The REPLACE statement is very dangerous since it first DELETEs the row if
found.
Instead you should use :
INSERT . ON DUPLICATE KEY UPDATE
Check the manual for clearer syntax
But this one will work if the INSERT would create a Duplicate Key entry (I
mean, would violate the index or
Andreas,
What you're looking for is a syntax like CASE WHEN ... JOIN I think
that's not in SQL99 (just off the top of my head, such a syntax would seem
likely to defeat any optimiser). You're going to have to (i) store the
results of an initial query in a temp table and work from there, or
I think it is:
SET @a:=0; /* SELECT would return the value. Of course it would work but
*/
SELECT @a:[EMAIL PROTECTED], blah, blew from table where blah 1;
Thanks
Emery
- Original Message -
From: Diana Soares [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, October 16, 2003
SELECT *
FROM tablename
WHERE value 915
-Original Message-
From: delz [mailto:[EMAIL PROTECTED]
Sent: Friday, October 17, 2003 9:46 AM
To: [EMAIL PROTECTED]
Subject: Help in querying
Hi All,
I'm using mysql as my database. I have a table which has the following
colums:
idx
Hi group,
Someone asked a question about how he could change a column name and have his old
application continue using the same table while he is updating the client.
This means:
He has a table with columns: ColA, ColB and he is forced to change the columns names
into ColX, ColB and possibly
Hello,
I am new to mysql trying to learn by following the instructions from a trainning book.
While following the instructions from the book that I am using I got the following
error message, without the quotes, Column count doesn't match value count at row 1.
I entered the following command,
Me, I will say that such a function should be added as a feature request
since it doesn't exist.
MySQL 4.1.1 introduces MAKE_DATE()
The following can be read from
http://www.mysql.com/doc/en/Date_and_time_functions.html
MAKEDATE(year,dayofyear)
Returns a date, given year and day-of-year values.
What if you want it to listen to two or three interfaces (I mean not all
interfaces but on more than one interface).
Can one separate the addresses by Commas? Each on a new line???/
Thanks
Emery
- Original Message -
From: Matt W [EMAIL PROTECTED]
To: Dan Jones [EMAIL PROTECTED]; [EMAIL
Hello,
Peter Brawley schrieb am Freitag, 17. Oktober 2003 um 10:01:
What you're looking for is a syntax like CASE WHEN ... JOIN I think
that's not in SQL99 (just off the top of my head, such a syntax would seem
likely to defeat any optimiser). You're going to have to (i) store the
Jep,
Hello,
Roger Baklund schrieb am Freitag, 17. Oktober 2003 um 01:56:
I think you need the LEFT JOIN:
URL: http://www.mysql.com/doc/en/JOIN.html
I thought about that before, but how to catch up the problem, that I
have different tables where I have to get the ProdID and the
StationID?
Andreas
Try naming the columns in the insert explicitly, like so:
insert into `mysql`.`host`
(Host, Db, Select_priv, Insert_priv, Update_priv, Delete_priv,
Create_priv,
Drop_priv, Grant_priv, References_priv, Index_priv, Alter_priv
)
values
Starting from MySQL 4.0.., you have two new privileges named
Create_tmp_table_priv AND Lock_tables_priv, that's the reason, why you
aren't able to enter the values without specifying column name.
You can either specify column names like Rob told you or give two more
values in your syntax
That error means that the number of columns in the table is differnt from
the number of columns you are sending for insert.
For my system (4.1.0) I have got 14 fields.
For your query, you are trying to insert 12 fields.
Normally this should work even if the the number of fields is different and
Ganbold [EMAIL PROTECTED] wrote:
I'm having some trouble running one query. I'm using FreeBSD 4.8 with
linuxthread enabled mysql-4.0.14.
Server has 1GB ram and SCSI hard disk.
I need to get size of email message which is stored in MyISAM table.
The problematic query is:
select
Egor,
Result of explain:
mysql explain select sum(size) from message where uid='2945';
+-+--+---+--+-+---+--+-+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
Hi,
You can see the table structure with the command:
desc host;
after logging in to mysql using the table mysql
Greetings,
Richard
- Original Message -
From: Gregory Norman [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, October 17, 2003 10:31 AM
Subject: error message
* Andreas Ahlenstorf
Roger Baklund schrieb am Freitag, 17. Oktober 2003 um 01:56:
I think you need the LEFT JOIN:
URL: http://www.mysql.com/doc/en/JOIN.html
I thought about that before, but how to catch up the problem, that I
have different tables where I have to get the ProdID and the
Normally it's a good habit to explicitly name the columns.
But it also works whithout naming them in the INSERT instruction when you
are inserting exactly in all the columns.
Thanks
Emery
- Original Message -
From: Gregory Norman [EMAIL PROTECTED]
To: Director General: NEFACOMP [EMAIL
[EMAIL PROTECTED] wrote:
Do you talk about MySQL users or about users of the typo3?
Typo3 users. I setup typo to send error messages, and I get this:
There has been numerous attempts (4) to login at the Typo3
site typo3 server (www.wals.lib.wi.us).
But this problem is not related to the
Mike Knox wrote:
Traditionally, I'd take the following approach
1) Update - assume it's there
2) If update fails (0 rows) do an insert
Thats assuming that the update case is more prevalent. If the row is
unlikely to be there - insert and do the update if you get a duplicate key.
You have
IMPOSSIBLE DE RECEVOIR MON COURRIER SUR HOTMAIL DEPUIS AU MOINS UNE
SEMAINE...??
[EMAIL PROTECTED] MERCI DE VOTRE COMPRÉHENSIONANDRÉ THIBAULT...QUÉBEC.
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi
Ive been searching mysql.com documentation and the web for the subject but
not any usefull info. I want to setup a server running a NPTL capable
kernel (RedHat9 kernel + XFS patches) and a statically compiled mysqld
binary (my ideea is to not
Why would the REPLACE statement not work?
Simon
(ps mite have missed some thing if so sorry)
-Original Message-
From: gerald_clark [mailto:[EMAIL PROTECTED]
Sent: 17 October 2003 14:08
To: Mike Knox
Cc: '[EMAIL PROTECTED]'
Subject: Re: Check for data before inserting
Mike Knox wrote:
I didn't say it wouldn't.
I was pointing out the danger of the method listed.
Also, a danger with replace is that multiple records might be deleted
for one insert if there are multiple unique keys in the table.
Insert and replace statements will error out with the appropriate status.
Simon Green
Je ne vous comprends pas
- Original Message -
From: André Thibault [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, October 17, 2003 15:01
Subject: Hotmail
IMPOSSIBLE DE RECEVOIR MON COURRIER SUR HOTMAIL DEPUIS AU MOINS UNE
SEMAINE...??
[EMAIL PROTECTED] MERCI DE
REPLACE replaces the row (as in English).
That means it first DELETEs the row before inserting!!! Very dangerous.
Use INSERT ... ON DUPLICATE KEY UPDATE ...
Thanks
Emery
- Original Message -
From: gerald_clark [EMAIL PROTECTED]
To: Simon Green [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
FYI, as I stated in a previous email, INSERT . ON DUPLICATE KEY
UPDATE is only available in MySQL 4.1+, which is still in
development and not recommended for production use.
- Gabriel
On Friday, October 17, 2003, at 04:04 AM, Director General: NEFACOMP
wrote:
The REPLACE
Hi All,
I have been trying to assist in a replication problem with Solaris. Seems the
problem came down to using an IP address in the CHANGE MASTER command. The
Slave would not connect properly to a master defined by IP address, but when
the IP was out in a hosts file and the hostname
Hi group,
I wanted to start learning programming in JAVA.
What do you think is the right discussion group or mailing list for starters?
I have never touched JAVA.
Can someone advise on what software I should install?
Thanks,
__
NZEYIMANA Emery Fabrice
NEFA
Hi
what's the max table size we can use with mysql-max version?
Suggestions?
Thanks.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
It depends on your filesystem's and OS's max-size of a file in its
filesystem.
HOWEVER, there are a few ways you can get around this.
You can use InnoDB tables, or read about RAID types here:
http://www.mysql.com/doc/en/CREATE_TABLE.html (almost at the bottom of the
page).
You can also use
Hi,
I'm new to the list and I am just wondering..
Why don't you guys use a fixed font when pasting mysql queries..
Wouldn't it better to see something like this 'Monaco font on Mac OS X':
mysql explain select sum(size) from message where uid='2945';
I dunno, the font seems fairly fixed when I view it in pine
--ja
On Fri, 17 Oct 2003, Jerry Rocteur wrote:
Hi,
I'm new to the list and I am just wondering..
Why don't you guys use a fixed font when pasting mysql queries..
Wouldn't it better to see something like this 'Monaco font
Set your mail reader to use a fixed font.
That is where the problem lies.
Jerry Rocteur wrote:
Hi,
I'm new to the list and I am just wondering..
Why don't you guys use a fixed font when pasting mysql queries..
Wouldn't it better to see something like this 'Monaco font on Mac OS X':
--
MySQL
At 1:55 +0200 10/17/03, Kim G. Pedersen wrote:
Hello
I looking for a way to convert a datestring to date value
example
UnknowFunction('23.03.68','dd.mm.yy') - unixtimestamp
In oracle we have
to_date('23.03.68','dd.mm.yy')
I have search the net for hours without luck.
There will be a function
Agreed. Most emails are sent to the list in plain text format, which
does not include and font or style information. You could do that with
HTML formatted emails, but I imagine a large number of people would
dislike reading HTML email if their mail client cannot render it.
- Gabriel
On
Here's a good-free book, considered to be one of the best for learning Java:
http://mindview.net/Books/TIJ/DownloadSites - Thinking In Java, by Bruce
Eckels.
The key component is the Java Developers Kit - it has the Java Compiler,
plus the runtime engine that all Java code runs under (JVM, or
It is my understanding that at least through 4.0.14, INNODB does not
support using autoincrement on the last field in a multi field primary
key.
i.e. if a table has a primary key of three fields like
cpny_ID, acct_ID, list_ID
in MYISAM you can add the autoincrement attribute to
On Friday, October 17, 2003 6:38 PM CEST, Gabriel Ricard wrote:
Agreed. Most emails are sent to the list in plain text format, which
does not include and font or style information. You could do that with
HTML formatted emails, but I imagine a large number of people would
dislike reading HTML
Can anyone tell me what happened to GEMINI?
We've been customers of MySQL for a while and about two years ago
started using Nusphere's version to take advantage of their row level
locking/ACID transaction safe table type GEMINI. Things went well for a
while but one day I came back for help and
Hi,
I'm still learning on this. Pls. excuse me if I simply overlooked
something.
Originally I had this:
?
// list matches
while (list($fid, $title, $lname, $fname) = mysql_fetch_row($result))
{
echo table border=2 width=400 cellpadding=2 cellspacing=2tr;
echo td width=200a
My mistake, it was a setting in my mailer...
Thanks to Dave Christensen for putting me on the right track..
Sorry for the bandwidth!
Jerry
On Friday, Oct 17, 2003, at 18:26 Europe/Brussels,
[EMAIL PROTECTED] wrote:
I dunno, the font seems fairly fixed when I view it in pine
--ja
On
Hello, my name's Marlon. I have a question about sql and I need some help!
How can I do something like it using mysql?
update registre set (name='NewName' where lastname='OldLastName'),
(name='OldName' where lastname='NewLastName');
Tank you
Marlon
Hello, my name's Marlon. I have a question about sql and I need some help!
How can I do something like it using mysql?
update registre set (name='NewName' where lastname='OldLastName'),
(name='OldName' where lastname='NewLastName');
I _believe_ you can do it this way. I'm sure someone
I am not very familiar with php, but why are you using an echo within an
echo statement?
echo td width=200$fnamenbsp;$lname/tdtd
width=200$title/tdtdinput type=hidden name=fid value=? echo
$fid; ?input type=submit name=submit value=Choose this
faculty/td;
why not try this
echo td
I have a database where I want to count the number of days where a field gets to a
certain value. Right now I am using:
SELECT count(DISTINCT aqiValues.readingDate) as greenCount
FROMaqiRegions, aqiSites, aqiValues
WHERE aqiRegions.aqiRegionID = aqiSites.aqiRegionID AND
Thanks!
That helped me get pass that error. However, maybe my logic is wrong, when I
clicked the Submit button, it took me to the detail page of the last search
result. I figured I'd need to move that input tag for the hidden value
somewhere instead. Tried a few places but still didn't work.
any
To make this back to being a MySQL question
What if you used the encode or md5 functions when you retrieve the list of results,
and then do your lookup on the item details by matching it to decoding the same id?
ex. get list-
select fid as open_fid, MD5(fid) as crypt_id
from your_table
Hello!
I am new to the world of Linux and MySQL, I have been working with it for
about a month now. We decided to upgrade the 4.1Alpha instead of the
current release because we wanted to test sub Select statements.
After having some problems and hosing up the Red Hat machine, I
Thanks millions Dan! That's exactly what I need. And I used your method and it worked
beautifully!
I'm so excited! Thanks again!
Jordan
Dan Greene wrote:
To make this back to being a MySQL question
What if you used the encode or md5 functions when you retrieve the list of results,
Hi Ganbold,
It's taking 35 seconds because it has to look at 2000+ [large] rows in
the data file to get the value of size. If the index is just on uid,
remove it and make a composite index on (uid, size):
ALTER TABLE message DROP INDEX uid,
ADD INDEX uid_size (uid, size);
It should then be
Hello
Just wanted to let anyone interested that after almost 6 years,
http://www.weberdev.com
now has a new brother (sister?) site called http://www.weberblog.com.
This new site
is a community site, where matters related to PHP MySQL are discussed.
There are
already a few interesting blogs
Hello,
Roger Baklund schrieb am Freitag, 17. Oktober 2003 um 14:24:
SELECT IF(ISNULL(d.ProdID),d2.status, d.status) AS status
FROM Picklists AS a
LEFT JOIN PicklistData AS b ON
a.ID = b.Picklist
LEFT JOIN ProdID AS c ON
b.EAN = c.EAN
LEFT JOIN EncodingData AS d ON
* Andreas Ahlenstorf
Roger Baklund schrieb am Freitag, 17. Oktober 2003 um 14:24:
SELECT IF(ISNULL(d.ProdID),d2.status, d.status) AS status
FROM Picklists AS a
LEFT JOIN PicklistData AS b ON
a.ID = b.Picklist
LEFT JOIN ProdID AS c ON
b.EAN = c.EAN
LEFT JOIN
Hi,
I am trying to select the first 10 rows of a table by the date the
record was created. I have a date field in the table and I tried using
the TOP syntax of SELECT, but I keep getting syntax error.
SELECT TOP 10 DateCreatedField
FROM my_table
ORDER BY DateCreatedField DESCENDING
Does MySQL
MySQL doesn't support TOP, however, there's a LIMIT syntax for MySQL that
roughly does the same thing:
http://www.mysql.com/doc/en/SELECT.html for a brief explanation.
In your example what you are looking for is:
SELECT DateCreatedField
FROM my_table
ORDER BY DateCreatedField DESC
LIMIT 10;
Hi Group,
Does anyone have a good site that contains the current SQL standard? Or
does anyone know is ISNULL() and IFNULL(), and similar null testing
functions, standard SQL or extensions to SQL?
Thanks! I'm porting from Sybase SQLAnywhere to MySQL and just found what
worked in Sybase (ISNULL())
Hi,
I want to backup a MySQL 3.23 database to upgrade it to MySQL 4.0. My tables
are InnoDB and they have foreign keys. I would like to know how to backup
it, because it seems that neither mysqldump nor mysqlhotcopy can be easily
used.
The restore script created by mysqldump contains the
I desperately need everyone's help.
I normally build database driven websites on NT Servers with an MS Access database,
using perl 5 to read/write the database.
Now I need to build a website that can handle a database with almost a gigabyte worth
of images to catalog (no it's not porn).
At 0:15 + 10/18/03, MartÌn Lahittette wrote:
Hi,
I want to backup a MySQL 3.23 database to
upgrade it to MySQL 4.0. My tables are InnoDB
and they have foreign keys. I would like to know
how to backup it, because it seems that neither
mysqldump nor mysqlhotcopy can be easily used.
The
Hello,
How can I run garbage collection (optimize sql command, I think) on
Innob tables?
Thanks,
Leo
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
On Fri, 2003-10-17 at 20:16, Mike Bailey wrote:
I desperately need everyone's help.
I normally build database driven websites on NT Servers with an MS Access database,
using perl 5 to read/write the database.
Now I need to build a website that can handle a database with almost a
Hey guys -
I'm running a piece of software that is making database queries but failing on
some of them and I'm trying to figure out why so I can fix it. The table
structure for the table where the query fails looks like:
---
create table dspam_token_data (
uid smallint unsigned,
token
I am doing a lot of inserts, updates and reads with big among of data into
longtext and longblob fields using JDBC driver and mysql 4.0.15 for Windows
with innodb tables.
After 1 hours of intensive working the database sent me a message Lock wait
timeout exceeded; Try restarting transaction SQL
67 matches
Mail list logo
