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" <anandkl@stripped>
>> Datum: ti 16 sep 2008 13.19.43 GMT+02:00
>> Till: "Johan Thorvaldsson" <johan.t@stripped>
>> Kopia: mysql@stripped
>> Ä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 <johan.t@stripped> 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 <pradeep.chandru@stripped>
>> Datum: ti 16 sep 2008 10.13.53 GMT+02:00
>> Till: Johan Thorvaldsson <johan.t@stripped>
>> Kopia: mysql@stripped
>> Ä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 <johan.t@stripped> 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
>> johan.t@stripped
>>
>> 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
>> johan.t@stripped
>>
>> 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 admin@stripped
>>
>>
>>
>> ---------------------------------------------------------------------
>> Montania System AB
>> Halmstad
>> http://www.montania.se
>>
>> Johan Thorvaldsson
>> johan.t@stripped
>>
>> 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
> johan.t@stripped
>
> Kristinebergsvägen 17, S-30241 HALMSTAD
> Telefon +46(0)35-13 68 00 | Fax +46(0)35-13 68 01
>
>
