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
