Welcome to the Question2Answer Q&A. There's also a demo if you just want to try it out.
+3 votes
404 views
in Q2A Core by
I'm finding that the admin/stats page is taking a long time to load nowadays, and in fact on localhost just hit the maximum 30 seconds execution time. From the debug mode I understand that it's running a lot of queries to obtain stats.

The slowest by a long way is "SHOW TABLE STATUS" (6+ seconds) which seems to only be for getting the size of the database. Also this is getting the ENTIRE database, not just the current Q2A installation. On my site I have 3 Q2A instances plus all the tables for my whole site.

Perhaps we could use "SHOW TABLE STATUS LIKE 'qa\_%'" (with the correct prefix for current Q2A) - that one still takes about a second for me but it's a big improvement.
Q2A version: 1.6.2
by
It's a good idea. I'll check into it for Q2A 1.6.3. Might need two such queries for cases where multiple sites are sharing a user database.

1 Answer

+1 vote
by

Here's the drop-in replacement for function qa_db_table_size() in qa-db-admin.php:

  function qa_db_table_size()
/*
  Return the total size in bytes of all relevant tables in the Q2A database
*/
  {
    if (defined('QA_MYSQL_USERS_PREFIX')) { // check if one of the prefixes is a prefix itself of the other
      if (stripos(QA_MYSQL_USERS_PREFIX, QA_MYSQL_TABLE_PREFIX)===0)
        $prefixes=array(QA_MYSQL_TABLE_PREFIX);
      elseif (stripos(QA_MYSQL_TABLE_PREFIX, QA_MYSQL_USERS_PREFIX)===0)
        $prefixes=array(QA_MYSQL_USERS_PREFIX);
      else
        $prefixes=array(QA_MYSQL_TABLE_PREFIX, QA_MYSQL_USERS_PREFIX);
    
    } else
      $prefixes=array(QA_MYSQL_TABLE_PREFIX);
      
    $size=0;
    foreach ($prefixes as $prefix) {
      $statuses=qa_db_read_all_assoc(qa_db_query_raw(
        "SHOW TABLE STATUS LIKE '".$prefix."%'"
      ));
 
      foreach ($statuses as $status)
        $size+=$status['Data_length']+$status['Index_length'];
    }
    
    return $size;
  }
 
Note that if you share user tables across Q2A installations, the data for those users will be included on every admin/stats page, so totalling them up with cause double counting.

This fix will be rolled into Q2A 1.6.3.

...