Hi pradeep, What are you trying to say ?. The query clearly denotes that Index is not been used anywhere. So i strongly suggest to use the index explicitly for better performance. >>Forcing that can only increase the volume of data that is fetched. ?.... I cant understand how it increases the volume of data that is fetched. Can u please explain on this ??????. In this case, index is no where present in the select query, so naturally we have to force the query to use the index. Thanks & Regards, Dilipkumar
________________________________
From: chandru [mailto:[EMAIL PROTECTED]
Sent: Thursday, September 18, 2008 12:23 PM
To: Parikh, Dilip Kumar
Cc: Johan Thorvaldsson; mysql@lists.mysql.com
Subject: Re: Fwd: Why dont my query use the index keys?
Hi Dilip,
I dont think forcing MySQL to use Index can improve your performance.
MySQL decides on the index to be used based on the cardinality. Since the
cardinality is low MySQL has chosen a Index that can give the best performance.
Forcing that can only increase the volume of data that is fetched.
Regards,
Pradeep chandru.
Parikh, Dilip Kumar wrote:
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]>
<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
Ä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
Ä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
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]
