Index_Merge : Very slow
Hello All, Our queries were running fine on MySQL 4.1.11. Since we upgraded to MySQL 5.0.22 The same queries are taking a long long time to execute. Running an explain on the queries shows an index_merge in the type column. And it shows using intersect algorithm in Extra column of the output. Previously(MySQL 4.1.11) Explain showed the usage of primary Key as the index. How can this be resolved? Thanks Ratheesh K J
Having problems with what appears to be a simple query.
I have a table with numerous columns but needing to perform a query based on three columns: Lab_number, result, release_time. What I want to do is search for lab_number where there is a result but not released. The problem that is making this difficult for me, is that there are multiple entries for the same lab_number, due to a lab_number having 1 or more tests being performed on it. The search I really want to do is find the lab_number where ALL the tests have been performed and not the lab_numbers which have partial results. *---* |lab_number | result |release_time | | 1 | 10 |-00-00 00:00:00| | 1 | 20 |-00-00 00:00:00| | 2 | 5 |-00-00 00:00:00| | 2 | |-00-00 00:00:00| | 3 | |-00-00 00:00:00| | 3 | |-00-00 00:00:00| | 3 | |-00-00 00:00:00| *---* So the query I want will only return 1, as 2 is not yet complete. The attempts I have made so far will return 2 as well. The thing that is really annoying me is that I know I will kick myself when I see a solution! Thanks Danny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Formatting a subquery?
Just so I'm straight on this. You want to select 20 random people, then select one random picture for each of those 20 people, ending up with 20 random pictures, each from a different account Your query I'm pretty sure gives you all pictures from the 20 random accounts, ordered randomly. If we step through each query we should come up with something workable. Get the people: - Original Message - From: Brian Dunning [EMAIL PROTECTED] To: mysql mysql@lists.mysql.com Sent: Tuesday, January 16, 2007 2:59 PM Subject: Re: Formatting a subquery? Hmmm, I see the problem. I can't think of any other way to do it. I have a table of people, and I want to return 20 random people. Each person has multiple picture records, and I want to return a random picture for each. That's why I'm trying to put the 'order by rand()' in the subquery: if I make it a single query and try to do a 'group by' it always gives the same picture for each person. On Jan 16, 2007, at 11:55 AM, Rhino wrote: - Original Message - From: Brian Dunning [EMAIL PROTECTED] To: mysql mysql@lists.mysql.com Sent: Tuesday, January 16, 2007 2:23 PM Subject: Formatting a subquery? I can't get MySQL 5 to like this query. Can you tell what I'm trying to do, and is there a problem with my formatting? select account_id,picture_id from pictures where account_id in (select account_id from accounts order by rand() limit 20) order by rand(); I haven't used MySQL 5 and I'm not sure what it will tolerate but in DB2 SQL, which I have used for many years, you cannot do an ORDER BY within a subquery. Only the other query can contain an ORDER BY. (Then again, I am not completely up-to-date on the SQL standards any more so maybe DB2 V9 will even tolerate an ORDER BY in the subquery.) You may also be having trouble because of the LIMIT clause in the subquery. In any case, I think the ORDER BY in the subquery is redundant: you shouldn't care what order the rows in the subquery are read, just what order is used to PRESENT them in the final query. -- Rhino -- MySQL General Mailing List 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: Formatting a subquery?
Sorry about the last email, sent it before I finished it. As I was saying... Just so I'm straight on this. You want to select 20 random people, then select one random picture for each of those 20 people, ending up with 20 random pictures, each from a different account Your query I'm pretty sure gives you all pictures from the 20 random accounts, ordered randomly. When you just want 1 random picture for each account. I think if you just add another select with a group by, you should have what you want. select account_id,picture_id FROM ( select account_id,picture_id from pictures where account_id in (select account_id from accounts order by rand() limit 20) order by rand() ) as pics group by account_id - Original Message - From: Brian Dunning [EMAIL PROTECTED] To: mysql mysql@lists.mysql.com Sent: Tuesday, January 16, 2007 2:23 PM Subject: Formatting a subquery? I can't get MySQL 5 to like this query. Can you tell what I'm trying to do, and is there a problem with my formatting? select account_id,picture_id from pictures where account_id in (select account_id from accounts order by rand() limit 20) order by rand(); -- MySQL General Mailing List 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: Having problems with what appears to be a simple query.
Daniel, find the lab_number where ALL the tests have been performed and not the lab_numbers which have partial results. SELECT t1.lab,t1.res,t1.dt FROM tbl t1 WHERE NOT EXISTS( SELECT lab FROM tbl t2 WHERE t1.lab=t2.lab AND t2.res IS NULL ); The decorrelated version uses an exclusion join, runs faster but looks a bit strange: SELECT t1.lab_number,t1.result,t1.release_time FROM tbl t1 LEFT JOIN tbl t2 ON t1.lab_number=t2.lab_number AND t2.result IS NULL WHERE t2.lab_number IS NULL There's a bit of discussion of this query pattern at http://www.artfulsoftware.com/queries.php nder 'Joins', 'The [Not] Exists Query Pattern'. PB - Daniel Smith wrote: I have a table with numerous columns but needing to perform a query based on three columns: Lab_number, result, release_time. What I want to do is search for lab_number where there is a result but not released. The problem that is making this difficult for me, is that there are multiple entries for the same lab_number, due to a lab_number having 1 or more tests being performed on it. The search I really want to do is find the lab_number where ALL the tests have been performed and not the lab_numbers which have partial results. *---* |lab_number | result |release_time | | 1 | 10 |-00-00 00:00:00| | 1 | 20 |-00-00 00:00:00| | 2 | 5 |-00-00 00:00:00| | 2 | |-00-00 00:00:00| | 3 | |-00-00 00:00:00| | 3 | |-00-00 00:00:00| | 3 | |-00-00 00:00:00| *---* So the query I want will only return 1, as 2 is not yet complete. The attempts I have made so far will return 2 as well. The thing that is really annoying me is that I know I will kick myself when I see a solution! Thanks Danny -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.410 / Virus Database: 268.16.13/632 - Release Date: 1/16/2007 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Having problems with what appears to be a simple query.
On Wed, 2007-01-17 at 09:56 -0600, Peter Brawley wrote: Daniel, find the lab_number where ALL the tests have been performed and not the lab_numbers which have partial results. SELECT t1.lab,t1.res,t1.dt FROM tbl t1 WHERE NOT EXISTS( SELECT lab FROM tbl t2 WHERE t1.lab=t2.lab AND t2.res IS NULL ); The decorrelated version uses an exclusion join, runs faster but looks a bit strange: SELECT t1.lab_number,t1.result,t1.release_time FROM tbl t1 LEFT JOIN tbl t2 ON t1.lab_number=t2.lab_number AND t2.result IS NULL WHERE t2.lab_number IS NULL Playing around with this examples, I realise I forgot to mention something I didn't make clear in my original post. The reason for doing the search is to find completed lab_numbers which have not yet been released, hence the -00-00 00:00:00 timestamp. Using the examples you have given me don't seem to be working in the way I'd hoped. I'm just changing the tbl to the correct table name and seem to be getting results that are released, rows with no results and the completed results awaiting release. Using this query, based on yours: SELECT t1.lab_number,t1.result,t1.release_time FROM requesting_test_results t1 LEFT JOIN requesting_test_results t2 ON t1.lab_number=t2.lab_number AND t2.result IS NULL WHERE t2.lab_number IS NULL AND t1.result !='' AND t1.release_time =' 000-00-00 00:00:00' GROUP BY t1.lab_number I get 2 records, one is a lab_number which is completed but not released (lab_number 999), the other is a lab_number that has is only partially completed (3265). Removing the GROUP statement returns 6 records, four entries for 999 and 2 for 3265, though there are still 8 rows for 3265 with no result. I will try and get my head around joining to the same table in meantime. Thanks for prompt answer. Danny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Having problems with what appears to be a simple query.
Daniel, CREATE TABLE tbl ( lab_number int(11) default NULL, result int(11) default NULL, release_time datetime default NULL ); select * from tbl; +++-+ | lab_number | result | release_time| +++-+ | 1 | 10 | 2007-01-17 00:00:00 | | 1 | 20 | 2007-01-17 00:00:00 | | 2 | 5 | 2007-01-17 00:00:00 | | 2 | NULL | -00-00 00:00:00 | | 3 | NULL | -00-00 00:00:00 | | 4 | 25 | -00-00 00:00:00 | | 4 | 35 | -00-00 00:00:00 | +++-+ SELECT t1.lab_number,t1.result,t1.release_time FROM tbl t1 LEFT JOIN tbl t2 ON t1.lab_number=t2.lab_number AND t2.result IS NULL WHERE t1.release_time=0 AND t2.lab_number IS NULL +++-+ | lab_number | result | release_time| +++-+ | 4 | 25 | -00-00 00:00:00 | | 4 | 35 | -00-00 00:00:00 | +++-+ PB Daniel Smith wrote: On Wed, 2007-01-17 at 09:56 -0600, Peter Brawley wrote: Daniel, find the lab_number where ALL the tests have been performed and not the lab_numbers which have partial results. SELECT t1.lab,t1.res,t1.dt FROM tbl t1 WHERE NOT EXISTS( SELECT lab FROM tbl t2 WHERE t1.lab=t2.lab AND t2.res IS NULL ); The decorrelated version uses an exclusion join, runs faster but looks a bit strange: SELECT t1.lab_number,t1.result,t1.release_time FROM tbl t1 LEFT JOIN tbl t2 ON t1.lab_number=t2.lab_number AND t2.result IS NULL WHERE t2.lab_number IS NULL Playing around with this examples, I realise I forgot to mention something I didn't make clear in my original post. The reason for doing the search is to find completed lab_numbers which have not yet been released, hence the -00-00 00:00:00 timestamp. Using the examples you have given me don't seem to be working in the way I'd hoped. I'm just changing the tbl to the correct table name and seem to be getting results that are released, rows with no results and the completed results awaiting release. Using this query, based on yours: SELECT t1.lab_number,t1.result,t1.release_time FROM requesting_test_results t1 LEFT JOIN requesting_test_results t2 ON t1.lab_number=t2.lab_number AND t2.result IS NULL WHERE t2.lab_number IS NULL AND t1.result !='' AND t1.release_time =' 000-00-00 00:00:00' GROUP BY t1.lab_number I get 2 records, one is a lab_number which is completed but not released (lab_number 999), the other is a lab_number that has is only partially completed (3265). Removing the GROUP statement returns 6 records, four entries for 999 and 2 for 3265, though there are still 8 rows for 3265 with no result. I will try and get my head around joining to the same table in meantime. Thanks for prompt answer. Danny No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.410 / Virus Database: 268.16.13/632 - Release Date: 1/16/2007 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Having problems with what appears to be a simple query.
On Wed, 2007-01-17 at 11:47 -0600, Peter Brawley wrote: Daniel, CREATE TABLE tbl ( lab_number int(11) default NULL, result int(11) default NULL, release_time datetime default NULL ); select * from tbl; +++-+ | lab_number | result | release_time| +++-+ | 1 | 10 | 2007-01-17 00:00:00 | | 1 | 20 | 2007-01-17 00:00:00 | | 2 | 5 | 2007-01-17 00:00:00 | | 2 | NULL | -00-00 00:00:00 | | 3 | NULL | -00-00 00:00:00 | | 4 | 25 | -00-00 00:00:00 | | 4 | 35 | -00-00 00:00:00 | +++-+ SELECT t1.lab_number,t1.result,t1.release_time FROM tbl t1 LEFT JOIN tbl t2 ON t1.lab_number=t2.lab_number AND t2.result IS NULL WHERE t1.release_time=0 AND t2.lab_number IS NULL +++-+ | lab_number | result | release_time| +++-+ | 4 | 25 | -00-00 00:00:00 | | 4 | 35 | -00-00 00:00:00 | +++-+ PB This works!! It seems it was my defaults for my original table that was causing me my problems. Sorry for troubling you, thanks once again for the prompt solution. Danny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Does Update allow for aliases?
I concede to the MySQL engineer :-) -Original Message- From: Shawn Green [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 10, 2007 3:30 PM To: Jonathan Langevin Cc: Chris White; Richard Reina; mysql@lists.mysql.com Subject: Re: Does Update allow for aliases? Hi all, Multi-table updates are not possible for versions older than 4.0.4. (http://dev.mysql.com/doc/refman/4.1/en/update.html) so the operation is not possible with your current version. To be complete, though, each of you missed the second syntax error in his statement Jonathan Langevin wrote: The proper syntax would need to be: UPDATE maindb.orders o, altdb.orders ao SET o.price=ao.price WHERE o.ID=a.ID; snip The second table is aliased to 'ao' not 'a': UPDATE maindb.orders o, altdb.orders ao SET o.price=ao.price WHERE o.ID=ao.ID; ^^ Look here :) An alternative form is: UPDATE maindb.orders o INNER JOIN altdb.orders ao ON o.ID=ao.ID SET o.price=ao.price; The table reference portion of the mulitple-table UPDATE command will accept any valid JOIN syntax, not just the implied INNER JOIN of a comma separated table list. Yours, -- Shawn Green, Support Engineer MySQL Inc., USA, www.mysql.com Office: Blountville, TN Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table update
I want to update new column in a table with data from other tables. The following query give me the data: select t.id,min(r.starttime) from teams as t inner join rider_team as rt on (rt.team_id=t.id) inner join participants as p on (p.rider_id=rt.rider_id) inner join races as r on (r.id=p.race_id) group by t.id; Is it possible to write a query that do a: update teams set created=min(r.starttime) ... which updates all records in the teams table based on first query? Thanks BTW: mysql select version(); ++ | version() | ++ | 4.1.8-standard | ++ -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ASP.NET Access to MySQL BIT field????-Help
OK. I don't recall having a problem with this before, but, how do I assign a variable to a MySQL Bit field? I'm trying to set up a CheckBox that is on my form, and I've tried variations of the following: FirstTime.Checked = CBool(RS(FirstTime)) FirstTime.Checked = CBool(RS(FirstTime).ToString) FirstTime.Checked = RS(FirstTime) FirstTime.Checked = Convert.ToBoolean(RS(FirstTime)) I believe that all of them give the error, String was not recognized as a valid Boolean. When I view the field value in SQL Manager 2005 Lite, It presents a check box for the field value. When I view it through the MySQL Query Browser, it presents the value as b'1' Any hints? I'm sure this is a basic issue, but I just can't seem to find a way to do it. Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[PART 1/2] InnoDB - Different EXPLAINs for same query
Greetings, It seems the lists.mysql.com imposes a 50KB limit on messages, so this message will be sent in two parts. Thanks for your patience. I'm in the process of converting most of my databases from MyISAM to InnoDB, and I've come across the most peculiar problem. It seems that after running for some time, MySQL starts to choose different indexes to run same the exact same query with! After the daemon has been up and running for an undetermined amount of time, the output of EXPLAIN on some queries differs from the output of EXPLAIN taken upon server startup! The different EXPLAIN usually means a much, much slower query. This only happens on InnoDB tables. I can reproduce this error in both MySQL 5.0.24 and MySQL 5.0.27 installed from source on x86_64 Linux. The running kernel is 2.6.14.6, glibc is 2.3.9, and MySQL is linked against OpenSSL 0.9.8a Here's an example of what I mean by the exact same query being run in different ways: After the server has been up and running for a couple of days, I get complaints from end users about web applications running slowly. I log in and look at the processlist and see some queries have been on the clock for over 400 seconds. Since I'm testing and have separate InnoDB and MyISAM servers, I push the front ends back to the MyISAM version of the database, and begin troubleshooting the query on the now unused InnoDB version. mysql EXPLAIN SELECT COUNT(rb.subscription_id) as recurring_cc_count, - SUM(rb.grace_price) as recurring_cc, - COUNT(sb.subscription_id) as single_cc_count, - SUM(sb.initial_amt) as single_cc - FROM customerdetail a - LEFT JOIN recurringbilling rb - ON a.subscription_id = rb.subscription_id - LEFT JOIN singlebilling sb - ON a.subscription_id = sb.subscription_id - LEFT JOIN (global.subscriptionExchangeRate as ser INNER JOIN global.currencyCodes as cur) - ON (a.subscription_id = ser.subscriptionId AND ser.billedCurrencyCode = cur.currencyCode) - WHERE client_accnum = '12345' - AND a.trans_timestamp - BETWEEN '2007010800' AND '20070108235959'; ++-+---++--- -++-+--- +---+-+ | id | select_type | table | type | possible_keys | key| key_len | ref | rows | Extra | ++-+---++--- -++-+--- +---+-+ | 1 | SIMPLE | a | ref| client_idx,trans_idx,accno_trans_idx,accnumactive,accsubactive | client_idx | 3 | const | 25032 | Using where | | 1 | SIMPLE | rb| eq_ref | PRIMARY | PRIMARY| 8 | company.a.subscription_id | 1 | | | 1 | SIMPLE | sb| eq_ref | PRIMARY | PRIMARY| 8 | company.a.subscription_id | 1 | | | 1 | SIMPLE | ser | ref| PRIMARY,billedCurrencyCode | PRIMARY| 8 | company.a.subscription_id | 1 | | | 1 | SIMPLE | cur | eq_ref | PRIMARY | PRIMARY| 2 | global.ser.billedCurrencyCode | 1 | Using index | ++-+---++--- -++-+--- +---+-+ 5 rows in set (0.61 sec) mysql show index from customerdetail; +++-+--+ -+---+-+--++--+- ---+-+ | Table | Non_unique | Key_name| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +++-+--+ -+---+-+--++--+- ---+-+ | customerdetail | 0 | subscription|1 | subscription_id | A |49382031 | NULL | NULL | | BTREE | | | customerdetail | 1 | client_idx |1 | client_accnum | A | 86181 | NULL | NULL | | BTREE | | | customerdetail | 1 | client_idx |2 | client_subacc | A | 103309 | NULL | NULL | | BTREE | SNIP. ++-+---++--- -++-+--- + mysql SELECT SQL_NO_CACHE COUNT(rb.subscription_id) as recurring_cc_count, - SUM(rb.grace_price) as recurring_cc, - COUNT(sb.subscription_id) as single_cc_count, - SUM(sb.initial_amt) as single_cc - FROM
[PART 2/2] InnoDB - Different EXPLAINs for same query
mysql SELECT SQL_NO_CACHE COUNT(rb.subscription_id) as recurring_cc_count, - SUM(rb.grace_price) as recurring_cc, - COUNT(sb.subscription_id) as single_cc_count, - SUM(sb.initial_amt) as single_cc - FROM customerdetail a - LEFT JOIN recurringbilling rb - ON a.subscription_id = rb.subscription_id - LEFT JOIN singlebilling sb - ON a.subscription_id = sb.subscription_id - LEFT JOIN (global.subscriptionExchangeRate as ser INNER JOIN global.currencyCodes as cur) - ON (a.subscription_id = ser.subscriptionId AND ser.billedCurrencyCode = cur.currencyCode) - WHERE client_accnum = '12345' - AND a.trans_timestamp - BETWEEN '2007010800' AND '20070108235959'; ++--+-+---+ | recurring_cc_count | recurring_cc | single_cc_count | single_cc | ++--+-+---+ | 4 | 119.80 | 0 | NULL | ++--+-+---+ 1 row in set (0.40 sec) mysql explain SELECT SQL_NO_CACHE COUNT(rb.subscription_id) as recurring_cc_count, - SUM(rb.grace_price) as recurring_cc, - COUNT(sb.subscription_id) as single_cc_count, - SUM(sb.initial_amt) as single_cc - FROM customerdetail a - LEFT JOIN recurringbilling rb - ON a.subscription_id = rb.subscription_id - LEFT JOIN singlebilling sb - ON a.subscription_id = sb.subscription_id - LEFT JOIN (global.subscriptionExchangeRate as ser INNER JOIN global.currencyCodes as cur) - ON (a.subscription_id = ser.subscriptionId AND ser.billedCurrencyCode = cur.currencyCode) - WHERE client_accnum = '12345' - AND a.trans_timestamp - BETWEEN '2007010800' AND '20070108235959'; ++-+---++--- -+-+-+-- -+--+--+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra| ++-+---++--- -+-+-+-- -+--+--+ | 1 | SIMPLE | a | range | client_idx,trans_idx,accno_trans_idx,accnumactive,accsubactive | accno_trans_idx | 7 | NULL |4 | Using where; Using index | | 1 | SIMPLE | rb| eq_ref | PRIMARY | PRIMARY | 8 | company.a.subscription_id |1 | | | 1 | SIMPLE | sb| eq_ref | PRIMARY | PRIMARY | 8 | company.a.subscription_id |1 | | | 1 | SIMPLE | ser | ref| PRIMARY,billedCurrencyCode | PRIMARY | 8 | company.a.subscription_id |1 | | | 1 | SIMPLE | cur | eq_ref | PRIMARY | PRIMARY | 2 | global.ser.billedCurrencyCode |1 | Using index | ++-+---++--- -+-+-+-- -+--+--+ 5 rows in set (0.00 sec) mysql show index from customerdetail; +++-+--+ -+---+-+--++--+- ---+- + | Table | Non_unique | Key_name| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +++-+--+ -+---+-+--++--+- ---+- ..SNIP | customerdetail | 1 | accno_trans_idx |1 | client_accnum | A | 17052 | NULL | NULL | | BTREE | | | customerdetail | 1 | accno_trans_idx |2 | trans_timestamp | A |49042196 | NULL | NULL | | BTREE | ..SNIP +++-+--+ -+---+-+--++--+- ---+-+ The query executes orders of magnitude faster, and the EXPLAIN shows why. MySQL has now chosen to use the accno_trans_idx index for the customerdetail table which has much better cardinality (almost 1 key per row). The obvious workaround is use FORCE INDEX(accno_trans_idx) in the query for now, but I'm beginning to think this may be a bug. Has anyone else had any similar issues? I haven't found anything like this in the bug database though. If anyone doesn't see
Re: [PART 2/2] InnoDB - Different EXPLAINs for same query
Just a thought, did you try running Optimize Table from the MySQL Administrator. I'm thinking that when you restarted it re-examined the table statistics and was able to pick a better index. On Wed, January 17, 2007 14:31, John Anderson said: mysql SELECT SQL_NO_CACHE COUNT(rb.subscription_id) as recurring_cc_count, - SUM(rb.grace_price) as recurring_cc, - COUNT(sb.subscription_id) as single_cc_count, - SUM(sb.initial_amt) as single_cc - FROM customerdetail a - LEFT JOIN recurringbilling rb - ON a.subscription_id = rb.subscription_id - LEFT JOIN singlebilling sb - ON a.subscription_id = sb.subscription_id - LEFT JOIN (global.subscriptionExchangeRate as ser INNER JOIN global.currencyCodes as cur) - ON (a.subscription_id = ser.subscriptionId AND ser.billedCurrencyCode = cur.currencyCode) - WHERE client_accnum = '12345' - AND a.trans_timestamp - BETWEEN '2007010800' AND '20070108235959'; ++--+-+---+ | recurring_cc_count | recurring_cc | single_cc_count | single_cc | ++--+-+---+ | 4 | 119.80 | 0 | NULL | ++--+-+---+ 1 row in set (0.40 sec) mysql explain SELECT SQL_NO_CACHE COUNT(rb.subscription_id) as recurring_cc_count, - SUM(rb.grace_price) as recurring_cc, - COUNT(sb.subscription_id) as single_cc_count, - SUM(sb.initial_amt) as single_cc - FROM customerdetail a - LEFT JOIN recurringbilling rb - ON a.subscription_id = rb.subscription_id - LEFT JOIN singlebilling sb - ON a.subscription_id = sb.subscription_id - LEFT JOIN (global.subscriptionExchangeRate as ser INNER JOIN global.currencyCodes as cur) - ON (a.subscription_id = ser.subscriptionId AND ser.billedCurrencyCode = cur.currencyCode) - WHERE client_accnum = '12345' - AND a.trans_timestamp - BETWEEN '2007010800' AND '20070108235959'; ++-+---++--- -+-+-+-- -+--+--+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra| ++-+---++--- -+-+-+-- -+--+--+ | 1 | SIMPLE | a | range | client_idx,trans_idx,accno_trans_idx,accnumactive,accsubactive | accno_trans_idx | 7 | NULL |4 | Using where; Using index | | 1 | SIMPLE | rb| eq_ref | PRIMARY | PRIMARY | 8 | company.a.subscription_id |1 | | | 1 | SIMPLE | sb| eq_ref | PRIMARY | PRIMARY | 8 | company.a.subscription_id |1 | | | 1 | SIMPLE | ser | ref| PRIMARY,billedCurrencyCode | PRIMARY | 8 | company.a.subscription_id |1 | | | 1 | SIMPLE | cur | eq_ref | PRIMARY | PRIMARY | 2 | global.ser.billedCurrencyCode |1 | Using index | ++-+---++--- -+-+-+-- -+--+--+ 5 rows in set (0.00 sec) mysql show index from customerdetail; +++-+--+ -+---+-+--++--+- ---+- + | Table | Non_unique | Key_name| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +++-+--+ -+---+-+--++--+- ---+- ..SNIP | customerdetail | 1 | accno_trans_idx |1 | client_accnum | A | 17052 | NULL | NULL | | BTREE | | | customerdetail | 1 | accno_trans_idx |2 | trans_timestamp | A |49042196 | NULL | NULL | | BTREE | ..SNIP +++-+--+ -+---+-+--++--+- ---+-+ The query executes orders of magnitude faster, and the EXPLAIN shows why. MySQL has now chosen to use the accno_trans_idx index for the
MySQL Administrator problem
Unable to manage user accounts through MySQL Administrator 1.2.8. I've found that I'm not the only one with this problem but no one seems to have an answer for it. The problem is that when you select a user in the user list of the prog the user's detail don't appear on the right side so that they can be edited. If I right click on the user in the list then the only enabled options are Add, Refresh and Show Hosts. If I add a user, there's nothing else I can do it. The new user shows up in the list but I can't modify it or delete it. I'm logged in a root. I don't know what else to do. Any thoughts?
Re: [PART 2/2] InnoDB - Different EXPLAINs for same query
While I think optimize does an analyze, you may find that just an ANALYZE will do it instead of a full OPTIMIZE: http://dev.mysql.com/doc/refman/5.0/en/analyze-table.html On 18/01/07, William R. Mussatto [EMAIL PROTECTED] wrote: Just a thought, did you try running Optimize Table from the MySQL Administrator. I'm thinking that when you restarted it re-examined the table statistics and was able to pick a better index. On Wed, January 17, 2007 14:31, John Anderson said: mysql SELECT SQL_NO_CACHE COUNT(rb.subscription_id) as recurring_cc_count, - SUM(rb.grace_price) as recurring_cc, - COUNT(sb.subscription_id) as single_cc_count, - SUM(sb.initial_amt) as single_cc - FROM customerdetail a - LEFT JOIN recurringbilling rb - ON a.subscription_id = rb.subscription_id - LEFT JOIN singlebilling sb - ON a.subscription_id = sb.subscription_id - LEFT JOIN (global.subscriptionExchangeRate as ser INNER JOIN global.currencyCodes as cur) - ON (a.subscription_id = ser.subscriptionId AND ser.billedCurrencyCode = cur.currencyCode) - WHERE client_accnum = '12345' - AND a.trans_timestamp - BETWEEN '2007010800' AND '20070108235959'; ++--+-+---+ | recurring_cc_count | recurring_cc | single_cc_count | single_cc | ++--+-+---+ | 4 | 119.80 | 0 | NULL | ++--+-+---+ 1 row in set (0.40 sec) mysql explain SELECT SQL_NO_CACHE COUNT(rb.subscription_id) as recurring_cc_count, - SUM(rb.grace_price) as recurring_cc, - COUNT(sb.subscription_id) as single_cc_count, - SUM(sb.initial_amt) as single_cc - FROM customerdetail a - LEFT JOIN recurringbilling rb - ON a.subscription_id = rb.subscription_id - LEFT JOIN singlebilling sb - ON a.subscription_id = sb.subscription_id - LEFT JOIN (global.subscriptionExchangeRate as ser INNER JOIN global.currencyCodes as cur) - ON (a.subscription_id = ser.subscriptionId AND ser.billedCurrencyCode = cur.currencyCode) - WHERE client_accnum = '12345' - AND a.trans_timestamp - BETWEEN '2007010800' AND '20070108235959'; ++-+---++--- -+-+-+-- -+--+--+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra| ++-+---++--- -+-+-+-- -+--+--+ | 1 | SIMPLE | a | range | client_idx,trans_idx,accno_trans_idx,accnumactive,accsubactive | accno_trans_idx | 7 | NULL |4 | Using where; Using index | | 1 | SIMPLE | rb| eq_ref | PRIMARY | PRIMARY | 8 | company.a.subscription_id |1 | | | 1 | SIMPLE | sb| eq_ref | PRIMARY | PRIMARY | 8 | company.a.subscription_id |1 | | | 1 | SIMPLE | ser | ref| PRIMARY,billedCurrencyCode | PRIMARY | 8 | company.a.subscription_id |1 | | | 1 | SIMPLE | cur | eq_ref | PRIMARY | PRIMARY | 2 | global.ser.billedCurrencyCode |1 | Using index | ++-+---++--- -+-+-+-- -+--+--+ 5 rows in set (0.00 sec) mysql show index from customerdetail; +++-+--+ -+---+-+--++--+- ---+- + | Table | Non_unique | Key_name| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +++-+--+ -+---+-+--++--+- ---+- ..SNIP | customerdetail | 1 | accno_trans_idx |1 | client_accnum | A | 17052 | NULL | NULL | | BTREE | | | customerdetail | 1 | accno_trans_idx |2 | trans_timestamp | A |49042196 | NULL | NULL | | BTREE | ..SNIP +++-+--+
Re: MySQL Administrator problem
Ed Reed wrote: Unable to manage user accounts through MySQL Administrator 1.2.8. I've found that I'm not the only one with this problem but no one seems to have an answer for it. Have you reported a bug to bugs.mysql.com ? kind regards -- Colin Charles, Community Engineer MySQL AB, Melbourne, Australia, www.mysql.com Mobile: +614 12 593 292 / Skype: colincharles Web: http://www.bytebot.net/blog/ MySQL Forge: http://forge.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Log Warnings Level
Kristen G. Thorson wrote: Hi! But all I really get from this reading is 0 turns it off, 1 prints some warnings, and 2 prints level 1 warnings plus aborted connections warnings. I have not been able to find any additional information in my search. Am I missing something, or is this all the documentation there is on this? What kind of additional information are you looking about the --W (or --log-warnings=N) option? I'd pay attention to aborted connections, as MySQL Network's monitoring service actually does do so kind regards -- Colin Charles, Community Engineer MySQL AB, Melbourne, Australia, www.mysql.com Mobile: +614 12 593 292 / Skype: colincharles Web: http://www.bytebot.net/blog/ MySQL Forge: http://forge.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld_safe failing
Nishant Gupta wrote: When i try to run mysqld_safe cmd, it fails saying : Starting mysqld daemon with databases from /var/lib/mysql STOPPING server from pid file /var/run/mysqld/mysqld.pid 070105 20:23:40 mysqld ended Also mysqld.pid does not exist at the given path! It looks like the mysql server can't access /var/run/mysqld I suggest making sure the permissions are correct (using chown will fix it) -- Colin Charles, Community Engineer MySQL AB, Melbourne, Australia, www.mysql.com Mobile: +614 12 593 292 / Skype: colincharles Web: http://www.bytebot.net/blog/ MySQL Forge: http://forge.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help*
VeeJay wrote: How and at What place, can I configure the Makefile under /usr/ports/databases/mysql50-server/ to have MySQL server working with above mentioned configurations? Why not just use MySQL that comes from ports? If you really want to build from source, read: http://dev.mysql.com/doc/refman/5.0/en/installing-source.html -- Colin Charles, Community Engineer MySQL AB, Melbourne, Australia, www.mysql.com Mobile: +614 12 593 292 / Skype: colincharles Web: http://www.bytebot.net/blog/ MySQL Forge: http://forge.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: yum version 5.*
Karl Larsen wrote: I am using Red Hat Fedora Core 4 and I wanted to yum mysql version 5 of any other and find with Core 4 I can yum only mysql version 4. I imagine Core 5 might be able to yum mysql version 5 but not certain of that. Is there a way I can yum the later version? I studied the man for yum but could not see a way to do that. Fedora Core 4 doesn't include MySQL 5, which is why yum will not pick it up If you set yum to use a Fedora Core 5 repository, you can download MySQL 5 from there. You might also consider upgrading to Fedora Core 6, which is what is currently released -- Colin Charles, Community Engineer MySQL AB, Melbourne, Australia, www.mysql.com Mobile: +614 12 593 292 / Skype: colincharles Web: http://www.bytebot.net/blog/ MySQL Forge: http://forge.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fwd: Re: MySQL 5.0.x and 5.1.x Compiling options and settings on Linux IA64: one more question: ./bin/mysqladmin: connect to server at 'localhost' failed
Hi, After having compiled (debug and optimized version) of MySQL 5.1.14 on Itanium-2 using icc, I have run mysql_install_db --user=... and started mysqld_safe in both scenarios (debug and optimized). In both cases mysqld starts without any errors. Unfortunaly when running the optimized version of mysqld I am not able to connect and set the root password. ./bin/mysqladmin -u root password '***' ./bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)' The same procedure applied on the mysqld compiled with debug version (--with-debug) has no problem and I am able to set up the root password without any problems. Is there a bug in the MySQL 5.1.14 version when not compiled with --with-debug or am I missing something ? P.S. I have also tried to set the root password using the debug version, shut-down the mysqld debug, replaced the mysql subdirectory in the data subdir in the optimized version and restarted the optimized mysqld with the new mysql subdir in the data subdir, but I got the same error. Could you please help ? Thanks in advance for your help, Mariella - Need a quick answer? Get one in minutes from people who know. Ask your question on Yahoo! Answers.
Re: MySQL Administrator problem
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Ed Reed wrote: Unable to manage user accounts through MySQL Administrator 1.2.8. I've found that I'm not the only one with this problem but no one seems to have an answer for it. The problem is that when you select a user in the user list of the prog the user's detail don't appear on the right side so that they can be edited. If I right click on the user in the list then the only enabled options are Add, Refresh and Show Hosts. If I add a user, there's nothing else I can do it. The new user shows up in the list but I can't modify it or delete it. I'm logged in a root. I don't know what else to do. Any thoughts? Hello! I think, that you haven't chosen a host for user. Choose it and user options will be enabled to edit. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) Comment: Using GnuPG with SUSE - http://enigmail.mozdev.org iD8DBQFFrwYIn6e/N0YuRmcRAmJuAJ4u50WeNgr+fuTudhwXNk0mvi7VsACgpk1b sNzrOlk/rpVfD8ssw6hukUo= =Z2sQ -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Administrator problem
There was already a bug report submitted. That's where I found other users that have the same problem. Colin Charles [EMAIL PROTECTED] 1/17/07 6:05 PM Ed Reed wrote: Unable to manage user accounts through MySQL Administrator 1.2.8. I've found that I'm not the only one with this problem but no one seems to have an answer for it. Have you reported a bug to bugs.mysql.com ? kind regards -- Colin Charles, Community Engineer MySQL AB, Melbourne, Australia, www.mysql.com Mobile: +614 12 593 292 / Skype: colincharles Web: http://www.bytebot.net/blog/ MySQL Forge: http://forge.mysql.com/
Re: MySQL Administrator problem
I don't have any way of selecting a host. I only have one host on my system and I don't see anything in the program that explicitly shows me where to select the host. Igor Alexandrov [EMAIL PROTECTED] 1/17/07 9:30 PM -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Ed Reed wrote: Unable to manage user accounts through MySQL Administrator 1.2.8. I've found that I'm not the only one with this problem but no one seems to have an answer for it. The problem is that when you select a user in the user list of the prog the user's detail don't appear on the right side so that they can be edited. If I right click on the user in the list then the only enabled options are Add, Refresh and Show Hosts. If I add a user, there's nothing else I can do it. The new user shows up in the list but I can't modify it or delete it. I'm logged in a root. I don't know what else to do. Any thoughts? Hello! I think, that you haven't chosen a host for user. Choose it and user options will be enabled to edit. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) Comment: Using GnuPG with SUSE - http://enigmail.mozdev.org ( http://enigmail.mozdev.org/ ) iD8DBQFFrwYIn6e/N0YuRmcRAmJuAJ4u50WeNgr+fuTudhwXNk0mvi7VsACgpk1b sNzrOlk/rpVfD8ssw6hukUo= =Z2sQ -END PGP SIGNATURE-
Load Balance on MySql
Hi Friends , I have huge WAP content database and it's included with wallpapers, ringtones, games ...etc.That content database getting hits more that 1000 /sec. This is actually massive. My Webserver s are Tomcat and apache , because some applications written in java and some are php . perl. Hence i needed to use that both wap servers.Due high load for the content database ,it's getting hang/stuck/panic. I couldn't even restart the mysql service. I have restart the machine , no any other way to release the stucked load. OS - RHEL : 2.6.9-42.0.3.ELsmp MySql - 4.1.7 [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock old_passwords=1 # flush back_log=2000 connect_timeout=10 interactive_timeout=25 join_buffer_size=4M key_buffer=1024M max_allowed_packet=64M max_connections=2000 max_connect_errors=10 myisam_sort_buffer_size=256M read_buffer_size=8M read_rnd_buffer_size=8M sort_buffer_size=8M table_cache=4096 thread_cache_size=400 thread_concurrency=16 wait_timeout=50 query_cache_size=1024M query_cache_limit=32M query_cache_type=1 log=/var/log/mysqld_sql.log log-error=/var/log/mysqld_error.log # [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Now , i have to think about any perfect load balancing method , i can't duplicate the databse in another machine. It's directly conflict with serving contents for each request. can somebody help me on it ? what would be the perfect way to have a balance the load ? How can we take the statics of mysql databse ? is there any open source product available ? Thanx in advance, Shaine. - New Yahoo! Mail is the ultimate force in competitive emailing. Find out more at the Yahoo! Mail Championships. Plus: play games and win prizes.
Re: Load Balance on MySql
Hi Lee better you start to benchmark your system using tools like http://sysbench.sourceforge.net/ You will find out, what boundary you hit, if you hit max thread, reduce thread stack size but very careful on this stuff :) On 1/18/07, Shain Lee [EMAIL PROTECTED] wrote: Hi Friends , I have huge WAP content database and it's included with wallpapers, ringtones, games ...etc.That content database getting hits more that 1000 /sec. This is actually massive. My Webserver s are Tomcat and apache , because some applications written in java and some are php . perl. Hence i needed to use that both wap servers.Due high load for the content database ,it's getting hang/stuck/panic. I couldn't even restart the mysql service. I have restart the machine , no any other way to release the stucked load. OS - RHEL : 2.6.9-42.0.3.ELsmp MySql - 4.1.7 [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock old_passwords=1 # flush back_log=2000 connect_timeout=10 interactive_timeout=25 join_buffer_size=4M key_buffer=1024M max_allowed_packet=64M max_connections=2000 max_connect_errors=10 myisam_sort_buffer_size=256M read_buffer_size=8M read_rnd_buffer_size=8M sort_buffer_size=8M table_cache=4096 thread_cache_size=400 thread_concurrency=16 wait_timeout=50 query_cache_size=1024M query_cache_limit=32M query_cache_type=1 log=/var/log/mysqld_sql.log log-error=/var/log/mysqld_error.log # [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Now , i have to think about any perfect load balancing method , i can't duplicate the databse in another machine. It's directly conflict with serving contents for each request. can somebody help me on it ? what would be the perfect way to have a balance the load ? How can we take the statics of mysql databse ? is there any open source product available ? Thanx in advance, Shaine. - New Yahoo! Mail is the ultimate force in competitive emailing. Find out more at the Yahoo! Mail Championships. Plus: play games and win prizes. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: automated character set conversions for tables
Hello Jerry, Thanks for the reply. You are right. Thats why it finally took me 10-12 work hours to convert a single DB (split over two work days) to a UTF-8 compliant version. While it wasnt necessarily difficult to do (once you figured it out), it can put extra pressure on your eyes if you have to concentrate on the screen all the time ;-). If somebody knows of a smart tool that is doing the hart work feel free to speak ;-) Best regards Nils Valentin Quoting Jerry Schwartz [EMAIL PROTECTED]: Columns can have character set definitions, also. In this case, I hope not. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Chris White [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 10, 2007 10:59 AM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: automated character set conversions for tables I did a DB conversion before that with ALTER DATABASE db_name CHARACTER SET utf8 That worked wonderfully, except not as expected. ;-) It basically converted only the database itself. so I had to do a separate ALTER TABLE ... for each table. The database encoding more establishes the default to use when creating new tables. As far as adjusting every single table, you can work with your Favorite Scripting Program (tm) and run the query: `SHOW TABLES` to get a list of all tables for that database (the column you want is called Tables_in_[database name here]), which you can get the exact column by running it in console or your Favorite SQL Program (tm). Then simply loop over the result set and run the alter table command on each table. -- MySQL General Mailing List 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]
