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 <[EMAIL PROTECTED]>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 <[EMAIL PROTECTED]<mailto: >> [EMAIL PROTECTED]>> 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:[EMAIL PROTECTED] Sent: >>> Thursday, September 18, 2008 11:36 AM >>> To: Johan Thorvaldsson >>> Cc: mysql@lists.mysql.com <mailto: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 <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 >>>>> >>>>> >>>>> >>>>> >>>> --------------------------------------------------------------------- >>>> 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 >>>> >>>> >>>> >>>> >>> -- >>> MySQL General Mailing List >>> For list archives: http://lists.mysql.com/mysql >>> To unsubscribe: >>> http://lists.mysql.com/[EMAIL PROTECTED] >>> >>> >>> >>> >> >> >>
