Note: forwarded message attached.
Yahoo! FareChase - Search multiple travel sites in one click.
---BeginMessage---
Michael Stassen [EMAIL PROTECTED] wrote: ali asghar torabi parizy wrote: hi to all i have a problem when i trying to delet a user by drop user directive
Thanks for your answer.
I´m alreay logging slow queries, i gather no results.
But when I take a look with the MySQL Administrator Tool
i see that there are many full table scans.
I want to discover which queries produce this full table scans.
I´ve read that in MySQL 5 full table scans are logged
Good morning everybody!
We are working on a big project involving SugarCRM and in the end it will
contain around 1 000 000 records per table in MySQL database. Whole system
is running on dedicated server which consists of two Xeons @ 3 Ghz, 2 GB RAM
and two 150 GB disks @ 1 rpm running
On Tuesday 22 Nov 2005 09:24, Marko Knezevic wrote:
is running on dedicated server which consists of two Xeons @ 3 Ghz, 2 GB
RAM and two 150 GB disks @ 1 rpm running under RAID 1 controller. OS is
CentOS 4.2
A very recent article that crossed my eyes indicates that in certain cases, HT
Hello,
I run MySQL-server-pro-gpl-4.1.10a-0.sles9 on two AMD Opteron dual cpu systems
with 16GB RAM. Due to some hardware problems the mainboard of a system was
replaced with a new one. The kernel is the original one from SuSE version
kernel-smp-2.6.5-7.151.
The system runs MySQL replication
In article [EMAIL PROTECTED],
Ferindo Middleton Jr [EMAIL PROTECTED] writes:
I have this SQL statement:
CREATE TABLE rooms (
idSERIAL,
room_name TEXT UNIQUE,
location TEXT,
on 11/22/05 1:24 AM, Marko Knezevic at [EMAIL PROTECTED] wrote:
We've been testing the system with 100 test records to see how it will
work when doing selects from db and we are pretty disappointed with the
results. Some selects needs around 10 seconds to finish, some take even
longer.
See below, and check that the indexes are right, or that the queries
aren't
negating the indexes. I can run queries against a properly indexed table
of
26 million rows in less than a second on lesser hardware (ok, so the SAN
isn't lesser). The manual also contains a lot of information on how
On Tuesday 22 Nov 2005 10:02, Marko Knezevic wrote:
See below, and check that the indexes are right, or that the queries
aren't
negating the indexes. I can run queries against a properly indexed table
of
26 million rows in less than a second on lesser hardware (ok, so the SAN
isn't
At 3:56 PM + 11/21/05, Tom Brown wrote:
is it possible to do a mysql dump to more than 1 file? We will
shortly be needing to dump a db that will be in excess of 50gb so
will encounter file size issues
This is on 4.1.x and rhel 4
Probably the best approach - knowing nothing about your
Jerry Swanson wrote:
Both Linux computers.
This is what I do...
$ more ssh_to_mysql_on_beta
#!/bin/sh
ssh \
-t \
-g \
-L 3306:beta.domain.ac.uk:3306 \
[EMAIL PROTECTED] \
./bin/sleep.plx mysql
That last line is a trick to prevent the terminal from 'timeing out'.
The sleep.plx
I have this SQL statement:
CREATE TABLE rooms (
idSERIAL,
room_name TEXT UNIQUE,
location TEXT,
last_updated TIMESTAMP DEFAULT
CURRENT_TIMESTAMP NOT NULL,
The output of mysqldump is standard output, not a file. You can pipe
it into another program, or redirect the output to a file, but
mysqldump does not make a file. Therefore, there is no option in
mysqldump to make more than 1 file.
How is your database stored on disk? The documentation
Hy
I tried to set the default character set for mysql-4.1-15 server in my.cnf
[client] section
with set :
character-sets-dir=/usr/local/mysql/share/mysql/charsets
and
default_character_set=latin2
but when I check the defaults by show variables like '%_server'
I get these value is
Does anyone on the list know what REITF stands for? I'm guessing it's
some type of data format for real estate information but I can't find any
information on it anywhere.
Thanks,
Ed
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
Marko,
I have a table with 5,000,000 records that takes about 1 second
to show the results.
Perhaps your queries aren´t optimized properly.
Ronan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
The output of mysqldump is standard output, not a file. You can pipe
it into another program, or redirect the output to a file, but
mysqldump does not make a file. Therefore, there is no option in
mysqldump to make more than 1 file.
How is your database stored on disk? The documentation
I have a table with 5,000,000 records that takes about 1 second
to show the results.
All rows?
Perhaps your queries aren´t optimized properly.
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL
Server
Upscene Productions
Hi Jörg, all!
Jörg Nowak wrote:
I try to run
./configure --with-ndbcluster --prefix=/STEFAN/mysql-5.1.2-binary
--with-partition --with-row-based-replication --with-innodb
--without-berkeley-db --enable-thread-safe-client --enable-shared
but it fails with
checking for termcap functions
Martijn,
I have a table with 5,000,000 records that takes about 1 second
to show the results.
All rows?
No. The queries return an average of 30 rows.
I just wanted to say that the problem isn´t the table size, but the
query X indexes used.
Ronan
--
MySQL General Mailing List
For list
I have a table with 5,000,000 records that takes about 1 second
to show the results.
All rows?
No. The queries return an average of 30 rows.
I just wanted to say that the problem isn´t the table size, but the
query X indexes used.
Right :-)
I just wanted to say that it's a weird
do any one have ready made combo box with all the country codes present
- Original Message -
From: Rhino [EMAIL PROTECTED]
To: Björn Persson [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: 21/11/2005 6:18 AM
Subject: Re: Country codes
I'm not saying it was an inappropriate way to
Ave,
You can use the attached SQL file to create a table with all Country names,
ISO codes, and easily write up a code to fill in the values from the table.
If you have any problems filing up the combo box with the values from the
table, let me know.
Dominor,
Rahul S. Johari
Coordinator,
Marko,
I have a table with 5,000,000 records that takes about 1 second
to show the results.
Perhaps your queries aren´t optimized properly.
Ronan
Here are my tables and queries i am running on them with index explanations.
Hope this will help.
ACCOUNTS TABLE:
I also have following situation:
SELECT count(*) FROM accounts LEFT JOIN users ON
accounts.assigned_user_id=users.id where ((accounts.phone_office like '01
389437%' OR accounts.phone_alternate like '01 389437%' OR accounts.phone_fax
like '01 389437%')) AND accounts.deleted=0
Explain
Mike,
The documentation at
http://dev.mysql.com/doc/refman/5.0/en/update.html explains that MySQL
is aware of the fact that it only needs to update different values --
for instance, it returns only the # of rows changed, not the # of rows
looked at. Given that, I will extrapolate that MySQL is
http://www.google.com/search?hl=enlr=q=real+estate+investment+trustbtnG=Search
REIT = real estate investment trust
Maybe the extra F is for fund? I didn't see any mention of REITF -
but I have seen the acronym REIT before.
kgt
Ed Curtis wrote:
Does anyone on the list know what REITF
I understand that MySQL returns the # of rows changed. What I am wondering
is if I change only one value, therefore a row change, but leave the rest,
what happens. Does the row get removed from the index and re-placed?? Does
MySQL look at each column value first to compare?? Mike
-
(I used to have separate date/time cols. in all tables
but changed them to datetime and buggered up some stuff.
Now I'm trying to find the best way to fix this.)
If I have an indexed datetime column (`date`), and say:
select date, other_cols
from table1
where date between
I'm using the following to find the nearest 10 records by proximity
to the ZIP code $zip. The problem is that it only returns one record
per ZIP code, even when there are multiple records with the same ZIP:
(Note this uses a temp table, but I already double-checked that all
the desired
Jonathan Mangin [EMAIL PROTECTED] wrote on 11/22/2005 10:37:00
AM:
(I used to have separate date/time cols. in all tables
but changed them to datetime and buggered up some stuff.
Now I'm trying to find the best way to fix this.)
If I have an indexed datetime column (`date`), and say:
Brian Dunning [EMAIL PROTECTED] wrote on 11/22/2005 10:43:13 AM:
I'm using the following to find the nearest 10 records by proximity
to the ZIP code $zip. The problem is that it only returns one record
per ZIP code, even when there are multiple records with the same ZIP:
(Note this uses a
Marko Knezevic [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
Here are my tables and queries i am running on them with index explanations.
Hope this will help.
ACCOUNTS TABLE:
+-+--+--+-+-+
| Field
You're exactly right, and that solved it! Thank you.
On Nov 22, 2005, at 7:49 AM, [EMAIL PROTECTED] wrote:
Brian Dunning [EMAIL PROTECTED] wrote on 11/22/2005 10:43:13
AM:
I'm using the following to find the nearest 10 records by proximity
to the ZIP code $zip. The problem is that it
Hello,
We just upgraded from 3.23.58 to 4.0.24. Our database connection, which can
remain open for a very long time, is timing out after being idle overnight,
where it never had in 3.23.58. I learned from the manual that there is a
global variable WAIT_TIMEOUT, which appears to be set at
Hi Friends,
I have a table like
name
MAD
LHR
MAD
LHR
AKL
AWL
AKL
LHR
I want the output as:
LHR 3
AKL 2
AWL 1
etc...
ie the no of entires sorted by their no of appearences.
I cannot do that by select name from tab_name group by name as it will not
give me the no of rows.
Pl. help me to find
Hi Abishek,
I want the output as:
LHR 3
AKL 2
AWL 1
This should do it:
SELECT name, COUNT(*) as num
FROM tab_name
GROUP BY name
ORDER BY num DESC
Regards,
Jeremy
--
Jeremy Cole
MySQL Geek, Yahoo! Inc.
Desk: 408 349 5104
--
MySQL General Mailing List
For list archives:
Abhishek
I want the output as:
LHR 3
AKL 2
AWL 1...
ie the no of entires sorted by their no of appearences.
SELECT
tbl.name AS Name,
COUNT( tbl.name) AS Count
FROM tbl
GROUP BY tbl.name
ORDER BY Count DESC;
PB
[EMAIL PROTECTED] wrote:
Hi Friends,
I have a table like
name
MAD
LHR
MAD
Hello all
I've installed MySQL Server 5.0.16 and MySQL Client 5.0.16 (from the RPMs on
the MySQL site) on a server running Fedora Core 4. Every time I try to start
the service, I get the Unable to connect to the MySQL server through the
/tmp/mysql.sock error. I've tried several things from the
Betty Trakimas [EMAIL PROTECTED] wrote on 11/22/2005 11:08:51 AM:
Hello,
We just upgraded from 3.23.58 to 4.0.24. Our database connection, which
can
remain open for a very long time, is timing out after being idle
overnight,
where it never had in 3.23.58. I learned from the manual that
Thanks Shawn. Do you know why anyone would ever need the reconnect flag
then? The MySQL developers must think that it has a purpose since they
opened up the API to allow access to it from the client. What would be its
purpose otherwise?
- Original Message -
From: [EMAIL PROTECTED]
This query should tell you how many occurrences of each value you have in
the table:
select name, count(*) as count
from mytable
group by name
order by count desc
Rhino
- Original Message -
From: [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Tuesday, November 22, 2005 11:25 AM
Hello.
MYSQL structure has a field reconnect, you may set it from your
application. Also, as I remember, if you're using prepared statements
your client program automatically reconnects.
Betty Trakimas wrote:
Hello,
We just upgraded from 3.23.58 to 4.0.24. Our database connection,
Hello.
If you have such a big database, may be you should think about
--tab option of mysqldump:
http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html
Tom Brown wrote:
is it possible to do a mysql dump to more than 1 file? We will shortly
be needing to dump a db that will be
Hello.
My doubt is: Is it the only way to have UDF server-side in MySQL(4.1.x)?
Do I need to learn C/C++ to have a UDF?
In my opinion - yes, because UFDs usually uses includes (say, mysql.h)
which are written in C/C++. Speaking in general - UDF for MySQL is just
a shared library, so
Hello.
For server you should put these lines into [mysqld] section. See:
http://dev.mysql.com/doc/refman/5.0/en/charset-server.html
Mester József wrote:
Hy
I tried to set the default character set for mysql-4.1-15 server in my.cnf
[client] section
with set :
Hello.
In my opinion it is a bug. You may add your comments at:
http://bugs.mysql.com/bug.php?id=15137
If this is a bug, I'll be happy to file a report... but I'd really like a
solution that will let me use the function.
I've noticed that you may create a temporary tables from views
I've seen a few posts out there regarding this subject, but no solutions
that I could understand.
I am unable to execute a stored procedure from the MySql data provider.
I get a message that says:
'42000 SELECT command denied to user' etc.
Can anyone tell me what I need to do to enable my
The flag is there to help automate the renegotiation of a broken
connection by allowing the client library to conceal from the
controlling application the fact that the connection went away (for any
reason).
Let's imagine that your client needs to update 3 tables. It makes a
connection then
You're going to have to read the manual on the different types of
indexes. Without knowing what kind of index (Hash, B-tree) you're
using and on what kind of table, I cannot help you.
Parts of Chapter 14 of the MySQL manual are good, as is the section on
query indexing (starting p.301) of the
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
Hi all!
I had chroot working under 3.23 and 4.x, although it was always rather
kludgy. Under 5.0 i can't get it to work to save my life. I start MySQL
with the mysql.server script that is included in 5.0. It seems that no
matter how i set
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
I am wondering if there is a set-based minus operator.
I am assuming there isn't, but I thought I would ask.
Here is the query I am trying that fails.
Thank you.
select n.name
from names n, transactions t
where n.name=t.name
MINUS
select
James Black [EMAIL PROTECTED] wrote on 11/22/2005 03:48:28 PM:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
I am wondering if there is a set-based minus operator.
I am assuming there isn't, but I thought I would ask.
Here is the query I am trying that fails.
Thank you.
select
I tried to go into mysql with root and the password I had set, and access was
refused.
I finally gave up and decided to reset the root password using the techniques
described on
http://dev.mysql.com/doc/refman/4.1/en/resetting-permissions.html
This is a linux machine running mysql Your MySQL
I'm trying to correct a situation I've created after
combining separate date/time columns into a datetime column.
Here are skeletons of new table definitions.
CREATE TABLE products
(id mediumint unsigned primary key not null auto_increment,
item varchar(40) not null,
priceA decimal(6,3),
Hi,
Is there a way to load a section of an input file into mysql
(MyISAM table) using mysqlimport or LOAD DATA INTO?
The input data is in relatively large ascii files (10 million
rows per file), and I'd like to break the load into smaller
pieces rather than load whole file at once. Of course I
If I have table like:
CODE QTY
100 30
200 20
300 25
and I want a result like:
CODE QTY SUBTOTAL
100 30 30
200 20 50 (this come from 30+20)
300 25 75 (this come from 50+25)
How to write query like that?
mysql select * from table5;
++--+
| code | qty |
++--+
|100 | 30 |
|200 | 20 |
|300 | 25 |
++--+
3 rows in set (0.00 sec)
mysql set @a=0;
Query OK, 0 rows affected (0.05 sec)
mysql select code,qty,@a:[EMAIL PROTECTED] as subtotal from
I have sudo access to Linux box. How to start mysql 4.0.14.
TH
What happened to MySQL Control Center (aka mycc or mysqlcc)? The
dev.mysql.com site redirects to the Query Browser page. QB is a poor
substitute for mycc. It looks like neither of them has had active
development much lately but at least mycc, even in its beta stage, is
fairly useful.
I did read the manuals (three including MySQL reference manual and DuBois
ver 5) as well as Google searches. I am using Myisam tables (installed by
default) and from what I can tell by the from the manual the indexes would
be B-tree but I would be willing to work with another type if necessary.
Hi,
Newb question... ok just a dumb question:
If I have never created or edited a 'my.cnf' file and yet I have a
working MySQL, what conf file is being used and where is it (OS X
Server)? Also, where does the my.cnf go once created?
Thanks,
TR
--
MySQL General Mailing List
For list
On Tue, Nov 22, 2005 at 04:50:42PM -0800, Jon Drukman wrote:
What happened to MySQL Control Center (aka mycc or mysqlcc)? The
dev.mysql.com site redirects to the Query Browser page. QB is a poor
substitute for mycc. It looks like neither of them has had active
development much lately but
[EMAIL PROTECTED] wrote:
If I have never created or edited a 'my.cnf' file and yet I have a
working MySQL, what conf file is being used and where is it (OS X
Server)? Also, where does the my.cnf go once created?
http://dev.mysql.com/doc/refman/4.1/en/option-files.html
:: might be
How to reset mysql password to mysql?
mysql -u root
ERROR 1045 (0): Access denied for user: '[EMAIL PROTECTED]' (Using
password: NO)
Jerry Swanson wrote:
I have sudo access to Linux box. How to start mysql 4.0.14.
For 4.1, but you can probably figure out the differences :-)
http://dev.mysql.com/doc/refman/4.1/en/server-side-scripts.html
--
Hassan Schroeder - [EMAIL PROTECTED]
Webtuitive Design
Exactly as it is documented in the manual at :
http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html
Regards
David Logan
Database Administrator
HP Managed Services
148 Frome Street,
Adelaide 5000
Australia
+61 8 8408 4273 - Work
+61 417 268 665 - Mobile
+61 8 8408 4259 - Fax
This makes no sense to me, please explain if you can. I have two
queries, both run in under a tenth of a second. But, when one is run
as a sub-query of the other, run time is essentially infinite ( I
haven't had that much patience, yet ).
For example, although these are not the
Hot to start mysql server at boot?
On 11/22/05, Logan, David (SST - Adelaide) [EMAIL PROTECTED] wrote:
Exactly as it is documented in the manual at :
http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html
Regards
David Logan
Database Administrator
HP Managed Services
148
You are the man, Joe. The wall next to my desk thanks you, for
stopping me from continuing to beat my head against it.
--
David Hillman
LiveText, Inc
1.866.LiveText x235
On Nov 22, 2005, at 7:50 PM, Joseph Cochran wrote:
Subqueries in 1.4.14 don't use indexes, instead performing full-
I have no idea as to whether you are running windows or linux, however
the manual does go into this at :
http://dev.mysql.com/doc/refman/5.0/en/automatic-start.html
Regards
David Logan
Database Administrator
HP Managed Services
148 Frome Street,
Adelaide 5000
Australia
+61 8 8408 4273
Hello:
I created tables with CHARACTER SET=utf8. And stored Chinese in them.
When I used SELECT * FROM ... ORDER BY ...
I can't get the right order.
But when I used CHARACTER SET=gbk, I can get the right order.
I didn't see 'Chinese' or 'China' when SHOW COLLATION.
What can I do?
Thanks.
Not sure if this is the place to ask but hopefully someone can help me
here. I am setting an application system and have successfully setup
MySQL 5.0.15 and Tomcat 5.5.12. However, I cannot seem to get a
connection object from my servlet and for the life of me, I can't figure
out why. Here
What I need to do is take this query and this result:
SELECT segfees FROM fsf;
+-+
| segfees |
+-+
| FS=5.00 |
| AY=2.50 |
| XF=1.75 |
| ZP=3.20 |
+-+
I make it just return ONE row like this:
+-+
| segfees |
After my MySQL server start i see this processes:
[EMAIL PROTECTED]:/ps -HC mysqld -o pid,command
PID COMMAND
3116 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql
3117 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mys
3118 /usr/libexec/mysqld --basedir=/usr
Hi,
You can use select group_concat(segfees) from fsf;
The information bellow is from mysql manual, but you have to have mysql
4.1.x
=
*
|GROUP_CONCAT(/|expr|/)|
This function returns a string result with the concatenated
non-|NULL| values from a group. It returns
Jerry Swanson schrieb:
How to reset mysql password to mysql?
mysql -u root
ERROR 1045 (0): Access denied for user: '[EMAIL PROTECTED]' (Using
password: NO)
You could start your mysqld with --*without-grant-tables* option. But
keep in mind that this will
stop the complete permission
Suppose that I have a dummy table named, Dummy, and it has a column name
Status, which can contain values Neg, or Pos.
If I have the following data in table, Dummy:
id, status
1 Neg
2 Neg
3 Pos
4 Neg
I need to write a query returns the count of number of Neg in status but
filters out
Thanks for the tip.. Here's the query that worked...
SELECT GROUP_CONCAT(DISTINCT segfees SEPARATOR ',')
from segfees
WHERE ID_flights_segments=[insert value here]
group by ID_flights_segments;
inferno wrote:
Hi,
You can use select group_concat(segfees) from fsf;
The information bellow is from
79 matches
Mail list logo