Body: <p>Per the title, our application's db query has from time to time pegged our CPU to the max and taken down the site. Our site has gotten quite large in terms of content and trying to figure out how best to tune our db.</p>
  <p>Noticing that our queries are performing full table scans which I believe means there usually is a missing index in the table and wondering if there is programmatic way for craft to fix/address that?</p>
  <pre><code>Time: 210612  9:28:35
  # User@Host: b4mhz722nsjh4[b4mhz722nsjh4] @  [192.168.0.7]
  # Thread_id: 2408679  Schema: b4mhz722nsjh4  QC_hit: No
  # Query_time: 0.567324  Lock_time: 0.000205  Rows_sent: 1  Rows_examined: 142606
  # Full_scan: Yes  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: Yes
  # Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0  Priority_queue: Yes`
  </code></pre>
  <p>Below is a screenshot of my elements table as an example in SQLPRO. It does list all the indexes so not sure what I'm missing.</p>
  <p><a href="https://i.sstatic.net/KBwUk.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/KBwUk.png" alt="enter image description here" /></a></p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2021-06-15T17:57:22.053Z
Comments.Text: What query are you trying to perform? .5 seconds for 142,000 rows doesn't seem that out of the ordinary... but if you're trying to get at one row, that does seem room for improvement...
Comments.UserDisplayName: RitterKnight
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2021-06-15T18:08:13.817Z
Comments.Text: thanks. I think that is part of the issue as i haven't been able to pinpoint where these slower queries are coming from. We get a decent amount of site traffic but not enough where loading the templates and the queries associated on those pages would down the site.
Comments.UserDisplayName: zhua89
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2021-06-16T03:48:01.127Z
Comments.Text: Finding the offending MySQL quer(y|ies) will be the best way to debug this, I think. Maybe enable MySQL's slow query logs to see if it captures anything interesting? Alternatively, some APM/profiling software (new relic and the like) might be able to capture something.
Comments.UserDisplayName: Brad Bell
Tags: <database><performance><mysql>
Title: SQL Queries Performing Full Table Scan
ThreadText: # SQL Queries Performing Full Table Scan
  
  (Asked by: username_0 on 2021-06-15)
  
  Per the title, our application's db query has from time to time pegged our CPU to the max and taken down the site. Our site has gotten quite large in terms of content and trying to figure out how best to tune our db.
  
  Noticing that our queries are performing full table scans which I believe means there usually is a missing index in the table and wondering if there is programmatic way for craft to fix/address that?
  
  ```
  Time: 210612  9:28:35
  # User@Host: b4mhz722nsjh4[b4mhz722nsjh4] @  [192.168.0.7]
  # Thread_id: 2408679  Schema: b4mhz722nsjh4  QC_hit: No
  # Query_time: 0.567324  Lock_time: 0.000205  Rows_sent: 1  Rows_examined: 142606
  # Full_scan: Yes  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: Yes
  # Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0  Priority_queue: Yes`
  
  ```
  
  Below is a screenshot of my elements table as an example in SQLPRO. It does list all the indexes so not sure what I'm missing.
  
  [![enter image description here](https://i.sstatic.net/KBwUk.png)](https://i.sstatic.net/KBwUk.png)
  
  **Tags:** database, performance, mysql
  
  > **Comments:**
  > **username_1**: What query are you trying to perform? .5 seconds for 142,000 rows doesn't seem that out of the ordinary... but if you're trying to get at one row, that does seem room for improvement...  
  > **username_0**: thanks. I think that is part of the issue as i haven't been able to pinpoint where these slower queries are coming from. We get a decent amount of site traffic but not enough where loading the templates and the queries associated on those pages would down the site.  
  > **username_2**: Finding the offending MySQL quer(y|ies) will be the best way to debug this, I think. Maybe enable MySQL's slow query logs to see if it captures anything interesting? Alternatively, some APM/profiling software (new relic and the like) might be able to capture something.  
  
  ---
  
  ## 0 answers
  
  