Hi nagaraj,
yes by default, but if you know that the cardinality value that mysql
has calculated is very old ( since you have not optimized it) then
forcing a index can improve your performance as Dilip has mentioned in
the before mail. If you are not sure always better to let mysql decide
on the index to be used.
Regards
Pradeep chandru.
Nagaraj S wrote:
> 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
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>
>
