ERROR 2013 (HY000): Lost connection to MySQL server during query
Hi, I am trying to load data into my table from a very large file but after some time I get this error: ERROR 2013 (HY000): Lost connection to MySQL server during query My file size is around 4G and I have 220M lines in my file which have to be loaded in to my table. I have 10 of these files which have to loaded in the same table. My MySQL version is 5.1.59, I have changed the max_allowed_packet to 346030080. any ideas how I can solve this problem? Best regards, Javad Bakhshi,
Re: ERROR 2013 (HY000): Lost connection to MySQL server during query
Il 01/03/2012 11:03, javad bakhshi ha scritto: Hi, I am trying to load data into my table from a very large file but after some time I get this error: ERROR 2013 (HY000): Lost connection to MySQL server during query My file size is around 4G and I have 220M lines in my file which have to be loaded in to my table. I have 10 of these files which have to loaded in the same table. My MySQL version is 5.1.59, I have changed the max_allowed_packet to 346030080. any ideas how I can solve this problem? Best regards, Javad Bakhshi, In order to resolve the problem you may need to increase these two values in mysql.ini or my.cnf, and restart mysql: wait_timeout = 28800 connect_timeout = 28800 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: does the number of column affect performance
- Original Message - From: Baron Schwartz ba...@xaprb.com You may be interested in this: http://www.mysqlperformanceblog.com/2009/09/28/how-number-of-columns-affects-performance/ Heeh, very interesting. You guys keep churning out the good stuff :-) -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How do I use a dynamic filename for an into outfile statement
2012/02/29 11:43 -0500, Ed Patterson The select concat() works from the command line I can manually add the file name but would like to automate the process Lastly, if it makes a difference, I don't use any graphical tools for DB stuff. Look up the command! http://dev.mysql.com/doc/refman/5.5/en/select.html Aside from PREPARE, the OUTFILE-name does not automate. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How do I use a dynamic filename for an into outfile statement
By using your shell's variable substitution, I'm afraid. export outfile='/path/to/file' mysql -e select blah into outfile '$outfile' You'll have to build a shell script or something that generates your filename, and then do an interactive call. Maybe you could write a stored procedure that generates a query string and then executes it, too - I've little to no experience with SPs in MySQL. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Getting data from 2 tables if records have same date!
On 2/29/2012 5:54 PM, LUCi5R wrote: JW, I'm trying to understand LEFT JOIN as we go - but it's not working. This query SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE WHERE CUSTOMERS.DATE = 02/28/12 AND (CALLS.PHONE IS NULL OR CALLS.DATE = 02/28/12) Is giving me some results which I'm not quite sure what they are - but it's not the right results. The way I'm testing is, on 02/28/12 I had 57 Customers created in the CUSTOMERS table. I also had a total of 105 Calls recorded in the CALLS table. Some calls were from the same customers more then once. Essentially, I need the result to be 86 which I got from some manual calculations. Out of those 86 records, 1 record is in the CUSTOMERS table but not in the CALLS table. The other 85 were in both tables. The above LEFT JOIN query gave me 69 records and quite a few duplicate entries. I'm trying to dissect it to understand what exactly it selected. Thanks! ~~ LUCi5R e: luc...@luci5r.com w: http://www.luci5r.com From: Johnny Withers [mailto:joh...@pixelated.net] Sent: Wednesday, February 29, 2012 1:30 PM To: luc...@luci5r.com Cc: mysql@lists.mysql.com Subject: Re: Getting data from 2 tables if records have same date! Sounds like you need to LEFT JOIN: SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND CALLS.DATE = 02/28/12 WHERE CUSTOMERS.DATE = 02/28/12 But that would only get customers created on 2/28 AND having a call on 2/28 OR not call at all on 2/28. This would give you customers created on 2/28 with no calls AND customers created on 2/28 with a call on 2/28: SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE WHERE CUSTOMERS.DATE = 02/28/12 AND (CALLS.PHONE IS NULL OR CALLS.DATE = 02/28/12) Try this: SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND CUSTOMERS.DATE=CALLS.DATE WHERE CUSTOMERS.DATE = 02/28/12 This will give you a list of all customers for a given date and a list of every call they made on that date. If a customer made no calls on a date, then all of the columns for that table will be NULL. If you only want a list of customers and details about the calls on a date then an INNER JOIN is appropriate. If you want to see the full list of customers and any calls on that date use this: SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND CUSTOMERS.DATE=CALLS.DATE = 02/28/12 If you only want a list of customers that made any calls on a given date, you can use the EXISTS comparator like this: SELECT customers.* FROM customers WHERE EXISTS (SELECT * FROM calls WHERE CUSTOMERS.PHONE = CALLS.PHONE AND CUSTOMERS.DATE=CALLS.DATE = 02/28/12) http://dev.mysql.com/doc/refman/5.5/en/exists-and-not-exists-subqueries.html It's possible to get you any combination of data you want, we just need you to clarify the relationship you are trying to find and how much data you really want to get back. NOTE: the name of the column date is using a reserved word. You may want to enclose it in backticks to avoid confusion as in `date`. Also, the standard MySQL syntax for date literals uses ISO notation. So instead of using 02/28/12 (using double quotes) I expected to see '2012-02-28' (using single quotes) -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Getting data from 2 tables if records have same date!
On Thu, Mar 1, 2012 at 8:57 AM, Shawn L Green shawn.l.gr...@oracle.comwrote: On 2/29/2012 5:54 PM, LUCi5R wrote: JW, I'm trying to understand LEFT JOIN as we go - but it's not working. This query SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE WHERE CUSTOMERS.DATE = 02/28/12 AND (CALLS.PHONE IS NULL OR CALLS.DATE = 02/28/12) Is giving me some results which I'm not quite sure what they are - but it's not the right results. The way I'm testing is, on 02/28/12 I had 57 Customers created in the CUSTOMERS table. I also had a total of 105 Calls recorded in the CALLS table. Some calls were from the same customers more then once. Essentially, I need the result to be 86 which I got from some manual calculations. Out of those 86 records, 1 record is in the CUSTOMERS table but not in the CALLS table. The other 85 were in both tables. The above LEFT JOIN query gave me 69 records and quite a few duplicate entries. I'm trying to dissect it to understand what exactly it selected. Thanks! ~~ LUCi5R e: luc...@luci5r.com w: http://www.luci5r.com From: Johnny Withers [mailto:joh...@pixelated.net] Sent: Wednesday, February 29, 2012 1:30 PM To: luc...@luci5r.com Cc: mysql@lists.mysql.com Subject: Re: Getting data from 2 tables if records have same date! Sounds like you need to LEFT JOIN: SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND CALLS.DATE = 02/28/12 WHERE CUSTOMERS.DATE = 02/28/12 But that would only get customers created on 2/28 AND having a call on 2/28 OR not call at all on 2/28. This would give you customers created on 2/28 with no calls AND customers created on 2/28 with a call on 2/28: SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE WHERE CUSTOMERS.DATE = 02/28/12 AND (CALLS.PHONE IS NULL OR CALLS.DATE = 02/28/12) Try this: SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND CUSTOMERS.DATE=CALLS.DATE WHERE CUSTOMERS.DATE = 02/28/12 This will give you a list of all customers for a given date and a list of every call they made on that date. If a customer made no calls on a date, then all of the columns for that table will be NULL. If you only want a list of customers and details about the calls on a date then an INNER JOIN is appropriate. If you want to see the full list of customers and any calls on that date use this: SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND CUSTOMERS.DATE=CALLS.DATE = 02/28/12 If you only want a list of customers that made any calls on a given date, you can use the EXISTS comparator like this: SELECT customers.* FROM customers WHERE EXISTS (SELECT * FROM calls WHERE CUSTOMERS.PHONE = CALLS.PHONE AND CUSTOMERS.DATE=CALLS.DATE = 02/28/12) http://dev.mysql.com/doc/**refman/5.5/en/exists-and-not-** exists-subqueries.htmlhttp://dev.mysql.com/doc/refman/5.5/en/exists-and-not-exists-subqueries.html It's possible to get you any combination of data you want, we just need you to clarify the relationship you are trying to find and how much data you really want to get back. NOTE: the name of the column date is using a reserved word. You may want to enclose it in backticks to avoid confusion as in `date`. Also, the standard MySQL syntax for date literals uses ISO notation. So instead of using 02/28/12 (using double quotes) I expected to see '2012-02-28' (using single quotes) -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN Another gem from Shawn. ;-)
Re: How do I use a dynamic filename for an into outfile statement
On Feb 29, 2012, at 10:43 AM, Ed Patterson wrote: Be kind, I am by no means any type of DB expert. I would like to eventually move this to a stored procedure but for now I am using the \. to fire it off. Here is what I have so far (generating a list of machines missing software) select last_logon.host_name_short from last_logon left join mcafee on last_logon.host_name_short = last_logon.host_name_short where mcafee.host_name_short is null and last_logon.host_name_short like 'w%' -- the above works into outfile (select concat('Missing-',date_format(now(),'%Y%m%d%H%i'),'txt'); -- this line breaks it The select concat() works from the command line I can manually add the file name but would like to automate the process Lastly, if it makes a difference, I don't use any graphical tools for DB stuff. Inevitably someone says 'click here' :-) Thanks for any help Ed -- create statement, assign to user variable set @s = concat(' select last_logon.host_name_short from last_logon left join mcafee on last_logon.host_name_short = last_logon.host_name_short where mcafee.host_name_short is null and last_logon.host_name_short like \'w%\' into outfile \'Missing-',date_format(now(),'%Y%m%d%H%i'),'.txt\''); -- display so you can verify what it looks like select @s; -- prepare statement, execute it, discard it prepare s from @s; execute s; deallocate prepare s; Note: I added a '.' before 'txt' and a closing quote to the file name. http://dev.mysql.com/doc/refman/5.5/en/sql-syntax-prepared-statements.html -- Paul DuBois Oracle Corporation / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Getting data from 2 tables if records have same date!
JW, Youre correct .. that is what I was getting with the LEFT JOIN and therefore it wasnt the correct answer. I was able to get the correct answer using UNION ALL, however, like you said, I needed 2 queries in that case. One to get the PHONE numbers from both tables and a 2nd query to get all the CUSTOMERS matching the PHONE numbers from the CUSTOMERS table. Although I still have a few issues with that (since I cant use a VIEW cause VIEWS dont allow subqueries in SELECT statements; and I dont really want to create a new table every time this query is run) but anyhow, this is the UNION ALL query that got the correct 86 records result: SELECT PHONE FROM ( (SELECT PHONE,DATE FROM CUSTOMERS) UNION ALL (SELECT PHONE,DATE FROM CALLS) ) results WHERE DATE = 02/28/12 GROUP BY PHONE; I would still like to explore the possibility of doing this using 1 single query which gives me the results I need rather than first getting the correct PHONE numbers; inserting them in a temporary table; and then pulling records out of CUSTOMERS matching those PHONE numbers. THANKS! ~~ LUCi5R e: luc...@luci5r.com w: http://www.luci5r.com From: Johnny Withers [mailto:joh...@pixelated.net] Sent: Wednesday, February 29, 2012 3:15 PM To: luc...@luci5r.com Cc: mysql@lists.mysql.com Subject: Re: Getting data from 2 tables if records have same date! After looking at this again, the query you are using; SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE WHERE CUSTOMERS.DATE = 02/28/12 AND (CALLS.PHONE IS NULL OR CALLS.DATE = 02/28/12) Would return all customers in the customer's table created on 2/28/12 WITH no calls or a call on 2/28. Maybe you should try.. SELECT * FROM CUSTOMERS WHERE CUSTOMERS.DATE = 02/28/12 UNION SELECT * FROM CUSTOMERS INNER JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE WHERE CALLS.DATE = 02/28/12 Basically, you have to do two queries to get the data you want. The WHERE customers.date=2/28 is only getting those customers created on 2/28 regardless if they had a call or not on 2/28. So if you had a customer created on 2/27 and a call on 2/28, the query we were using is not going to pick that customer up. If you want duplicate customers from the second query in the UNION above, you can use UNION ALL instead of just UNION between the queries. -JW On Wed, Feb 29, 2012 at 4:54 PM, LUCi5R luc...@luci5r.com wrote: JW, Im trying to understand LEFT JOIN as we go but its not working. This query SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE WHERE CUSTOMERS.DATE = 02/28/12 AND (CALLS.PHONE IS NULL OR CALLS.DATE = 02/28/12) Is giving me some results which Im not quite sure what they are but its not the right results. The way Im testing is, on 02/28/12 I had 57 Customers created in the CUSTOMERS table. I also had a total of 105 Calls recorded in the CALLS table. Some calls were from the same customers more then once. Essentially, I need the result to be 86 which I got from some manual calculations. Out of those 86 records, 1 record is in the CUSTOMERS table but not in the CALLS table. The other 85 were in both tables. The above LEFT JOIN query gave me 69 records and quite a few duplicate entries. Im trying to dissect it to understand what exactly it selected. Thanks! ~~ LUCi5R e: luc...@luci5r.com w: http://www.luci5r.com From: Johnny Withers [mailto:joh...@pixelated.net] Sent: Wednesday, February 29, 2012 1:30 PM To: luc...@luci5r.com Cc: mysql@lists.mysql.com Subject: Re: Getting data from 2 tables if records have same date! Sounds like you need to LEFT JOIN: SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND CALLS.DATE = 02/28/12 WHERE CUSTOMERS.DATE = 02/28/12 But that would only get customers created on 2/28 AND having a call on 2/28 OR not call at all on 2/28. This would give you customers created on 2/28 with no calls AND customers created on 2/28 with a call on 2/28: SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE WHERE CUSTOMERS.DATE = 02/28/12 AND (CALLS.PHONE IS NULL OR CALLS.DATE = 02/28/12) JW On Wed, Feb 29, 2012 at 1:02 PM, LUCi5R luc...@luci5r.com wrote: Guys, I've been working with MySQL for a while (been on off this list over the last 10 years or so); I'm definitely not a n00b and have worked with SQL extensively. Used JOIN and all quite a bit ... but I haven't done subqueries, union or nested joins. I'm completely stumped on this problem; and Google hasn't been helpful at all. I'll try to be as descriptive as possible. I have 2 tables ... CUSTOMERS and CALLS. Think of Customers table as your Directory. It has the customer's contact information some other information. In total about 20 fields in there. The Calls table has only about 7 fields. Each time a customer calls in, the conversation details gets recorded in this Calls table. The PHONE field is the key field that joins the CUSTOMERS CALLS
RE: Getting data from 2 tables if records have same date!
SELECT * FROM CUSTOMERS LEFT JOIN CALLS USING(PHONE) WHERE CUSTOMERS.DATE = 02/28/12 OR CALLS.DATE = 02/28/12 ^^^ This is going into an endless loop; I'm not getting any result at all. I'm not sure why. I haven't used USING before so I need to read up a bit on that to understand what you're doing here. Thanks! ~~ LUCi5R e: luc...@luci5r.com w: http://www.luci5r.com -Original Message- From: Halász Sándor [mailto:h...@tbbs.net] Sent: Wednesday, February 29, 2012 2:57 PM To: mysql@lists.mysql.com Subject: Re: Getting data from 2 tables if records have same date! ; 2012/02/29 15:29 -0600, Johnny Withers Sounds like you need to LEFT JOIN: SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND CALLS.DATE = 02/28/12 WHERE CUSTOMERS.DATE = 02/28/12 But that would only get customers created on 2/28 AND having a call on 2/28 OR not call at all on 2/28. This would give you customers created on 2/28 with no calls AND customers created on 2/28 with a call on 2/28: SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE WHERE CUSTOMERS.DATE = 02/28/12 AND (CALLS.PHONE IS NULL OR CALLS.DATE = 02/28/12) Exactly; but I believe that this is the right thing: SELECT * FROM CUSTOMERS LEFT JOIN CALLS USING(PHONE) WHERE CUSTOMERS.DATE = 02/28/12 OR CALLS.DATE = 02/28/12 If you have a hit --PHONE found in both tables--, you will get a record if either date matches, and I believe that you wanted that. If it is a miss --there is no CALLS-record for the PHONE-- CALLS.DATE will be NULL and not equal and only CUSTOMERS.DATE will match a date. And if this works, surely it is clear where to put BETWEEN. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Getting data from 2 tables if records have same date!
-Original Message- From: Shawn L Green [mailto:shawn.l.gr...@oracle.com] Sent: Thursday, March 01, 2012 6:57 AM To: luc...@luci5r.com Cc: 'Johnny Withers'; mysql@lists.mysql.com Subject: Re: Getting data from 2 tables if records have same date! Try this: SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND CUSTOMERS.DATE=CALLS.DATE WHERE CUSTOMERS.DATE = 02/28/12 This will give you a list of all customers for a given date and a list of every call they made on that date. If a customer made no calls on a date, then all of the columns for that table will be NULL. If you only want a list of customers and details about the calls on a date then an INNER JOIN is appropriate. If you want to see the full list of customers and any calls on that date use this: SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND CUSTOMERS.DATE=CALLS.DATE = 02/28/12 If you only want a list of customers that made any calls on a given date, you can use the EXISTS comparator like this: SELECT customers.* FROM customers WHERE EXISTS (SELECT * FROM calls WHERE CUSTOMERS.PHONE = CALLS.PHONE AND CUSTOMERS.DATE=CALLS.DATE = 02/28/12) http://dev.mysql.com/doc/refman/5.5/en/exists-and-not-exists-subqueries.html It's possible to get you any combination of data you want, we just need you to clarify the relationship you are trying to find and how much data you really want to get back. NOTE: the name of the column date is using a reserved word. You may want to enclose it in backticks to avoid confusion as in `date`. Also, the standard MySQL syntax for date literals uses ISO notation. So instead of using 02/28/12 (using double quotes) I expected to see '2012-02-28' (using single quotes) -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN ~~ Shawn, I still need to try your queries but I wanted to quickly get back to you on a couple of things. You're correct! The DATE column is a reserved word and in my queries I do indeed use `backticks`. Interestingly, I never knew that's what they are called!!! I used to call that symbol `Grave Accent` ... never heard it being called `backticks`. Good to know :) Secondly, unfortunately this DATE field came from an original FoxPro Database (DBF) which was an Char (8) field and got translated into mysql as such. So unfortunately it's not a DATE field ... it's a Char (8) field. For simplicity, I've been using Date = 02/28/12 to explain my queries here, but in reality, in my program, I've actually been using the STR_TO_DATE() function as such ... STR_TO_DATE(`DATE`, '%m/%d/%Y') = STR_TO_DATE('02/2/12', '%m/%d/%Y') Lastly, you're correct again, I don't actually use the Double Quotes around the dates ... I do use Single Quotes. In regards to the relationship/data that I'm looking for ... this is what I'm looking for ... I need to pull the records from the CUSTOMERS table, where a) The customer was created on given date (Eg:- '02/28/12') b) The customer called on given date and the call was recorded in the Calls table (Eg:- '02/28/12') Let me give your queries a shot understand them! THANKS!! ~~ LUCi5R e: luc...@luci5r.com w: http://www.luci5r.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Getting data from 2 tables if records have same date!
Stupid wrapping helped me to make a simple mistake. I wrote On 3/1/2012 10:40 AM, LUCi5R wrote: SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND CUSTOMERS.DATE=CALLS.DATE = 02/28/12 But I meant it to be SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND CALLS.DATE = 02/28/12 But based on your description: a) The customer was created on given date (Eg:- '02/28/12') b) The customer called on given date and the call was recorded in the Calls table There are possibly two different dates at play, a creation date (customers.date) and an activity date (calls.date). Therefore, we need to list them separately. Also, you said you wanted just the CUSTOMERS records (without any call details) so I assume you only want to see a single copy of each customer. This would work best using the EXISTS pattern I provided last with a simple modification: SELECT customers.* FROM customers WHERE EXISTS (SELECT * FROM calls WHERE CUSTOMERS.PHONE = CALLS.PHONE AND CUSTOMERS.DATE=CALLS.DATE = activity date) AND customers.date=creation date There are many other ways to find this same set of data. Here is a two-step process using an indexed temporary table (technically, it's a 3-step process as you need to drop the explicit temp table, too). CREATE TEMPORARY TABLE tmp_custs(key(phone) USINB BTREE) ENGINE=MEMORY SELECT DISTINCT phone FROM CALLS WHERE date=activity date; SELECT customers.* FROM customers INNER JOIN tmp_custs ON tmp_custs.phone = customers.phone WHERE customers.date = create date; DROP TEMPORARY TABLE tmp_custs; By default the MEMORY engine creates all indexes as HASH indexes. So in order to replace the =activity date comparison with any sort of ranged comparison, you need a BTREE index. Regards, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySQL on 64 bit Windows 7?
I would imagine the installer is 32-bit only just so they don't have to release two versions of it. I'm sure it'll allow you to download the 64-bit version of the server though. JW On Thu, Mar 1, 2012 at 3:10 PM, Dotan Cohen dotanco...@gmail.com wrote: Although 74 bit Windows 7 is listed as supported [1], I do not see such a binary listed on the download page [2]. Should one use the 32 bit installer on 64 bit Windows? Is the installer page sniffing my UA (Firefox on Debian) and trying to guess as to the correct binary for me? Am I looking in the wrong place? Thanks. [1] http://www.mysql.com/support/supportedplatforms/database.html [2] http://dev.mysql.com/downloads/installer/ -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- - Johnny Withers 601.209.4985 joh...@pixelated.net
Re: MySQL on 64 bit Windows 7?
On Thu, Mar 1, 2012 at 23:13, Johnny Withers joh...@pixelated.net wrote: I would imagine the installer is 32-bit only just so they don't have to release two versions of it. I'm sure it'll allow you to download the 64-bit version of the server though. I see, thanks. I did not realise that a Windows installer might install applications of a different architecture than itself. -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Multi select Query help...
Appreciate a little guidance here: Background: I have an invoicing system. Invoices are generated and (invoice and Invoice Items) and Payments are generated (Payments and Payment Items). Payment items are amount of the Payment Total allocated to payoff open invoices. So I may have 3 open invoice for $100 each and I may generate one payment for $300 with 3 payment items for $100 each to pay off those 3 open invoices. In most cases, clients will pay their own invoices off, but in rare cases another client will pay an invoice for that client (ie... spouse, parent, etc...) My client want me to some how display when the payee (or one of the payees - there can be multiple) of the invoice IS NOT the same client as the invoice being paid. So I need to display a result that show a comma delineated string of payees OMITTING the invoice's client_id. I only want to show a result if one or more of the payees are different than the invoice's client_id. So now with the mySQL queries that are working: First of all, the client_id of the invoice I am querying on is 251719. query 1 = select group_concat(payment_id) from tl_trans_pmt_items where inv_id = 1033911 This produces a string 1033882,1021630,1021632. These are parent Payment records which have the payee client_ids. So if I run a query: query 2 = select group_concat(client_id) FROM tl_transactions WHERE transaction_id IN (1033882,1021630,1021632) AND client_id != 251719 This produces a string 251711,251713. These are the client_ids of the Payment records OMITTING the Invoice's client_id So far this works fine. Now where I run into issues is where I try to combine these queries together: query 3 = select group_concat(client_id) FROM tl_transactions WHERE transaction_id IN ((select group_concat(payment_id) from tl_trans_pmt_items where inv_id = 1033911)) AND client_id != 251719 I do not get the same results. Am I missing something? Hopefully something simple ;-) Don -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql