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]

Reply via email to