1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Rep Power

    Question Calculate Rows Examined when using Union

    I'm a novice regarding mysql optimization and I'd like to check if my understanding is correct with regards to reading the output from EXPLAIN when using union.

    If I have the following query:

    select name from competitors c1 where name like 'A%'
    select name from competitors c2 where name like 'B%' and country_id = 2

    and I use EXPLAIN to view the number of rows that mysql will examine I get the following output:

    id: 1
    select_type: PRIMARY
    table: c1
    type: range
    possible_keys: idx_competitor_name
    Key: idx_competitor_name
    key_len: 66
    rows: 57
    Extra: Using where; Using index

    id: 2
    select_type: UNION
    table: c2
    type: ref
    possible_keys: idx_competitor_name,idx_country_id
    Key: idx_country_id
    key_len: 4
    ref: const
    rows: 126
    Extra: Using where

    select_type: UNION RESULT
    table: <union1,2>
    type: ALL

    From the above I can see that 57 rows are going to be examined for table c1 and 126 for table c2, but what I want to know is whether (in the case of union) I should still calculate the product of the rows (ie. 57 x 126) to determine the final number of rows that mysql examines? If so it would equate to 7182 rows.

    (It doesn't make sense to me why you'd have to multiply the rows when using union - I understand that for regular joins that don't use union that you'd need to determine the product of the rows to check how many rows are actually being examined, but is it the same when using union as in the above example? In my mind UNION is like two different resultsets that are simply appended together once individually gathered by mysql.)

    Any advice would be appreciated.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Toronto Canada
    Rep Power
    UNION simply appends rows, as you said

    multiplying rows occurs in cross joins, whether written with explicit CROSS JOIN syntax or inadvertently through old-style comma joins (e.g. FROM table1, table2) without a join condition

    you should use UNION ALL instead of UNION because there can be no duplicate rows coming out of your two SELECTs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo