Changeset 19090

Timestamp:

07/26/18 20:06:53 (6 years ago)

Author:

tbretz

Message:

A very simple implementation which will EXPLAIN the query with what MySQL offers -- some formatting might help to read the output, but I found no easy way to do that.

File:

• trunk/FACT++/src/rootifysql.cc (modified) (5 diffs)

trunk/FACT++/src/rootifysql.cc

@@ -36 +36 @@
         ("no-fill",       po_switch(),                 "Do not fill events into the root file (mainly for debugging purposes, e.g. performance studies)")
         ("delimiter",     var<string>(""),             "The delimiter used if contents are displayed with --display (default=\\t)")
+        ("explain",       po_switch(),                 "Requests an EXPLAIN from the server (shows the serveroptimized query)")
         ("var.*",         var<string>(),               "Predefined SQL user variables (@VAR)")
         ("env.*",         vars<string>(),              "Predefined environment for substitutions in the query ($ENV)")
@@ -123 +124 @@
     const bool     noout       = conf.Get<bool>("null");
     const bool     nofill      = conf.Get<bool>("no-fill");
+    const bool     explain     = conf.Get<bool>("explain");
     const uint16_t verbose     = conf.Get<uint16_t>("verbose");
     const uint16_t compression = conf.Get<uint16_t>("compression");
@@ -244 +246 @@
     }
 
+    // ------------------------- Explain query if requested --------------------
+
+    if (explain)
+    {
+        try
+        {
+            const auto res0 =
+                connection.query("EXPLAIN FORMAT=JSON "+query).store();
+
+            cout << res0[0][0] << endl;
+            cout << endl;
+
+            const mysqlpp::StoreQueryResult res1 =
+                connection.query("EXPLAIN "+query).store();
+
+            for (size_t i=0; i<res1.num_rows(); i++)
+            {
+                const mysqlpp::Row &row = res1[i];
+
+                cout << "\nid           : " << row["id"];
+                cout << "\nselect type  : " << row["select_type"];
+
+                if (!row["table"].is_null())
+                    cout << "\ntable        : " << row["table"];
+
+                if (!row["partitions"].is_null())
+                    cout << "\npartitions   : " << row["partitions"];
+
+                if (!row["key"].is_null())
+                    cout << "\nselected key : " << row["key"] << " [len=" << row["key_len"] << "] out of (" << row["possible_keys"] << ")";
+
+                if (!row["type"].is_null())
+                    cout << "\njoin type    : " << row["type"];
+
+                //if (!row["possible_keys"].is_null())
+                //    cout << "\npossible_keys: " << row["possible_keys"];
+
+                //if (!row["key_len"].is_null())
+                //    cout << "\nkey_len      : " << row["key_len"];
+
+                if (!row["ref"].is_null())
+                    cout << "\nref          : (" << row["ref"] << ") compared to the index";
+
+                if (!row["rows"].is_null())
+                    cout << "\nrows         : " << row["rows"];
+
+                if (!row["filtered"].is_null())
+                    cout << "\nfiltered     : " << row["filtered"];
+
+                if (!row["extra"].is_null())
+                    cout << "\nExtra        : " << row["extra"];
+
+                cout << endl;
+            }
+
+            cout << endl;
+
+            return 0;
+
+            const mysqlpp::StoreQueryResult res2 =
+                connection.query("SHOW WARNINGS").store();
+
+            for (size_t i=0; i<res2.num_rows(); i++)
+            {
+                const mysqlpp::Row &row = res2[i];
+
+                // 1003 //
+                cout << row["Level"] << '[' << row["Code"] << "]:\n";
+                cout << row["Message"] << '\n' << endl;
+
+            }
+
+        }
+        catch (const exception &e)
+        {
+            cerr << query << "\n\n";
+            cerr << "SQL query failed:\n" << e.what() << endl;
+            return 6;
+        }
+
+        return 0;
+    }
+
     // -------------------------- Request data from database -------------------
     if (verbose>0)
@@ -262 +347 @@
     TFile tfile(path, update?"UPDATE":(force?"RECREATE":"CREATE"), "Rootify SQL", compression);
     if (tfile.IsZombie())
-        return 6;
+        return 7;
 
     // -------------------------------------------------------------------------
@@ -271 +356 @@
     {
         cerr << "Empty set returned... nothing to write." << endl;
-        return 7;
+        return 8;
     }