Hi All,
How can we optimize MySQL queries, plz define easy method
comparing as well as better performance for data retrieval.
Regards:
aaziz
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi all,
I'm trying to move my Mambo (content management) database from one server into
another.
In the old server, the mysql version is 4.0.13, while in the new one it's
4.0.15.
The error was when restoring:
ERROR 1064 at line 141: You have an erro
Does REPLACE INTO not work in your case?
|
|
|REPLACE| works exactly like |INSERT|, except that if an old record
in the table has the same value as a new record for a |PRIMARY KEY|
or a |UNIQUE| index, the old record is deleted before the new record
is inserted. See section 14.1.4 |INSE
I'm developing a program where I try an "UPDATE ... LIMIT 1" and if
mysql_affected_rows == 0, then I know nothing was updated and so I do an
INSERT. I find this is much cleaner and the majority of the time, I'm going
to do UPDATES, so I didn't want to waste a SELECT (even though I hear
they're "che
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Wednesday 26 May 2004 11:22 pm, [EMAIL PROTECTED] wrote:
> Hi, I am trying to use the foreign key constraints from InnoDB
> and creating indexes is a requirement for foreign key.
> The problem is that by creating index for my foreign key,
> it does
Hi, I am trying to use the foreign key constraints from InnoDB
and creating indexes is a requirement for foreign key.
The problem is that by creating index for my foreign key,
it does not allow my foreign key to have null or blank values which my records will
have.
For eg. a BorrowerID is a foreig
Andy Henshaw wrote:
I have the following query that takes anywhere from 1 to 3 seconds
to run. I would expect it to run in less than 1/2 a second (and
I really need it to do so). I've added the appropriate indices
and tried EXPLAIN and ANALYZE; but, I'm not getting anywhere.
SELECT message.id
On Wed, May 26, 2004 at 06:20:22PM -0700, Scott Haneda wrote:
> I have a field in mysql 4, using InnoDB
> Field is timestamp 14 and defualt is set to 00, which I want to
> be the result of NOW() so that every record made will get NOW() as the
> value, I can not get it to work...
>
> AL
On Wed, May 26, 2004 at 06:22:45PM -0700, Scott Haneda wrote:
> I can not find this in the docs.
>
> I can see in phpmyadmin what a table comment is and what type of table it
> is, such as MyISAM etc.
>
> From the mysql shell, how do I get to this info?
You can use SHOW TABLE STATUS:
http://d
Scott Haneda wrote:
I can not find this in the docs.
I can see in phpmyadmin what a table comment is and what type of table it
is, such as MyISAM etc.
From the mysql shell, how do I get to this info?
Try
mysql> SHOW CREATE TABLE tablename;
(Not sure if that covers comments since I've, yes I admi
I can not find this in the docs.
I can see in phpmyadmin what a table comment is and what type of table it
is, such as MyISAM etc.
>From the mysql shell, how do I get to this info?
--
-
Scott HanedaTel: 4
I have a field in mysql 4, using InnoDB
Field is timestamp 14 and defualt is set to 00, which I want to
be the result of NOW() so that every record made will get NOW() as the
value, I can not get it to work...
ALTER TABLE `addresses` CHANGE `added` `added` TIMESTAMP( 14 ) DEFAULT
'NOW(
Hello,
Is there any way to have per user stats? In the bin log we have
nothing but exec_time=0 (seems this is "seconds") - is there a way for
more precise statistics what different users do regarding CPU usage by
MySQL per user?
Best regards,
Doytchin Spiridonovmailto:[EMAIL PROTECTED]
ht
Mark Susol|Ultimate Creative Media wrote:
I've been trying to fine tune my mysqld settings in my.cnf on a very busy
server. It seems to be doing fine, as in the server loading. BUT every so
often I see the number of processes spike and then it sesms mysqld is
unresponsive through httpd and I end up
are you using:
INSERT INTO
SELECT
FROM ;
I am still a relative beginner and this was the way I was taught. I have
personally never had a problem (but then I was using Oracle in school).
Respectfully,
Ligaya Turmelle
"David Blomstrom" <[EMAIL PROTECTED]> wrote in message
news:[EMAI
I've been trying to fine tune my mysqld settings in my.cnf on a very busy
server. It seems to be doing fine, as in the server loading. BUT every so
often I see the number of processes spike and then it sesms mysqld is
unresponsive through httpd and I end up having to restart mysqld to get it
going
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
robert kuzelj wrote:
> hallo mark,
>
>> Hopefully the following JUnit testcase helps show that your problem
>> doesn't exist at the JDBC level. It creates a UTF-8 connection to
>> MySQL-4.1.2, stores UTF-8 encoded strings directly and by prepared
>> s
hallo mark,
Hopefully the following JUnit testcase helps show that your problem
doesn't exist at the JDBC level. It creates a UTF-8 connection to
MySQL-4.1.2, stores UTF-8 encoded strings directly and by prepared
statements, retrieves them, compares to original as strings, and
byte-for-byte using t
The easy way around this is to change your hostname to 'localhost,' install
the scripts, and then change your hostname back. There are other ways to
fixing this, but that's the fastest, I've found.
J.R.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe
> Hmm, I don't see any changes in ft-related files since 4.0.18 that could
> cause it (there were bugfixes, but they affect only *searching* - that
> is MATCH - and not *updating*).
>
> Can you create a test case ?
Well, I put up a file in the secret folder a few days ago as referenced in a
bug r
We start mysql with 'service mysql start' (we install from the RPM for
linux).
I've never seen mysql create binlog files under the name root before, and
after reverting to an old version, it doesn't again. It created a big mess
with all the slaves stuck at the end of an older binlog and not advanc
If there is an FAQ where this is addressed, please point me to it so
I can RTFM.
I need to find out if table joins lock the table or, alternatively,
how to find out what query is locking the table.
We're doing a miles-from-zipcode query and I'm looking to speed it up.
Since we only have a few o
I have the following query that takes anywhere from 1 to 3 seconds
to run. I would expect it to run in less than 1/2 a second (and
I really need it to do so). I've added the appropriate indices
and tried EXPLAIN and ANALYZE; but, I'm not getting anywhere.
SELECT message.id
FROM messag
Hi,
Currently, I have a situation where an app makes connections (via JDBC)
to a mysql server, 50 connections at once, and everything just becomes
super-slow. For instance, a SELECT that should take 0.01 sec takes
several minutes. SHOW PROCESSLIST says that these threads that are
connections
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
robert kuzelj wrote:
> hi mark,
>
> > However, once you start displaying things on the console, all bets are
> > off...because your console needs to understand UTF-8 as well, so using
> > the 'eyeball' method of testing won't work too well here.
>
hi mark,
> However, once you start displaying things on the console, all bets are
> off...because your console needs to understand UTF-8 as well, so using
> the 'eyeball' method of testing won't work too well here.
in principle i agree with you. but only having java tests is simply
not enough. i ha
Unless outlook is just formatting your message strangley it looks like
the actual name of the ID field is `ID ` with two space char's
included. As such you probably want to reccreate the table using `ID`
in the create statement so that it will get created as you expect
without these extra chars.
Hi,
Following is the output of show create table Spot
command
| Table | Create Table
Dear All,
Hello
Installed the MySQL-server-4.0.20-0.i386.rpm on a RHL8 system
Initialized the grant tables and then did:
/usr/bin/mysqladmin -u rt password ACTUALPASSWORD
And got:
-bash: /usr/bin/mysqladmin: No such file or directory
What gives mates?
I had used the exact command before in
ColdFusion Lists <[EMAIL PROTECTED]> wrote:
>
> we're using MySQL 4.0.12 with InnoDB... please do you have any scripts, comments,
> suggestions, ideas, tutorials... about how to backup from that DB? It's possible to?
>
You can use mysqldump utility that comes with MySQL distribution:
ht
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Wednesday 26 May 2004 02:34 pm, ColdFusion Lists wrote:
> we're using MySQL 4.0.12 with InnoDB... please do you have any scripts,
> comments, suggestions, ideas, tutorials... about how to backup from that
> DB? It's possible to?
If you can afford t
Hi all
we're using MySQL 4.0.12 with InnoDB... please do you have any scripts, comments,
suggestions, ideas, tutorials... about how to backup from that DB? It's possible to?
Thanx for your time.
Diga-me e eu esquecerei
Mostre-me e lembrarei
Ensina-me e aprenderei
-
"Mazhar Bilen" <[EMAIL PROTECTED]> wrote:
> How can I add Turkish support to the latest version of MySQL?
> I tried many times, but I can't do this.
>
latin5 is used for turkish language.
Start MySQL server with --default-character-set=latin5 option or put in the my.cnf:
[mysqld]
default-charact
Maybe a long shot, but creating this table should have required using
backticks around `Column` because it's a reserved word. Made me wonder
if the ID field in the create statement was in backticks as well and
maybe has an extra character.
What does "show create table Spot" show?
Hi,
I have a t
How can I add Turkish support to the latest version of MySQL?
I tried many times, but I can't do this.
Mazhar Bilen
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
backticks didnt work :(
--- Victor Pendleton <[EMAIL PROTECTED]> wrote:
> Try using backticks `ID`
>
> -Original Message-
> From: kaustubh shinde
> To: [EMAIL PROTECTED]
> Sent: 5/26/04 1:05 PM
> Subject: Error 1054
>
> Hi,
> I have a table Spot in the database as follows
>
> desc Spot
Try using backticks `ID`
-Original Message-
From: kaustubh shinde
To: [EMAIL PROTECTED]
Sent: 5/26/04 1:05 PM
Subject: Error 1054
Hi,
I have a table Spot in the database as follows
desc Spot
-> ;
+---+-+--+-+-+---+
| Field | Type
This is just a shot in the dark, but I recall getting similar behavior
(error...lost connection...reconnect) when I was trying to "LOAD DATA" that
happened to violate foreign key constraints in my InnoDB tables.
Hope that helps.
---
Fyodor Golos
Stockworm, Inc.
-Original Message-
From:
Hi,
I have a table Spot in the database as follows
desc Spot
-> ;
+---+-+--+-+-+---+
| Field | Type| Null | Key | Default |
Extra |
+---+-+--+-+-+---+
| Name | varchar(32) | YES |
Hi,
I'm using MySql 4.0.18 on Linux (SuSE 8.2)
and I'm getting this error
ERROR 2013: Lost connection to MySQL server during query
a few seconds after launching
the following command
LOAD DATA LOCAL INFILE '/root/hc.txt' into table af_afiliados ;
soon after that I repeat t
Plinio Conti <[EMAIL PROTECTED]> wrote:
> I need to prepare some sql scripts which take parameters... how can I do?
>
> For example, suppose I want to have a sql script to create a new user granting to
> him some privileges and inserting its name in some application specific tables
>
Seems
Can you run into problems when copying tables with
foreign keys? I want to copy my Nations and States
tables, making versions designed only to hold lengthy
articles. I copied and coverted the States table
successfully, but I'm running into problems with the
Nations table.
I get an error message re
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
robert kuzelj wrote:
> [code]
> 18String family = new String(rs.getBytes("FAMILY_NAME"));
> 19String given = new String(rs.getBytes("GIVEN_NAME"));
> [/code]
>
> [result]
> [java] .Käßsel - Böb
> [java] Ægÿl - Àlbért
>
seems like my problem is not necesseraly tied to mysql.
at least i can reproduce all of this also with postgres.
instead of writing to the database i tried to read from
it (after inserting data via the following simple script on the
commandline)
[code]
[EMAIL PROTECTED]:> more example_insert.sql
in
At 14:07 -0500 on 05/25/2004, <[EMAIL PROTECTED]> wrote about need
help with a complicated join:
I am trying to come up with a query that takes two tables, one with
non-split-adjusted historical stock prices, and one with information on
splits, for instance:
CREATE TABLE quotes (
symbol
hi Yiannis,
Try changing Eclipse's setting to saving the files as UTF-8 and also force the
JVM to the UTF-8 file encoding.
eclipse is already set to only write UTF-8. but how should i force
the jvm to work with that encoding?
ciao robertj
smime.p7s
Description: S/MIME Cryptographic Signature
Try changing Eclipse's setting to saving the files as UTF-8 and also force the
JVM to the UTF-8 file encoding.
-Original Message-
From: robert kuzelj [mailto:[EMAIL PROTECTED]
Sent: 26 May 2004 15:01
To: [EMAIL PROTECTED]
Subject: UTF-8 settings and woes
hi,
i am trying to write utf-8 d
At 11:01 +0300 on 05/26/2004, Egor Egorov wrote about Re: DATETIME question:
John Mistler <[EMAIL PROTECTED]> wrote:
Given a column DATETIMEcolumn (-MM-DD HH:MM:SS), is there a SELECT
statement that will:
select all entries whose (TIME) of DATETIMEcolumn is BETWEEN 'HH:MM:SS' AND
'HH:MM:SS'
Wow! What a trick! Harold, I am ashamed for not remembering that log/exp
technique myself.
Just out of curiosity, what is the difference between these two:
COALESCE(*expression*, 1)
IFNULL(*expression*, 1)
Right off the bat, I know that COALESCE accepts multiple arguments, while
I would also question the sub-select in the WHERE clause. I suspect that it
is being evaluated once for each row of rptPricingTEST. You could get a
significant performance boost if you move the results of that select into a
temp table and INNER JOIN to it. That way you calculate the MAX() date onl
Everyone can thank Harold Fuchs for contributing this one:
exp(sum(log(coalesce(*the field you want to multiply*,1)))
The coalesce() function is there to guard against trying to calculate the
logarithm of a null value and may be optional depending on your
circumstances.
Shawn Green
Database Admi
Hi,
I would start with finding out if it's the select or the insert that's
taking a long time...
Does the priceLast5 table have heavy indexes to build?
Try running the select seperately and see how long it takes...
Andrew
-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTE
hi,
i am trying to write utf-8 data via java into sql but
it wont work as expected.
first my setup
- suse 9.0
- kde 3.2
mysql> SHOW VARIABLES LIKE 'char%';
+--++
| Variable_name| Value |
+--
Harold, you win the "EUREKA" prize of the month!
I had forgotten all about that silly algebraic trick. This answers another
person's post from last week. (I will try to find it again) also looking
for a PRODUCT() function.
I agree about the unknown performance. If you only need to return adjuste
Andrew
I've not seen any problems, but them none of the databases are particularly
loaded and most of the time are not being accessed concurrently.
In all cases except for Mysql.3.23.52 - (which is the default patched)
install on SLES8, I built the databases from source Postgres-7.4.2,
Mysql-4.1.
Use the time_format function.
-Original Message-
From: John Mistler
To: [EMAIL PROTECTED]
Sent: 5/26/04 1:15 AM
Subject: DATETIME question
Given a column DATETIMEcolumn (-MM-DD HH:MM:SS), is there a SELECT
statement that will:
select all entries whose (TIME) of DATETIMEcolumn is BETW
If you do the math a large result set will be created. You could rewriting
your query or adding more indexes to see if this speeds up the process.
-Original Message-
From: nyem
To: [EMAIL PROTECTED]
Sent: 5/26/04 2:57 AM
Subject: slow insert into select statement
I have this insert statem
Thanks Kevin,
I am comfortable with the software installs etc.. I was more concerned
with hardware bottlenecks and OS (linux 2.4) problems etc..
Any pointers would be great..
Cheers,
Andrew
-Original Message-
From: Kevin Cowley [mailto:[EMAIL PROTECTED]
Sent: Wednesday 26 May 2004 10
man i didnt even know you can do this
AND s.date > q.date
i assumed that goes in a where clause ?
> -Original Message-
> From: news [mailto:[EMAIL PROTECTED] Behalf Of Harald Fuchs
> Sent: Wednesday, May 26, 2004 8:01 PM
> To: [EMAIL PROTECTED]
> Subject: Re: need help with a complicated
I'm on Linux and had to install the myself server since that didn't work
from the Linux install cd. When I run mysql_install_db I get "host name
lookup failure" . Please configure the hostname.
However in my Linux hosts table I DO have the localhost address. and if
I do resolveip localhost from
Hi,
On Linux this feature is available in 2.6 through system calls.
See:
http://www.linuxjournal.com/article.php?sid=6799
On Windows it is also available at least as function calls
See:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/
dllproc/base/multiple_processors.asp
On Solar
Hi.
We need to bind mysql-server process to a specific processor on dual
pentium iii machine. Is it possible?
Ilia.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Andrew
I've done it but only in a test environment. I actually had 3 different
versions of Mysql running plus Postgres.
Each of the Mysql's and Postgress were installed to /opt/-version
and paths set accordingly.
I've had no problems and they databases have been continuously up for over a
month.
Hi All,
Does anyone have any experience of running MySQL and PostgreSQL on the
same hardware?
At the moment we have several reasonable fast servers (dual Xeon GHz,
1GB ram, 15,000rpm scsi disk) running MySQL in a replicated environment
with high volumes of queries (high read:write ratio) and I no
From: "Sergei Golubchik" <[EMAIL PROTECTED]>
> > 4. Thread stack warnings:
> >Warning: Asked for 196608 thread stack, but got 126976
> Same here.
OK, we can disable the warnings in the log file, but what's really behind
this warning?
A brand new, plain vanilla Fedora Core2 (aka RedHat FC2) ins
Hi!
On May 25, Steven Roussey wrote:
> We had some servers that were upgraded from 4.0.17/18 to 4.0.20 and had
> several problems thereafter:
>
> 1. Tables with FTS indices became corrupted, with queries on them causing
> segfaults on the servers.
Hmm, I don't see any changes in ft-related files
Hi Sheni,
I would start by running MySQL 3.23.58 which is the latest released version
of the MySQL 3.23 branch. Also running MySQL on FreeBSD with linuxthreads
enabled.
If you cvsup your ports collection doing something like:
cd /usr/ports/databases/mysql323-server
make WITH_LINUXTHREADS=yes BU
Batara Kesuma <[EMAIL PROTECTED]> wrote:
> I have a table that looks like:
>
> mysql> select * from test3;
> ++++
> | sub_id | date | data |
> ++++
> | 1 | 2004-05-01 | data 001 |
> | 1 | 2004-05-02 | data 002
John Mistler <[EMAIL PROTECTED]> wrote:
> Given a column DATETIMEcolumn (-MM-DD HH:MM:SS), is there a SELECT
> statement that will:
>
> select all entries whose (TIME) of DATETIMEcolumn is BETWEEN 'HH:MM:SS' AND
> 'HH:MM:SS', but whose (DATE) is anything?
>
SELECT .. FROM t1 WHERE DATE_FORMA
I have this insert statement that took a long time to execute:
INSERT INTO priceLast5
SELECT
DISTINCT a.commodity,
a.PricingDt
FROM rptPricingTEST a
WHERE
a.PricingDt = (
SELECT MAX(PricingDt)
FROM rptPricing
70 matches
Mail list logo
