Possible to Optimize Based on DB Structure Only - No Data

Question

There are some queries in my MySQL DB that seems to take a bit of time to execute. As the data is somewhat sensitive, it is not possible for me to share it with outsiders. However, I do need assistance trying to pinpoint what's slowing down the queries.

Can this be done by me only sharing the DB structure? And perhaps the queries that I find to be slow? Or do I also need to present the data?

Answer 1

Questions about improving performance should include:

• The full text of the query.
• SQL CREATE statements for the tables and indexes involved
  Use SHOW CREATE TABLE\G
• The version of the product you are using.
• Number of rows in each table.
• Query plan/explain output.
• Brief server hardware details including:
  • The amount of RAM
  • CPU info
  • Disk configuration; and
  • Network details (if applicable).
• Other important system/database configuration details
  Including SHOW VARIABLES LIKE '%buffer%';
• Expected results, and ideally, some sample data (it's OK to obfuscate).
  Sample data should be in the form:
  INSERT INTO My_Table VALUES(A, B, C,...);
  ...so it can be easily copied and pasted.
• Where possible, a SQL Fiddle*, or other off-site hosted runnable code.
• Any constraints on answers (e.g. no new indexes).
• Current and desired performance.

The first two are vital; the more of the rest that you can provide, the more likely your question is to attract high-quality answers.

_{* When it's working}

---

MySQL experts: Please edit this Community Wiki answer to improve it.