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
