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] @  []
# 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

  • 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... – RitterKnight Jun 15 at 17:57
  • 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. – zhua89 Jun 15 at 18:08
  • 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. – Brad Bell Jun 16 at 3:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.