On 1/18/11 10:22 AM, Simon Wilkinson wrote:
SELECT articles.* FROM articles INNER JOIN newsletters ON
articles.newsletter_id = newsletters.id INNER JOIN users ON users.id =
newsletters.user_id WHERE users.id =12 AND MONTH(articles.created_at) = '12'
AND DAY(articles.created_at) = '5' ORDER BY
On 1/16/11 5:22 AM, Jørn Dahl-Stamnes wrote:
mysql select album_id, updated_at, created_at from album_stats group by
album_id order by updated_at desc limit 8;
I believe that your problem is that the group by happens before the
order by. Since you're grouping, the updated_at column is not
On 1/17/11 9:52 AM, Jerry Schwartz wrote:
[JS] I don't understand how an index on a timestamp would help. Theoretically,
each record could have a unique value for the timestamp; so the index would
have an entry for each record. Would MySQL really use that in preference to,
or in combination
On 1/14/11 3:52 AM, Bruce Ferrell wrote:
select count(*) as count
from alerts where (unix_timestamp(stamp) (unix_timestamp(now()) -
'300' ) )
and devid = '244';
Bruce -
The problem is that the index is useless, because you're running a
function on the timestamp. What you want is this:
before doing this. It's probably the best solution in the long
term for you, but I can't guarantee that.
Another possibility would be to queue writes in a separate table (or
memcache, or something like that). Then do the writes in batches.
Steve Meyers
--
MySQL General Mailing List
For list
On 1/13/11 2:13 PM, Steve Staples wrote:
On Thu, 2011-01-13 at 13:51 -0700, Steve Meyers wrote:
On 1/13/11 1:21 PM, Steve Staples wrote:
table type is MyISAM, it is a customer_account table, which holds the
email address, and the customer_id field, the queries that are
constantly being
On 1/13/11 3:51 PM, Reindl Harald wrote:
Are you sure that the lags are really the query and not the connection?
I have seen on a windows server with ipv7 large lags because mysql
treid by every connect to make a dns-reverse-lookup first on ipv6
and after fail ipv4
skip-name-resolve in the
On 1/11/11 9:31 AM, Simon Wilkinson wrote:
select users.id from users where users.id in (select newletters.user_id from
newletters left join articles on newletters.id = articles.newsletter_id
where articles.newsletter_id is null);
I think this would do what you require:
SELECT
u.id AS
I've come up with pretty much the same solution to that problem. Here's
an alternative solution that requires a lot more work, but is prettier.
Set up a MySQL proxy server (of sorts). What it will do is act as a
slave to multiple masters, merge the log files it receives from them,
and act as
http://dev.mysql.com/doc/mysql/en/INSERT.html
INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name [(col_name,...)]
VALUES ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
If you specify the ON
David Griffiths wrote:
I'm not sure what the sql standard says on the matter, but Oracle, DB2
and Postgres would through an exception. In fact, there is a page on
MySQL gotachs to document MySQL behaviour when it differs
significnatly from other databases (like the first datetime field in a
increases our odds.
I just wondered how many other people have seen it, and if the suggested
export LD_ASSUME_KERNEL=2.2.5; mysqld_safe has worked for anyone.
In order to turn that on, I will need to take our site down completely,
which is (of course) not desirable.
Thanks!
Steve Meyers
--
MySQL
From http://dev.mysql.com/doc/mysql/en/Replication_Options.html
--log-slave-updates
Normally, updates received from a master server by a slave are not
logged to its binary log. This option tells the slave to log the updates
performed by its SQL thread to the slave's own binary log. For this
On Wed, 2001-11-21 at 05:19, Heikki Tuuri wrote:
The way to defragment InnoDB tables, or tables in any database, is from time
to time to dump and reimport them. That can give a significant performance
boost.
That is actually not entirely true. For MyISAM tables, one simply needs
to run
Wait for 4.1, it will have multi-table updates in it. Until then,
you'll have to use two separate queries.
Steve
On Wed, 2001-11-21 at 07:28, Charles Allen wrote:
Hi,
A v. basic question from a mySql newbie:
I want to update a table based on the contents of another table. The SQL I
am
is making it to whoever the intended recipient is anyway,
since their address is not in the TO field. If the taiwan.com address
was unsubscribed, I don't think it would actually have any detrimental
affects on anyone...
Just my 2c.
Steve Meyers
!!!
Try:
SHOW TABLES LIKE 'tablename'
Steve Meyers
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL
never been such a big rush to get them
in. In this case, you'd be better served by a join. Try:
SELECT tbl1.* FROM tbl1, tbl2 WHERE tbl1.column = tbl2.column AND
tbl2.intcolumn = 15;
Steve Meyers
-
Before posting, please check
opinion among many, I'm sure! Good luck with
whatever you do, but one way or another I hope we can all share the
benefits of your changes. If you do fork, perhaps it would be
appropriate to post announcements of new versions on this list?
Steve Meyers
in your machine. RAM is cheap :)
Steve Meyers
On Fri, 2001-11-09 at 18:08, Jeff Isom wrote:
I'm trying to figure out how to optimize a query on a fairly large table.
I've been reading the MySQL documentation and have tried a few of the
suggestions, but not seem to have much effect on the query
character strings (or even fairly short ones). A 32-bit hash value of a
20-character field takes 1/5 the space. That means five times as much
key can be in memory at once.
Hope that makes sense...
Steve Meyers
-
Before posting
, will allow quick lookups on the
author name, and will prevent duplicates. In essence, it gives the best
of both worlds, with one exception: you can't do partial lookups or
type searches.
Steve Meyers
-
Before posting, please
them in other tables and such. I assume its slightly faster
for MySQL to work with shorter integers than longer strings as primary keys
but I could be wrong.
Chris
It's not just slightly faster -- it's WAY faster, especially as your
tables grow larger.
Steve Meyers
on the main branch and add extra
value to it, such as Heikki has done. That way all users of MySQL can
benefit from your fixes, etc.
Steve Meyers
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http
of the features that are
on the TODO list.
I'm not trying to be disagreeable, I'm just not quite convinced yet and
would like to hear more from you about your reasoning and justification
for forking the code, as opposed to contributing to the main MySQL code
(even if it is in the 3.23 branch).
Steve
set that value
at all, like:
INSERT INTO Location (LocationID, Name, Address1, Address2, City, State,
Zip, Phone, Email, URL) VALUES (100, 1, 2, 3, 4, 5, 6, 7, 8, 9)
Steve Meyers
-
Before posting, please check:
http
columns as necessary.
Also, Rick, isn't it a bit unnecessary to use LIKE in your example?
Steve Meyers
On Fri, 2001-11-02 at 06:50, Rick Emery wrote:
Funny you should ask that. There was a similar questoin answer on the PH-DB
mailing list (different poster, though)
Answer is:
REPLACE
On Fri, 2001-11-02 at 09:57, Paul DuBois wrote:
At 9:47 AM -0700 11/2/01, Steve Meyers wrote:
That's a dangerous solution. If there are more columns in test2 than ID
and Value, the REPLACE will delete those values. As you noted in the
manual, the old record is deleted before the new record
, you can delete rows from it. For
example DELETE FROM user WHERE User='fred' would delete the user named
fred.
Steve Meyers
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http
On Tue, 2001-10-30 at 13:10, Bennett Haselton wrote:
I'm creating a database where one of the tables stores data about news Web
sites, and I'm using the URL of the site as a primary key field. This
field value might change occasionally. I'm wondering if this is bad
practice, especially
David,
First of all, please post to the list in the future. I'm not always
available to help with problems, and others may benefit from the
problem/solution.
I would change your query to the following:
SELECT DISTINCT a.addrdsp,a.listdate,a.solddate,a.lpricea,a.sprice
FROM archive a,
goes after the ORDER BY. On a related note, maybe you should
check for MySQL error codes when you run a query :)
Steve Meyers
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http
://www.innodb.com/
Steve Meyers
-Original Message-
From: Alex [mailto:[EMAIL PROTECTED]]
Sent: Thursday, October 25, 2001 6:56 AM
To: Steve Meyers
Subject: RE: foreign key
Hello Steve
I was reading this email that you did and Looks like you know this better
than I do
therefore I want
like I can help you find the best way to get your database working smoothly.
Steve Meyers
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive
Sure there could be problems if it's large. In my explanation (in a different
message) I noted that it wasn't the most efficient query in the world, but he wanted
one query to get that answer.
Steve Meyers
-Original Message-
From: Woolsey, Fred [mailto:[EMAIL PROTECTED]]
Sent
Yes, and yes. They're both documented in the manual.
http://www.mysql.com/doc/A/L/ALTER_TABLE.html
http://www.mysql.com/doc/I/N/INSERT.html
Steve Meyers
-Original Message-
From: Tony [mailto:[EMAIL PROTECTED]]
Sent: Thursday, October 25, 2001 12:57 PM
To: [EMAIL PROTECTED
to deal with this. is there a better way?
thanks!
There will be! Version 4.1 should have multi-table updates.
Steve Meyers
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http
from Mobile_Ringtone_Manialogs
where datesent between '2001-09-24' and '2001-10-24' and
(returncode 0 and returncode 10) group by hpnumber HAVING counts 10 order by
counts DESC
Steve Meyers
-
Before posting, please check
.
Steve Meyers
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL
http://www.mysql.com/doc/S/H/SHOW_PROCESSLIST.html
Steve Meyers
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e
http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Da
tabase_Administration.html#mysqld-max
I just tried that, it gave me a page not found error.
That's because the link is too long for one line -- try copy and pasting, making sure
to get the entire link.
Steve Meyers
I think you're looking for:
SELECT username, ip, count(*) FROM users GROUP BY 1, 2
Steve Meyers
-Original Message-
From: David Wolf [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, October 24, 2001 12:21 PM
To: [EMAIL PROTECTED]
Subject: Query help...
I'm trying to come up
I think I understand. This should work...
select distinct a.username, a.ip from users a, users b where a.ip=b.ip a.username
!= b.username;
Steve Meyers
-Original Message-
From: David Wolf [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, October 24, 2001 3:17 PM
To: Steve Meyers
I understand that you only have one table. The query I gave you joins the same table
against itself, and aliases it to a, b, and c. The only question was whether the
depth is always the same.
Steve Meyers
Hi
I have one table and the depth is not always the same.
Anyway thanks
be good enough though.
Steve Meyers
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED
buffer to keep as much index
information in memory as possible. The longer the key, the less info it can keep in
memory, and the more often it will have to swap to disk. If your key doesn't fit in
the key buffer, my tests have shown that there is a HUGE performance loss.
Steve Meyers
. However, I would not
include part of the URL in the index -- see my previous message about the key buffer.
Steve Meyers
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com
practice -- try running the PHP function nl2br() on
the data before displaying it.
Steve Meyers
-Original Message-
From: tim gales [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, October 23, 2001 9:31 PM
To: [EMAIL PROTECTED]
Subject: Formatting Large amounts of Text into Mysql
Hi. I am
having
fixed-length, very short rows.
If at all possible, I would try out several different structures, and run some test
scenarios on each of them.
Steve Meyers
-
Before posting, please check:
http://www.mysql.com
better for you, but I must warn you that finding the
max integer value of a char field is far from efficient...
SELECT MAX(FLOOR(field)) FROM table WHERE field RLIKE ^[0-9]+$
SELECT MAX(LPAD(field, 10, 0) FROM table WHERE field RLIKE ^[0-9]+$
Steve Meyers
, );
Then, when that's finished, you might have to try replacing all double spaces with
single spaces again, until the data is correct.
Steve Meyers
-Original Message-
From: Adrian D'Costa [mailto:[EMAIL PROTECTED]]
Sent: Monday, October 22, 2001 2:29 AM
To: Clyde Jones
Cc: Mysql
-- for
instance, lock the table, do a few extended inserts, and unlock it. As well as not
updating the index until all inserts are done, the extended insert also cuts down on
query overhead.
Hope that helps!
Steve Meyers
-Original Message-
From: Priya Ramkumar [mailto:[EMAIL PROTECTED
What is the best choice for my index on this query?
SELECT id_team,
sum(IF(m.id_visitor = t.id_team,m.visitor_score,
m.home_score)) AS But_pour,
sum(IF(m.id_visitor != t.id_team,m.visitor_score,
m.home_score)) AS But_contre
FROM
in the source, but it is
difficult to point them to it. For instance, people asking what various error codes
translate to... It would be nice to just point them to
http://www.mysql.com/source/somepath/somefile.h.
Just my 2c.
Steve Meyers
-Original Message-
From: Sinisa
is billions of times better (4 billion, to be exact).
Steve Meyers
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail
, you'll have to do a recursive search (keep on querying
for the next parent until you run out of parents).
Steve Meyers
-Original Message-
From: Daniel a [mailto:[EMAIL PROTECTED]]
Sent: Monday, October 22, 2001 3:34 PM
To: Lista MySQL
Subject: Tree in SQL
Hi
I have a table
keys.
Steve Meyers
-Original Message-
From: Chris Bolt [mailto:[EMAIL PROTECTED]]
Sent: Saturday, October 20, 2001 8:31 PM
To: [EMAIL PROTECTED]
Subject: RE: Length limit of 500 on primary keys?
Is there a way to raise this limit? We have some tables with
columns
in the MySQL
`errmsg.h' header file. Server error message numbers are listed in `mysqld_error.h'.
In the MySQL source distribution you can find a complete list of error messages and
error numbers in the file `Docs/mysqld_error.txt'.
Steve Meyers
-Original Message-
From: Javier Armendáriz
, and repeat the above. And from
now on, make sure the data gets put in right :)
Steve Meyers
-Original Message-
From: Adrian D'Costa [mailto:[EMAIL PROTECTED]]
Sent: Thursday, October 18, 2001 11:22 PM
To: Steve Meyers
Cc: DL Neil; Mysql Mailing List
Subject: RE: how to get the correct
That will be in some release of either 4.0 or 4.1, I'm not sure which.
Steve Meyers
-Original Message-
From: JohnHomer [mailto:[EMAIL PROTECTED]]
Sent: Friday, October 19, 2001 2:11 AM
To: [EMAIL PROTECTED]
Subject: join tables on UPDATE
hi list,
can mysql allow table
See:
http://www.mysql.com/doc/C/R/CREATE_INDEX.html
http://www.mysql.com/doc/M/y/MySQL_indexes.html
Steve Meyers
-Original Message-
From: Michael [mailto:[EMAIL PROTECTED]]
Sent: Friday, October 19, 2001 2:07 PM
To: [EMAIL PROTECTED]
Subject: indexing question
Can anyone
), but if this will not work, switching to
InnoDB may be the most beneficial for you.
http://www.mysql.com/doc/T/a/Table_locking.html
Steve Meyers
-Original Message-
From: Dan Uyemura [mailto:[EMAIL PROTECTED]]
Sent: Friday, October 19, 2001 3:31 PM
To: mySQL List
Subject: Locked
You haven't given your table definition, but I'm guessing that your primary key is a
TINYINT, which only supports -128 to +127. Figure out how big you need that key to
be, then change the column as appropriate to a SMALLINT, MEDIUMINT, INT, or BIGINT.
Steve Meyers
-Original Message
Okay, then I'll go through it point by point :)
Thanks. I've read the manual. I guess I was looking for a more direct
explanation to make sure I had it clear and to learn any tips that might
be useful that wouldn't be in the manual. As my database will be quite
large I'm worried about
So there is no magic bullet that indexes everything so it works well with
any given query? Does it help to index each field by itself for general
queries and then I guess you index combinations of fields that will be
used together in a WHERE clause?
Nope, no magic bullet... Indexes speed
InnoDB tables support foreign keys with full referential integrity constraints. They
do not yet support cascading deletes and updates. You'll want to use the MySQL-Max
version of MySQL to get support for InnoDB tables.
Steve Meyers
-Original Message-
From: Sandra Rovena Frigeri
Since this is an open source product, it could happen a lot sooner if you wrote it :)
I don't know of any plans to include that, at least in the near future.
Steve Meyers
-Original Message-
From: can [mailto:[EMAIL PROTECTED]]
Sent: Friday, October 19, 2001 8:14 PM
To: [EMAIL
That's why I told you to keep on running it until it was all fixed. Every time there
will be one less white space, until you're down to just one.
Steve Meyers
-Original Message-
From: Adrian D'Costa [mailto:[EMAIL PROTECTED]]
Sent: Friday, October 19, 2001 3:44 AM
To: Steve
Well, for one, I believe that Slashdot uses InnoDB tables, which tend to handle a
little better under very high load.
Steve Meyers
-Original Message-
From: Matthew Bloch [mailto:[EMAIL PROTECTED]]
Sent: Thursday, October 18, 2001 3:34 AM
To: [EMAIL PROTECTED]
Cc: Peter Taphouse
. However, if you really need it lowercase,
try this:
SELECT LOWER(nome_hotel) FROM hotel GROUP BY 1
Steve Meyers
-Original Message-
From: DL Neil [mailto:[EMAIL PROTECTED]]
Sent: Thursday, October 18, 2001 4:46 AM
To: Adrian D'Costa; Mysql Mailing List
Subject: Re: how to get
It would help if you posted the actual query and results you are getting, instead of
showing simulated results. You mention the query below in your first post, but you
never give the actual results of it.
Steve Meyers
-Original Message-
From: Ashwin Kutty [mailto:[EMAIL PROTECTED
Since it's a LIKE, you need to put the table name in quotes:
SHOW COLUMNS FROM test LIKE 'Var'
This also allows you to use things like 'Var%' in you query.
Steve Meyers
-Original Message-
From: TD - Sales International Holland B.V. [mailto:[EMAIL PROTECTED]]
Sent: Thursday
Could you also give a sample of the results you're getting that are incorrect? You
don't have to give every field, just the title should do.
Steve Meyers
-Original Message-
From: Ashwin Kutty [mailto:[EMAIL PROTECTED]]
Sent: Thursday, October 18, 2001 11:31 AM
Cc: [EMAIL
73 matches
Mail list logo
