Från: "Ananda Kumar" <[EMAIL PROTECTED]> <mailto:[EMAIL PROTECTED]>
Datum: ti 16 sep 2008 13.19.43 GMT+02:00
Till: "Johan Thorvaldsson" <[EMAIL PROTECTED]> <mailto:
[EMAIL PROTECTED]>
Kopia: mysql@lists.mysql.com <mailto: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]> <mailto:
[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]> <mailto:
[EMAIL PROTECTED]>
Datum: ti 16 sep 2008 10.13.53 GMT+02:00
Till: Johan Thorvaldsson <[EMAIL PROTECTED]> <mailto:
[EMAIL PROTECTED]>
Kopia: mysql@lists.mysql.com <mailto: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]> <mailto:
[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 <http://www.montania.se/>
Johan Thorvaldsson
[EMAIL PROTECTED] <mailto:[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 <http://www.montania.se/>
Johan Thorvaldsson
[EMAIL PROTECTED] <mailto:[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] <mailto:
[EMAIL PROTECTED]>
---------------------------------------------------------------------
Montania System AB
Halmstad
http://www.montania.se <http://www.montania.se/>
Johan Thorvaldsson
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
Kristinebergsvägen 17, S-30241 HALMSTAD Telefon +46(0)35-13 68 00 |
Fax +46(0)35-13 68 01
