Thomas Wiedmann Wrote:
-------------------------------------------------------
> FORCE_INDEX is not the right way to optimize the
> query only a compromise.
> Did you add my index suggestion on table NODE and
> TERM_NODE?
>
> First please try my Index hints.
>
>
> ALTER TABLE term_node
> DROP KEY vid,
> DROP KEY tid,
> ADD KEY idx_vid_tid (vid,tid),
> ADD KEY idx_tid_vid (tid,vid);
>
> ALTER TABLE node
> DROP KEY node_type,
> ADD KEY idx_type_status_vid (type(4), status,
> vid);
>
mysql> EXPLAIN SELECT node.nid AS nid, node.created AS node_created, node.title AS node_title
FROM node node INNER JOIN term_node term_node ON node.vid = term_node.vid
WHERE node.type IN ('news') AND node.STATUS <>0 AND term_node.tid =10
ORDER BY node_created DESC LIMIT 0 , 3\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: term_node
type: ref
possible_keys: PRIMARY,vid_tid,idx_vid_tid,idx_tid_vid
key: PRIMARY
key_len: 4
ref: const
rows: 20035
Extra: Using index; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: node
type: eq_ref
possible_keys: vid,vid_created,node_status_type,idx_type_status_vid
key: vid
key_len: 4
ref: andribas.term_node.vid
rows: 1
Extra: Using where
2 rows in set (0.00 sec)
Edited 1 time(s). Last edit at 12/17/2010 07:59AM by Andrey Petukhov.