SmartCJ Support Forum

Code: Select all

SELECT gt.tag_id, t.tag_name as tag_name, count(*) as hits FROM rot_gal2tag AS gt left join rot_tags as t on t.tag_id = gt.tag_id WHERE t.status = 'active' and gt.site_id = 59 GROUP BY gt.tag_id;

Code: Select all

SELECT gt.tag_id, t.tag_name as tag_name, count(*) as hits FROM rot_gal2tag AS gt force index(tag_id) left join rot_tags as t on t.tag_id = gt.tag_id WHERE t.status = 'active' and gt.site_id = 59 GROUP BY gt.tag_id;

Code: Select all

SELECT   gs.gallery_id, gs.thumb_id, gi.sponsor_id, gi.content_count, gi.content_type, gs.total_shows, gs.total_clicks, gs.total_ctr, gt.thumb_url, gd.*, gi.content_count, gi.crop_profile_id, gs.activation_date, gi.added_date, gi.duration, gi.url, gi.gallery_total_shows, gi.sponsor_id, gi.source_url, gi.custom_gallery  FROM rot_gallery_stats34 as gs  FORCE INDEX (group_id)   JOIN  rot_gallery_info as gi on gi.gallery_id = gs.gallery_id
 JOIN  rot_gallery_data32 as gd on gd.gallery_id = gs.gallery_id
  JOIN rot_thumbs as gt on gt.thumb_id = gs.thumb_id 
			WHERE 1 = 1  AND gi.gallery_status = 'active'  AND gi.gallery_type = 0  and gs.best_thumb = 'yes'  and gs.group_id IN (170)  ORDER BY  tested_thumb DESC,  gs.total_ctr DESC  LIMIT 0, 80

Code: Select all

+----+-------------+-------+------------+--------+------------------------------------------------+--------------+---------+------------------------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys                                  | key          | key_len | ref                    | rows | filtered | Extra                                        |
+----+-------------+-------+------------+--------+------------------------------------------------+--------------+---------+------------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | gi    | NULL       | ref    | PRIMARY,sponsor_id,gallery_type,gallery_status | gallery_type | 1       | const                  |    1 |   100.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | gd    | NULL       | eq_ref | PRIMARY                                        | PRIMARY      | 4       | adamporn.gi.gallery_id |    1 |   100.00 | NULL                                         |
|  1 | SIMPLE      | gs    | NULL       | ref    | group_id                                       | group_id     | 3       | const,const            |  692 |     0.01 | Using index condition                        |
|  1 | SIMPLE      | gt    | NULL       | eq_ref | PRIMARY                                        | PRIMARY      | 4       | adamporn.gs.thumb_id   |    1 |   100.00 | NULL                                         |
+----+-------------+-------+------------+--------+------------------------------------------------+--------------+---------+------------------------+------+----------+----------------------------------------------+