explain a query - please help
Results 1 to 2 of 2

Thread: explain a query - please help

  1. #1
    Web Interface Architect
    Join Date
    Jan 2002
    Location
    Central Texas
    Posts
    989

    explain a query - please help

    I am not as familiar with the EXPLAIN feature as I should be, can anyone help me with this slow query? Sorry if the table is a bit scattered:



    here is my query:

    EXPLAIN SELECT m . * /* number of leases that show present COUNT(DISTINCT l.ID) AS Leases,*/
    FROM _v_properties_master_list m
    LEFT JOIN gl_leases l ON m.ID = l.Units_ID
    AND /* the unit/house/etc is leased AS OF THE INTENDED MOVE-IN DAY OF THE SEARCH - we filter this out outside of the left join .. */ '2013-05-03'
    BETWEEN l.LeaseStartDate
    AND IF( l.LeaseTerminationDate, l.LeaseTerminationDate, IF( l.LeaseEndDate, l.LeaseEndDate, '2199-12-31' ) )
    WHERE m.Active =1
    AND
    TYPE IN (
    'APT'
    )
    AND Bedrooms =1
    AND Bathrooms =1
    AND PropertyCity
    IN (
    'San Marcos'
    )
    AND (
    1
    )
    AND m.ID
    IN ( 105, 848 )
    GROUP BY m.ID
    ORDER BY PropertyName ASC


    and here is the result:

    id select_type table type possible_keys key key_len ref rows Extra
    1 PRIMARY <derived2> ALL NULL NULL NULL NULL 777 Using where; Using temporary; Using filesort
    1 PRIMARY l ref LeaseStartDate,Units_ID Units_ID 4 m.ID 20
    2 DERIVED cl ALL PRIMARY NULL NULL NULL 423 Using temporary; Using filesort
    2 DERIVED p ref Clients_ID Clients_ID 4 cpm151_sanmarcos.cl.ID 1 Using where
    2 DERIVED u ref Clients_ID Clients_ID 4 cpm151_sanmarcos.p.ID 2
    2 DERIVED a ref LeaseStartDate,Units_ID Units_ID 4 cpm151_sanmarcos.u.ID 20
    2 DERIVED flprimary ref Type,Leases_ID Leases_ID 4 cpm151_sanmarcos.a.ID 1
    2 DERIVED flc eq_ref PRIMARY PRIMARY 4 cpm151_sanmarcos.flprimary.Contacts_ID 1
    2 DERIVED flall ref Leases_ID Leases_ID 4 cpm151_sanmarcos.a.ID 1
    2 DERIVED a ref LeaseStartDate,Units_ID Units_ID 4 cpm151_sanmarcos.u.ID 20
    2 DERIVED clprimary ref Type,Leases_ID Leases_ID 4 cpm151_sanmarcos.a.ID 1
    2 DERIVED clc eq_ref PRIMARY PRIMARY 4 cpm151_sanmarcos.clprimary.Contacts_ID 1
    2 DERIVED clall ref Leases_ID Leases_ID 4 cpm151_sanmarcos.a.ID 1

    now, _v_properties_master_list is a fairly complex and I will post that separately if needed. Thanks!
    Hey! You may have inadvertently stumbled onto some genius there! :-)

  2. #2
    Pedantic Curmudgeon Weedpacket's Avatar
    Join Date
    Aug 2002
    Location
    General Systems Vehicle "Thrilled To Be Here"
    Posts
    21,845
    Quote Originally Posted by sfullman
    Sorry if the table is a bit scattered:
    That's what [code]..[/code] tags are for...
    THERE IS AS YET INSUFFICIENT DATA FOR A MEANINGFUL ANSWER
    FAQs! FAQs! FAQs! Most forums have them!
    Search - Debugging 101 - Collected Solutions - General Guidelines - Getting help at all

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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