I am trying to find the performance of my database using schema crawler and I am looking out for ways how to do it. I am using a Spring Boot application to do this for my MySQL/PGS database. I used "EXPLAIN" but I am not getting the relevant output.
I got the below using DB tool: Seq Scan on users u (cost=0.00..14.30 rows=430 width=160)
But in Spring Boot, I did not get anything like this. This is the code I used:
for (final Schema schema : catalog.getSchemas()) {
System.out.println(schema);
for (final Table table : catalog.getTables(schema)) {
System.out.print("o--> " + table);
var sql = "EXPLAIN SELECT * FROM " + table;
try {
Statement statement = dataSource.get().createStatement();
var result = statement.execute(sql);
System.out.println(result);
} catch (Exception e) {
System.out.println(e);
return;
}
if (table instanceof View) {
System.out.println(" (VIEW)");
} else {
System.out.println();
}
for (final Column column : table.getColumns()) {
System.out.printf(" o--> %s (%s)%n", column, column.getType());
}
}
}
This is just an example, but I am mainly trying to find the performance of a database programmatically using schemacrawler. Kindly help with suggestions. Thanks.