{"id":3412,"date":"2016-07-29T18:30:24","date_gmt":"2016-07-29T16:30:24","guid":{"rendered":"https:\/\/fd.xuwubk.eu.org:443\/http\/blog.ulf-wendel.de\/?p=3412"},"modified":"2016-07-29T18:30:24","modified_gmt":"2016-07-29T16:30:24","slug":"faceted-search-devapi-matter-one-day","status":"publish","type":"post","link":"https:\/\/fd.xuwubk.eu.org:443\/http\/blog.ulf-wendel.de\/2016\/faceted-search-devapi-matter-one-day\/","title":{"rendered":"Faceted search, why the DevAPI could matter one day"},"content":{"rendered":"<p>\nFaceted search or <a href=\"https:\/\/fd.xuwubk.eu.org:443\/https\/www.flickr.com\/photos\/morville\/collections\/72157603789246885\/\">faceted navigation<\/a> is a highly praised and widely use search pattern. And, it is a great reply to an off the records sales engineering question. MySQL finally has some <a href=\"https:\/\/fd.xuwubk.eu.org:443\/http\/dev.mysql.com\/doc\/refman\/5.7\/en\/document-store.html\">document store features<\/a> built-in. A bit of a yawn in 2016. There is a new <a href=\"https:\/\/fd.xuwubk.eu.org:443\/http\/mysqlserverteam.com\/mysql-5-7-12-part-3-more-than-just-sql\/\">X DevAPI<\/a> available with some <a href=\"https:\/\/fd.xuwubk.eu.org:443\/http\/dev.mysql.com\/doc\/index-connectors.html\">Connectors<\/a>. A bit of a yawn technically. But it is a non-technical change of mind: developer centric counts! Sales, all, technical value could show at non-trivial developer tasks, like faceted search.\n<\/p>\n<h3>Todays X DevAPI does not get you very far<\/h3>\n<p>\nThere are great stories to tell about the X DevAPI, see <a href=\"https:\/\/fd.xuwubk.eu.org:443\/http\/mysqlserverteam.com\/mysql-5-7-12-part-3-more-than-just-sql\/\">MySQL 5.7.12 \u2013 Part 3: More Than \u201cJust\u201d SQL?<\/a>:<\/p>\n<ul>\n<li>Non-blocking API<\/li>\n<li>CRUD API for schemaless documents in collections and schemaful rows in tables<\/li>\n<li>SQL support<\/li>\n<li>Prepared for &#8220;<a href=\"https:\/\/fd.xuwubk.eu.org:443\/http\/image.slidesharecdn.com\/mysqlndloadbalancing2012-120604032337-phpapp01\/95\/mysql-load-clustering-balancing-peclmysqlndms-14-14-728.jpg?cb=1338790641\">we need new APIs<\/a>&#8221; for distributed databases<\/li>\n<\/ul>\n<p>Most importantly the &#8220;look and feel&#8221; is similar to comparable offerings from NoSQL vendors. Competitive offerings have been described as easy, natural, developer friendly. We tried to follow these maximes. Albeit a leap step forward the feature set of the first version of the X DevAPI is limited. It works for basic apps like the <a href=\"https:\/\/fd.xuwubk.eu.org:443\/http\/insidemysql.com\/develop-by-example-new-mysql-document-store-series\/\">demo app<\/a>. But instead of adding a bulk of additional features we broaden our view to achieve the goal of improving developer ease of use on a whole:<\/p>\n<ul>\n<li>Out-of-the box experience<\/li>\n<li><a href=\"https:\/\/fd.xuwubk.eu.org:443\/http\/mysqlserverteam.com\/mysql-5-7-12-part-4-a-new-mysql-command-line-shell\/\">X Shell <\/a>&#8211; new commandline shell which supports scripting<\/li>\n<li><a href=\"https:\/\/fd.xuwubk.eu.org:443\/http\/dev.mysql.com\/doc\/refman\/5.7\/en\/document-store.html\">User guides, tutorials<\/a>, demo app\n<\/li>\n<li>&#8230;<\/li>\n<\/ul>\n<h3>Selling point readability<\/h3>\n<p>\nThe off the records sales engineers question was: why would one use the X DevAPI, given that it is only a dump SQL wrapper? Let a developer write some code to fetch all products from a collection that have a price higher than 1.2 Euros:<br \/>\n<code><\/p>\n<pre>\r\nproducts.find(\"price &gt; 1.2\").exeucte();\r\n<\/pre>\n<p><\/code><br \/>\nUpon execution, the Connectors (drivers) send the query to the X server plugin which translates it to some SQL and executes it. The SQL statement makes use of ANSI\/ISO SQL standard JSON features which MySQL 5.7.12+ supports:<br \/>\n<code><\/p>\n<pre>\r\nSELECT product FROM products WHERE\r\n   JSON_UNQUOTE(JSON_EXTRACT(product, \"$.price\")) &gt; 1.2;\r\n<\/pre>\n<p><\/code><br \/>\nThere is no technical value in this. Schemaless? Works with SQL. JSON processing? Works with SQL, too. The X DevAPI selling point is readability. But none of the available features today and none of feature on the short term roadmap has the potential to add more value to the X DevAPI.\n<\/p>\n<h3>Faceted search: let daddy sew a coat<\/h3>\n<p>\nFaceted search supports exploring large amounts of data by displaying summaries about various partitions of the data and later allowing to narrow the navigation to a specific partition. Let there be a father that wants to sew a rain coat exactly as the one below for his one year old daughter. <\/p>\n<div align=\"center\">\n<a data-flickr-embed=\"true\"  href=\"https:\/\/fd.xuwubk.eu.org:443\/https\/www.flickr.com\/photos\/ulfwendel\/28379778521\" title=\"Self-made baby rain coat\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/fd.xuwubk.eu.org:443\/https\/c2.staticflickr.com\/9\/8772\/28379778521_8d4729c1ba_n.jpg\" width=\"320\" height=\"213\" alt=\"Self-made baby rain coat\"\/><\/a><script async src=\"\/\/fd.xuwubk.eu.org:443\/http\/embedr.flickr.com\/assets\/client-code.js\" charset=\"utf-8\"><\/script>\n<\/div>\n<p>Daddy opens his web browser in the middle of the night after having completed all baby care and feeding duties and starts to search for fabric in an online retailer. But what to search for, how to quicky find the best fabric out of 100.000 offered by the online store? Many stores will display a search box and a faceted navigation at the left side. Possible facets (partitions, dimensions) are:<\/p>\n<ul>\n<li>Material\n<ul>\n<li>Blends (16647)<\/li>\n<li>Cotton (16762)<\/li>\n<li>&#8230;<\/li>\n<\/ul>\n<\/li>\n<li>Usage\n<ul>\n<li>Baby (19913)<\/li>\n<li>Dress (20005)<\/li>\n<li>&#8230;<\/li>\n<\/ul>\n<\/li>\n<li>Price\n<ul>\n<li>Less than 10 Euros (13815)<\/li>\n<li>10-20 Euros (16207)<\/li>\n<li>&#8230;<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<h3>A faceted document search using SQL<\/h3>\n<p>\nAssume the products are stored in the database using (JSON) documents. All documents are in JSON column called &#8220;product&#8221; of a table called &#8220;products&#8221;. How to get the totals for all the facets using SQL?<br \/>\n<code><\/p>\n<pre>\r\n{\r\n  \"title\": \"Beach motive M182-16\"\r\n  \"description\": ...  \r\n  \"weight\": ...\r\n  \"material\": \"cotton\"\r\n  ...\r\n}\r\n<\/pre>\n<p><\/code><br \/>\nThe SQL for one facet is pretty much straight-forward:<br \/>\n<code><\/p>\n<pre>\r\nSELECT \r\n  JSON_UNQUOTE(JSON_EXTRACT(product, \"$.material\")) AS facet_value, \r\n  COUNT(*) AS facet_count \r\nFROM products \r\nWHERE JSON_EXTRACT(product, \"$.material\") IS NOT NULL \r\nGROUP BY JSON_EXTRACT(product, \"$.material\")\r\n<\/pre>\n<p><\/code><br \/>\nTo get the values for more than one facet using no more than one query call in your application, combine the results for each facet using UNION ALL. Add a column &#8220;facet&#8221; to mark the orgininating facet in the combined row result.<br \/>\n<code><\/p>\n<pre>\r\n  SELECT \r\n    \"material\" AS facet, \r\n    JSON_UNQUOTE(JSON_EXTRACT(product, \"$.material\")) AS facet_value, \r\n    COUNT(*) AS facet_count \r\n  FROM products \r\n  WHERE JSON_EXTRACT(product, \"$.material\") IS NOT NULL \r\n  GROUP BY JSON_EXTRACT(product, \"$.material\") \r\nUNION ALL \r\n  SELECT \r\n   \"usage\" AS facet, \r\n   JSON_UNQUOTE(JSON_EXTRACT(product, \"$.usage\")) AS facet_value, \r\n   COUNT(*) AS facet_count \r\n  FROM products \r\n  WHERE JSON_EXTRACT(product, \"$.usage\") IS NOT NULL \r\n  GROUP BY JSON_EXTRACT(product, \"$.usage\")\r\n<\/pre>\n<p><\/code>\n<\/p>\n<h3>Speeding it up and shortening the SQL<\/h3>\n<p>\nThere&#8217;s quite a bit to come and the length of the SQL statement will grow. Let&#8217;s shorten it a bit by adding <a href=\"https:\/\/fd.xuwubk.eu.org:443\/https\/dev.mysql.com\/doc\/refman\/5.7\/en\/create-table-generated-columns.html\">generated columns<\/a> (5.7.6) and speed things up using indicies. Long story short, here&#8217;s the CREATE TABLE statement I&#8217;m using for this blog post:<br \/>\n<code><\/p>\n<pre>\r\nCREATE TABLE `products` (\r\n  `product_id` int(11) NOT NULL AUTO_INCREMENT,\r\n  `product` json DEFAULT NULL,\r\n  `facet_size` varchar(255) GENERATED ALWAYS AS (json_unquote(json_extract(`product`,'$.size'))) VIRTUAL,\r\n  `facet_material` varchar(255) GENERATED ALWAYS AS (json_unquote(json_extract(`product`,'$.material'))) VIRTUAL,\r\n  `facet_usage` varchar(255) GENERATED ALWAYS AS (json_unquote(json_extract(`product`,'$.usage'))) VIRTUAL,\r\n  `facet_gender` varchar(255) GENERATED ALWAYS AS (json_unquote(json_extract(`product`,'$.gender'))) VIRTUAL,\r\n  `facet_price` double GENERATED ALWAYS AS (json_unquote(json_extract(`product`,'$.price'))) VIRTUAL,\r\n  PRIMARY KEY (`product_id`),\r\n  KEY `idx_facet_size` (`facet_size`),\r\n  KEY `idx_facet_material` (`facet_material`),\r\n  KEY `idx_facet_usage` (`facet_usage`),\r\n  KEY `idx_facet_gender` (`facet_gender`),\r\n  KEY `idx_face_price` (`facet_price`)\r\n)\r\n<\/pre>\n<p><\/code>\n<\/p>\n<p>\nUsing the above the query to gather one facets totals is shortened to:<br \/>\n<code><\/p>\n<pre>\r\nSELECT \r\n  facet_material AS facet_value, \r\n  count(*) AS facet_count \r\nFROM products \r\nWHERE facet_material IS NOT NULL \r\nGROUP BY facet_material\r\n<\/pre>\n<p><\/code>\n<\/p>\n<h3>A range based face: price<\/h3>\n<p>\nThe price facet in the fabric online shop is range based. In Germany, the home of the example daddy, fabrics are sold per meter. The typical price of a fabric ranges from a few Euros up to 100 Euros with the majority topping around 40 Euros. The show owner wants to display totals for prices from 0..10, 10..20, 20..50, 50+ Euros. Note that the ranges are of different size.\n<\/p>\n<p>\nOf course, SQL can deliver! For example, use a subquery and CASE expressions. Stackoverflow will guide a developer within seconds.<br \/>\n<code><\/p>\n<pre>\r\nSELECT \r\n  \"price\" AS facet, \r\n  fpsub.facet_price_range AS facet_value, \r\n  COUNT(*) AS facet_count \r\nFROM \r\n  (SELECT \r\n    (CASE \r\n      WHEN facet_price BETWEEN 0 AND 10 THEN \"0-10\"\r\n      WHEN facet_price BETWEEN 10.01 AND 20 THEN \"10-20\" \r\n      WHEN facet_price BETWEEN 20.01 AND 50 THEN \"20-50\" \r\n      ELSE \"50+\" \r\n    END) AS facet_price_range \r\n  FROM products \r\n  WHERE facet_price IS NOT NULL) AS fpsub\r\nGROUP BY fpsub.facet_price_range\r\n<\/pre>\n<p><\/code><br \/>\nCombine all the individual facet queries using UNION ALL and you have the basic search.\n<\/p>\n<h3>Refinement: user selects a facet value<\/h3>\n<p>\nExample Daddy made up his mind on the material for the inner jacket, selects &#8220;cotton&#8221; and enters &#8220;Beach&#8221; into the search box. Likely, the users expectation is that the selection will have no impact on the totals shown for any other material but cotton. In other words the material face query needs to be split in two: one to count the total for &#8220;cotton&#8221; and &#8220;Beach&#8221; and another one for the totals of all the other facet values. Note that I ignore the problem of full text search and use LIKE exactly how one should not use it.<br \/>\n<code><\/p>\n<pre>\r\n  SELECT \r\n    facet_material, \r\n    count(*) \r\n  FROM products \r\n  WHERE \r\n    facet_material = \"cotton\" AND \r\n    JSON_UNQUOTE(JSON_EXTRACT(product, '$.title')) LIKE \"%Beach%\"  \r\n  GROUP BY facet_material \r\nUNION ALL \r\n  SELECT \r\n    facet_material, \r\n    COUNT(*) \r\n  FROM products \r\n  WHERE \r\n   facet_material != \"cotton\" AND\r\n   facet_material IS NOT NULL   \r\n  GROUP BY facet_material\r\n<\/pre>\n<p><\/code>\n<\/p>\n<h3>The grand total<\/h3>\n<p>\nProof: you can use SQL for the job.<br \/>\n<code><\/p>\n<pre>\r\nSELECT \r\n    \"material\" AS facet,\r\n    facet_material AS facet_value, \r\n    COUNT(*) AS facet_count\r\n  FROM products \r\n  WHERE \r\n    facet_material = \"cotton\" AND \r\n    JSON_UNQUOTE(JSON_EXTRACT(product, '$.title')) LIKE \"%Beach%\"  \r\n  GROUP BY facet_material \r\nUNION ALL \r\n  SELECT\r\n    \"material\" AS facet,\r\n    facet_material AS facet_value, \r\n    COUNT(*) AS facet_count\r\n  FROM products \r\n  WHERE \r\n   facet_material IS NOT NULL AND\r\n   facet_material != \"cotton\" \r\n  GROUP BY facet_material\r\nUNION ALL\r\n  SELECT \r\n    \"price\" AS facet, \r\n    fpsub.facet_price_range AS facet_value, \r\n    COUNT(*) AS facet_count \r\n  FROM \r\n    (SELECT \r\n      (CASE \r\n        WHEN facet_price BETWEEN 0 AND 10 THEN \"0-10\"\r\n        WHEN facet_price BETWEEN 10.01 AND 20 THEN \"10-20\" \r\n        WHEN facet_price BETWEEN 20.01 AND 50 THEN \"20-50\" \r\n        ELSE \"50+\" \r\n      END) AS facet_price_range \r\n    FROM products\r\n    WHERE facet_price IS NOT NULL) AS fpsub\r\n  GROUP BY fpsub.facet_price_range\r\nUNION ALL\r\n  SELECT\r\n   \"size\" AS facet,\r\n   facet_size AS facet_value,\r\n   COUNT(*) AS facet_count\r\n  FROM products\r\n  WHERE\r\n    facet_size IS NOT NULL\r\n  GROUP BY\r\n    facet_size\r\nUNION ALL\r\n  SELECT \r\n    \"usage\" AS facet,\r\n    facet_usage AS facet_value,\r\n    COUNT(*) AS facet_count\r\n  FROM\r\n    products\r\n  WHERE\r\n    facet_usage IS NOT NULL\r\n  GROUP BY \r\n    facet_usage\r\nUNION ALL\r\n  SELECT    \r\n    \"gender\" AS facet,\r\n    facet_gender AS facet_value,\r\n    COUNT(*) AS facet_count\r\n  FROM \r\n    products\r\n  WHERE\r\n    facet_gender IS NOT NULL\r\n  GROUP BY\r\n    facet_gender\r\nORDER BY \r\n facet, \r\n facet_value\r\n\r\n+----------+------------------+-------------+\r\n| facet    | facet_value      | facet_count |\r\n+----------+------------------+-------------+\r\n| gender   | female           |       33387 |\r\n| gender   | male             |       33327 |\r\n| material | blends           |       16647 |\r\n| material | designer fabrics |       16703 |\r\n| material | knits            |       16739 |\r\n| material | silk             |       16594 |\r\n| material | wool             |       16555 |\r\n| price    | 0-10             |       13815 |\r\n| price    | 10-20            |       16207 |\r\n| price    | 20-50            |       55668 |\r\n| price    | 50+              |       14310 |\r\n| size     | 114cm            |       14464 |\r\n| size     | 140cm            |       14366 |\r\n| size     | L                |       14009 |\r\n| size     | M                |       14303 |\r\n| size     | S                |       14327 |\r\n| size     | XL               |       14211 |\r\n| size     | XXL              |       14320 |\r\n| usage    | baby             |       19913 |\r\n| usage    | dress            |       20005 |\r\n| usage    | inside           |       19929 |\r\n| usage    | outdoor          |       20166 |\r\n+----------+------------------+-------------+\r\n22 rows in set (0,34 sec)\r\n<\/pre>\n<p><\/code><br \/>\nThe execution time of 0.34s? Well, this is a source build of MySQL running in a VM on a notebook. No server parameters set. Inacceptable result for interactive search experience.\n<\/p>\n<h3>How the DevAPI could make a difference<\/h3>\n<p>\nA DevAPI counterpart of the above could read very different.<br \/>\n<code><\/p>\n<pre>\r\nproducts\r\n  .find(\"title LIKE :search\")\r\n  .facets({\r\n   \"usage\", \r\n   \"material\", \r\n   \"gender\", \r\n   \"size\",\r\n   \"price\" {\r\n     \"0-10\": {\"min\": 0, \"max\": 10\"}\r\n     ...\r\n   }\r\n  .bind(\"search\", \"%Beach%\")\r\n<\/pre>\n<p><\/code><br \/>\nAs a developer, I could immediately grasp what the higher level DevAPI does. I would have no clue what the SQL is about if the column aliases would not hint me. Note also that the DevAPI and the X plugin are not limited to SQL. They could, if they want, use the lower level MySQL internal data access APIs. The internal APIs can be faster than SQL, way faster. SQL runs on top of them.\n<\/p>\n<p>\nDear Sales, All, the DevAPI is only a tiny bit of the MySQL document store story. You may see it as a dump SQL wrapper of little value. Or, as a chance to offer an alternative query API that solves web developer problems much easier than raw SQL could do.\n<\/p>\n<p>Happy hacking!\n<\/p>\n<p align=\"center\"><a href=\"https:\/\/fd.xuwubk.eu.org:443\/http\/twitter.com\/#!\/Ulf_Wendel\">@Ulf_Wendel <img decoding=\"async\" src=\"https:\/\/fd.xuwubk.eu.org:443\/http\/blog.ulf-wendel.de\/images\/twitter.png\" alt=\"Follow me on Twitter\" align=\"middle\" \/><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Faceted search or faceted navigation is a highly praised and widely use search pattern. And, it is a great reply to an off the records sales engineering question. MySQL finally has some document store features built-in. A bit of a &hellip; <a href=\"https:\/\/fd.xuwubk.eu.org:443\/http\/blog.ulf-wendel.de\/2016\/faceted-search-devapi-matter-one-day\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,17,1],"tags":[],"class_list":["post-3412","post","type-post","status-publish","format-standard","hentry","category-mysql","category-planetmysql-en","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/fd.xuwubk.eu.org:443\/http\/blog.ulf-wendel.de\/wp-json\/wp\/v2\/posts\/3412","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/fd.xuwubk.eu.org:443\/http\/blog.ulf-wendel.de\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/fd.xuwubk.eu.org:443\/http\/blog.ulf-wendel.de\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/fd.xuwubk.eu.org:443\/http\/blog.ulf-wendel.de\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/fd.xuwubk.eu.org:443\/http\/blog.ulf-wendel.de\/wp-json\/wp\/v2\/comments?post=3412"}],"version-history":[{"count":19,"href":"https:\/\/fd.xuwubk.eu.org:443\/http\/blog.ulf-wendel.de\/wp-json\/wp\/v2\/posts\/3412\/revisions"}],"predecessor-version":[{"id":3431,"href":"https:\/\/fd.xuwubk.eu.org:443\/http\/blog.ulf-wendel.de\/wp-json\/wp\/v2\/posts\/3412\/revisions\/3431"}],"wp:attachment":[{"href":"https:\/\/fd.xuwubk.eu.org:443\/http\/blog.ulf-wendel.de\/wp-json\/wp\/v2\/media?parent=3412"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/fd.xuwubk.eu.org:443\/http\/blog.ulf-wendel.de\/wp-json\/wp\/v2\/categories?post=3412"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/fd.xuwubk.eu.org:443\/http\/blog.ulf-wendel.de\/wp-json\/wp\/v2\/tags?post=3412"}],"curies":[{"name":"wp","href":"https:\/\/fd.xuwubk.eu.org:443\/https\/api.w.org\/{rel}","templated":true}]}}