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.