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 user_i
tion, and you should do some
research 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
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
consta
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 my
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:
S
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 with,
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 YEA
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
ately
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!
St
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
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
t
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 DUPLICATE
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]
e done in a case insensitive manner. 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'
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 Kutt
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:[EMA
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
returns (\r) with a space, 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
ng up the ordering?
Steve Meyers
> -Original Message-
> From: Sommai Fongnamthip [mailto:[EMAIL PROTECTED]]
> Sent: Friday, October 19, 2001 3:19 AM
> To: [EMAIL PROTECTED]
> Subject: group by clause
>
>
> Hi,
> did someone tell me why group by clause
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
>
>
>
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 quest
), 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 Li
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
> -Ori
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 ef
> 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 sp
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: Sa
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, 200
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,
The problem is that you're setting all the rows to "%". The % symbol is only
recognized as a wildcard in like expressions, as documented in the manual. Try this
instead:
UPDATE words_bak1 SET word = LEFT(word, LENGTH(word) - 1) WHERE word LIKE "%s";
Steve Meyers
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
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 Messag
be text fields. This would keep your most frequently used table 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
--
onder why the ODBC can't handle it... One of
the following queries may work 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
_hotel=REPLACE(nome_hotel, "\r", " ");
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
for you -- 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 [ma
> 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
>
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:
reds of
thousands of unique URL's stored in our database. We found one collision. A 64-bit
hash is billions of times better (4 billion, to be exact).
Steve Meyers
-
Before posting, please check:
http://www.mysql.com/ma
depth, 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
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.
>
>
ly a
128-bit hash, which you could store across two fields in MySQL. I'm guessing that the
64-bit hash will probably be good enough though.
Steve Meyers
-
Before posting, please check:
http://www.mysql.com/manual.p
in reasons you would NOT want
to use a 500-character primary key. MySQL uses a key 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 buf
e
care of uniqueness. If not, then you can do it that way. 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
s standard HTML 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 La
s are much more useful.
In this case, you need to put your clause "counts > 10" in a HAVING clause. Try this:
select hpnumber,count(*) as counts from Mobile_Ringtone_Manialogs
where datesent between '2001-0
this was after
several months of abuse -- it shouldn't be the case on a newly created table.
Steve Meyers
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/
nd he told you exactly where to find it. Try SHOW PROCESSLIST
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.
> > 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
t
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 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:
y in the world, but it should be fine if it's
not done frequently, or there isn't much traffic on the table.
Hope that helps!
Steve Meyers
-
Before posting, please check:
http://www.mysql.com/manual.php (the ma
://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 k
ery= "select distinct T3.service, T1.application,
> >T2.machine from T3,T1 LEFT JOIN T2 ON
> >T1.application=T2.application where T1.service=T3.service";
> >
Yes, you need another left join.
select distinct t3.servic
ose problems
There are other possible solutions. I've done some work in similar environments, if
you'd like I can help you find the best way to get your database working smoothly.
Steve Meyers
-
Before posting, please check:
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 PR
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:
Use the latest verson of InnoDB, it supports foreign key constraints.
See the documentation for more info on how to declare foreign keys.
Steve Meyers
On Fri, 2001-10-26 at 06:13, McGrotty, Charles wrote:
> Hello,
>
> I have tables in a user administration database and nee
the two tables)
> and LOAD DATA INFILEing 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
n the same query at the MySQL prompt,
do you get the full result set you're looking for?
Steve Meyers
> -Original Message-
> From: M. A. Alves [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, October 30, 2001 11:23 AM
> To: [EMAIL PROTECTED]
> Subject: What are MYSQL_RES
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, especiall
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, archive
You might have a corrupt table. For 3.22 format, you'll need to use
isamchk, I think... It's been a long time since I've worked with ISAM
tables.
Now that I look closer at your email, though, it is probably the fact
that your id's start with 0, not 1. The alter table actually dumps the
data in
>execute;
>
>
http://www.mysql.com/doc/S/E/SELECT.html
The LIMIT 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, pleas
Mark,
That does look like a bug, or at least a much-needed feature. I've
verified that this still doesn't work with the latest 4.0 code.
On Thu, 2001-11-01 at 11:54, Mark Jenison wrote:
> Hi all,
>
> I created a table with the key defined as binary(15), then inserted a
> record like this:
>
>
X(s.date)
Steve Meyers
> At 07:52 PM 11/1/01, Alejandro Zuzenberg wrote:
> >I need to select the appropriate price for a product in a table, and the
> >product has a compund key with 2 fields: product number and date.
> >For every transaction with a certain product, I need to r
lumn. Repeat for as many 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 (differ
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
"user". It's just a normal table, you can delete rows from it. For
example "DELETE FROM user WHERE User='fred'" would delete the user named
"fred".
Steve Meyers
-
Before posting,
between the two.
For the first, see the INSERT INTO table SELECT ... syntax.
For the second, extend that by using the db.table syntax for the table
you're selecting from.
Steve Meyers
-
Before posting, please check:
http://ww
t a value -- namely, '', which becomes a
0. Try NULL instead, or better, try a query that doesn't 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
t is best to use hash values when indexing long
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
me.
>
> The manual will tell you lots more about auto_increment and how keys are used.
>
> b.
If searches will always be on exact matches, then use a hash value for
the primary key -- it's an integer, will allow quick lookups on th
cially when
> you reference 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, especia
noise. Just to the point if we can.
>
I think it would be more useful to work 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
--
the code, as opposed to contributing to the main MySQL code
(even if it is in the 3.23 branch).
Steve Meyers
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (
n pretty much do whatever you want, and my
opinion is just one 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
re RAM, if
it will fit 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, b
and FROM headers, while it should only look at the RCPT and FROM SMTP
commands. It's really rather amusing, until you get dozens of them.
Because of the above problem with the mailer, I don't believe that the
MySQL list is making it to whoever the intended recipient is anyway,
si
and properly
> Please help !!!
>
Try:
SHOW TABLES LIKE 'tablename'
Steve Meyers
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the l
idea anyway, which is why it's 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
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
83 matches
Mail list logo
