Improving Query Performance

NuoDB tries to optimize query execution when it chooses table join order and indexes for the query plan. However, query execution might still be less than optimal and if that is the case then you can try any of the following alternatives to improve query performance:

  • Finding slow queries by checking NuoDB performance metrics (SYSTEM.QUERYSTATS and SYSTEM.CONNECTIONS)

  • Determining table join order and which indexes are being chosen by NuoDB for specific SQL statements (EXPLAIN)

  • Evaluating index statistics to see if the best index is being used given the data being evaluated (SYSTEM.INDEXSTATISTICS, SYSTEM.INDEXPREFIXSTATISTICS, SYSTEM.INDEXHISTOGRAMS, and SYSTEM.INDEXHISTOGRAMBUCKETS)

SYSTEM.QUERYSTATS, SYSTEM.CONNECTIONS, SYSTEM.INDEXSTATISTICS, SYSTEM.INDEXPREFIXSTATISTICS, SYSTEM.INDEXHISTOGRAMS, and SYSTEM.INDEXHISTOGRAMBUCKETS are pseudo system tables. This means they contain information that is dynamically generated by NuoDB processes and presented as a SQL table. Client applications should never contain code that accesses pseudo system tables as they are subject to, and furthermore likely to, change from release to release.