Permissions for the install
What should the permissions be for the MySQL 5 install on Mac OS X. By default it was Owner = System Group = wheel. Could someone give me a list of directories and what owner:group settings I should have? Bruce Martin The Martin Solution PO Box 644 Delaware Water Gap, PA (570) 421-0670 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql_library_init()
I noticed my client crashes when I try to connect to MySQL 5 server. My client was written against the MySQL 4 C API's. So I looked at the new Docs for MySQL 5 C API's and noticed a new function, mysql_library_init(). I see it has 3 parameters. int mysql_library_init(int argc, char **argv, char **groups) Can someone explain what the 3 parameters are and what they need for info? The docs are a bit light on this subject and it says it is synonym for the mysql_server_init() function, but that function is not listed and is not linked to in the on-line docs. Bruce Martin The Martin Solution PO Box 644 Delaware Water Gap, PA (570) 421-0670 [EMAIL PROTECTED]
5.0.1 vs 5.0.15: view performance
Dear list, I need some inputs/help on my finding below: 5.0.15 make my view (below) almost useless compare with 5.0.1-alpha: with the same data set, 5.0.15 took 18min but 5.0.1 took 6.3sec to get the result: mysqlselect count(1) from unit_address; +--+ | count(1) | +--+ | 1438 | +--+ Also 5.0.15 took 100% of my CPU and make the machine not responsive to any requests! Maybe my whole approach was no good? see below (notice that the number of rows are not so big at all!) create view unit_address as select t0.association_id,t0.property_dict as asso_property, t0.status_code as asso_status_code,t0.flag as asso_flag, t0.type_id as asso_type_id,t1.address_id,t1.city, t1.country_id,t1.county,t1.state_id, t1.status_code as addr_status_code,t1.street,t1.zip, t1.zip_ext,t2.name,t2.unit_id, t2.property_dict as unit_property,t2.type_id as unit_type_id, t2.parent_id as unit_parent_id,t2.status_code as unit_status, t2.gb_name,t2.b5_name,t2.path as unit_path from address_association t0, address t1, enterprise_unit t2 Where t0.address_id = t1.address_id and t0.owner_class='EnterpriseUnit' and t0.owner_id = t2.unit_id; mysql desc enterprise_unit; +---+---+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+---+--+-+-+---+ | name | varchar(80) | YES | | NULL| | | unit_id | mediumint(8) unsigned | NO | | 0 | | | property_dict | text | YES | | NULL| | | type_id | smallint(5) unsigned | YES | | NULL| | | parent_id | mediumint(8) unsigned | YES | | NULL| | | status_code | tinyint(4)| YES | | NULL| | | gb_name | varchar(80) | YES | | NULL| | | b5_name | varchar(80) | YES | | NULL| | | path | varchar(80) | YES | | NULL| | +---+---+--+-+-+---+ mysql desc address; +-+---+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+-+---+ | city| varchar(48) | YES | | NULL| | | country_id | smallint(5) unsigned | YES | | NULL| | | county | varchar(36) | YES | | NULL| | | address_id | int(11) | YES | | NULL| | | status_code | tinyint(4)| YES | | NULL| | | street | text | YES | | NULL| | | zip | varchar(12) | YES | | NULL| | | state_id| mediumint(8) unsigned | YES | | NULL| | | zip_ext | varchar(8)| YES | | NULL| | +-+---+--+-+-+---+ mysql desc address_association; ++---+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++---+--+-+-+---+ | address_id | mediumint(8) unsigned | YES | | NULL| | | association_id | int(10) unsigned | NO | | 0 | | | property_dict | text | YES | | NULL| | | type_id| smallint(5) unsigned | YES | | NULL| | | owner_id | mediumint(8) unsigned | YES | | NULL| | | owner_class| varchar(32) | YES | | NULL| | | status_code| tinyint(4)| YES | | NULL| | | flag | varchar(64) | YES | | NULL| | ++---+--+-+-+---+ mysql select count(1) from address; +--+ | count(1) | +--+ | 1588 | +--+ mysql select count(1) from enterprise_unit; +--+ | count(1) | +--+ | 1444 | +--+ mysql select count(1) from address_association; +--+ | count(1) | +--+ | 1456 | +--+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 5.0.1 vs 5.0.15: view performance
YL [EMAIL PROTECTED] wrote on 10/30/2005 10:24:24 AM: Dear list, I need some inputs/help on my finding below: 5.0.15 make my view (below) almost useless compare with 5.0.1-alpha: with the same data set, 5.0.15 took 18min but 5.0.1 took 6.3sec to get the result: mysqlselect count(1) from unit_address; +--+ | count(1) | +--+ | 1438 | +--+ Also 5.0.15 took 100% of my CPU and make the machine not responsive to any requests! Maybe my whole approach was no good? see below (notice that the number of rows are not so big at all!) create view unit_address as select t0.association_id,t0.property_dict as asso_property, t0.status_code as asso_status_code,t0.flag as asso_flag, t0.type_id as asso_type_id,t1.address_id,t1.city, t1.country_id,t1.county,t1.state_id, t1.status_code as addr_status_code,t1.street,t1.zip, t1.zip_ext,t2.name,t2.unit_id, t2.property_dict as unit_property,t2.type_id as unit_type_id, t2.parent_id as unit_parent_id,t2.status_code as unit_status, t2.gb_name,t2.b5_name,t2.path as unit_path from address_association t0, address t1, enterprise_unit t2 Where t0.address_id = t1.address_id and t0.owner_class='EnterpriseUnit' and t0.owner_id = t2.unit_id; mysql desc enterprise_unit; +---+---+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+---+--+-+-+---+ | name | varchar(80) | YES | | NULL| | | unit_id | mediumint(8) unsigned | NO | | 0 | | | property_dict | text | YES | | NULL| | | type_id | smallint(5) unsigned | YES | | NULL| | | parent_id | mediumint(8) unsigned | YES | | NULL| | | status_code | tinyint(4)| YES | | NULL| | | gb_name | varchar(80) | YES | | NULL| | | b5_name | varchar(80) | YES | | NULL| | | path | varchar(80) | YES | | NULL| | +---+---+--+-+-+---+ mysql desc address; +-+---+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+-+---+ | city| varchar(48) | YES | | NULL| | | country_id | smallint(5) unsigned | YES | | NULL| | | county | varchar(36) | YES | | NULL| | | address_id | int(11) | YES | | NULL| | | status_code | tinyint(4)| YES | | NULL| | | street | text | YES | | NULL| | | zip | varchar(12) | YES | | NULL| | | state_id| mediumint(8) unsigned | YES | | NULL| | | zip_ext | varchar(8)| YES | | NULL| | +-+---+--+-+-+---+ mysql desc address_association; ++---+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++---+--+-+-+---+ | address_id | mediumint(8) unsigned | YES | | NULL| | | association_id | int(10) unsigned | NO | | 0 | | | property_dict | text | YES | | NULL| | | type_id| smallint(5) unsigned | YES | | NULL| | | owner_id | mediumint(8) unsigned | YES | | NULL| | | owner_class| varchar(32) | YES | | NULL| | | status_code| tinyint(4)| YES | | NULL| | | flag | varchar(64) | YES | | NULL| | ++---+--+-+-+---+ mysql select count(1) from address; +--+ | count(1) | +--+ | 1588 | +--+ mysql select count(1) from enterprise_unit; +--+ | count(1) | +--+ | 1444 | +--+ mysql select count(1) from address_association; +--+ | count(1) | +--+ | 1456 | +--+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Something I recently gleaned by lurking on the INTERNALs list is that the comma operator is scheduled to evaluate AFTER explicit inner joins. I don't think that your SQL statement is efficiently declaring your view. Please try the EXPLICITLY JOINed version of your select statement and verify that an EXPLAIN on your statement still shows that you are using the indexes you wanted used in the first place. If it
FW: What development environment?
Hello, I am a website designer and am committed to learning how to create database driven websites. Currently I am thinking about using php with MySql, but was wondering if there were any more compelling development environments such as visual studio.net, asp, and or java. Based on the experience of users on this list, can anyone tell me what they prefer and why? What do most people use here? Joe.
Re: mysql_library_init()
At 10:23 -0500 10/30/05, Bruce Martin wrote: I noticed my client crashes when I try to connect to MySQL 5 server. My client was written against the MySQL 4 C API's. So I looked at the new Docs for MySQL 5 C API's and noticed a new function, mysql_library_init(). I see it has 3 parameters. int mysql_library_init(int argc, char **argv, char **groups) Can someone explain what the 3 parameters are and what they need for info? The docs are a bit light on this subject and it says it is synonym for the mysql_server_init() function, but that function is not listed and is not linked to in the on-line docs. http://dev.mysql.com/doc/refman/5.0/en/c-embedded-server-func.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 5.0.1 vs 5.0.15: view performance
At 15:53 -0500 10/30/05, [EMAIL PROTECTED] wrote: YL [EMAIL PROTECTED] wrote on 10/30/2005 10:24:24 AM: Dear list, I need some inputs/help on my finding below: 5.0.15 make my view (below) almost useless compare with 5.0.1-alpha: with the same data set, 5.0.15 took 18min but 5.0.1 took 6.3sec to get the result: mysqlselect count(1) from unit_address; +--+ | count(1) | +--+ | 1438 | +--+ Also 5.0.15 took 100% of my CPU and make the machine not responsive to any requests! Maybe my whole approach was no good? see below (notice that the number of rows are not so big at all!) create view unit_address as select t0.association_id,t0.property_dict as asso_property, t0.status_code as asso_status_code,t0.flag as asso_flag, t0.type_id as asso_type_id,t1.address_id,t1.city, t1.country_id,t1.county,t1.state_id, t1.status_code as addr_status_code,t1.street,t1.zip, t1.zip_ext,t2.name,t2.unit_id, t2.property_dict as unit_property,t2.type_id as unit_type_id, t2.parent_id as unit_parent_id,t2.status_code as unit_status, t2.gb_name,t2.b5_name,t2.path as unit_path from address_association t0, address t1, enterprise_unit t2 Where t0.address_id = t1.address_id and t0.owner_class='EnterpriseUnit' and t0.owner_id = t2.unit_id; mysql desc enterprise_unit; +---+---+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+---+--+-+-+---+ | name | varchar(80) | YES | | NULL| | | unit_id | mediumint(8) unsigned | NO | | 0 | | | property_dict | text | YES | | NULL| | | type_id | smallint(5) unsigned | YES | | NULL| | | parent_id | mediumint(8) unsigned | YES | | NULL| | | status_code | tinyint(4)| YES | | NULL| | | gb_name | varchar(80) | YES | | NULL| | | b5_name | varchar(80) | YES | | NULL| | | path | varchar(80) | YES | | NULL| | +---+---+--+-+-+---+ mysql desc address; +-+---+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+-+---+ | city| varchar(48) | YES | | NULL| | | country_id | smallint(5) unsigned | YES | | NULL| | | county | varchar(36) | YES | | NULL| | | address_id | int(11) | YES | | NULL| | | status_code | tinyint(4)| YES | | NULL| | | street | text | YES | | NULL| | | zip | varchar(12) | YES | | NULL| | | state_id| mediumint(8) unsigned | YES | | NULL| | | zip_ext | varchar(8)| YES | | NULL| | +-+---+--+-+-+---+ mysql desc address_association; ++---+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++---+--+-+-+---+ | address_id | mediumint(8) unsigned | YES | | NULL| | | association_id | int(10) unsigned | NO | | 0 | | | property_dict | text | YES | | NULL| | | type_id| smallint(5) unsigned | YES | | NULL| | | owner_id | mediumint(8) unsigned | YES | | NULL| | | owner_class| varchar(32) | YES | | NULL| | | status_code| tinyint(4)| YES | | NULL| | | flag | varchar(64) | YES | | NULL| | ++---+--+-+-+---+ mysql select count(1) from address; +--+ | count(1) | +--+ | 1588 | +--+ mysql select count(1) from enterprise_unit; +--+ | count(1) | +--+ | 1444 | +--+ mysql select count(1) from address_association; +--+ | count(1) | +--+ | 1456 | +--+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Something I recently gleaned by lurking on the INTERNALs list is that the comma operator is scheduled to evaluate AFTER explicit inner joins. I Several changes to join processing were made in 5.0.2 for compliance with standard SQL. You can read about implications of these changes here: http://dev.mysql.com/doc/refman/5.0/en/join.html don't think that your SQL
Re: 5.0.1 vs 5.0.15: view performance
Thanks SGreen's help. I don't know much about how to use the result of 'Explain' but here it is mysql explain - select t0.association_id - , t0.property_dict as asso_property - , t0.status_code as asso_status_code - , t0.flag as asso_flag - , t0.type_id as asso_type_id - , t1.address_id,t1.city - , t1.country_id - , t1.county - , t1.state_id - , t1.status_code as addr_status_code - , t1.street - , t1.zip - , t1.zip_ext - , t2.name - , t2.unit_id - , t2.property_dict as unit_property - , t2.type_id as unit_type_id - , t2.parent_id as unit_parent_id - , t2.status_code as unit_status - , t2.gb_name - , t2.b5_name - , t2.path as unit_path - FROM address_association t0 - INNER JOIN address t1 - ON t0.address_id = t1.address_id - INNER JOIN enterprise_unit t2 - ON t0.owner_id = t2.unit_id - WHERE t0.owner_class='EnterpriseUnit'; ++-+---+--+---+--+-+--+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+--+-+--+--+-+ | 1 | SIMPLE | t1| ALL | NULL | NULL | NULL| NULL | 1588 | | | 1 | SIMPLE | t2| ALL | NULL | NULL | NULL| NULL | 1444 | | | 1 | SIMPLE | t0| ALL | NULL | NULL | NULL| NULL | 1456 | Using where | ++-+---+--+---+--+-+--+--+-+ 3 rows in set (0.11 sec) mysql - Original Message - From: [EMAIL PROTECTED] To: YL [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Sunday, October 30, 2005 1:53 PM Subject: Re: 5.0.1 vs 5.0.15: view performance YL [EMAIL PROTECTED] wrote on 10/30/2005 10:24:24 AM: Dear list, I need some inputs/help on my finding below: 5.0.15 make my view (below) almost useless compare with 5.0.1-alpha: with the same data set, 5.0.15 took 18min but 5.0.1 took 6.3sec to get the result: mysqlselect count(1) from unit_address; +--+ | count(1) | +--+ | 1438 | +--+ Also 5.0.15 took 100% of my CPU and make the machine not responsive to any requests! Maybe my whole approach was no good? see below (notice that the number of rows are not so big at all!) create view unit_address as select t0.association_id,t0.property_dict as asso_property, t0.status_code as asso_status_code,t0.flag as asso_flag, t0.type_id as asso_type_id,t1.address_id,t1.city, t1.country_id,t1.county,t1.state_id, t1.status_code as addr_status_code,t1.street,t1.zip, t1.zip_ext,t2.name,t2.unit_id, t2.property_dict as unit_property,t2.type_id as unit_type_id, t2.parent_id as unit_parent_id,t2.status_code as unit_status, t2.gb_name,t2.b5_name,t2.path as unit_path from address_association t0, address t1, enterprise_unit t2 Where t0.address_id = t1.address_id and t0.owner_class='EnterpriseUnit' and t0.owner_id = t2.unit_id; mysql desc enterprise_unit; +---+---+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+---+--+-+-+---+ | name | varchar(80) | YES | | NULL| | | unit_id | mediumint(8) unsigned | NO | | 0 | | | property_dict | text | YES | | NULL| | | type_id | smallint(5) unsigned | YES | | NULL| | | parent_id | mediumint(8) unsigned | YES | | NULL| | | status_code | tinyint(4)| YES | | NULL| | | gb_name | varchar(80) | YES | | NULL| | | b5_name | varchar(80) | YES | | NULL| | | path | varchar(80) | YES | | NULL| | +---+---+--+-+-+---+ mysql desc address; +-+---+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+-+---+ | city| varchar(48) | YES | | NULL| | | country_id | smallint(5) unsigned | YES | | NULL| | | county | varchar(36) | YES | | NULL| | | address_id | int(11) | YES | | NULL| | | status_code | tinyint(4)| YES | | NULL| | | street | text | YES | |
Re: 5.0.1 vs 5.0.15: view performance
You just identified the reason this is SO slow. You seem to have no indexes. Can you post the results of SHOW CREATE TABLE address_association\G SHOW CREATE TABLE address\G SHOW CREATE TABLE enterprise_unit\G That will let me know exactly what is and isn't already indexed. Thanks! Shawn Green Database Administrator Unimin Corporation - Spruce Pine YL [EMAIL PROTECTED] wrote on 10/30/2005 08:23:14 PM: Thanks SGreen's help. I don't know much about how to use the result of 'Explain' but here it is mysql explain - select t0.association_id - , t0.property_dict as asso_property - , t0.status_code as asso_status_code - , t0.flag as asso_flag - , t0.type_id as asso_type_id - , t1.address_id,t1.city - , t1.country_id - , t1.county - , t1.state_id - , t1.status_code as addr_status_code - , t1.street - , t1.zip - , t1.zip_ext - , t2.name - , t2.unit_id - , t2.property_dict as unit_property - , t2.type_id as unit_type_id - , t2.parent_id as unit_parent_id - , t2.status_code as unit_status - , t2.gb_name - , t2.b5_name - , t2.path as unit_path - FROM address_association t0 - INNER JOIN address t1 - ON t0.address_id = t1.address_id - INNER JOIN enterprise_unit t2 - ON t0.owner_id = t2.unit_id - WHERE t0.owner_class='EnterpriseUnit'; ++-+---+--+---+--+- +--+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+--+- +--+--+-+ | 1 | SIMPLE | t1| ALL | NULL | NULL | NULL| NULL | 1588 | | | 1 | SIMPLE | t2| ALL | NULL | NULL | NULL| NULL | 1444 | | | 1 | SIMPLE | t0| ALL | NULL | NULL | NULL| NULL | 1456 | Using where | ++-+---+--+---+--+- +--+--+-+ 3 rows in set (0.11 sec) mysql - Original Message - From: [EMAIL PROTECTED] To: YL [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Sunday, October 30, 2005 1:53 PM Subject: Re: 5.0.1 vs 5.0.15: view performance YL [EMAIL PROTECTED] wrote on 10/30/2005 10:24:24 AM: Dear list, I need some inputs/help on my finding below: 5.0.15 make my view (below) almost useless compare with 5.0.1-alpha: with the same data set, 5.0.15 took 18min but 5.0.1 took 6.3sec to get the result: mysqlselect count(1) from unit_address; +--+ | count(1) | +--+ | 1438 | +--+ Also 5.0.15 took 100% of my CPU and make the machine not responsive to any requests! Maybe my whole approach was no good? see below (notice that the number of rows are not so big at all!) create view unit_address as select t0.association_id,t0.property_dict as asso_property, t0.status_code as asso_status_code,t0.flag as asso_flag, t0.type_id as asso_type_id,t1.address_id,t1.city, t1.country_id,t1.county,t1.state_id, t1.status_code as addr_status_code,t1.street,t1.zip, t1.zip_ext,t2.name,t2.unit_id, t2.property_dict as unit_property,t2.type_id as unit_type_id, t2.parent_id as unit_parent_id,t2.status_code as unit_status, t2.gb_name,t2.b5_name,t2.path as unit_path from address_association t0, address t1, enterprise_unit t2 Where t0.address_id = t1.address_id and t0.owner_class='EnterpriseUnit' and t0.owner_id = t2.unit_id; mysql desc enterprise_unit; +---+---+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+---+--+-+-+---+ | name | varchar(80) | YES | | NULL| | | unit_id | mediumint(8) unsigned | NO | | 0 | | | property_dict | text | YES | | NULL| | | type_id | smallint(5) unsigned | YES | | NULL| | | parent_id | mediumint(8) unsigned | YES | | NULL| | | status_code | tinyint(4)| YES | | NULL| | | gb_name | varchar(80) | YES | | NULL| | | b5_name | varchar(80) | YES | | NULL| | | path | varchar(80) | YES | | NULL| | +---+---+--+-+-+---+ mysql desc address; +-+---+--+-+-+---+ | Field | Type | Null | Key | Default | Extra |
Active user sessions
Hi, Is there a way of listing all the active db sessions and their IP addresses and/or db user names? Something like v$session in oracle? Thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 5.0.1 vs 5.0.15: view performance
Thanks Shawn for the help: The same query took 2min less than before on 5.0.15 after using inner join. Is what you ask: mysql show create table address\G; *** 1. row *** Table: address Create Table: CREATE TABLE `address` ( `city` varchar(48) default NULL, `country_id` smallint(5) unsigned default NULL, `county` varchar(36) default NULL, `address_id` int(10) unsigned NOT NULL default '0', `status_code` tinyint(4) default NULL, `street` text, `zip` varchar(12) default NULL, `state_id` mediumint(8) unsigned default NULL, `zip_ext` varchar(8) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.01 sec) ERROR: No query specified mysql show create table address_association\G; *** 1. row *** Table: address_association Create Table: CREATE TABLE `address_association` ( `address_id` mediumint(8) unsigned default NULL, `association_id` int(10) unsigned NOT NULL default '0', `property_dict` text, `type_id` smallint(5) unsigned default NULL, `owner_id` mediumint(8) unsigned default NULL, `owner_class_name` varchar(32) default NULL, `status_code` tinyint(3) unsigned default NULL, `flag` varchar(16) default 'default' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) ERROR: No query specified mysql show create table enterprise_unit\G; *** 1. row *** Table: enterprise_unit Create Table: CREATE TABLE `enterprise_unit` ( `name` varchar(80) default NULL, `unit_id` mediumint(8) unsigned NOT NULL default '0', `property_dict` text, `type_id` smallint(5) unsigned default NULL, `parent_id` mediumint(8) unsigned default NULL, `status_code` tinyint(4) default NULL, `gb_name` varchar(80) default NULL, `b5_name` varchar(80) default NULL, `path` varchar(80) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) ERROR: No query specified From: [EMAIL PROTECTED] To: YL [EMAIL PROTECTED] You just identified the reason this is SO slow. You seem to have no indexes. Can you post the results of SHOW CREATE TABLE address_association\G SHOW CREATE TABLE address\G SHOW CREATE TABLE enterprise_unit\G That will let me know exactly what is and isn't already indexed. Thanks! Shawn Green Database Administrator Unimin Corporation - Spruce Pine YL [EMAIL PROTECTED] wrote on 10/30/2005 08:23:14 PM: Thanks SGreen's help. I don't know much about how to use the result of 'Explain' but here it is mysql explain - select t0.association_id - , t0.property_dict as asso_property - , t0.status_code as asso_status_code - , t0.flag as asso_flag - , t0.type_id as asso_type_id - , t1.address_id,t1.city - , t1.country_id - , t1.county - , t1.state_id - , t1.status_code as addr_status_code - , t1.street - , t1.zip - , t1.zip_ext - , t2.name - , t2.unit_id - , t2.property_dict as unit_property - , t2.type_id as unit_type_id - , t2.parent_id as unit_parent_id - , t2.status_code as unit_status - , t2.gb_name - , t2.b5_name - , t2.path as unit_path - FROM address_association t0 - INNER JOIN address t1 - ON t0.address_id = t1.address_id - INNER JOIN enterprise_unit t2 - ON t0.owner_id = t2.unit_id - WHERE t0.owner_class='EnterpriseUnit'; ++-+---+--+---+--+- +--+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+--+- +--+--+-+ | 1 | SIMPLE | t1| ALL | NULL | NULL | NULL| NULL | 1588 | | | 1 | SIMPLE | t2| ALL | NULL | NULL | NULL| NULL | 1444 | | | 1 | SIMPLE | t0| ALL | NULL | NULL | NULL| NULL | 1456 | Using where | ++-+---+--+---+--+- +--+--+-+ 3 rows in set (0.11 sec) mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Active user sessions
On 31/10/2005 3:43 p.m., Cabbar Duzayak wrote: Hi, Is there a way of listing all the active db sessions and their IP addresses and/or db user names? Something like v$session in oracle? Thanks... SHOW PROCESSLIST; -Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 5.0.1 vs 5.0.15: view performance
As I suspected, you have no indexes. You didn't even define a primary key (PK). For each table, decide which column or combination of columns you can use to uniquely identify each row. Make that your PRIMARY KEY for each table. For other columns or combinations of columns you frequently use in your queries, create INDEXes (KEYs) on them to speed up your query responses. I suspect that on your `address` table, the `addresss_id` column is unique for each row. To mark it as the primary key for the `address` table you could use this command ALTER TABLE `address` ADD PRIMARY KEY(`address_id`); The table `address_association` should probably only contain a single row for any (address_id, association_id) combination. That would make those two columns the likely candidate for acting as the PRIMARY KEY for that table. You would declare such a key as ALTER TABLE `adress_association` ADD PRIMARY KEY(`address_id`, `association_id`); You should perform the same analysis for all of your other tables. Very rarely is it ever a good design to allow duplicates of entire rows within the same table. There should always be something that makes one row unique from every other row in the same table. In your case, an additional index on several columns of `address_association` will make your particular view much faster. ALTER TABLE `address_association` ADD KEY(`owner_class`,`owner_id`, `association_id`); You need to add all of the appropriate indexes to all of your tables. Then, look at another EXPLAIN of your SELECT statement, the KEY column should be filled in for at least 2 of your tables. With the tiny number of rows you have in your database you should be seeing results returned in less than 0.05 seconds (even if you have an 1980's machine). It's the complete lack of indexes that has killed your performance. Shawn Green Database Administrator Unimin Corporation - Spruce Pine YL [EMAIL PROTECTED] wrote on 10/30/2005 10:21:39 PM: Thanks Shawn for the help: The same query took 2min less than before on 5.0.15 after using inner join. Is what you ask: mysql show create table address\G; *** 1. row *** Table: address Create Table: CREATE TABLE `address` ( `city` varchar(48) default NULL, `country_id` smallint(5) unsigned default NULL, `county` varchar(36) default NULL, `address_id` int(10) unsigned NOT NULL default '0', `status_code` tinyint(4) default NULL, `street` text, `zip` varchar(12) default NULL, `state_id` mediumint(8) unsigned default NULL, `zip_ext` varchar(8) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.01 sec) ERROR: No query specified mysql show create table address_association\G; *** 1. row *** Table: address_association Create Table: CREATE TABLE `address_association` ( `address_id` mediumint(8) unsigned default NULL, `association_id` int(10) unsigned NOT NULL default '0', `property_dict` text, `type_id` smallint(5) unsigned default NULL, `owner_id` mediumint(8) unsigned default NULL, `owner_class_name` varchar(32) default NULL, `status_code` tinyint(3) unsigned default NULL, `flag` varchar(16) default 'default' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) ERROR: No query specified mysql show create table enterprise_unit\G; *** 1. row *** Table: enterprise_unit Create Table: CREATE TABLE `enterprise_unit` ( `name` varchar(80) default NULL, `unit_id` mediumint(8) unsigned NOT NULL default '0', `property_dict` text, `type_id` smallint(5) unsigned default NULL, `parent_id` mediumint(8) unsigned default NULL, `status_code` tinyint(4) default NULL, `gb_name` varchar(80) default NULL, `b5_name` varchar(80) default NULL, `path` varchar(80) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) ERROR: No query specified From: [EMAIL PROTECTED] To: YL [EMAIL PROTECTED] You just identified the reason this is SO slow. You seem to have no indexes. Can you post the results of SHOW CREATE TABLE address_association\G SHOW CREATE TABLE address\G SHOW CREATE TABLE enterprise_unit\G That will let me know exactly what is and isn't already indexed. Thanks! Shawn Green Database Administrator Unimin Corporation - Spruce Pine YL [EMAIL PROTECTED] wrote on 10/30/2005 08:23:14 PM: Thanks SGreen's help. I don't know much about how to use the result of 'Explain' but here it is mysql explain - select t0.association_id - , t0.property_dict as asso_property - , t0.status_code as asso_status_code - , t0.flag as asso_flag - , t0.type_id as asso_type_id - , t1.address_id,t1.city - , t1.country_id - , t1.county
run mysqld-nt.exe in background
Hi, How could run mysqld-nt.exe in background on win2000/XP, not as a service of windows. mysqld-nt.exe --console alway show a dos/ text window . Regards! Shuming Wang Xtech Company limited Room 17G,17/F,Plaza 2, Tiancheng Building,No 96 Longkouxi Road, Tianhe Guangzhou,Guangdong,China http://www.xtech.com.cn http://www.87595959.com Email: [EMAIL PROTECTED] QQ:370783763 , MSN: [EMAIL PROTECTED] Tel:86-20-87595959---2001,2002 Fax:86-20-87576779
Re: run mysqld-nt.exe in background
wang shuming [EMAIL PROTECTED] wrote on 10/31/2005 01:11:06 AM: Hi, How could run mysqld-nt.exe in background on win2000/XP, not as a service of windows. mysqld-nt.exe --console alway show a dos/ text window . Regards! Shuming Wang Xtech Company limited Room 17G,17/F,Plaza 2, Tiancheng Building,No 96 Longkouxi Road, Tianhe Guangzhou,Guangdong,China http://www.xtech.com.cn http://www.87595959.com Email: [EMAIL PROTECTED] QQ:370783763 , MSN: [EMAIL PROTECTED] Tel:86-20-87595959---2001,2002 Fax:86-20-87576779 Windows does not do background execution like the *-nix operating systems do. Sorry, it's just one of those things that Bill didn't think would be useful in HIS operating system. AFAIK, there has never, ever, been a Windows (or DOS) command or command modifier to launch an executable in the background and I have been a Windows(DOS) user since the mid 80's. I believe I would have probably run across it a long time ago if it existed. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: 5.0.1 vs 5.0.15: view performance
Thanks a lot Shawn: After adding index, it's amazingly faster( 0.15 sec, was 16 min !). I thought index columns would be necessary only for tables have 10,000 rows or more but I was so wrong. address_id, association_id and unit_id are PKs for tables Address, Address_Association and Enterprise_Unit respectively and I have a table eo_pk_table to store the max pk of each table, which is maintained by the application to figure out the next key and set the foreign key for new records and keep the uniqueness of records. Now tables were modified and PKs became explicit at database level. Does this help the performance? association_id is not refered very often, but address_id is. so I indexed address_id in address_association. Is it a good idea to index PKs for big table? Thanks again, YL mysql show create table address\G; *** 1. row *** Table: address Create Table: CREATE TABLE `address` ( . `county` varchar(36) default NULL, `address_id` int(10) unsigned NOT NULL default '0', . `zip_ext` varchar(8) default NULL, PRIMARY KEY (`address_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 mysql show create table address_association\G; *** 1. row *** Table: address_association Create Table: CREATE TABLE `address_association` ( . `association_id` int(10) unsigned NOT NULL default '0', . PRIMARY KEY (`association_id`), KEY `owner_class` (`owner_class`,`owner_id`,`address_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 mysql show create table enterprise_unit\G; *** 1. row *** Table: enterprise_unit Create Table: CREATE TABLE `enterprise_unit` ( . `unit_id` mediumint(8) unsigned NOT NULL default '0', . PRIMARY KEY (`unit_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 Thanks again - Original Message - From: [EMAIL PROTECTED] To: YL Cc: mysql@lists.mysql.com Sent: Sunday, October 30, 2005 9:25 PM Subject: Re: 5.0.1 vs 5.0.15: view performance As I suspected, you have no indexes. You didn't even define a primary key (PK). For each table, decide which column or combination of columns you can use to uniquely identify each row. Make that your PRIMARY KEY for each table. For other columns or combinations of columns you frequently use in your queries, create INDEXes (KEYs) on them to speed up your query responses. I suspect that on your `address` table, the `addresss_id` column is unique for each row. To mark it as the primary key for the `address` table you could use this command ALTER TABLE `address` ADD PRIMARY KEY(`address_id`); The table `address_association` should probably only contain a single row for any (address_id, association_id) combination. That would make those two columns the likely candidate for acting as the PRIMARY KEY for that table. You would declare such a key as ALTER TABLE `adress_association` ADD PRIMARY KEY(`address_id`, `association_id`); You should perform the same analysis for all of your other tables. Very rarely is it ever a good design to allow duplicates of entire rows within the same table. There should always be something that makes one row unique from every other row in the same table. In your case, an additional index on several columns of `address_association` will make your particular view much faster. ALTER TABLE `address_association` ADD KEY(`owner_class`,`owner_id`, `association_id`); You need to add all of the appropriate indexes to all of your tables. Then, look at another EXPLAIN of your SELECT statement, the KEY column should be filled in for at least 2 of your tables. With the tiny number of rows you have in your database you should be seeing results returned in less than 0.05 seconds (even if you have an 1980's machine). It's the complete lack of indexes that has killed your performance. Shawn Green Database Administrator Unimin Corporation - Spruce Pine YL [EMAIL PROTECTED] wrote on 10/30/2005 10:21:39 PM: Thanks Shawn for the help: The same query took 2min less than before on 5.0.15 after using inner join. Is what you ask: mysql show create table address\G; *** 1. row *** Table: address Create Table: CREATE TABLE `address` ( `city` varchar(48) default NULL, `country_id` smallint(5) unsigned default NULL, `county` varchar(36) default NULL, `address_id` int(10) unsigned NOT NULL default '0', `status_code` tinyint(4) default NULL, `street` text, `zip` varchar(12) default NULL, `state_id` mediumint(8) unsigned default NULL, `zip_ext` varchar(8) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.01 sec) ERROR: No query specified mysql show create table address_association\G; *** 1. row
Setting root password, causing problems.
Hello all! I wanted to discuss a problem I am having with setting up MySQL. I noticed that my root password isn't set. When i try to set it via phpMyAdmin, MySQL doesn't recognize my databases, and denies all connections. Now I've tried setting the password via SSH by accessing the MySQL prompt, and through Webmin and phpMyAdmin, etc.. but to no avail, it won't work! I have MySQL 4.0.16 and I run on FreeBSD. I would really appreciate any insight you can give me. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
