Re: Update Column in table only if variable is Not NULL

nly update the FieldName2 field if the value of MyVariable is NOT NULL ? Thanks Neil -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- "Sometimes it pays to stay in bed on Monday, rather than spending the rest of the week debugging Monday's code.&quo

Re: Performance hiccoughs..

On 8/14/13 10:46 AM, Manuel Arostegui wrote: 2013/8/14 Andy Wallace mailto:awall...@ihouseweb.com>> Hey all - We have been focusing on performance in our systems a lot lately, and have made some pretty good progress. Upgrading the mySQL engine from 5.1 to 5.5 was eye-o

Performance hiccoughs..

of buffer pool size innodb_log_file_size=100M innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_lock_wait_timeout=50 innodb=on -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- "Sometimes it pays to stay in bed on Monday,

database perfomance worries

`parent_acnt` varchar(20) DEFAULT NULL, `number_of_agentlinks` int(11) DEFAULT NULL, `number_of_emails` int(11) DEFAULT NULL, `fname` varchar(30) DEFAULT NULL, `lname` varchar(30) DEFAULT NULL, <> PRIMARY KEY (`acnt`), KEY `parent_acnt` (`parent_acnt`), KEY `status` (`status`), KEY

Re: database perfomance worries

2/13 3:50 PM, Reindl Harald wrote: Am 02.07.2013 23:28, schrieb Andy Wallace: mysql> update agent set number_of_emails = 5 where acnt = 'AR287416'; Query OK, 1 row affected (36.35 sec) Rows matched: 1 Changed: 1 Warnings: 0 36 seconds to update one table? The primary

Re: NOW() is stuck...

ec) mysql> show global variables like 'timestamp'; +---++ | Variable_name | Value | +---++ | timestamp | 1372404371 | +---++ 1 row in set (0.00 sec) re, wh Am 27.06.2013 20:19, schrieb Andy Wallace: Be

Re: NOW() is stuck...

it N. -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- "Sometimes it pays to stay in bed on Monday, rather than spending the rest of the week debugging Monday's code." - Christopher Thompson -- MySQL General Mailing List For list

Re: NOW() is stuck...

13 12:22 PM, Claudio Nanni wrote: Hi, On 06/27/2013 08:19 PM, Andy Wallace wrote: Benjamin - Unfortunately: mysql> show global variables like 'timestamp'; +---++ | Variable_name | Value | +---++ | timestam

Re: NOW() is stuck...

hope that it would fail, but the only thing I can think of is if it's being set as a global variable. If this does fix your problem, and if you're using replication, you may have an issue with your replicated data. Replication uses timestamp extensively. On 6/27/13 1:44 PM, "Andy Wa

Re: NOW() is stuck...

eturn anything? On Thu, Jun 27, 2013 at 11:19 AM, Andy Wallace wrote: Benjamin - Unfortunately: mysql> show global variables like 'timestamp'; +---++ | Variable_name | Value | +---++ | timestamp | 1372238834 | +---+-

Re: NOW() is stuck...

| sysdate() | +-+-+ | 2013-06-27 13:21:34 | 2013-06-27 13:21:34 | +-+-+ 1 row in set (0.00 sec) Cliff's notes: set timestamp = 0; On 6/26/13 6:10 PM, "Andy Wallace" wrote: We've been having some issues with one of ou

Re: NOW() is stuck...

4 | +-+ 1 row in set (0.00 sec) On Wed, Jun 26, 2013 at 4:18 PM, John Meyer wrote: Well, if you want to get unstuck in time, maybe you need to call Billy Pilgrim ;-) Andy Wallace wrote: We've been having some issues with one of our MySQL servers lately, and currently t

NOW() is stuck...

em time on the machine is correct: $ date Wed Jun 26 15:08:56 PDT 2013 This is MySQL 5.1.46 running on solaris2.10. Any ideas short of restarting the MySQL engine? I'm willing to do that, but would much rather wait and not do it in the middle of the day. Thanks, Andy -- Andy Wallace iHOUSEweb,

