MySQL Forums
Forum List  »  Performance

Re: Slow query with order by and limit
Posted by: Andrey Petukhov
Date: December 17, 2010 07:58AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
4964
December 16, 2010 07:43AM
Re: Slow query with order by and limit
1153
December 17, 2010 07:58AM
1251
December 17, 2010 09:04AM
1163
December 17, 2010 09:53AM
1608
December 17, 2010 11:47PM
1258
December 18, 2010 10:50AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.