SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Aug 2013
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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%'
    union
    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
    ref:
    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
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Aug 2013
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you very much for taking the time to respond. Your advice is appreciated.


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •