Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>There are a lot of things that could be affecting your query, using <code>EXPLAIN</code> should reveal the issues that have the most impact on performance.</p> <p>But here are a few general guidelines:</p> <ul> <li>Ensure all the tables have an index on the fields used for joining the tables.</li> <li>Why do you use <code>LEFT JOIN</code> and then filter on concrete values on the <code>WHERE</code> clause? Using outer joins will increment the number of rows that would later be filtered and discarded by the conditions on the <code>WHERE</code> clause.</li> <li>Ensure that all the columns used for filtering are covered by an index. But, also, why do you filter by text values? Even if the city, state, and instrument fields are covered by an index, it takes more processing to compare strings than comparing integers.</li> <li>Why group by <code>Person.id</code> if then you order randomly? The grouping forces a sort operation that most probably requires a temporary table due to the table joins.</li> </ul> <p>Now think also about this:</p> <p>The way databases process joins in general is by combining all rows from each table and then discard the combinations that don't fulfill the join condition. That's why it's very important to have indexes on the join fields, because that way the combination uses the index keys and not a temporary table with all fields from both tables. That's also a reason not to use a <code>LEFT JOIN</code> and then later discard row combinations (in the <code>WHERE</code> clause) that could have been discarded earlier.</p> <p>To get an idea of what this query is doing, and why it's affecting performance so badly calculate:</p> <pre><code>Total rows to filter = Number of rows in table Person * Number of rows in table TeacherDriveCity * Number of rows in table Instrument * Number of rows in table Teacher * Number of rows in table Upload * Number of rows in table TeacherBiography * Number of rows in table TeacherPhilosophy </code></pre> <p>I'm pretty sure that would be a very big number.</p> <p><strong><code>EXPLAIN</code> interpretation</strong></p> <p>From the posted <code>EXPLAIN</code> results it's very bad:</p> <ul> <li>As mentioned, all the <code>ALL</code> values under join <code>type</code> mean that MySQL is doing a full table scan, that is it must read all the rows on table. This is very, very bad.</li> <li>There are no usable indexes as revealed by <code>NULL</code> under <code>possible_keys</code> for most of the join conditions. This is another reason for MySQL to do table scans, it must look through all the joined rows to filter the needed rows.</li> </ul> <p>I would say that the worst part is filtering by <code>city</code> and <code>state</code>. Without joins a simple look up in the <code>TeacherDriveCity</code> by those two fields would require MySQL to look at potentially 7489 rows. Not to mention that both fields hold character strings.</p> <p><strong>Quick Fix</strong></p> <p>Add indexes on all columns used in <code>JOIN</code> and <code>WHERE</code> clauses. But consider not using outer (<code>LEFT</code>) joins on tables other that <code>TeacherBiography</code> and <code>TeacherPhilosophy</code>.</p>
    singulars
    1. This table or related slice is empty.
    1. POServer Crash - Optimizing Mysql queries
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USXint0
    1. USXint0
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POServer Crash - Optimizing Mysql queries
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThank you so much for the reply! I did notice the city and state seemed to be the issue because the same query without it was working just fine. I've always just accepted the query if it gives me the results I'm looking for and never really put too much thought in optimizing them. For now I just took out the city and state filters and will spend some real time learning about indexes joins and what not. Honestly I've never really understood the differences in left right inner join etc. Left always seemed to return the result I was looking for. Anyways thanks again!
      singulars
      1. PO
      1. USBrian Jenkins
    2. COWould there be any reason why it just started to act up now? I mean the pages with the queries always loaded fast and never had any problems for months until now. Although I'll definitely work on fixing this query (and most likely all others I've ever written) but I'm just wondering is there something else in addition to the poorly written query that would cause such a problem? Is 7489 rows really that many to search through?
      singulars
      1. PO
      1. USBrian Jenkins
    3. COThe problem is that it's not just the 7489, but that times the result of the previous filters.
      singulars
      1. PO
      1. USXint0
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload