Social networking type queries with NDB (part 3)

In the previous 2 posts of this series, we basically talked about how to execute social networking type queries using SQL IN clause and how handle multiple columns IN clause. In this last post on the topic, I will introduce the notion of NDB API filters, although I don’t consider myself as an NDB API expert. Filters are to NDB API the equivalent WHERE clause in SQL. The point is that the filters can be nested and they are sent to the storage nodes only when the transaction is executed.

As an example, let’s consider the following table:

Create Table: CREATE TABLE `MultiColPK` (
  `region_id` int(11) NOT NULL DEFAULT '0',
  `application_id` int(11) NOT NULL DEFAULT '0',
  `first_name` varchar(30) NOT NULL DEFAULT '',
  `payload` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`region_id`,`application_id`,`first_name`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1

with:

mysql> select * from MultiColPK;
+-----------+----------------+------------+------------+
| region_id | application_id | first_name | payload    |
+-----------+----------------+------------+------------+
|         2 |              1 | Yves       | eeeeeeeeee |
|         1 |              2 | Arun       | bbbbbbbbbb |
|         1 |              1 | Matt       | aaaaaaaaaa |
|         1 |              2 | Chris      | ffffffffff |
|         1 |              3 | Brian      | cccccccccc |
|         2 |              3 | Brian      | dddddddddd |
|         2 |              2 | Anand      | gggggggggg |
+-----------+----------------+------------+------------+
7 rows in set (0.01 sec)

The query we will try to reproduce with the NDB API is the following:

mysql> select payload from MultiColPK where (region_id,application_id,first_name) in ((2,3,'Brian'),(1,1,'Matt'),(2,1,'Yves'));
+------------+
| payload    |
+------------+
| eeeeeeeeee |
| aaaaaaaaaa |
| dddddddddd |
+------------+
3 rows in set (0.00 sec)

For this example, I modified the ndbapi_scan.cpp example in the MySQL source tree. The IN clause converted in NDB API filter is presented below.

if(filter.begin(NdbScanFilter::OR) < 0  ||
         filter.begin(NdbScanFilter::AND) < 0 ||
           filter.cmp(NdbScanFilter::COND_EQ, column_region_id, &region_id_1) < 0 ||
	   filter.cmp(NdbScanFilter::COND_EQ, column_application_id, &application_id_1) < 0 ||
           filter.cmp(NdbScanFilter::COND_LIKE, column_first_name, "Brian",5) < 0 ||
         filter.end() ||
         filter.begin(NdbScanFilter::AND) < 0 ||
           filter.cmp(NdbScanFilter::COND_EQ, column_region_id, &region_id_2) < 0 ||
	   filter.cmp(NdbScanFilter::COND_EQ, column_application_id, &application_id_2) < 0 ||
           filter.cmp(NdbScanFilter::COND_LIKE, column_first_name, "Matt",4) < 0 ||
         filter.end() ||
         filter.begin(NdbScanFilter::AND) < 0 ||
           filter.cmp(NdbScanFilter::COND_EQ, column_region_id, &region_id_3) < 0 ||
	   filter.cmp(NdbScanFilter::COND_EQ, column_application_id, &application_id_3) < 0 ||
           filter.cmp(NdbScanFilter::COND_LIKE, column_first_name, "Yves",4) < 0 ||	   
         filter.end() ||
       filter.end() < 0)

The handling of strings in filter is a bit tricky, Johan has a blog post on this topic. By the way, the unmodified ndbapi_scan.cpp example does not work as it supposed to, the filter on the COLOR uses unpadded strings. In our case, the example is functional and produce the following results:

$ ./ndbapi_scan localhost
Connecting to cluster.
connect to storage nodes.
Retrieving colnums.
Creating the filter

eeeeeeeeee

aaaaaaaaaa

dddddddddd

My modified ndbapi_scan.cpp can be found here. Download the MySQL source, compile it, install it, start a cluster, create and populate the table, replace the ndbapi_scan.cpp with my version, type make in the ndbapi_scan directory and execute.