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> 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
> 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
Johan Thorvaldsson
johan.t@stripped
Kristinebergsvägen 17, S-30241 HALMSTAD
Telefon +46(0)35-13 68 00 | Fax +46(0)35-13 68 01
