On Fri, Aug 22, 2003 at 10:07:46AM -0700, Steven Roussey wrote:
> Hmmm, just in case you can't change the table layout...
>
> Run this through MySQL. First I get rid of the other index I made, then
> add chained indexes so there is no need for data file lookup. Also, one
> direction of the query table join chain was not always using the indexes
> for the where. One direction is preferable (not knowing what the data
> is) since then we can use the index for the sort, but the other
> direction may have benefits that outweigh that, so that is why we used
> the composite index I last suggested.
My previous message should have helped to clarify what's going
on here, and why I used the structures I did.
> Anyhow, just to be clear, lets force all composite indexes for this
> query in both direction and force the optimizer to use both directions
> and see what we get (and sorry if I misspell or something, since I don't
> have your DB to check against). Run this and send back the results:
>
>
> # Get rid of the index I added before
> ALTER TABLE cg DROP INDEX q_id_2;
>
>
> # Get a baseline for direction 1
> SELECT STRAIGHT_JOIN cg.cw FROM cg,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;
This took the same 1 m 15 sec the first time, and on an
immediately succeeding run, the same 3.5 sec.
> EXPLAIN SELECT STRAIGHT_JOIN cg.cw FROM cg,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;
*************************** 1. row ***************************
table: cg
type: range
possible_keys: q_id,cw
key: cw
key_len: 101
ref: NULL
rows: 190550
Extra: Using where
*************************** 2. row ***************************
table: q
type: eq_ref
possible_keys: PRIMARY,cit_id
key: PRIMARY
key_len: 4
ref: cg.q_id
rows: 1
Extra:
*************************** 3. row ***************************
table: cit
type: eq_ref
possible_keys: PRIMARY,sref_id
key: PRIMARY
key_len: 4
ref: q.cit_id
rows: 1
Extra:
*************************** 4. row ***************************
table: sref
type: eq_ref
possible_keys: PRIMARY,cd
key: PRIMARY
key_len: 4
ref: cit.sref_id
rows: 1
Extra: Using where
4 rows in set (0.00 sec)
> # Get a baseline for direction 2
> SELECT STRAIGHT_JOIN cg.cw FROM sref,cit,q,cg
> 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;
This took 2.7 sec the first time, and 2.4 sec after. Getting better.
> EXPLAIN SELECT STRAIGHT_JOIN cg.cw FROM sref,cit,q,cg
> 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;
*************************** 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,cw
key: q_id
key_len: 4
ref: q.id
rows: 1
Extra: Using where
I note there's the temporary _and_ filesort for the sref section.
> # add indexes
> ALTER TABLE cg
> ADD INDEX dir1(cw,q_id),
> ADD INDEX dir2(q_id,cw);
>
> ALTER TABLE q
> ADD INDEX dir1(id,cit_id),
> ADD INDEX dir2(cit_id,id);
>
> ALTER TABLE cit
> ADD INDEX dir1(id,sref_id),
> ADD INDEX dir2(sref_id,id);
>
> ALTER TABLE sref
> ADD INDEX dir1(id,cd),
> ADD INDEX dir2(cd,id);
>
>
> # Get a new result for direction 1
> SELECT STRAIGHT_JOIN cg.cw FROM
> cg USE INDEX(dir1),
> q USE INDEX(dir1),
> cit USE INDEX(dir1),
> sref USE INDEX(dir1)
> 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;
This took 7.86 sec first, and then 2.30 sec.
> EXPLAIN SELECT STRAIGHT_JOIN cg.cw FROM
> cg USE INDEX(dir1),
> q USE INDEX(dir1),
> cit USE INDEX(dir1),
> sref USE INDEX(dir1)
> 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;
+-------+-------+---------------+------+---------+-------------+--------+--------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+-------+-------+---------------+------+---------+-------------+--------+--------------------------+
| cg | range | dir1 | dir1 | 101 | NULL | 179078 | Using where;
Using index |
| q | ref | dir1 | dir1 | 4 | cg.q_id | 1 | Using index
|
| cit | ref | dir1 | dir1 | 4 | q.cit_id | 1 | Using index
|
| sref | ref | dir1 | dir1 | 4 | cit.sref_id | 1 | Using where;
Using index |
+-------+-------+---------------+------+---------+-------------+--------+--------------------------+
Oops, sorry about spacing.
> # Get a new result for direction 2
> SELECT STRAIGHT_JOIN cg.cw FROM
> sref USE INDEX(dir2),
> cit USE INDEX(dir2),
> q USE INDEX(dir2),
> cg USE INDEX(dir2)
> 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;
This took 3.15 sec and then 2.89 sec.
I note that in both cases, there isn't a drastic difference
between what we had before the composite indexes, despite all
of this effort.
> EXPLAIN SELECT STRAIGHT_JOIN cg.cw FROM
> sref USE INDEX(dir2),
> cit USE INDEX(dir2),
> q USE INDEX(dir2),
> cg USE INDEX(dir2)
> 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;
*************************** 1. row ***************************
table: sref
type: range
possible_keys: dir2
key: dir2
key_len: 4
ref: NULL
rows: 1806
Extra: Using where; Using index; Using temporary; Using filesort
*************************** 2. row ***************************
table: cit
type: ref
possible_keys: dir2
key: dir2
key_len: 4
ref: sref.id
rows: 3
Extra: Using index
*************************** 3. row ***************************
table: q
type: ref
possible_keys: dir2
key: dir2
key_len: 4
ref: cit.id
rows: 31
Extra: Using index
*************************** 4. row ***************************
table: cg
type: ref
possible_keys: dir2
key: dir2
key_len: 4
ref: q.id
rows: 1
Extra: Using where; Using index
Hmm. Looks like fewer rows, but with both temporary and filesort
on sref, and no huge speed difference.
Well, there you go--thanks again for looking over this so closely!
Best,
Jesse
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
