Hi Jesse,
Have you tried the following:
1) ordering by only part.d and seeing how long the query takes
2) putting an index on (part.d, cwGroup.stripped_cw) and seeing how
long the query takes.
1 will help pinpoint the problem, and 2 might actually help.
-Sheeri
SELECT part.d, quotation.qt, cwGroup.cw
FROM cwGroup
JOIN quotation ON (quotation.id = cwGroup.quotation_id )
JOIN part ON ( part.id = quotation.part_id )
WHERE ( part.d BETWEEN 1950 AND 1970 AND cwGroup.stripped_cw LIKE 'man%' )
ORDER BY part.d, cwGroup.stripped_cw
LIMIT 25
and the EXPLAIN for it looks like:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: cwGroup
type: range
possible_keys: quotation_id,stripped_cw
key: stripped_cw
key_len: 101
ref: NULL
rows: 8489
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: quotation
type: eq_ref
possible_keys: PRIMARY,part_id
key: PRIMARY
key_len: 4
ref: rqs_incs.cwGroup.quotation_id
rows: 1
Extra:
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: part
type: eq_ref
possible_keys: PRIMARY,d
key: PRIMARY
key_len: 4
ref: rqs_incs.quotation.part_id
rows: 1
Extra: Using where
Without the ORDER BY it drops to about 1.5s and EXPLAIN no longer
shows the use of "temporary" and "filesort".
An even worse example, but unfortunately a common need in this
app, is a query that returns a lot of rows (but which I'm paging
through, of course), such as:
SELECT part.d, quotation.qt, cwGroup.cw
FROM cwGroup
JOIN quotation ON (quotation.id = cwGroup.quotation_id )
JOIN part ON ( part.id = quotation.part_id )
WHERE ( cwGroup.stripped_cw BETWEEN 'ant' AND 'asx' )
ORDER BY cwGroup.stripped_cw, part.d
LIMIT 25
This takes 2m31s to execute, obviously due to the large number
of rows (the total result is about 47K rows), but a similar
query without the ORDER BY took only .08s (though a COUNT(*)
took a similar 2-3m):
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: cwGroup
type: range
possible_keys: quotation_id,stripped_cw
key: stripped_cw
key_len: 101
ref: NULL
rows: 54745
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: quotation
type: eq_ref
possible_keys: PRIMARY,part_id
key: PRIMARY
key_len: 4
ref: rqs_incs.cwGroup.quotation_id
rows: 1
Extra:
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: part
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: rqs_incs.quotation.part_id
rows: 1
Extra:
Other queries, as said, are more complicated, adding
additional columns in the searches or joining in other tables
(sometimes with range searches here as well), but these don't
seem to affect the underlying problem. Adding multiple-column
indexes also doesn't affect things in any significant way.
Any thoughts? I clearly need a significant speed improvement,
not just a tweak like making a bigger sort_buffer_size or
getting faster disks.
Thanks for reading this far.
Jesse Sheidlower
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