Re: Understanding Slow Query Log

e entire list has been paged through, about N*N/2 units of work have been done. My technique is Order(1) for a page, Order(N) for a complete scan. N is the number of pages. Some implementations have more than 10,000 pages. 10,000 * 10,000 = 100 million ! -Original Message- From: And

Re: Understanding Slow Query Log

Ok, this raises a question for me - what's a better way to do pagination? On 9/5/12 2:02 PM, Rick James wrote: * LIMIT 0, 50 -- are you doing "pagination" via OFFSET? Bad idea. -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- "Sometime

Re: strange select/join/group by with rollup issue....

-02-08' " and see what you get. HTH, Arthur -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- There are two ways to build software: Make it so simple that there are obviously no bugs, or make it so complex that there are no obvious bugs. -- MySQL General M

strange select/join/group by with rollup issue....

f I get rid of the group by (and the count(*)), there are rows with all 7 dates. I have tried changing the "to" date from '2012-02-07' to '2012-02-08', in case this was a "less than" issue, but that doesn't change. Why is the group by dropping the la

Re: why does basic MySQLdump on db table fail with "Permission denied", or else it creates a zero KB file

in my knowledge. .. but I would appreciate any help ;-) [1] http://stackoverflow.com/questions/8663454/why-does-basic-mysqldump-on-db-table-fail-with-permission-denied Thanks -Govinda -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- There are two ways to b

Re: Deleting Records in Big tables

to increase the id value: DELETE FROM my_big_table WHERE id> 5000; DELETE FROM my_big_table WHERE id> 4000; etc -- Derek On Nov 4, 2011, at 12:47 PM, Andy Wallace wrote: I've had some luck in the past under similar restrictions deleting in chunks: delete from my_big_table where id&

Re: Deleting Records in Big tables

oo takes time. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=anan...@gmail.com -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- There are two ways to build software:

Re: Help with slow query

ne is varchar, the other int) This last one can be HUGE. I tracked a big performance issue to this exact problem - the columns used in the join had the same name, but different data types. Correcting to be the same type (both ints) made a terrific performance increase. -- Andy Wallace iHOUSEweb,

Re: Question about database value checking

TO tester_table2 (acnt, required) VALUES ('A123456', 'THIS IS NOT REAL'); Though it just puts an empty string into the "required" column. Is there a setting for mysql to return errors in these cases? It seems silly to set up an enum column, or a not null column, and not h

Question about database value checking

RSET=utf8 Lets you insert: INSERT INTO tester_table2 (acnt, required) VALUES ('A123456', 'THIS IS NOT REAL'); Though it just puts an empty string into the "required" column. Is there a setting for mysql to return errors in these cases? It seems silly to set up an

Re: Table/select problem...

o holes. Should try this sometime. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- There are two ways to build software: Make it so simple that th

Table/select problem...

** 3. row *** id: 1 select_type: SIMPLE table: E type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: idx_acnt.EL.enduser_acnt rows: 1 Extra: Using where 3 rows in set (0.00 sec) --

Re: question about restoring...

nably simple to extract what you need; in the former case you're gonna have to try attaching them to a new instance - good luck with that. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Andy Wallace iHOUSEweb, In

question about restoring...

, ansdy -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- There are two ways to build software: Make it so simple that there are obviously no bugs, or make it so complex that there are no obvious bugs. -- MySQL General Mailing List For list archives: http

Update query problem

M.domain = IF( (!M.domain AND A.domain != ''), A.domain, M.domain) Any thoughts? THanks, andy -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- There are two ways to build software: Make it so simple that there are obviously no bugs, or make it so complex

Re: Possible tricks to ALTER on huge tables?

YMMV andu Jigal van Hemert wrote: Daevid Vincent wrote: We currently have some tables that are approaching 1 BILLION rows (real Billion, with nine zeros, not that silly six zero version). Trying to do an "ALTER" on them to add a column can sometimes take hours. -- Andy Wallace i

