Re: Fwd: Why dont my query use the index keys?

[chandru]

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 NULL 15353 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 NULL 15353 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 '0000-00-00 00:00:00', `dt_updated` datetime NOT NULL default '0000-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 '0000-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 +------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | tag_ad_map | 0 | PRIMARY | 1 | tag_id | A | 35921 | NULL | NULL | | BTREE | | | tag_ad_map | 0 | PRIMARY | 2 | ad_id | A | 215531 | NULL | NULL | | BTREE | | | tag_ad_map | 1 | tag_id | 1 | tag_id | A | 35921 | NULL | NULL | | BTREE | | | tag_ad_map | 1 | ad_id | 1 | ad_id | A | 17960 | NULL | NULL | | BTREE | | +------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 16 sep 2008 kl. 09.01 skrev Ananda Kumar: can u please do show index for ad; show index for tag_ad_map; and past the output. I am not able to understand the index setup on these two tables. regards anandkl On 9/15/08, Johan Thorvaldsson <[EMAIL PROTECTED]> wrote: My query dont use the indexes for the 2 tables that I have joined. Is there someone nice and helpful that could help me optimize this query and make it use the indexes? Query: SELECT SQL_NO_CACHE COUNT(*) antal, ad.ad_url, ad.adtext_plain FROM ad LEFT JOIN tag_ad_map tm ON 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; Explain version: 1 SIMPLE tm range is_active,ad_id,tag_id tag_id 4 NULL 15353 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 Using where Indexes: ad 0 PRIMARY 1 ad_id A 18679 NULL NULL BTREE ad 1 is_removed 1 is_removed A 1 NULL NULL BTREE ad 1 is_active 1 is_active A 1 NULL NULL BTREE ad 1 source_id 1 source_id A 3 NULL NULL YES BTREE ad 1 whitelisted 1 whitelisted A 1 NULL NULL YES BTREE ad 1 ant_feedback_alert 1 ant_feedback_alert A 1 NULL NULL YES BTREE ad 1 dt_pub 1 dt_pub A 3113 NULL NULL BTREE ad 1 dt_updated 1 dt_updated A 3113 NULL NULL BTREE ad 1 ad_url 1 ad_url A 18679 NULL NULL BTREE ad 1 adtext_plain 1 adtext_plain NULL 1 NULL NULL FULLTEXT --------------------------------------------------------------------- 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 --------------------------------------------------------------------- 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] --------------------------------------------------------------------- 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 --------------------------------------------------------------------- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]