Re: Fwd: Why dont my query use the index keys?
Hi Johan, the query is going for a temporary table creation Using temporary; Using filesort In case your sort_buffer_size is too low please try increasing the same. Be cautious don't try to increase it massively, since it is a per thread memory allocation. please let me know what is your sort_buffer_size by using the command show global variables like 'sort%'; Regards, Pradeep Chandru. Johan Thorvaldsson wrote: So query and index:es are ok ? Because the query runs very slow. Vidarebefordrat brev: Från: Ananda Kumar [EMAIL PROTECTED] Datum: ti 16 sep 2008 13.19.43 GMT+02:00 Till: Johan Thorvaldsson [EMAIL PROTECTED] Kopia: mysql@lists.mysql.com Ämne: Re: Why dont my query use the index keys? Hi Johan, Its doing a range based search, so its using index. regards anandkl On 9/16/08, Johan Thorvaldsson [EMAIL PROTECTED] wrote: But this one doesnt use any indexes? SIMPLE tm range is_active,ad_id,tag_id * tag_id* 4 NULL15353 Using where; Using temporary; Using filesort Thanks for your replies. Vidarebefordrat brev: Från: chandru [EMAIL PROTECTED] Datum: ti 16 sep 2008 10.13.53 GMT+02:00 Till: Johan Thorvaldsson [EMAIL PROTECTED] Kopia: mysql@lists.mysql.com Ämne: Re: Why dont my query use the index keys? Hi johan, i find that the query is using a index already. Explain version: 1 SIMPLE tm range is_active,ad_id,tag_id * tag_id* 4 NULL15353 Using where; Using temporary; Using filesort 1 SIMPLE ad eq_ref PRIMARY,ad_id,is_removed,is_active,whitelisted,ant_feedback_alert *PRIMARY* 4 rubbt.tm.ad_id 1 I have bold the index names that are being used. Can you please clarify is that the one you are trying to find? Regards, Pradeep Chandru. Johan Thorvaldsson wrote: AD Create Table: CREATE TABLE `ad` ( `ad_id` int(11) NOT NULL auto_increment, `ad_key` varchar(32) collate utf8_swedish_ci NOT NULL default '', `pris` varchar(32) collate utf8_swedish_ci NOT NULL default '', `pris_original` varchar(32) collate utf8_swedish_ci NOT NULL default '', `dt_pub` datetime NOT NULL default '-00-00 00:00:00', `dt_updated` datetime NOT NULL default '-00-00 00:00:00', `ad_password` varchar(32) collate utf8_swedish_ci NOT NULL default '', `adtext` text collate utf8_swedish_ci NOT NULL, `unique_key` varchar(32) collate utf8_swedish_ci NOT NULL default '', `is_removed` int(11) NOT NULL default '0', `is_active` int(11) NOT NULL default '0', `num_images` int(11) default NULL, `sh_phone` int(11) default '0', `postalcode` varchar(32) collate utf8_swedish_ci default NULL, `ad_url` varchar(128) collate utf8_swedish_ci NOT NULL default '', `source_id` int(11) default '0', `youtube_link` text collate utf8_swedish_ci, `ad_sections` varchar(128) collate utf8_swedish_ci default NULL, `flickr_link` text collate utf8_swedish_ci, `ant_feedback_good` int(32) default '0', `ant_feedback_alert` int(32) default '0', `whitelisted` int(11) default '0', `adtext_plain` text collate utf8_swedish_ci NOT NULL, `dt_img_path` datetime NOT NULL default '-00-00 00:00:00', PRIMARY KEY (`ad_id`), KEY `sortkey` (`dt_pub`,`pris`), KEY `webbid` (`ad_key`), KEY `ad_id` (`ad_id`) ) ENGINE=MyISAM AUTO_INCREMENT=18721 DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci +---++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+-+---+-+--++--++-+ | ad| 0 | PRIMARY |1 | ad_id | A | 18691 | NULL | NULL | | BTREE | | | ad| 1 | sortkey |1 | dt_pub | A |3115 | NULL | NULL | | BTREE | | | ad| 1 | sortkey |2 | pris| A |3115 | NULL | NULL | | BTREE | | | ad| 1 | webbid |1 | ad_key | A | 18691 | NULL | NULL | | BTREE | | | ad| 1 | ad_id|1 | ad_id | A | 18691 | NULL | NULL | | BTREE | | +---++--+--+-+---+-+--++--++-+ Tag_ad_map Create Table: CREATE TABLE `tag_ad_map` ( `ad_id` int(11) NOT NULL default '0', `tag_id` int(11) NOT NULL default '0', `termfreq` int(11) NOT NULL default '0', `weight` int(11) NOT NULL default '0', `is_active` int(11) NOT NULL default '0', PRIMARY KEY (`tag_id`,`ad_id`), KEY `tag_id` (`tag_id`), KEY `ad_id` (`ad_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8
Re: Why dont my query use the index keys?
mysql show global variables like 'sort%'; +--+-+ | Variable_name| Value | +--+-+ | sort_buffer_size | 4194296 | +--+-+ 1 row in set (0.01 sec) 18 sep 2008 kl. 08.05 skrev chandru: Hi Johan, the query is going for a temporary table creation Using temporary; Using filesort In case your sort_buffer_size is too low please try increasing the same. Be cautious don't try to increase it massively, since it is a per thread memory allocation. please let me know what is your sort_buffer_size by using the command show global variables like 'sort%'; Regards, Pradeep Chandru. Johan Thorvaldsson wrote: - Montania System AB Halmstad http://www.montania.se Johan Thorvaldsson [EMAIL PROTECTED] Kristinebergsvägen 17, S-30241 HALMSTAD Telefon +46(0)35-13 68 00 | Fax +46(0)35-13 68 01
Re: Why dont my query use the index keys?
Hi johan, you have allocated 4M for your sort buffer size is ok. So i feel that the query is loading more than 4MB of data so it is creating a temporary file to do a sorting. please try to put some logic into the query that you use to load the appropriate data. I find that the tag_id has a cardinality of 35921 which is low which tells that there are more records matching the condition tm.tag_id IN (99, 10807, 20728, 447, 807) you might have to put some logic to over ride the same. Even putting some partitioning can help you. Regards, Pradeep Chandru. Johan Thorvaldsson wrote: mysql show global variables like 'sort%'; +--+-+ | Variable_name| Value | +--+-+ | sort_buffer_size | 4194296 | +--+-+ 1 row in set (0.01 sec) 18 sep 2008 kl. 08.05 skrev chandru: Hi Johan, the query is going for a temporary table creation Using temporary; Using filesort In case your sort_buffer_size is too low please try increasing the same. Be cautious don't try to increase it massively, since it is a per thread memory allocation. please let me know what is your sort_buffer_size by using the command show global variables like 'sort%'; Regards, Pradeep Chandru. Johan Thorvaldsson wrote: - Montania System AB Halmstad http://www.montania.se Johan Thorvaldsson [EMAIL PROTECTED] Kristinebergsvägen 17, S-30241 HALMSTAD Telefon +46(0)35-13 68 00 | Fax +46(0)35-13 68 01 ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail notify us immediately at [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fwd: Why dont my query use the index keys?
Hi johan, U can use and try to write a query to use index in it (like use index(index name) before where condition. And other variables also looks like ok seems let me check, Please try to send the output of global status. Show global status; Thanks Regards, Dilipkumar -Original Message- From: chandru [mailto:[EMAIL PROTECTED] Sent: Thursday, September 18, 2008 11:36 AM To: Johan Thorvaldsson Cc: mysql@lists.mysql.com Subject: Re: Fwd: Why dont my query use the index keys? Hi Johan, the query is going for a temporary table creation Using temporary; Using filesort In case your sort_buffer_size is too low please try increasing the same. Be cautious don't try to increase it massively, since it is a per thread memory allocation. please let me know what is your sort_buffer_size by using the command show global variables like 'sort%'; Regards, Pradeep Chandru. Johan Thorvaldsson wrote: So query and index:es are ok ? Because the query runs very slow. Vidarebefordrat brev: Från: Ananda Kumar [EMAIL PROTECTED] Datum: ti 16 sep 2008 13.19.43 GMT+02:00 Till: Johan Thorvaldsson [EMAIL PROTECTED] Kopia: mysql@lists.mysql.com Ämne: Re: Why dont my query use the index keys? Hi Johan, Its doing a range based search, so its using index. regards anandkl On 9/16/08, Johan Thorvaldsson [EMAIL PROTECTED] wrote: But this one doesnt use any indexes? SIMPLE tm range is_active,ad_id,tag_id * tag_id* 4 NULL15353 Using where; Using temporary; Using filesort Thanks for your replies. Vidarebefordrat brev: Från: chandru [EMAIL PROTECTED] Datum: ti 16 sep 2008 10.13.53 GMT+02:00 Till: Johan Thorvaldsson [EMAIL PROTECTED] Kopia: mysql@lists.mysql.com Ämne: Re: Why dont my query use the index keys? Hi johan, i find that the query is using a index already. Explain version: 1 SIMPLE tm range is_active,ad_id,tag_id * tag_id* 4 NULL15353 Using where; Using temporary; Using filesort 1 SIMPLE ad eq_ref PRIMARY,ad_id,is_removed,is_active,whitelisted,ant_feedback_alert *PRIMARY* 4 rubbt.tm.ad_id 1 I have bold the index names that are being used. Can you please clarify is that the one you are trying to find? Regards, Pradeep Chandru. Johan Thorvaldsson wrote: AD Create Table: CREATE TABLE `ad` ( `ad_id` int(11) NOT NULL auto_increment, `ad_key` varchar(32) collate utf8_swedish_ci NOT NULL default '', `pris` varchar(32) collate utf8_swedish_ci NOT NULL default '', `pris_original` varchar(32) collate utf8_swedish_ci NOT NULL default '', `dt_pub` datetime NOT NULL default '-00-00 00:00:00', `dt_updated` datetime NOT NULL default '-00-00 00:00:00', `ad_password` varchar(32) collate utf8_swedish_ci NOT NULL default '', `adtext` text collate utf8_swedish_ci NOT NULL, `unique_key` varchar(32) collate utf8_swedish_ci NOT NULL default '', `is_removed` int(11) NOT NULL default '0', `is_active` int(11) NOT NULL default '0', `num_images` int(11) default NULL, `sh_phone` int(11) default '0', `postalcode` varchar(32) collate utf8_swedish_ci default NULL, `ad_url` varchar(128) collate utf8_swedish_ci NOT NULL default '', `source_id` int(11) default '0', `youtube_link` text collate utf8_swedish_ci, `ad_sections` varchar(128) collate utf8_swedish_ci default NULL, `flickr_link` text collate utf8_swedish_ci, `ant_feedback_good` int(32) default '0', `ant_feedback_alert` int(32) default '0', `whitelisted` int(11) default '0', `adtext_plain` text collate utf8_swedish_ci NOT NULL, `dt_img_path` datetime NOT NULL default '-00-00 00:00:00', PRIMARY KEY (`ad_id`), KEY `sortkey` (`dt_pub`,`pris`), KEY `webbid` (`ad_key`), KEY `ad_id` (`ad_id`) ) ENGINE=MyISAM AUTO_INCREMENT=18721 DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci +---++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+-+---+-+--++--++-+ | ad| 0 | PRIMARY |1 | ad_id | A | 18691 | NULL | NULL | | BTREE | | | ad| 1 | sortkey |1 | dt_pub | A |3115 | NULL | NULL | | BTREE | | | ad| 1 | sortkey |2 | pris| A |3115 | NULL | NULL | | BTREE | | | ad| 1 | webbid |1 | ad_key | A | 18691 | NULL | NULL | | BTREE | | | ad| 1 | ad_id|1 | ad_id | A | 18691 | NULL | NULL |
RE: Fwd: Why dont my query use the index keys?
OR Try Using this query ...Just a try SELECT COUNT(*) antal, ad.ad_url, ad.adtext_plain FROM ad use index(index_name) LEFT JOIN tag_ad_map tm ON concat(tm.ad_id=ad.ad_id) WHERE tm.tag_id IN (99, 10807, 20728, 447, 807) AND (ad.is_removed = 0 AND ad.is_active=1 AND (ad.ant_feedback_alert = 5 OR ad.whitelisted = 1) AND tm.is_active=1 AND ad.ad_id != 13687 GROUP BY ad.ad_id ORDER BY antal DESC LIMIT 10 ; Thanks Regards, Dilipkumar -Original Message- From: Parikh, Dilip Kumar [mailto:[EMAIL PROTECTED] Sent: Thursday, September 18, 2008 12:07 PM To: Johan Thorvaldsson Cc: mysql@lists.mysql.com Subject: RE: Fwd: Why dont my query use the index keys? Hi johan, U can use and try to write a query to use index in it (like use index(index name) before where condition. And other variables also looks like ok seems let me check, Please try to send the output of global status. Show global status; Thanks Regards, Dilipkumar -Original Message- From: chandru [mailto:[EMAIL PROTECTED] Sent: Thursday, September 18, 2008 11:36 AM To: Johan Thorvaldsson Cc: mysql@lists.mysql.com Subject: Re: Fwd: Why dont my query use the index keys? Hi Johan, the query is going for a temporary table creation Using temporary; Using filesort In case your sort_buffer_size is too low please try increasing the same. Be cautious don't try to increase it massively, since it is a per thread memory allocation. please let me know what is your sort_buffer_size by using the command show global variables like 'sort%'; Regards, Pradeep Chandru. Johan Thorvaldsson wrote: So query and index:es are ok ? Because the query runs very slow. Vidarebefordrat brev: Från: Ananda Kumar [EMAIL PROTECTED] Datum: ti 16 sep 2008 13.19.43 GMT+02:00 Till: Johan Thorvaldsson [EMAIL PROTECTED] Kopia: mysql@lists.mysql.com Ämne: Re: Why dont my query use the index keys? Hi Johan, Its doing a range based search, so its using index. regards anandkl On 9/16/08, Johan Thorvaldsson [EMAIL PROTECTED] wrote: But this one doesnt use any indexes? SIMPLE tm range is_active,ad_id,tag_id * tag_id* 4 NULL15353 Using where; Using temporary; Using filesort Thanks for your replies. Vidarebefordrat brev: Från: chandru [EMAIL PROTECTED] Datum: ti 16 sep 2008 10.13.53 GMT+02:00 Till: Johan Thorvaldsson [EMAIL PROTECTED] Kopia: mysql@lists.mysql.com Ämne: Re: Why dont my query use the index keys? Hi johan, i find that the query is using a index already. Explain version: 1 SIMPLE tm range is_active,ad_id,tag_id * tag_id* 4 NULL15353 Using where; Using temporary; Using filesort 1 SIMPLE ad eq_ref PRIMARY,ad_id,is_removed,is_active,whitelisted,ant_feedback_alert *PRIMARY* 4 rubbt.tm.ad_id 1 I have bold the index names that are being used. Can you please clarify is that the one you are trying to find? Regards, Pradeep Chandru. Johan Thorvaldsson wrote: AD Create Table: CREATE TABLE `ad` ( `ad_id` int(11) NOT NULL auto_increment, `ad_key` varchar(32) collate utf8_swedish_ci NOT NULL default '', `pris` varchar(32) collate utf8_swedish_ci NOT NULL default '', `pris_original` varchar(32) collate utf8_swedish_ci NOT NULL default '', `dt_pub` datetime NOT NULL default '-00-00 00:00:00', `dt_updated` datetime NOT NULL default '-00-00 00:00:00', `ad_password` varchar(32) collate utf8_swedish_ci NOT NULL default '', `adtext` text collate utf8_swedish_ci NOT NULL, `unique_key` varchar(32) collate utf8_swedish_ci NOT NULL default '', `is_removed` int(11) NOT NULL default '0', `is_active` int(11) NOT NULL default '0', `num_images` int(11) default NULL, `sh_phone` int(11) default '0', `postalcode` varchar(32) collate utf8_swedish_ci default NULL, `ad_url` varchar(128) collate utf8_swedish_ci NOT NULL default '', `source_id` int(11) default '0', `youtube_link` text collate utf8_swedish_ci, `ad_sections` varchar(128) collate utf8_swedish_ci default NULL, `flickr_link` text collate utf8_swedish_ci, `ant_feedback_good` int(32) default '0', `ant_feedback_alert` int(32) default '0', `whitelisted` int(11) default '0', `adtext_plain` text collate utf8_swedish_ci NOT NULL, `dt_img_path` datetime NOT NULL default '-00-00 00:00:00', PRIMARY KEY (`ad_id`), KEY `sortkey` (`dt_pub`,`pris`), KEY `webbid` (`ad_key`), KEY `ad_id` (`ad_id`) ) ENGINE=MyISAM AUTO_INCREMENT=18721 DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci +---++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+-+---+-+--++--++-+ | ad| 0
Re: Fwd: Why dont my query use the index keys?
Hi Dilip, I dont think forcing MySQL to use Index can improve your performance. MySQL decides on the index to be used based on the cardinality. Since the cardinality is low MySQL has chosen a Index that can give the best performance. Forcing that can only increase the volume of data that is fetched. Regards, Pradeep chandru. Parikh, Dilip Kumar wrote: Hi johan, U can use and try to write a query to use index in it (like use index(index name) before where condition. And other variables also looks like ok seems let me check, Please try to send the output of global status. Show global status; Thanks Regards, Dilipkumar -Original Message- From: chandru [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 18, 2008 11:36 AM To: Johan Thorvaldsson Cc: mysql@lists.mysql.com Subject: Re: Fwd: Why dont my query use the index keys? Hi Johan, the query is going for a temporary table creation "Using temporary; Using filesort" In case your sort_buffer_size is too low please try increasing the same. Be cautious don't try to increase it massively, since it is a per thread memory allocation. please let me know what is your sort_buffer_size by using the command show global variables like 'sort%'; Regards, Pradeep Chandru. Johan Thorvaldsson wrote: So query and index:es are ok ? Because the query runs very slow. Vidarebefordrat brev: Frn: "Ananda Kumar" [EMAIL PROTECTED] Datum: ti 16 sep 2008 13.19.43 GMT+02:00 Till: "Johan Thorvaldsson" [EMAIL PROTECTED] Kopia: mysql@lists.mysql.com mne: Re: Why dont my query use the index keys? Hi Johan, Its doing a range based search, so its using index. regards anandkl On 9/16/08, Johan Thorvaldsson [EMAIL PROTECTED] wrote: But this one doesnt use any indexes? SIMPLE tm range is_active,ad_id,tag_id * tag_id* 4 NULL15353 Using where; Using temporary; Using filesort Thanks for your replies. Vidarebefordrat brev: Frn: chandru [EMAIL PROTECTED] Datum: ti 16 sep 2008 10.13.53 GMT+02:00 Till: Johan Thorvaldsson [EMAIL PROTECTED] Kopia: mysql@lists.mysql.com mne: Re: Why dont my query use the index keys? Hi johan, i find that the query is using a index already. Explain version: 1 SIMPLE tm range is_active,ad_id,tag_id * tag_id* 4 NULL15353 Using where; Using temporary; Using filesort 1 SIMPLE ad eq_ref PRIMARY,ad_id,is_removed,is_active,whitelisted,ant_feedback_alert *PRIMARY* 4 rubbt.tm.ad_id 1 I have bold the index names that are being used. Can you please clarify is that the one you are trying to find? Regards, Pradeep Chandru. Johan Thorvaldsson wrote: AD Create Table: CREATE TABLE `ad` ( `ad_id` int(11) NOT NULL auto_increment, `ad_key` varchar(32) collate utf8_swedish_ci NOT NULL default '', `pris` varchar(32) collate utf8_swedish_ci NOT NULL default '', `pris_original` varchar(32) collate utf8_swedish_ci NOT NULL default '', `dt_pub` datetime NOT NULL default '-00-00 00:00:00', `dt_updated` datetime NOT NULL default '-00-00 00:00:00', `ad_password` varchar(32) collate utf8_swedish_ci NOT NULL default '', `adtext` text collate utf8_swedish_ci NOT NULL, `unique_key` varchar(32) collate utf8_swedish_ci NOT NULL default '', `is_removed` int(11) NOT NULL default '0', `is_active` int(11) NOT NULL default '0', `num_images` int(11) default NULL, `sh_phone` int(11) default '0', `postalcode` varchar(32) collate utf8_swedish_ci default NULL, `ad_url` varchar(128) collate utf8_swedish_ci NOT NULL default '', `source_id` int(11) default '0', `youtube_link` text collate utf8_swedish_ci, `ad_sections` varchar(128) collate utf8_swedish_ci default NULL, `flickr_link` text collate utf8_swedish_ci, `ant_feedback_good` int(32) default '0', `ant_feedback_alert` int(32) default '0', `whitelisted` int(11) default '0', `adtext_plain` text collate utf8_swedish_ci NOT NULL, `dt_img_path` datetime NOT NULL default '-00-00 00:00:00', PRIMARY KEY (`ad_id`), KEY `sortkey` (`dt_pub`,`pris`), KEY `webbid` (`ad_key`), KEY `ad_id` (`ad_id`) ) ENGINE=MyISAM AUTO_INCREMENT=18721 DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci +---++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+-+---+-+--++--++-+ | ad| 0 | PRIMARY |1 | ad_id | A | 18691 | NULL | NULL | | BTREE | | | ad| 1 | sortkey |1 | dt_pub | A |3115 | NULL | NULL | | BTREE | | | ad| 1 | sortkey |2 | pris| A |3115
Re: Finding gaps
HI ! Stut schrieb: On 17 Sep 2008, at 22:12, Jerry Schwartz wrote: I have records that should be sequentially (not auto-increment) numbered, but there are gaps. Is there any elegant way of finding the gaps? Why do they need to be sequential? When this requirement comes up it's usually for illogical reasons. I don't know his application, but I do remember a similar requirement caused by some German rules on bookkeeping which demanded that booking numbers were assigned without gaps. That law may be illogical, but for the DB application designer this doesn't help - s/he has no choice but to follow it. Regards, Jörg -- Joerg Bruehe, MySQL Build Team, [EMAIL PROTECTED] Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding gaps
Hi ! Stut schrieb: On 17 Sep 2008, at 22:12, Jerry Schwartz wrote: I have records that should be sequentially (not auto-increment) numbered, but there are gaps. Is there any elegant way of finding the gaps? Why do they need to be sequential? When this requirement comes up it's usually for illogical reasons. I don't know his application, but I do remember a similar requirement caused by some German rules on bookkeeping which demanded that booking numbers were assigned without gaps. That law may be illogical, but for the DB application designer this doesn't help - s/he has no choice but to follow it. Regards, Jörg -- Joerg Bruehe, MySQL Build Team, [EMAIL PROTECTED] Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fwd: Why dont my query use the index keys?
Hi pradeep, What are you trying to say ?. The query clearly denotes that Index is not been used anywhere. So i strongly suggest to use the index explicitly for better performance. Forcing that can only increase the volume of data that is fetched. ? I cant understand how it increases the volume of data that is fetched. Can u please explain on this ??. In this case, index is no where present in the select query, so naturally we have to force the query to use the index. Thanks Regards, Dilipkumar From: chandru [mailto:[EMAIL PROTECTED] Sent: Thursday, September 18, 2008 12:23 PM To: Parikh, Dilip Kumar Cc: Johan Thorvaldsson; mysql@lists.mysql.com Subject: Re: Fwd: Why dont my query use the index keys? Hi Dilip, I dont think forcing MySQL to use Index can improve your performance. MySQL decides on the index to be used based on the cardinality. Since the cardinality is low MySQL has chosen a Index that can give the best performance. Forcing that can only increase the volume of data that is fetched. Regards, Pradeep chandru. Parikh, Dilip Kumar wrote: Hi johan, U can use and try to write a query to use index in it (like use index(index name) before where condition. And other variables also looks like ok seems let me check, Please try to send the output of global status. Show global status; Thanks Regards, Dilipkumar -Original Message- From: chandru [mailto:[EMAIL PROTECTED] Sent: Thursday, September 18, 2008 11:36 AM To: Johan Thorvaldsson Cc: mysql@lists.mysql.com Subject: Re: Fwd: Why dont my query use the index keys? Hi Johan, the query is going for a temporary table creation Using temporary; Using filesort In case your sort_buffer_size is too low please try increasing the same. Be cautious don't try to increase it massively, since it is a per thread memory allocation. please let me know what is your sort_buffer_size by using the command show global variables like 'sort%'; Regards, Pradeep Chandru. Johan Thorvaldsson wrote: So query and index:es are ok ? Because the query runs very slow. Vidarebefordrat brev: Från: Ananda Kumar [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Datum: ti 16 sep 2008 13.19.43 GMT+02:00 Till: Johan Thorvaldsson [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Kopia: mysql@lists.mysql.com Ämne: Re: Why dont my query use the index keys? Hi Johan, Its doing a range based search, so its using index. regards anandkl On 9/16/08, Johan Thorvaldsson [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: But this one doesnt use any indexes? SIMPLE tm range is_active,ad_id,tag_id * tag_id* 4 NULL15353 Using where; Using temporary; Using filesort Thanks for your replies. Vidarebefordrat brev: Från: chandru [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Datum: ti 16 sep 2008 10.13.53 GMT+02:00 Till: Johan Thorvaldsson [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Kopia: mysql@lists.mysql.com Ämne: Re: Why dont my query use the index keys? Hi johan, i find that the query is using a index already. Explain version: 1
Re: Fwd: Why dont my query use the index keys?
Pradeep, Can you be clear on your statement. Becoz i read in few links if the cardinality is low also mysql doen't use the index. It depends up on the where condition used.. Regards, Naga. On Thu, Sep 18, 2008 at 12:22 PM, chandru [EMAIL PROTECTED]wrote: Hi Dilip, I dont think forcing MySQL to use Index can improve your performance. MySQL decides on the index to be used based on the cardinality. Since the cardinality is low MySQL has chosen a Index that can give the best performance. Forcing that can only increase the volume of data that is fetched. Regards, Pradeep chandru. Parikh, Dilip Kumar wrote: Hi johan, U can use and try to write a query to use index in it (like use index(index name) before where condition. And other variables also looks like ok seems let me check, Please try to send the output of global status. Show global status; Thanks Regards, Dilipkumar -Original Message- From: chandru [mailto:[EMAIL PROTECTED] [EMAIL PROTECTED]] Sent: Thursday, September 18, 2008 11:36 AM To: Johan Thorvaldsson Cc: mysql@lists.mysql.com Subject: Re: Fwd: Why dont my query use the index keys? Hi Johan, the query is going for a temporary table creation Using temporary; Using filesort In case your sort_buffer_size is too low please try increasing the same. Be cautious don't try to increase it massively, since it is a per thread memory allocation. please let me know what is your sort_buffer_size by using the command show global variables like 'sort%'; Regards, Pradeep Chandru. Johan Thorvaldsson wrote: So query and index:es are ok ? Because the query runs very slow. Vidarebefordrat brev: Från: Ananda Kumar [EMAIL PROTECTED] [EMAIL PROTECTED] Datum: ti 16 sep 2008 13.19.43 GMT+02:00 Till: Johan Thorvaldsson [EMAIL PROTECTED] [EMAIL PROTECTED] Kopia: mysql@lists.mysql.com Ämne: Re: Why dont my query use the index keys? Hi Johan, Its doing a range based search, so its using index. regards anandkl On 9/16/08, Johan Thorvaldsson [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: But this one doesnt use any indexes? SIMPLE tm range is_active,ad_id,tag_id * tag_id* 4 NULL15353 Using where; Using temporary; Using filesort Thanks for your replies. Vidarebefordrat brev: Från: chandru [EMAIL PROTECTED] [EMAIL PROTECTED] Datum: ti 16 sep 2008 10.13.53 GMT+02:00 Till: Johan Thorvaldsson [EMAIL PROTECTED] [EMAIL PROTECTED] Kopia: mysql@lists.mysql.com Ämne: Re: Why dont my query use the index keys? Hi johan, i find that the query is using a index already. Explain version: 1 SIMPLE tm range is_active,ad_id,tag_id * tag_id* 4 NULL15353 Using where; Using temporary; Using filesort 1 SIMPLE ad eq_ref PRIMARY,ad_id,is_removed,is_active,whitelisted,ant_feedback_alert *PRIMARY* 4 rubbt.tm.ad_id 1 I have bold the index names that are being used. Can you please clarify is that the one you are trying to find? Regards, Pradeep Chandru. Johan Thorvaldsson wrote: AD Create Table: CREATE TABLE `ad` ( `ad_id` int(11) NOT NULL auto_increment, `ad_key` varchar(32) collate utf8_swedish_ci NOT NULL default '', `pris` varchar(32) collate utf8_swedish_ci NOT NULL default '', `pris_original` varchar(32) collate utf8_swedish_ci NOT NULL default '', `dt_pub` datetime NOT NULL default '-00-00 00:00:00', `dt_updated` datetime NOT NULL default '-00-00 00:00:00', `ad_password` varchar(32) collate utf8_swedish_ci NOT NULL default '', `adtext` text collate utf8_swedish_ci NOT NULL, `unique_key` varchar(32) collate utf8_swedish_ci NOT NULL default '', `is_removed` int(11) NOT NULL default '0', `is_active` int(11) NOT NULL default '0', `num_images` int(11) default NULL, `sh_phone` int(11) default '0', `postalcode` varchar(32) collate utf8_swedish_ci default NULL, `ad_url` varchar(128) collate utf8_swedish_ci NOT NULL default '', `source_id` int(11) default '0', `youtube_link` text collate utf8_swedish_ci, `ad_sections` varchar(128) collate utf8_swedish_ci default NULL, `flickr_link` text collate utf8_swedish_ci, `ant_feedback_good` int(32) default '0', `ant_feedback_alert` int(32) default '0', `whitelisted` int(11) default '0', `adtext_plain` text collate utf8_swedish_ci NOT NULL, `dt_img_path` datetime NOT NULL default '-00-00 00:00:00', PRIMARY KEY (`ad_id`), KEY `sortkey` (`dt_pub`,`pris`), KEY `webbid` (`ad_key`), KEY `ad_id` (`ad_id`) ) ENGINE=MyISAM AUTO_INCREMENT=18721 DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci +---++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
Re: Fwd: Why dont my query use the index keys?
Pradeep, Can you be clear on your statement. Becoz i read in few links if the cardinality is low also mysql doen't use the index. It depends up on the where condition used.. Regards, Naga. On Thu, Sep 18, 2008 at 12:22 PM, chandru [EMAIL PROTECTED]wrote: Hi Dilip, I dont think forcing MySQL to use Index can improve your performance. MySQL decides on the index to be used based on the cardinality. Since the cardinality is low MySQL has chosen a Index that can give the best performance. Forcing that can only increase the volume of data that is fetched. Regards, Pradeep chandru. Parikh, Dilip Kumar wrote: Hi johan, U can use and try to write a query to use index in it (like use index(index name) before where condition. And other variables also looks like ok seems let me check, Please try to send the output of global status. Show global status; Thanks Regards, Dilipkumar -Original Message- From: chandru [mailto:[EMAIL PROTECTED] [EMAIL PROTECTED]] Sent: Thursday, September 18, 2008 11:36 AM To: Johan Thorvaldsson Cc: mysql@lists.mysql.com Subject: Re: Fwd: Why dont my query use the index keys? Hi Johan, the query is going for a temporary table creation Using temporary; Using filesort In case your sort_buffer_size is too low please try increasing the same. Be cautious don't try to increase it massively, since it is a per thread memory allocation. please let me know what is your sort_buffer_size by using the command show global variables like 'sort%'; Regards, Pradeep Chandru. Johan Thorvaldsson wrote: So query and index:es are ok ? Because the query runs very slow. Vidarebefordrat brev: Från: Ananda Kumar [EMAIL PROTECTED] [EMAIL PROTECTED] Datum: ti 16 sep 2008 13.19.43 GMT+02:00 Till: Johan Thorvaldsson [EMAIL PROTECTED] [EMAIL PROTECTED] Kopia: mysql@lists.mysql.com Ämne: Re: Why dont my query use the index keys? Hi Johan, Its doing a range based search, so its using index. regards anandkl On 9/16/08, Johan Thorvaldsson [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: But this one doesnt use any indexes? SIMPLE tm range is_active,ad_id,tag_id * tag_id* 4 NULL15353 Using where; Using temporary; Using filesort Thanks for your replies. Vidarebefordrat brev: Från: chandru [EMAIL PROTECTED] [EMAIL PROTECTED] Datum: ti 16 sep 2008 10.13.53 GMT+02:00 Till: Johan Thorvaldsson [EMAIL PROTECTED] [EMAIL PROTECTED] Kopia: mysql@lists.mysql.com Ämne: Re: Why dont my query use the index keys? Hi johan, i find that the query is using a index already. Explain version: 1 SIMPLE tm range is_active,ad_id,tag_id * tag_id* 4 NULL15353 Using where; Using temporary; Using filesort 1 SIMPLE ad eq_ref PRIMARY,ad_id,is_removed,is_active,whitelisted,ant_feedback_alert *PRIMARY* 4 rubbt.tm.ad_id 1 I have bold the index names that are being used. Can you please clarify is that the one you are trying to find? Regards, Pradeep Chandru. Johan Thorvaldsson wrote: AD Create Table: CREATE TABLE `ad` ( `ad_id` int(11) NOT NULL auto_increment, `ad_key` varchar(32) collate utf8_swedish_ci NOT NULL default '', `pris` varchar(32) collate utf8_swedish_ci NOT NULL default '', `pris_original` varchar(32) collate utf8_swedish_ci NOT NULL default '', `dt_pub` datetime NOT NULL default '-00-00 00:00:00', `dt_updated` datetime NOT NULL default '-00-00 00:00:00', `ad_password` varchar(32) collate utf8_swedish_ci NOT NULL default '', `adtext` text collate utf8_swedish_ci NOT NULL, `unique_key` varchar(32) collate utf8_swedish_ci NOT NULL default '', `is_removed` int(11) NOT NULL default '0', `is_active` int(11) NOT NULL default '0', `num_images` int(11) default NULL, `sh_phone` int(11) default '0', `postalcode` varchar(32) collate utf8_swedish_ci default NULL, `ad_url` varchar(128) collate utf8_swedish_ci NOT NULL default '', `source_id` int(11) default '0', `youtube_link` text collate utf8_swedish_ci, `ad_sections` varchar(128) collate utf8_swedish_ci default NULL, `flickr_link` text collate utf8_swedish_ci, `ant_feedback_good` int(32) default '0', `ant_feedback_alert` int(32) default '0', `whitelisted` int(11) default '0', `adtext_plain` text collate utf8_swedish_ci NOT NULL, `dt_img_path` datetime NOT NULL default '-00-00 00:00:00', PRIMARY KEY (`ad_id`), KEY `sortkey` (`dt_pub`,`pris`), KEY `webbid` (`ad_key`), KEY `ad_id` (`ad_id`) ) ENGINE=MyISAM AUTO_INCREMENT=18721 DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci +---++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
Re: MySQL not running on fresh LAMP install
Part of the problem is that I can't stop the server. Oh, so it does start? I thought it also wasn't starting, if it's not starting then stopping it will fail. Is there anything else I can do to get a clean install of MySQL running again? Can you list what appears in the process list? A ps awux | grep -i mysql should do. One other thing... on debian in addition to my.cnf, there's a debain.cnf which also has the path to the socket in it and the start script uses that to pass the socket to mysql (in addition to my.cnf - which is a bit odd). I've never used ubuntu, but do you have something similar in /etc/mysql/ ? If that's not the case, just try setting the socket location back to where it was initially and then re-attempt to remove the package. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding gaps
On 18 Sep 2008, at 07:45, Joerg Bruehe wrote: Stut schrieb: On 17 Sep 2008, at 22:12, Jerry Schwartz wrote: I have records that should be sequentially (not auto-increment) numbered, but there are gaps. Is there any elegant way of finding the gaps? Why do they need to be sequential? When this requirement comes up it's usually for illogical reasons. I don't know his application, but I do remember a similar requirement caused by some German rules on bookkeeping which demanded that booking numbers were assigned without gaps. That law may be illogical, but for the DB application designer this doesn't help - s/he has no choice but to follow it. Autonumber will accomplish that, so long as you don't delete any. And if you do, renumbering the bookings would cause more problems than it solved. This reminds me of when my parents used to make me account for every cheque I'd written, and they did it by ensuring I had a reason for each sequentially numbered cheque in the cheque book. Any I had written and then destroyed had to be marked as destroyed - it couldn't actually be missing!! I see the same issue here and the same solution works. -Stut -- http://stut.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fwd: Why dont my query use the index keys?
Hi nagaraj, sorry.. Since the cardinality is low MySQL has chosen *not to use* a Index that can give the best performance. i missed that. Regards, Pradeep chandru. Nagaraj S wrote: Pradeep, Can you be clear on your statement. Becoz i read in few links if the cardinality is low also mysql doen't use the index. It depends up on the where condition used.. Regards, Naga. On Thu, Sep 18, 2008 at 12:22 PM, chandru [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Hi Dilip, I dont think forcing MySQL to use Index can improve your performance. MySQL decides on the index to be used based on the cardinality. Since the cardinality is low MySQL has chosen a Index that can give the best performance. Forcing that can only increase the volume of data that is fetched. Regards, Pradeep chandru. Parikh, Dilip Kumar wrote: Hi johan, U can use and try to write a query to use index in it (like use index(index name) before where condition. And other variables also looks like ok seems let me check, Please try to send the output of global status. Show global status; Thanks Regards, Dilipkumar -Original Message- From: chandru [mailto:[EMAIL PROTECTED] Sent: Thursday, September 18, 2008 11:36 AM To: Johan Thorvaldsson Cc: mysql@lists.mysql.com mailto:mysql@lists.mysql.com Subject: Re: Fwd: Why dont my query use the index keys? Hi Johan, the query is going for a temporary table creation Using temporary; Using filesort In case your sort_buffer_size is too low please try increasing the same. Be cautious don't try to increase it massively, since it is a per thread memory allocation. please let me know what is your sort_buffer_size by using the command show global variables like 'sort%'; Regards, Pradeep Chandru. Johan Thorvaldsson wrote: So query and index:es are ok ? Because the query runs very slow. Vidarebefordrat brev: Från: Ananda Kumar [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Datum: ti 16 sep 2008 13.19.43 GMT+02:00 Till: Johan Thorvaldsson [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Kopia: mysql@lists.mysql.com mailto:mysql@lists.mysql.com Ämne: Re: Why dont my query use the index keys? Hi Johan, Its doing a range based search, so its using index. regards anandkl On 9/16/08, Johan Thorvaldsson [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: But this one doesnt use any indexes? SIMPLE tm range is_active,ad_id,tag_id * tag_id* 4 NULL15353 Using where; Using temporary; Using filesort Thanks for your replies. Vidarebefordrat brev: Från: chandru [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Datum: ti 16 sep 2008 10.13.53 GMT+02:00 Till: Johan Thorvaldsson [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Kopia: mysql@lists.mysql.com mailto:mysql@lists.mysql.com Ämne: Re: Why dont my query use the index keys? Hi johan, i find that the query is using a index already. Explain version: 1 SIMPLE tm range is_active,ad_id,tag_id * tag_id* 4 NULL15353 Using where; Using temporary; Using filesort 1 SIMPLE ad eq_ref PRIMARY,ad_id,is_removed,is_active,whitelisted,ant_feedback_alert *PRIMARY* 4 rubbt.tm.ad_id 1 I have bold the index names that are being used. Can you please clarify is that the one you are trying to find? Regards, Pradeep Chandru. Johan Thorvaldsson wrote: AD Create Table: CREATE TABLE `ad` ( `ad_id` int(11) NOT NULL auto_increment, `ad_key` varchar(32) collate utf8_swedish_ci NOT NULL default '', `pris` varchar(32) collate utf8_swedish_ci NOT NULL default '', `pris_original` varchar(32) collate utf8_swedish_ci NOT NULL default '', `dt_pub` datetime NOT NULL default '-00-00 00:00:00', `dt_updated` datetime NOT NULL default '-00-00 00:00:00', `ad_password` varchar(32) collate utf8_swedish_ci NOT NULL default '', `adtext` text collate utf8_swedish_ci NOT NULL, `unique_key` varchar(32) collate utf8_swedish_ci NOT NULL default '', `is_removed` int(11) NOT NULL default '0', `is_active` int(11) NOT NULL default '0', `num_images` int(11) default NULL, `sh_phone` int(11) default '0', `postalcode` varchar(32) collate utf8_swedish_ci default NULL, `ad_url` varchar(128) collate utf8_swedish_ci NOT NULL default '', `source_id` int(11) default '0', `youtube_link` text collate utf8_swedish_ci, `ad_sections` varchar(128) collate utf8_swedish_ci default NULL, `flickr_link` text collate utf8_swedish_ci, `ant_feedback_good` int(32) default '0', `ant_feedback_alert` int(32) default '0', `whitelisted` int(11) default '0', `adtext_plain` text collate utf8_swedish_ci NOT NULL, `dt_img_path`
Re: Fwd: Why dont my query use the index keys?
Hi Dilip, MySQL algorithm decides based on cardinality. Cardinality means the value of unique entries in the table. Hence when MySQL finds that there are very less unique values, mysql does not use that index. If the table is optimized then value of the cardinality will stay updated. Scanning the complete table can be faster than a index lookup that is going to search more than 30 % the table size. What i meant by increase data fetch is the time of data fetch. Regards, Pradeep Chandru Parikh, Dilip Kumar wrote: Hi pradeep, What are you trying to say ?. The query clearly denotes that Index is not been used anywhere. So i strongly suggest to use the index explicitly for better performance. Forcing that can only increase the volume of data that is fetched. ? I cant understand how it increases the volume of data that is fetched. Can u please explain on this ??. In this case, index is no where present in the select query, so naturally we have to force the query to use the index. *Thanks Regards,* *Dilipkumar* *From:* chandru [mailto:[EMAIL PROTECTED] *Sent:* Thursday, September 18, 2008 12:23 PM *To:* Parikh, Dilip Kumar *Cc:* Johan Thorvaldsson; mysql@lists.mysql.com *Subject:* Re: Fwd: Why dont my query use the index keys? Hi Dilip, I dont think forcing MySQL to use Index can improve your performance. MySQL decides on the index to be used based on the cardinality. Since the cardinality is low MySQL has chosen a Index that can give the best performance. Forcing that can only increase the volume of data that is fetched. Regards, Pradeep chandru. Parikh, Dilip Kumar wrote: Hi johan, U can use and try to write a query to use index in it (like use index(index name) before where condition. And other variables also looks like ok seems let me check, Please try to send the output of global status. Show global status; Thanks Regards, Dilipkumar -Original Message- From: chandru [mailto:[EMAIL PROTECTED] Sent: Thursday, September 18, 2008 11:36 AM To: Johan Thorvaldsson Cc: mysql@lists.mysql.com Subject: Re: Fwd: Why dont my query use the index keys? Hi Johan, the query is going for a temporary table creation Using temporary; Using filesort In case your sort_buffer_size is too low please try increasing the same. Be cautious don't try to increase it massively, since it is a per thread memory allocation. please let me know what is your sort_buffer_size by using the command show global variables like 'sort%'; Regards, Pradeep Chandru. Johan Thorvaldsson wrote: So query and index:es are ok ? Because the query runs very slow. Vidarebefordrat brev: Från: Ananda Kumar [EMAIL PROTECTED] Datum: ti 16 sep 2008 13.19.43 GMT+02:00 Till: Johan Thorvaldsson [EMAIL PROTECTED] Kopia: mysql@lists.mysql.com Ämne: Re: Why dont my query use the index keys? Hi Johan, Its doing a range based search, so its using index. regards anandkl On 9/16/08, Johan Thorvaldsson [EMAIL PROTECTED] wrote: But this one doesnt use any indexes? SIMPLE tm range is_active,ad_id,tag_id * tag_id* 4 NULL15353 Using where; Using temporary; Using filesort Thanks for your replies. Vidarebefordrat brev: Från: chandru [EMAIL PROTECTED] Datum: ti 16 sep 2008 10.13.53 GMT+02:00 Till: Johan Thorvaldsson [EMAIL PROTECTED] Kopia: mysql@lists.mysql.com Ämne: Re: Why dont my query use the index keys? Hi johan, i find that the query is using a index already. Explain version: 1 SIMPLE tm range is_active,ad_id,tag_id * tag_id* 4 NULL15353 Using where; Using temporary; Using filesort 1 SIMPLE ad eq_ref PRIMARY,ad_id,is_removed,is_active,whitelisted,ant_feedback_alert *PRIMARY* 4 rubbt.tm.ad_id 1 I have bold the index names that are being used. Can you please clarify is that the one you are trying to find? Regards, Pradeep Chandru. Johan Thorvaldsson wrote: AD Create Table: CREATE TABLE `ad` ( `ad_id` int(11) NOT NULL auto_increment, `ad_key` varchar(32) collate utf8_swedish_ci NOT NULL default '', `pris` varchar(32) collate utf8_swedish_ci NOT NULL default '', `pris_original` varchar(32) collate utf8_swedish_ci NOT NULL default '', `dt_pub` datetime NOT NULL default '-00-00 00:00:00', `dt_updated` datetime NOT NULL default '-00-00 00:00:00', `ad_password` varchar(32) collate utf8_swedish_ci NOT NULL default '', `adtext` text collate utf8_swedish_ci NOT NULL, `unique_key` varchar(32) collate utf8_swedish_ci NOT NULL default '', `is_removed` int(11) NOT NULL
Re: Fwd: Why dont my query use the index keys?
Then do u mean if there is low cardinality index will not be used?. Regards, Naga On Thu, Sep 18, 2008 at 2:41 PM, chandru [EMAIL PROTECTED]wrote: Hi nagaraj, sorry.. Since the cardinality is low MySQL has chosen *not to use* a Index that can give the best performance. i missed that. Regards, Pradeep chandru. Nagaraj S wrote: Pradeep, Can you be clear on your statement. Becoz i read in few links if the cardinality is low also mysql doen't use the index. It depends up on the where condition used.. Regards, Naga. On Thu, Sep 18, 2008 at 12:22 PM, chandru [EMAIL PROTECTED]mailto: [EMAIL PROTECTED] wrote: Hi Dilip, I dont think forcing MySQL to use Index can improve your performance. MySQL decides on the index to be used based on the cardinality. Since the cardinality is low MySQL has chosen a Index that can give the best performance. Forcing that can only increase the volume of data that is fetched. Regards, Pradeep chandru. Parikh, Dilip Kumar wrote: Hi johan, U can use and try to write a query to use index in it (like use index(index name) before where condition. And other variables also looks like ok seems let me check, Please try to send the output of global status. Show global status; Thanks Regards, Dilipkumar -Original Message- From: chandru [mailto:[EMAIL PROTECTED]Sent: Thursday, September 18, 2008 11:36 AM To: Johan Thorvaldsson Cc: mysql@lists.mysql.com mailto:mysql@lists.mysql.com Subject: Re: Fwd: Why dont my query use the index keys? Hi Johan, the query is going for a temporary table creation Using temporary; Using filesort In case your sort_buffer_size is too low please try increasing the same.Be cautious don't try to increase it massively, since it is a per thread memory allocation. please let me know what is your sort_buffer_size by using the command show global variables like 'sort%'; Regards, Pradeep Chandru. Johan Thorvaldsson wrote: So query and index:es are ok ? Because the query runs very slow. Vidarebefordrat brev: Från: Ananda Kumar [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Datum: ti 16 sep 2008 13.19.43 GMT+02:00 Till: Johan Thorvaldsson [EMAIL PROTECTED] mailto: [EMAIL PROTECTED] Kopia: mysql@lists.mysql.com mailto:mysql@lists.mysql.com Ämne: Re: Why dont my query use the index keys? Hi Johan, Its doing a range based search, so its using index. regards anandkl On 9/16/08, Johan Thorvaldsson [EMAIL PROTECTED] mailto: [EMAIL PROTECTED] wrote: But this one doesnt use any indexes? SIMPLE tm range is_active,ad_id,tag_id * tag_id* 4 NULL15353 Using where; Using temporary; Using filesort Thanks for your replies. Vidarebefordrat brev: Från: chandru [EMAIL PROTECTED] mailto: [EMAIL PROTECTED] Datum: ti 16 sep 2008 10.13.53 GMT+02:00 Till: Johan Thorvaldsson [EMAIL PROTECTED] mailto: [EMAIL PROTECTED] Kopia: mysql@lists.mysql.com mailto:mysql@lists.mysql.com Ämne: Re: Why dont my query use the index keys? Hi johan, i find that the query is using a index already. Explain version: 1 SIMPLE tm range is_active,ad_id,tag_id * tag_id* 4 NULL15353 Using where; Using temporary; Using filesort 1 SIMPLE ad eq_ref PRIMARY,ad_id,is_removed,is_active,whitelisted,ant_feedback_alert *PRIMARY* 4 rubbt.tm.ad_id 1 I have bold the index names that are being used. Can you please clarify is that the one you are trying to find? Regards, Pradeep Chandru. Johan Thorvaldsson wrote: AD Create Table: CREATE TABLE `ad` ( `ad_id` int(11) NOT NULL auto_increment, `ad_key` varchar(32) collate utf8_swedish_ci NOT NULL default '', `pris` varchar(32) collate utf8_swedish_ci NOT NULL default '', `pris_original`varchar(32) collate utf8_swedish_ci NOT NULL default '', `dt_pub`datetime NOT NULL default '-00-00 00:00:00', `dt_updated`datetime NOT NULL default '-00-00 00:00:00', `ad_password`varchar(32) collate utf8_swedish_ci NOT NULL default '', `adtext`text collate utf8_swedish_ci NOT NULL, `unique_key` varchar(32)collate utf8_swedish_ci NOT NULL default '', `is_removed` int(11)NOT NULL default '0', `is_active` int(11) NOT NULL default '0', `num_images` int(11) default NULL, `sh_phone` int(11) default '0', `postalcode` varchar(32) collate utf8_swedish_ci default NULL, `ad_url` varchar(128) collate utf8_swedish_ci NOT NULL default '', `source_id` int(11) default '0', `youtube_link` text collate utf8_swedish_ci, `ad_sections` varchar(128) collate utf8_swedish_ci default NULL, `flickr_link` text collate utf8_swedish_ci, `ant_feedback_good` int(32) default '0', `ant_feedback_alert`int(32) default '0', `whitelisted`
RE: Fwd: Why dont my query use the index keys?
Hi , So you are trying to say that 1) when the Table has Low Cardinality, Mysql wont use Index? Is this the logic behind your words? And also do you mean that the select query without index will be faster than that of the select query with Index? I just don't believe it. Then what is the purpose of Index?? Please clarify... Thanks Regards, Dilipkumar -Original Message- From: chandru [mailto:[EMAIL PROTECTED] Sent: Thursday, September 18, 2008 2:59 PM To: Parikh, Dilip Kumar Cc: Johan Thorvaldsson; mysql@lists.mysql.com Subject: Re: Fwd: Why dont my query use the index keys? Hi Dilip, MySQL algorithm decides based on cardinality. Cardinality means the value of unique entries in the table. Hence when MySQL finds that there are very less unique values, mysql does not use that index. If the table is optimized then value of the cardinality will stay updated. Scanning the complete table can be faster than a index lookup that is going to search more than 30 % the table size. What i meant by increase data fetch is the time of data fetch. Regards, Pradeep Chandru Parikh, Dilip Kumar wrote: Hi pradeep, What are you trying to say ?. The query clearly denotes that Index is not been used anywhere. So i strongly suggest to use the index explicitly for better performance. Forcing that can only increase the volume of data that is fetched. ? I cant understand how it increases the volume of data that is fetched. Can u please explain on this ??. In this case, index is no where present in the select query, so naturally we have to force the query to use the index. *Thanks Regards,* *Dilipkumar* *From:* chandru [mailto:[EMAIL PROTECTED] *Sent:* Thursday, September 18, 2008 12:23 PM *To:* Parikh, Dilip Kumar *Cc:* Johan Thorvaldsson; mysql@lists.mysql.com *Subject:* Re: Fwd: Why dont my query use the index keys? Hi Dilip, I dont think forcing MySQL to use Index can improve your performance. MySQL decides on the index to be used based on the cardinality. Since the cardinality is low MySQL has chosen a Index that can give the best performance. Forcing that can only increase the volume of data that is fetched. Regards, Pradeep chandru. Parikh, Dilip Kumar wrote: Hi johan, U can use and try to write a query to use index in it (like use index(index name) before where condition. And other variables also looks like ok seems let me check, Please try to send the output of global status. Show global status; Thanks Regards, Dilipkumar -Original Message- From: chandru [mailto:[EMAIL PROTECTED] Sent: Thursday, September 18, 2008 11:36 AM To: Johan Thorvaldsson Cc: mysql@lists.mysql.com Subject: Re: Fwd: Why dont my query use the index keys? Hi Johan, the query is going for a temporary table creation Using temporary; Using filesort In case your sort_buffer_size is too low please try increasing the same. Be cautious don't try to increase it massively, since it is a per thread memory allocation. please let me know what is your sort_buffer_size by using the command show global variables like 'sort%'; Regards, Pradeep Chandru. Johan Thorvaldsson wrote: So query and index:es are ok ? Because the query runs very slow. Vidarebefordrat brev: Från: Ananda Kumar [EMAIL PROTECTED] Datum: ti 16 sep 2008 13.19.43 GMT+02:00 Till: Johan Thorvaldsson [EMAIL PROTECTED] Kopia: mysql@lists.mysql.com Ämne: Re: Why dont my query use the index keys? Hi Johan, Its doing a range based search, so its using index. regards anandkl On 9/16/08, Johan Thorvaldsson [EMAIL PROTECTED] wrote: But this one doesnt use any indexes? SIMPLE tm range is_active,ad_id,tag_id * tag_id* 4 NULL15353 Using where; Using temporary; Using filesort Thanks for your replies. Vidarebefordrat brev: Från: chandru [EMAIL PROTECTED] Datum: ti 16 sep 2008 10.13.53 GMT+02:00 Till: Johan Thorvaldsson [EMAIL PROTECTED] Kopia: mysql@lists.mysql.com Ämne: Re: Why dont my query use the index keys? Hi johan, i find that the query is using a index already. Explain version: 1 SIMPLE tm range is_active,ad_id,tag_id * tag_id* 4 NULL15353 Using where; Using temporary; Using filesort 1 SIMPLE ad eq_ref PRIMARY,ad_id,is_removed,is_active,whitelisted,ant_feedback_alert *PRIMARY* 4 rubbt.tm.ad_id 1 I have bold the index names that are being used. Can you please clarify is that the one you are trying to find? Regards, Pradeep Chandru. Johan Thorvaldsson
Re: Fwd: Why dont my query use the index keys?
Hi nagaraj, yes by default, but if you know that the cardinality value that mysql has calculated is very old ( since you have not optimized it) then forcing a index can improve your performance as Dilip has mentioned in the before mail. If you are not sure always better to let mysql decide on the index to be used. Regards Pradeep chandru. Nagaraj S wrote: Then do u mean if there is low cardinality index will not be used?. Regards, Naga On Thu, Sep 18, 2008 at 2:41 PM, chandru [EMAIL PROTECTED]wrote: Hi nagaraj, sorry.. Since the cardinality is low MySQL has chosen *not to use* a Index that can give the best performance. i missed that. Regards, Pradeep chandru. Nagaraj S wrote: Pradeep, Can you be clear on your statement. Becoz i read in few links if the cardinality is low also mysql doen't use the index. It depends up on the where condition used.. Regards, Naga. On Thu, Sep 18, 2008 at 12:22 PM, chandru [EMAIL PROTECTED]mailto: [EMAIL PROTECTED] wrote: Hi Dilip, I dont think forcing MySQL to use Index can improve your performance. MySQL decides on the index to be used based on the cardinality. Since the cardinality is low MySQL has chosen a Index that can give the best performance. Forcing that can only increase the volume of data that is fetched. Regards, Pradeep chandru. Parikh, Dilip Kumar wrote: Hi johan, U can use and try to write a query to use index in it (like use index(index name) before where condition. And other variables also looks like ok seems let me check, Please try to send the output of global status. Show global status; Thanks Regards, Dilipkumar -Original Message- From: chandru [mailto:[EMAIL PROTECTED]Sent: Thursday, September 18, 2008 11:36 AM To: Johan Thorvaldsson Cc: mysql@lists.mysql.com mailto:mysql@lists.mysql.com Subject: Re: Fwd: Why dont my query use the index keys? Hi Johan, the query is going for a temporary table creation Using temporary; Using filesort In case your sort_buffer_size is too low please try increasing the same.Be cautious don't try to increase it massively, since it is a per thread memory allocation. please let me know what is your sort_buffer_size by using the command show global variables like 'sort%'; Regards, Pradeep Chandru. Johan Thorvaldsson wrote: So query and index:es are ok ? Because the query runs very slow. Vidarebefordrat brev: Från: Ananda Kumar [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Datum: ti 16 sep 2008 13.19.43 GMT+02:00 Till: Johan Thorvaldsson [EMAIL PROTECTED] mailto: [EMAIL PROTECTED] Kopia: mysql@lists.mysql.com mailto:mysql@lists.mysql.com Ämne: Re: Why dont my query use the index keys? Hi Johan, Its doing a range based search, so its using index. regards anandkl On 9/16/08, Johan Thorvaldsson [EMAIL PROTECTED] mailto: [EMAIL PROTECTED] wrote: But this one doesnt use any indexes? SIMPLE tm range is_active,ad_id,tag_id * tag_id* 4 NULL15353 Using where; Using temporary; Using filesort Thanks for your replies. Vidarebefordrat brev: Från: chandru [EMAIL PROTECTED] mailto: [EMAIL PROTECTED] Datum: ti 16 sep 2008 10.13.53 GMT+02:00 Till: Johan Thorvaldsson [EMAIL PROTECTED] mailto: [EMAIL PROTECTED] Kopia: mysql@lists.mysql.com mailto:mysql@lists.mysql.com Ämne: Re: Why dont my query use the index keys? Hi johan, i find that the query is using a index already. Explain version: 1 SIMPLE tm range is_active,ad_id,tag_id * tag_id* 4 NULL15353 Using where; Using temporary; Using filesort 1 SIMPLE ad eq_ref PRIMARY,ad_id,is_removed,is_active,whitelisted,ant_feedback_alert *PRIMARY* 4 rubbt.tm.ad_id 1 I have bold the index names that are being used. Can you please clarify is that the one you are trying to find? Regards, Pradeep Chandru. Johan Thorvaldsson wrote: AD Create Table: CREATE TABLE `ad` ( `ad_id` int(11) NOT NULL auto_increment, `ad_key` varchar(32) collate utf8_swedish_ci NOT NULL default '', `pris` varchar(32)collate utf8_swedish_ci NOT NULL default '', `pris_original`varchar(32) collate utf8_swedish_ci NOT NULL default '', `dt_pub`datetime NOT NULL default '-00-00 00:00:00', `dt_updated`datetime NOT NULL default '-00-00 00:00:00', `ad_password`varchar(32) collate utf8_swedish_ci NOT NULL default '', `adtext`text collate utf8_swedish_ci NOT NULL, `unique_key` varchar(32)collate utf8_swedish_ci NOT NULL default '', `is_removed` int(11)NOT NULL default '0', `is_active` int(11) NOT NULL default '0', `num_images` int(11) default NULL, `sh_phone` int(11) default '0', `postalcode` varchar(32) collate utf8_swedish_ci default NULL, `ad_url` varchar(128) collate utf8_swedish_ci NOT NULL default '',
Re: MySQL not running on fresh LAMP install
On Thu, Sep 18, 2008 at 1:33 PM, Glyn Astill [EMAIL PROTECTED] wrote: Part of the problem is that I can't stop the server. Oh, so it does start? I thought it also wasn't starting, if it's not starting then stopping it will fail. Can you check the following : In my system, which is CentOS , I have [EMAIL PROTECTED] [~]# ls /usr/share/mysql/my-* /usr/share/mysql/my-huge.cnf* /usr/share/mysql/my-innodb-heavy-4G.cnf* /usr/share/mysql/my-large.cnf* /usr/share/mysql/my-medium.cnf* /usr/share/mysql/my-small.cnf* [EMAIL PROTECTED] [~]# your path may depend on installation. Copy any of the cnf file to /etc/my.cnf i.e. mv /etc/my.cnf /etc/my.cnf.bak cp /usr/share/mysql/my-medium.cnf /etc/my.cnf Then try to restart mysql /etc/init.d/mysql restart Also notice permissions/ownerships on the following folder [EMAIL PROTECTED] [~]# ls -lhd /var/lib/mysql/ drwxr-x--x 35 mysql mysql 4.0K Sep 18 04:11 /var/lib/mysql// [EMAIL PROTECTED] [~]# ls -lhd /var/lib/mysql/mysql drwx--x--x 2 mysql mysql 4.0K Sep 11 18:12 /var/lib/mysql/mysql/ [EMAIL PROTECTED] [~]# If problem persist, throw some light on how exactly you installed mysql (version as well as OS and path of mysql install )
Book: MySQL, Fourth Edition, now available
The book MySQL, Fourth Edition (aka Doorstop IV) has been published. More information is available at the book's Web site: http://www.kitebird.com/mysql-book/ -- Paul DuBois Sun Microsystems / 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/[EMAIL PROTECTED]
Re: Finding gaps
On Thu, 2008-09-18 at 09:58 +0100, Stut wrote: Autonumber will accomplish that, so long as you don't delete any. And if you do, renumbering the bookings would cause more problems than it solved. Autonumber has the possibility of gaps. When a record is insert, the counter is incremented. It is possible that a computer crash will stop the transaction after the increment but before the record is stored. Whatever record is inserted next will result in a gap. Either you have uniqueness and the possibility of gaps or no gaps and the possibility of duplicates. This is true even if the system is completely manual. That's just the way the universe is made. And yes, if you re-number, you introduce the possibility of duplicates. -- Just my 0.0002 million dollars worth, Shawn Where there's duct tape, there's hope. Cross Time Cafe Perl is the duct tape of the Internet. Hassan Schroeder, Sun's first webmaster There is more than one way to do things. A Perl axiom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fwd: Why dont my query use the index keys?
Hi ! Parikh, Dilip Kumar schrieb: Hi , So you are trying to say that 1) when the Table has Low Cardinality, Mysql wont use Index? Is this the logic behind your words? Extreme example: If you are manually looking up one entry from a list of five (say, in a cookbook), would you go through the index or just scan sequentially ? And also do you mean that the select query without index will be faster than that of the select query with Index? I just don't believe it. Then what is the purpose of Index?? Please clarify... Another extreme example: If you were to find a list all male soldiers in a typical army, would you go through an index on sex or just scan the payroll list, skipping the female ones ? For both cases, the logic is: Going through an index causes some overhead over a sequential scan (access the index, for each match follow the pointer to the real data) which you want (the system) to take only if that overhead is less than the overhead of scanning the base data and skipping the non-matches. Typically, both the index and the base data might be arranged sequentially, so scanning to the next entry is cheap, but following a reference from the index to some base record is a random access which is costly. So using the index is efficient only if the cost of (find matches in index) + ((hit rate) * (random data access)) is less than that of (sequential data scan). I have seen a 15 % hit rate used as a rule of thumb: If that optimizer expected a hit rate of more than 15 % (better: a selectivity worse than 15 %), it did not use the index at all but scan the base table. The reasoning was that sequentially scanning 6 - 7 entries (possibly using some read-ahead, disk caches etc) costs less than accessing one data record randomly. Regards, Jörg -- Joerg Bruehe, MySQL Build Team, [EMAIL PROTECTED] Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL not running on fresh LAMP install
MySQL List, Thanks for your advice and help. I tried various things suggested, and in the end, this is what seems to have worked (I've abbreviated most of the output): $ sudo pkill -9 mysqld $ sudo dpkg --force-all -r mysql-server-5.0 This removed MySQL, but left the configuration files. I went into /etc/mysql and edited both my.cnf and debian.cnf so that the socket = /tmp/mysql.sock Then I reinstalled MySQL server: $ sudo apt-get install mysql-server mysql-server-5.0 Seems to be running now. Thanks everyone for helping me get things sorted out. -- Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Finding gaps
-Original Message- From: Stut [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 17, 2008 6:30 PM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: Re: Finding gaps On 17 Sep 2008, at 22:34, Jerry Schwartz wrote: Our Japanese partners will notice and will ask. Similar things have come up before. I want to be pro-active. Notice what? Why would it be bad? What type of data are we dealing with here? [JS] In this case, we are dealing with a list of products. If each row requires a unique ID use an autonumber. If your partners don't understand that deleted items will create gaps, explain it to them. IMHO you're creating a problem that doesn't exist. [JS] I can pass along my boss's email address, if you want to explain to him why it doesn't matter. Personally, I depend upon my job. If you just need sequential numbers for display purposes, generate them when you do the displaying. There's no need for those numbers to be in the database. [JS] They are propagated into other databases that I do not control. They are managed and used by our main office in Japan. They notice everything (except misspellings). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Finding gaps
Yes, that would have been a very good idea. I did not design this. Even if we used auto-increment, my current problem would be the same: finding gaps in the numbering. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com From: Martin Gainty [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 17, 2008 6:29 PM To: Stut; Jerry Schwartz Cc: mysql@lists.mysql.com Subject: RE: Finding gaps unless you cant spare a few milliseconds off of each insert.. i strongly suggest to use autoincrement http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. CC: mysql@lists.mysql.com From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: Finding gaps Date: Wed, 17 Sep 2008 22:16:52 +0100 On 17 Sep 2008, at 22:12, Jerry Schwartz wrote: I have records that should be sequentially (not auto-increment) numbered, but there are gaps. Is there any elegant way of finding the gaps? Why do they need to be sequential? When this requirement comes up it's usually for illogical reasons. -Stut -- http://stut.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ See how Windows Mobile brings your life together-at home, work, or on the go. See Now http://clk.atdmt.com/MRT/go/msnnkwxp1020093182mrt/direct/01/
Re: Finding gaps
Hi all, I'm just throwing something out ... How about: select a.id,b.id from dataset a left join dataset b on a.id=b.id+1 where b.id is null; This should find single gaps. It won't find larger gaps. Just my $.02. Mike. On Thursday 18 September 2008 10:44:47 am Jerry Schwartz wrote: -Original Message- From: Stut [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 17, 2008 6:30 PM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: Re: Finding gaps On 17 Sep 2008, at 22:34, Jerry Schwartz wrote: Our Japanese partners will notice and will ask. Similar things have come up before. I want to be pro-active. Notice what? Why would it be bad? What type of data are we dealing with here? [JS] In this case, we are dealing with a list of products. If each row requires a unique ID use an autonumber. If your partners don't understand that deleted items will create gaps, explain it to them. IMHO you're creating a problem that doesn't exist. [JS] I can pass along my boss's email address, if you want to explain to him why it doesn't matter. Personally, I depend upon my job. If you just need sequential numbers for display purposes, generate them when you do the displaying. There's no need for those numbers to be in the database. [JS] They are propagated into other databases that I do not control. They are managed and used by our main office in Japan. They notice everything (except misspellings). -- Mike Diehl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Finding gaps
Alas, the gaps are as large as 500. Normally, products are never deleted from the system; but I put in some corrupt data that I did not want to pass along, even if I marked them as discontinued. They complain about that, too. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Mike Diehl [mailto:[EMAIL PROTECTED] Sent: Thursday, September 18, 2008 1:17 PM To: mysql@lists.mysql.com Cc: Jerry Schwartz; 'Stut' Subject: Re: Finding gaps Hi all, I'm just throwing something out ... How about: select a.id,b.id from dataset a left join dataset b on a.id=b.id+1 where b.id is null; This should find single gaps. It won't find larger gaps. Just my $.02. Mike. On Thursday 18 September 2008 10:44:47 am Jerry Schwartz wrote: -Original Message- From: Stut [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 17, 2008 6:30 PM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: Re: Finding gaps On 17 Sep 2008, at 22:34, Jerry Schwartz wrote: Our Japanese partners will notice and will ask. Similar things have come up before. I want to be pro-active. Notice what? Why would it be bad? What type of data are we dealing with here? [JS] In this case, we are dealing with a list of products. If each row requires a unique ID use an autonumber. If your partners don't understand that deleted items will create gaps, explain it to them. IMHO you're creating a problem that doesn't exist. [JS] I can pass along my boss's email address, if you want to explain to him why it doesn't matter. Personally, I depend upon my job. If you just need sequential numbers for display purposes, generate them when you do the displaying. There's no need for those numbers to be in the database. [JS] They are propagated into other databases that I do not control. They are managed and used by our main office in Japan. They notice everything (except misspellings). -- Mike Diehl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb log files but we only use MYISAM
I see the following log files -rw-rw 1 mysql mysql 10485760 Sep 16 17:30 ibdata1 -rw-rw 1 mysql mysql5242880 Sep 16 17:30 ib_logfile0 -rw-rw 1 mysql mysql5242880 Jan 17 2006 ib_logfile1 I checked every table on all databases. All are using MYISAM. innodb section in my.cnf is commented out. Why does this log file exist when we're not using innodb tables or dbs? -- Regards, Martin Corona
Re: innodb log files but we only use MYISAM
In the last episode (Sep 18), AM Corona said: I see the following log files -rw-rw 1 mysql mysql 10485760 Sep 16 17:30 ibdata1 -rw-rw 1 mysql mysql5242880 Sep 16 17:30 ib_logfile0 -rw-rw 1 mysql mysql5242880 Jan 17 2006 ib_logfile1 I checked every table on all databases. All are using MYISAM. innodb section in my.cnf is commented out. The innodb engine defaults to being enabled, so unless you have skip-innodb in your my.cnf, the engine itself will start and generate those three files, even if you don't have any InnoDB tables. Another set of files starting with falcon_* and maria_* will appear when you start mysql 6.0, since those engines create tablespace/log files too. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
