mysql Query Optimization with inner join

Question

I have a query

$query = "SELECT DISTINCT report_date,weekreportDate FROM contract_sales a 
    INNER JOIN contract b ON a.contract_UUID = b.UUID
    INNER JOIN geoPoint c ON b.customer_UUID = c.customerUUID
    WHERE c.com_UUID = '$com' AND a.report_date >= Date('$dateafter')
    AND c.city_UUID = '$cit' ORDER BY `report_date`";

What I need to do is first get rid of all the results via date filtering but as you can see I get everything and then do my date sorting in the checks..

I am inner join all of them - is there a better way to do this?

I have a report for each date - and have two years of data - I want to get only dates in 2014 so as you can see I have 700+ dates that are useless to me right away but I have to go through all of them can check the other string UUID as well... what can I do to speed up my (working - albeit slow implementation)?

Explain information as requested:

Generation Time: Feb 20, 2014 at 06:48 PM
Generated by: phpMyAdmin 3.3.10.4 / MySQL 5.1.53-log
SQL query: EXPLAIN SELECT DISTINCT report_date,weekreportDate FROM contract_sales a INNER JOIN contract b ON a.contract_UUID = '1234' INNER JOIN geoPoint c ON b.customer_UUID = '1234' WHERE c.com_UUID = '1234' AND a.report_date >= Date('2014-01-01') AND c.city_UUID = '1234' ORDER BY `report_date`; 
Rows: 3

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  a   ref uuid_conlcs uuid_conlcs 110 const   1   Using where; Using temporary; Using filesort
1   SIMPLE  b   ref uuid_cust   uuid_cust   110 const   1   Using where; Using index; Distinct
1   SIMPLE  c   ref uuid_gargp,uuid_citgp   uuid_citgp  110 const   1   Using where; Distinct

DRapp · Accepted Answer · 2014-02-21 05:01:17Z

First, a rewrite of your query. I would not suggest using aliases just a, b, c, but something closer to context of table "cs" for contract_sales, "con" for contract, and "gp" for geoPoint... especially easier in larger more complex queries.

Also, ALWAYS try to qualify your query with table.column (or alias.column) as the WeekReportDate is not clear, but it appears to be associated with your contract sales table.

As for indexes in this construct, I would have an index on (report_date, weekreportDate, contract_uuid). This way, its a covering index to handle the columns being retrieved, the where clause, order by, and the join to the contract table without having to go back to the raw data pages.

The contract table, I would have an index on ( UUID, customer_UUID), also to be a covering index for the join from contract sales, and also to support the join to the geoPoint table.

Finally, your geoPoint table, an index on ( customerUUID, com_uuid, city_uuid ) to also cover the join and your filtering criteria.

SELECT DISTINCT 
      cs.report_date,
      cs.weekreportDate 
   FROM 
      contract_sales cs
         INNER JOIN contract con
            ON cs.contract_UUID = con.UUID
            INNER JOIN geoPoint gp
               ON con.customer_UUID = gp.customerUUID
               AND gp.com_UUID = '$com' 
               AND gp.city_UUID = '$cit' 
   WHERE 
      cs.report_date >= Date('$dateafter')
   ORDER BY 
      cs.report_date

Now that being said, and I don't know the makeup of your tables for volume, but if you are looking for stuff for a particular COM/City, I would suspect that records qualifying that would be a much smaller set than ALL COM/City for the date range in question. So, I would reverse the query as below hoping the smaller dataset might query faster, but you would have to obviously try both out.

SELECT DISTINCT 
      cs.report_date,
      cs.weekreportDate 
   FROM 
      geoPoint gp
         INNER JOIN contract con
            ON gp.customerUUID = con.customer_UUID 
            JOIN contract_sales cs
               ON con.UUID = cs.contract_UUID
               AND cs.report_date >= Date('$dateafter')
   WHERE 
          gp.com_UUID = '$com' 
      AND gp.city_UUID = '$cit' 
   ORDER BY 
      cs.report_date

Actually the geoPoint index should be on your WHERE criteria first, then the customer UUID for the join to the next table (com_uuid, city_uuid, customeruuid ). The contract_sales index on ( contract_UUID, report_date ), and the contract table index on (customer_UUID, UUID ) to match the flow of joins of this query.

Thank you for the detailed information! its going to take me some time to digest exactly what you stated but from initial read I think I understand and will give this a shot! Appreciate it!!
@morty346, sorry late edit, but the second query actually would have different indexes as I revised the answer context.

Collectives™ on Stack Overflow

mysql Query Optimization with inner join

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related