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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
