On Fri, Aug 22, 2003 at 07:33:56AM -0700, Steven Roussey wrote:
> > Here's the CREATEs, somewhat edited to remove parts not relevant
> > to this discussion, to save space:
>
> I never actually looked at your JOIN statement more than a quick
> glimpse, but I will (though not just right now). Before I do, can you
> try this (I still don't have data or I'd play with it myself:
>
> mysql> EXPLAIN SELECT cg.cw FROM cg USE INDEX(q_id_2),q,cit,sref
> -> WHERE cg.q_id = q.id AND q.cit_id = cit.id AND cit.sref_id =
> sref.id
> -> AND cg.cw LIKE 't%'
> -> AND sref.cd > DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
> -> ORDER BY cg.cw
> -> LIMIT 1000,10;
The actual SELECT wasn't really any faster; the first run was
4.05 sec (OK, a lot faster than the 1 m 15 sec it had been
taking) but the second run was 3.66 sec, compared to the 3.5 sec
or so it had been taking.
Here's the EXPLAIN:
mysql> EXPLAIN SELECT cg.cw FROM cg USE INDEX(q_id_2),q,cit,sref
-> WHERE cg.q_id = q.id AND q.cit_id = cit.id AND cit.sref_id = sref.id
-> AND cg.cw LIKE 't%'
-> AND sref.cd > DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
-> ORDER BY cg.cw
-> LIMIT 1000,10\G
*************************** 1. row ***************************
table: sref
type: range
possible_keys: PRIMARY,cd
key: cd
key_len: 4
ref: NULL
rows: 3102
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
table: cit
type: ref
possible_keys: PRIMARY,sref_id
key: sref_id
key_len: 4
ref: sref.id
rows: 3
Extra:
************************** 3. row ***************************
table: q
type: ref
possible_keys: PRIMARY,cit_id
key: cit_id
key_len: 4
ref: cit.id
rows: 31
Extra:
*************************** 4. row ***************************
table: cg
type: ref
possible_keys: q_id_2
key: q_id_2
key_len: 4
ref: q.id
rows: 1
Extra: Using where; Using index
4 rows in set (0.00 sec)
Best,
Jesse
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