Re: IS NULL returns Empty set, but I have empty items

re is the table structure http://pastie.org/1066140 thanks for any insight. -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- There are two ways to build software: Make it so simple that there are obviously no bugs, or make it so complex that there are no obvious

Re: Join on a where clause.

A couple of thoughts - it's not "no quotes on integers", but "no quotes around column references". When you use 'mappings.end_ip', you are saying "the string mappings.end_ip", and not referring to a column in the mappings table. It just becomes a constant at that point. As for the performance, yo

Re: inserting csv - solved, but more to the puzzle

I've run into similar situations w/regard to Mac vs PC CSV files, it usually has to do with the EOL character sequence. Macs use LF (chr(10)), while PCs use CRLF (chr(13)chr(10)). andy Patrice Olivier-Wilson wrote: In case anyone might find this of the least interest, probably not, but I always

Re: avoiding use of Nulls

ggghh mich...@j3ksolutions.com wrote: On Fri, 13 Mar 2009, mich...@j3ksolutions.com wrote: Explanation(5): The more you understand how the database is to be used, and the more complexity and thought you put into your database design, the less co

Re: Selecting my data first

don't know how. Thanks, - Jake -- Andy Wallace - CISData - IDX Slave AIM: acmwallace [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: question about update/join query

ll table scan on the event_log table, which can be very bad if it is large. You can work around this by compiling groups of users at a time. This will require querying the users and for the group of users and joining on the event_log table. Brent Baisley Systems Architect On Thu, May 8, 2008 at 12

Re: question about update/join query

group by EL.event_time); // group by EL.enduser_acnt); HTH Martin - Original Message ----- From: "Andy Wallace" <[EMAIL PROTECTED]> To: "Martin" <[EMAIL PROTECTED]> Cc: "mysql list" Sent: Wednesday, May 07, 2008 6:21 PM Subject: Re: qu

Re: question about update/join query

group by EL.event_time); // group by EL.enduser_acnt); HTH Martin - Original Message ----- From: "Andy Wallace" <[EMAIL PROTECTED]> To: "Martin" <[EMAIL PROTECTED]> Cc: "mysql list" Sent: Wednesday, May 07, 2008 6:21 PM Subject: Re: questi

Re: question about update/join query

se in UPDATE statement? M - Original Message - From: "Andy Wallace" <[EMAIL PROTECTED]> To: "mysql list" Sent: Wednesday, May 07, 2008 1:07 PM Subject: Re: question about update/join query Clarification: I DON'T want to update the last_visit field if the

Re: question about update/join query

_acnt = E.enduser_acnt group by EL.enduser_acnt); but it updated the last_visit field to the default value if it found no matching event_log row... which I don't want to happen. thanks, andy Andy Wallace wrote: Hey all - I have two tables - an event_log table, and a user table.

question about update/join query

duser_acnt int table enduser enduser_acnt int last_visit datetime Any help appreciated. Thanks... andy -- Andy Wallace - CISData - IDX Slave AIM: acmwallace [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscrib

Re: Help with ORDER BY using two colomns [ solved thankyou :) ]

For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]

Re: MYSQL Limit

ast minute shopping deals? Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping -- Andy Wallace - CISData - IDX Slave AIM: acmwallace [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To

Re: Newbie: A single number

ave just 47 as a result of the SELECT. Any help would be appreciated. Warm regards, Mário Gamito -- Andy Wallace - CISData - IDX Slave AIM: acmwallace [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: help with select

y from idea where iid=1 i got: 1 | 4 | 10 2nd trial: select idd,concat(first," ",last),submitted_by from idea,employee where iid=1 and completed_by=eid; i got: 1 | betty smith | 10 now, instead of 10 for the submitted_by column, how do i get sun mcnab? thanks, T. Hiep -- Andy