Thanks all the info.
Just got what I wanted.
2009/9/10 Wolfgang Schaefer :
> John Daisley schrieb:
>> SELECT MAX(value), id FROM table
>> GROUP BY id;
>>
>>
>>
>
> I guess what Slackli had in mind was more something like this:
> SELECT id, value
> FROM table
> WHERE value = (SELECT max(value) FROM
Hello,
sorry I'm not good at SQL statement.
I have a table, whose stru is like:
idvalue
1 33
2 987
3 10
4 22
...
I want to get the max value and the corresponding id, using this sql:
select max(value),id from table;
but it won't work.
so what's the
Hello,
My mysql table has been created for long time, it increases day by day,
and become huge.
Right now a full scan to the table for the first time is very slow.
So I was thinking to optimize it.
This table is stored in many non-sequential disk fragments I think.
I want to make this table to be
>
> Add an alias for the subquery
>
> select * from ( select ) my_alias where dd >= 3;
>
> Better, use a having clause and eliminate the subquery. Odds are it
> will be more efficient in MySQL.
>
How to replace the original one with a having statement?
Thanks again.
Get the n
hello,
I try to execute this sql in mysql shell,but got error as:
mysql> select * from (select uin,count(*) as dd from active_users where
date >= date_add(curdate(),interval -30 day) group by uin) where dd >=3;
ERROR 1248 (42000): Every derived table must have its own alias
But I can execute
Hello members,
I have two mysqld run on the same host (redhat linux OS with 2.4 kernel).
the two mysqld are in different versions, one is 4.0.20,another is 5.0.45.
the mysql 4.0.20 uses /etc/my.cnf as its config file,listening on default
3306 port.
the mysql 5.0.45 uses /etc/mysql5.cnf as its con
> Anyone know whats wrong here?
Try as
...
>From (Klienter AS K, Tid As Td, Personal AS P)
JOIN Uppdrag AS U ON K.Klient_ID = U.Klient_ID
...
or
...
>From Tid As Td, Personal AS P, Klienter AS K
JOIN Uppdrag AS U ON K.Klient_ID = U.Klient_ID
...
This is the problem I had in one of my queries
It worked in 4.x but does not work in the new syntax. How should I
rewrite it to get the same result?
OK, that was a lie. It works in 5.x as well. I should learn to
describe my problem more accurately as well as RTFM :-(
The correct description of the query in question would have been:
select
I hope someone can clue me in what a syntax of query that produces the
same would look like for MySQL > 5.0.12
Old query meant to list most recent message from each thread, e.g.
select * from messages left join messages as messages_ on
messages.thread = messages_.thread and messages.created <
me
mysqlcheck -h$host -u$user -p$pass --analyze $dbname
I wish that was the case!
I tried analyze table ... and optimize table ..., which I presume
would be the same. It did not help. I also ran mysqlcheck just to see
if it will make a difference. Nope!
--
MySQL General Mailing List
For list
When you upgraded from 4.1 to 5.0, did you do an in-place upgrade, or mysqldump
your data and then re-import?
As replied to Sebastian's post, in-place.
Try using either mysqldump or mysql-administrator to dump out your data to an
.sql file. Then re-import all of your data into 5.x. You will
possible you had set up some query cache in 4, but not currently in 5?
may not be optimized, but yes, query cache is enabled, all 25 MB of it. :-)
how did you 'upgraded' your data?
regrettably, in-place.
interestingly, I was recovering after server crash that chopped of a
table. after upgra
I had perfectly working complex queries both with LEFT JOIN and without
and they were returning results in under a second. After upgrade to
5.0.x, the same queries would return results in 20-30 second range.
Through trial and error, I discovered that in case of SELECT ... FROM
table1, table2 ... O
Hello list:
Need some help with the following query:
mysql> SELECT header.date_in,header.pid,header.status,body.body_data from
header,body where header.date_in='1170705152' and
body.date_in=header.date_in and header.pid='26878' and body.pid=header.pid
into outfile '/tmp/mysql/117070515226878'
I need to update a table with the contents of a CSV file regularly, I've
used mysqlimport to load all the initial data, but I have a problem with
using it for updates. The data in the CSV file does not contain all of the
data in the table, there is a field that is updated by another application
as
On Tue, 2006-08-29 at 16:30 -0400, Jesse wrote:
> Does anyone have any ideas?
One technique is to calculate set distances (5,10,25,50) between the zip
codes in advance and stick the results in a table.
Enjoy,
Ed
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
T
On Thu, 2006-07-06 at 13:41 -0700, Chuck Holzwarth wrote:
> Is there a way to set the auto_increment start number? I
> am trying to set up a development and test system and the
> application that is writing to the tables is confused as
> to which MySQL it is writing to. I don't have any contrtol
... err, as would be South...
N+, S-, E+, W-
Ed :-)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
On Tue, 2006-04-25 at 00:43 -0700, Daevid Vincent wrote:
> So for a sanity check, I decided to look "online" and punch in some to see
> what the "real" lat/long should be. Well, different sites give different
> values, and not only are they "slightly" off, but sometimes they're
> _positive_ or _neg
I am stomped and not sure how to get results from the particular type of
query. While I am not sure, if this is an appropriate place to ask, if
nothing else perhaps someone will direct me to a more appropriate forum.
I am trying to figure out how to return the latest record in each group
of recor
t;
> ability to easily poll for partial matches and easily determine gross
> matching rankings makes it useful for many applications.
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
> Test USER <[EMAIL PROTECTED]> wrote on 12/19/2005 08:28:36 A
; that starts with a
"letter" smaller than "8".
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Test USER <[EMAIL PROTECTED]> wrote on 12/09/2005 05:47:41 PM:
> Oh, is it really such a bad design? Here is some more.
> What is substring
When using IN should i design the database to use int's or is the performance
equal?
WHERE col IN('test','test2','test3')
vs
WHERE col IN(1,2,3)
-
FREE E-MAIL IN 1 MINUTE!
- [EMAIL PROTECTED] - http://www.pc.nu
--
MySQL General Mailing List
Fo
han 120 and
250. And my concerne is that i might be situations where it thinks that 80
should be returned when doing a >=120.
Quoting Michael Stassen <[EMAIL PROTECTED]>:
> Test USER wrote:
> > Hi again :)
> >
> > The table contains a column named value and is in th
esolution`,`hw_port`,`cd_supp_format`
>
> Your data is unmanageable in its present format and you need to scrub
> and
> massage it into shape before what you have will be marginally useful.
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
Sorry for the confusion!
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
>
> Test USER <[EMAIL PROTECTED]> wrote on 12/09/2005 04:28:44 PM:
>
> > Hello, thanks for your help!
> > I dont really get it :)
> >
> >
this still have their place.
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
> Test USER <[EMAIL PROTECTED]> wrote on 12/09/2005 03:30:17 PM:
>
> > in an application i have written there is the need to do a search from
>
> mysql
in an application i have written there is the need to do a search from mysql
using numbers that are stored in a varchar column. it is not possible to store
only the results with numbers in a seperate column.
so i was looking at CAST(), is this a big performance loss? is there some way
of benchma
i´m using mysqldump to dump some tables and then load it into another mysql
server with mysql command.
but can i specify what columns in the tables to dump?
i´m using something like this now
mysqldump -h localhost -u root db tbl | mysql -h xxx.xxx.xxx -u login -pass -w
db
> the wget inline though, or at least write something in shell or perl to
> do it. Is this cron'd or something, or a one time thing?
>
>
>
> -
> Sent from my NYPL BlackBerry Handheld.
>
>
> - Original Message -
> From: Test USER [EMAIL
Can't get this to work, but i would like to specify LOAD DATA to use an INFILE
from an URL.
For example
LOAD DATA LOCAL INFILE 'http://www.testserver.com/data.csv'
But i get an error message saying file not found.
Anyone know if this is even possible ?
-
Thanks i also found the function CAST which works ok.
So now i have three options:
CAST
LPAD
Adding zero
Any other sugestions are welcome!
Quoting [EMAIL PROTECTED]:
> Test USER <[EMAIL PROTECTED]> wrote on 11/23/2005 07:36:43 PM:
>
> > Is the only way to sort numbers s
Is the only way to sort numbers stored in a varchar column to use lpad?
Are there any other columntypes that allow both characters and numbers that can
sort numbers correct?
-
FREE E-MAIL IN 1 MINUTE!
- [EMAIL PROTECTED] - http://www.pc.nu
--
MyS
David Lloyd wrote ..
>
> Hi,
>
> > I have a problem connecting to the mysql server. I installed a new
> > server with mysql 4.1.9, apache2, php4 on freebsd5.3 and have some
> > websites running on it using the mysql server. Last friday I had a
> > crash of one off my other servers and I copied th
t least. Check the real value of
> open_file_limits with such statement:
> show variables like '%open_f%';
>
> You can find some recommendations for SuSe Linux at:
> http://dev.mysql.com/doc/mysql/en/Linux-post-install.html
>
> Mysql user <[EMAIL PROTEC
Hi..
I've got an ISP, and all of our customers have databases in our mysql
system.
My backup command is:
mysqldump --force --opt -A -p' | gzip -c >
/var/sqlbackup/mysqldump-`date +\%A`.sql.gz
This has worked fine for some time.
Now, however, I get an error message:
mysqldump: Got
Hi Guys,
I have a problem with MySQL in conjunction with PHP so
I also decided to post here:
I have a PHP script that contains two consecutive
MySQL queries, something like this:
Query 1: Delete some rows from Table A
Query 2: Insert some rows into Table A
The problem is, only Query 2 seems to
Hi
I have a hockey pool database and I want to be able to add each weeks
totals (goals, assists, etc.) for the players on each team.
Example:
Team 1 may have Hossa, Redden, and Forsberg. If each of them scored 2 goals
and 2 assists for week one, I want to be able to get the total of 12. Team
tw
I recently saw and article that says MySQL will be shipping its cluster software
starting April 14th during the Users Conference & Expo this year. Does anyone have
any information about this? My company is considering using the Emic clustering
software. Has anyone had experience with that? W
Questions - Number of queries sent to the server.
See http://www.mysql.com/doc/en/SHOW_STATUS.html for more info
MySQL user
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Subject: Re: MySQL+Apache Optimization
Date: Wed, 7 Jan 2004 15:12:19 +0530
Hello ,
What are Questions in My
"Noamn" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> I asked about a week ago how to get mySQL to index correctly in Hebrew,
and
> the best answer that I received was to define a my.cnf file as follows
> [mysqld]
> set-variable = default-character-set = hebrew
>
> I created the fi
Someone told me that it is possible that MySQL will automatically change
column types in certain situations. For example the a table with a char(5)
type field might dynamically change to a varchar(5) type field. So a static
length column to a variable length column. Is this possible? If so what a
Hi,
We copy data from one table to another using:
insert into TBL1 select * from TBL 2;
The current database hangs and the process never finish when copying huge
tables (around 25million rows). Looking at the processlist it states that
the process stays in "closing table" or "wait on cond" st
Is there any difference in speed between the following select statements?
SELECT yada,yda FROM test WHERE id IN(1,2,3)
OR
SELECT yada,yda FROM test WHERE (id =1 or id = 2 or id =3)
Could anyone tell me the difference between the following two explains? It
seems the first takes longer to execute.
This first query is like so...
select m.*, mi.age from members m, members_addtl_info mi where m.nick
like '%anynickname%' AND m.nick = mi.nick order by nick desc,
account_login_las
While I found some information on this subject:
http://groups.google.com/groups?q=mysql+win32+encrypt&hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=be3g14%24ulu%241%40FreeBSD.csie.NCTU.edu.tw&rnum=1
I can't make much sense of it.
Is there a *clear* way to have encrypt() call working on mySQL running on Wi
Hello everyone. I have copied the results from and explain on a query that I want to
use. And I am wondering if anyone could tell me if these results are bad or good?
If everything below is coming up garbled for you I will basically I am using 7 tables
and the rows for 6 of the seven are 1 and t
Hi,
Is there a way I can run a query that will delete all items that are not in
a list? For example I have a bunch of records in a table and I want to
remove all of them that are not in a comma delimited list that I have
recieved from another application. I was thinking that I could create a
que
>Description:
Preparing db table
Preparing host table
Preparing user table
Preparing func table
Preparing tables_priv table
Preparing columns_priv table
Installing all prepared tables
/usr/lib/dld.sl: Unresolved symbol: _GLOBAL__FI_libstdc___sl_5_0 (code) from /u
sr/local/mysql/libexec/mys
HI,
Can anyone figure this out ? I have been trying this out for quite some
time. It compiles fine but when I run
root@love(scripts)# ./mysql_install_db
Preparing db table
Preparing host table
Preparing user table
Preparing func table
Preparing tables_priv table
Preparing columns_priv table
,
KEY userid (userid)
) TYPE=MyISAM;
INSERT INTO privatemessage VALUES
(128,0,33,33,8,':D','',996121863,1,0,2,996122850,2,0,0);
DROP TABLE IF EXISTS user;
CREATE TABLE user (
userid int(10) unsigned NOT NULL auto_increment,
usergroupid smallint(5) unsigned NOT NULL defaul
Hi,
I have a big problem with last_insert_id() query.
I am adding records to a table with about half a million records in it. The
insert takes well under a second. I then call last_insert_id as I need to
make a link to another table.
The last_insert_id takes around 2 minutes!!! Yes *minutes*
Hi,
In my mysql .err file I get a lot of the following error...
"Aborted connection 121031 to db: ..connection details... (Got an error
reading communication packets)"
What does this mean, and is it bad, and what do I do about it?
Any help appreciated.
Howard
-
Hi,
In my mysql .err file I get a lot of the following error...
"Aborted connection 121031 to db: ..connection details... (Got an error
reading communication packets)"
What does this mean, and is it bad, and what do I do about it?
Any help appreciated.
Howard
-
Sounds as though you haven't got Perl installed, or more particularly the
DBI modules for MySql. If you on Linux, almost certainly to be found on your
Linux CDs.
HM
Suresh R. Soni writes:
> Hi All,
>
> I am getting following error msg when I try to install
> MySQL using rpm -i My*
>
>
Hi,
I my mysql server database directory (that is the directory that holds the
.err and .pid files) so strange files have apeared. They look line
-bin.001
-bin.002
etc
-bin.index
Some of them are huge! They seem like binary files, but are full readable
SQL commands that could be f
Hi,
Thanks I DID read the documentation.
BUT there documentation gives no information about how to choose values
for these settings. There are no clues at all. What are good values, and/or
what is the effect on performance etc. of the values?
Howard
Egor Egorov writes:
> mysql,
>
You have written the following:
I have a mysql database table that is currently 1.5G in size with well over
a
million records. It is running on a twin pentium 3 1G processor machine with
SuSE Linux version 1.4.
Recently inserts have become VERY slow (several seconds). As I am adding
around
I have a very large mysql table (1.5G) and so will need to implement the
table
RAID option soon.
How do I pick the CHUNKSIZE and number of chunks values?
Howard
-
Before posting, please check:
http://www.mysql.com/ma
Is there any way to do an Update...Select like an InsertSelect?
For instance can I fill one table with data from another table. The columns
do not match exactly so a table copy won't do much good. But the data
retrieved in the select command is compatible with the new table fields.
sql,que
Hi!
Where can I find the MySQL error messages ?
I have error message "can't find file host.MYD errno: 2"
Is it permission problems?
Lacko
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
+
| round(16.5 + 0.01) |
++
| 17 |
++
1 row in set (0.08 sec)
But this slows down processing somewhat.
>Submitter-Id:
>Originator:User &
>Organization:
>MySQL support: [none | licence | email support | ex
Hi,
I keep running into the following error trying to compile (running
./configure) mysql -3.23.38 on Solaris 8 x86:
"checking return type of sprintf... configure: error: can not run test
program while cross compiling"
I'm using gcc 2.95.3.
Has anyone compiled succesfully on sol8 x86 and is w
You should be using echo in place of print.
echo "OK 2";
>Robert Henkel
>Shouldn't you have on line 7
>print ("OK 2 ");
>and not
>print ("OK 2 ")
>Im not a PHP person but thats what I noticed in your code. And if ;
>terminates a command that can't be helping
>
>
>
>
>
>magic words sql database
I had the same problem on with Red Hat 7 using mysql-3.23.33.
I installed from RPM and source. Never could get the darn thing working.
I just scrapped .33 and downloaded .36. Works like a charm now.
DG
>Description:
I am having a problem starting the mysql daemon from the command line.
I a
ication stopped in log
'master-bin.001' at position 73
010303 1:41:46 /tmp/mysql-3.23.33/sql/mysqld: Shutdown Complete
User time 0.01, System time 0.00
Maximum resident set size 0, Integral resident set size 0
Non-physical pagefaults 95, Physical pagefaults 381, Swaps 0
Blocks in 0 out 0,
1:46 /tmp/mysql-3.23.33/sql/mysqld: Shutdown Complete
User time 0.01, System time 0.00
Maximum resident set size 0, Integral resident set size 0
Non-physical pagefaults 95, Physical pagefaults 381, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 0, Involuntary co
r at the beginning of the year, I decided to try the new versions with
the test suite. I tried on different SGI boxes we have here. Not one could
complete the tests. They would all lock at one point or another, waiting for
a signal.
Recently, I decided to test with a different user than root. I repo
he configure
script itself. gcc builds the test program without any errors, and
it runs fine, producing the correct output result.
>Submitter-Id:
>Originator:Super-User
>Organization: Core Matrix Foundation
>MySQL support: [none | licence | email support | extend
69 matches
Mail list logo
