Then do u mean if there is low cardinality index will not be used?.
Regards,
Naga
On Thu, Sep 18, 2008 at 2:41 PM, chandru <pradeep.chandru@stripped>wrote:
> Hi nagaraj,
> sorry.. Since the cardinality is low MySQL has chosen *not to use* a Index
> that can give the best performance. i missed that.
>
>
> Regards,
> Pradeep chandru.
>
>
>
> Nagaraj S wrote:
>
>> Pradeep,
>> Can you be clear on your statement. Becoz i read in few links if the
>> cardinality is low also mysql doen't use the index. It depends up on the
>> where condition used..
>> Regards,
>> Naga.
>>
>> On Thu, Sep 18, 2008 at 12:22 PM, chandru
> <pradeep.chandru@stripped<mailto:
>> pradeep.chandru@stripped>> wrote:
>>
>> 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:pradeep.chandru@stripped] Sent:
>>> Thursday, September 18, 2008 11:36 AM
>>> To: Johan Thorvaldsson
>>> Cc: mysql@stripped <mailto:mysql@stripped>
>>> 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" <anandkl@stripped>
> <mailto:anandkl@stripped>
>>>>>
>>>>> Datum: ti 16 sep 2008 13.19.43 GMT+02:00
>>>>> Till: "Johan Thorvaldsson" <johan.t@stripped>
> <mailto:
>>>>> johan.t@stripped>
>>>>> Kopia: mysql@stripped <mailto: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>
> <mailto:
>>>>> 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>
> <mailto:
>>>>> pradeep.chandru@stripped>
>>>>> Datum: ti 16 sep 2008 10.13.53 GMT+02:00
>>>>> Till: Johan Thorvaldsson <johan.t@stripped> <mailto:
>>>>> johan.t@stripped>
>>>>> Kopia: mysql@stripped <mailto: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>
> <mailto:
>>>>> 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 <http://www.montania.se/>
>>>>>
>>>>> Johan Thorvaldsson
>>>>> johan.t@stripped <mailto: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 <http://www.montania.se/>
>>>>>
>>>>> Johan Thorvaldsson
>>>>> johan.t@stripped <mailto: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 <mailto:
>>>>> admin@stripped>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
> ---------------------------------------------------------------------
>>>>> Montania System AB
>>>>> Halmstad
>>>>> http://www.montania.se <http://www.montania.se/>
>>>>>
>>>>> Johan Thorvaldsson
>>>>> johan.t@stripped <mailto: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 <http://www.montania.se/>
>>>>
>>>> Johan Thorvaldsson
>>>> johan.t@stripped <mailto:johan.t@stripped>
>>>>
>>>> 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/mysql?unsub=1
>>>
>>>
>>>
>>>
>>
>>
>>
