title: MySQL Multi JOIN extremely slow
body: <p>This is a problem that I'm not sure exactly where the root cause could be, so I'm going to provide details and the trouble points I've thought of. Any help would be awesome (beer on me if you live nearby). I have these three tables:</p>
  
  <p><strong>practices:</strong></p>
  
  <pre><code>`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(125) NOT NULL,
  `description` text,
  `deleted` int(11) unsigned DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` varchar(70) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `last_update_by` varchar(70) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
  </code></pre>
  
  <p><strong>locations:</strong></p>
  
  <pre><code>`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `practice_fk` int(11) unsigned NOT NULL,
  `phone` char(12) DEFAULT NULL,
  `fax` char(12) DEFAULT NULL,
  `address` varchar(125) DEFAULT NULL,
  `address_two` varchar(125) DEFAULT NULL,
  `city` varchar(40) NOT NULL,
  `state` char(2) NOT NULL,
  `zip` char(5) DEFAULT NULL,
  `lat` decimal(7,5) DEFAULT NULL,
  `lng` decimal(7,5) DEFAULT NULL,
  `deleted` int(11) unsigned DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` varchar(70) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `last_update_by` varchar(70) NOT NULL,
  `email` varchar(150) DEFAULT NULL,
  `practice_name_temp` text,
  PRIMARY KEY (`id`)
  </code></pre>
  
  <p><strong>contacts:</strong></p>
  
  <pre><code>`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `location_fk` int(11) unsigned NOT NULL,
  `practice_fk` int(11) unsigned NOT NULL,
  `fname` varchar(25) NOT NULL,
  `lname` varchar(45) NOT NULL,
  `phone` varchar(35) DEFAULT NULL,
  `mobile` char(12) DEFAULT NULL,
  `email` varchar(125) DEFAULT NULL,
  `title` varchar(100) DEFAULT NULL,
  `description` text,
  `deleted` int(11) unsigned DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` varchar(70) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `last_update_by` varchar(70) NOT NULL,
  PRIMARY KEY (`id`)
  </code></pre>
  
  <p>The basic idea behind the architecture is there is a list of practices. A practice can have multiple locations, but a location cannot exist without being associated with a practice. Then, a practice can also have multiple contacts, but a contact must be associated with a practice and a location. [This is where one part of the problem may begin]. So, I have this query:</p>
  
  <pre><code>SELECT DISTINCT p.id AS practice_id, 
                  p.name, 
                  l.id AS location_id, 
                  address AS location_address, 
                  l.phone AS disp_phone, 
                  CONCAT(pc.fname, ' ', pc.lname) AS practiceContact, 
                  CONCAT(lc.fname, ' ', lc.lname) AS locationContact,
                  pcc.qty AS practice_only_contact_qty,
                  lcc.qty AS location_contact_qty,
                  (pcc.qty + lcc.qty) AS contactQty
              FROM practices p
              LEFT JOIN practice_locations l on l.practice_fk=p.id
              LEFT JOIN (
                  SELECT count(id) AS qty, practice_fk 
                  FROM practice_contacts 
                  GROUP BY practice_fk
              ) pcc ON pcc.practice_fk=p.id
              LEFT JOIN practice_contacts pc ON pc.practice_fk=pcc.practice_fk AND pcc.qty=1
              LEFT JOIN (
                  SELECT count(id) AS qty, location_fk 
                  FROM practice_contacts 
                  GROUP BY location_fk
              ) lcc ON lcc.location_fk=l.id
              LEFT JOIN practice_contacts lc ON lc.location_fk=lcc.location_fk AND lcc.qty=1
              WHERE p.name IS NOT NULL AND p.deleted IS NULL
              GROUP BY p.id
              ORDER BY p.name ASC, l.state, l.city, l.address;
  </code></pre>
  
  <p>What this query is supposed to do is:</p>
  
  <ul>
  <li>Gather the practice ID and name.</li>
  <li>If there is a single location, grab it's address. Otherwise, grab the first location's address</li>
  <li>If there is a single contact associated with the practice, grab their name. Otherwise, grab the first contact's name</li>
  <li>Count how many contacts there are associated with the practice</li>
  <li>Count how many locations are associated with the practice</li>
  <li>Group all this together by the practice ID, then order it alphabetically according to the practice name, then by location</li>
  </ul>
  
  <p>So, it does all of that right now. REALLLLLLLYYY SLOWLY. When I only had five records in the practices table, and less than 20 in the other two tables, the query worked great. Now I've imported data into these tables (~9,000 records in practices, 14,000+ in locations, and 25,000+ in contacts), and this query takes over 28 seconds to return what I need. If I pull the group by's out, we're looking at 33+ seconds. Screw me, right?!</p>
  
  <p>Obviously, this is unacceptable. This dataset is relatively small, and this application is only going to grow with potentially millions of contacts being in here at some point. So, I'm wondering if this is in fact a three plus part issue:</p>
  
  <ul>
  <li><p><strong>First part:</strong> should I introduce a reference table [kind of like a view], to store these relationships -- something like:</p>
  
  <pre><code>`id` int(11) unsigned not null,
  `practice_fk` int(11) unsigned not null,
  `location_fk` int(11) unsigned not null,
  `contact_fk` int(11) unsigned not null,
  PRIMARY KEY(id),
  KEY(practice_fk),
  KEY(location_fk),
  KEY(contact_fk)
  </code></pre>
  
  <p>But if I do that, I'm unsure of how I would construct the query to pull the data as I need? And would it offer any performance benefits.</p></li>
  <li><p><strong>Second Part:</strong> I don't have the proper indexes in place. After scouring the MySQL docs and stumbling across this post (<a href="https://dba.stackexchange.com/questions/75091/why-are-simple-selects-on-innodb-100x-slower-than-on-myisam">https://dba.stackexchange.com/questions/75091/why-are-simple-selects-on-innodb-100x-slower-than-on-myisam</a>) I've come to understand InnoDB is a slow pig. This is unacceptable from a UX perspective but from an architecture perspective, I'm locked into this engine. How would I set up the indexes properly to get this query back to the sub-second range?</p></li>
  <li><p><strong>Third part:</strong> my query is garbage. I think this is probably the biggest culprit. I'm still learning how to structure these more complex SQL queries, and this is took some effort to craft in itself, so any pointers on how to make this thing less of a pig would be great.</p></li>
  </ul>
  
  <p>I've tried a variety of manipulations on my query (pulling out group by's, dropping the order by, etc) and there's hardly any change. The query consistently is running at between 28 and 33 seconds. Any guidance will be beyond appreciated.</p>