How to do a Simple Query Analysis and Optimization in Couchbase

I’ve joined an Indonesian Startup named Mapan since this September, leaving my previous role at a multinational consulting company. And to be honest, in here there are so many things to learn and a whole new playground to play with, and let me share one of new thing i’ve learn during my one month service here.

After im spending some time with mandatory company induction and way of working, i started with some technical matters and found a quite interesting issue regarding slow queries to Couchbase database, thats why im share step by step on how to analyze and gives workarond so that perhaps it wont happen again.

Dislaimer : on the time this writing is created, im working with Couchbase for less than 1 month. This writing is still open for discussion and improvement :-D .

Study Case #1

SELECT inventory.*
FROM inventory
WHERE inventory.`key` LIKE "ih:RUMA_0101_AABY12349%"
limit 1000

With almost 1 million data, it took more than 30seconds to fetch 1000 data which have key that starts with “ih:RUMA_0101_AABY12349”.

First analysis is that corresponding query despite hitting on an index, it still gives a quite amount of time of fetching as you can see on below image. It is happen mostly due to fetching is done first with all the data (85% of time is used for this, around 38 seconds) and do the filtering later on.

First thing that i see is that query is done on “key” field which have a common format, like “key”: “ih:RUMA_0101_AABY12349:1534087762346131”. So, instead of searching based on wildcard, better do searching on split string of “key” field, and create and index based on it.

CREATE INDEX key02_split ON inventory ( split(inventory.`key`, ":")[1] )   
PARTITION BY HASH (split(inventory.`key`, ":")[1]) where split(inventory.`key`, ":")[1] is not null

And change the query needed into below,

SELECT inventory.*
FROM inventory
WHERE split(inventory.`key`, ":")[1] = 'RUMA_0101_AABY12349'
limit 1000

Drastically reducing the query time into 80ms, as on below screenshot.

Study Case #2

SELECT `inventory_trx`.* FROM `inventory_trx` 
WHERE client = "RUMA" 
and movement_type in ["IB001", "IB002", "IB003", "OB001", "OB002", "TF001", "TF002", 
"IA001", "IA003", "IA005", "IA007", "IA002", "IA004"] 
and transaction_timestamp between 1525107600 and 1537808399 and  
(origin.code = "0101" or destination.code = "0101")  
order by transaction_timestamp desc offset 0 limit 1000

Again, same issue happen here. Despite hitting get_stock_mutation index, it still takes some time for fetching. On below screenshot, it shows almost 36 seconds only for fetching.

Again, almost the same approach is used for this case. We can see which field is used on WHERE parameter, and start indexing it. The only difference is that it have range query on transaction_timestamp field and also order by criteria, which will make it a little bit complicated.

It took not only query optimization, but also some negotiation to implements some changes on User Interface. On previous query, transaction_timestamp parameter doesnt have any limitation on start and end date, therefore make it harder to do indexing. After consulting with user, they are agree that there should be a one month limitation for doing range query.

After user agree with changes on UI, next is to create an Index on Couchbase for corresponding query.

CREATE INDEX inventory_trx_ix01 
ON inventory_trx ( client, movement_type, DATE_PART_MILLIS(transaction_timestamp * 1000, "month"), 
origin.code, destination.code, DATE_PART_MILLIS(transaction_timestamp * 1000, "year"))

Tweaking the query into below,

SELECT `inventory_trx`.* FROM `inventory_trx` 
WHERE client = "RUMA" 
and movement_type in ["IB001", "IB002", "IB003", "OB001", "OB002", 
"TF001", "TF002", "IA001", "IA003", "IA005", "IA007", "IA002", "IA004"] 
and DATE_PART_MILLIS(transaction_timestamp * 1000, "month") = 9
and DATE_PART_MILLIS(transaction_timestamp * 1000, "year") = 2018
and  (origin.code = "0101" or destination.code = "0101")
limit 1000

Reducing query time needed significantly,


How to Convert from a Point to a KML files and Generate a Circle with Radius on Each Point

Yesterday I got a challenging assignment to generate a telco network coverage map. But the only data i got is BTS (Base Transceiver Station) location, which is only latitude, longitude and coverage radius given on excel file. I need to convert those excel sheet data into a google map page, and displayed it to subscribers accordingly.

The biggest problem that i had is the number of data is quite big, i need to map more than 20k BTS location and need displayed it fast and without lagging. So a simple google maps script wont work because google maps have a limitation for this.

So i need to create a workaround, and after researching for quite some times i pick KML and Google FusionTable. KML data format for creating radius, and Google FusionTable for displaying it.

This is my PHP code for converting latitude, longitude and radius into KML code.

$lats = array('4.53191944444444','5.34593888888889');
$longs = array('97.93600000000001','95.99299999999999');
$meter = 2000; 
$kml = '<?xml version="1.0" encoding="UTF-8"?><kml xmlns="" xmlns:atom=""><Document>        
            <Style id="style0">

for($i = 0; $i < count($lats); $i++) {

    $lat = $lats[$i];
    $long = $longs[$i];

    // Get circle coordinates
    $coordinatesList = getCirclecoordinates($lat, $long, $meter);

    // Output
    $kml  .= '<Placemark><name>Circle '.$i.'</name><styleUrl>#style0</styleUrl><Polygon><outerBoundaryIs><LinearRing><coordinates>'.$coordinatesList.'</coordinates></LinearRing></outerBoundaryIs></Polygon></Placemark>';

$kml .= '</Document></kml>';
function getCirclecoordinates($lat, $long, $meter) {
  // convert coordinates to radians
  $lat1 = deg2rad($lat);
  $long1 = deg2rad($long);
  $d_rad = $meter/6378137;
  $coordinatesList = "";
  // loop through the array and write path linestrings
  for($i=0; $i<=360; $i+=3) {
    $radial = deg2rad($i);
    $lat_rad = asin(sin($lat1)*cos($d_rad) + cos($lat1)*sin($d_rad)*cos($radial));
    $dlon_rad = atan2(sin($radial)*sin($d_rad)*cos($lat1), cos($d_rad)-sin($lat1)*sin($lat_rad));
    $lon_rad = fmod(($long1+$dlon_rad + M_PI), 2*M_PI) - M_PI;
    $coordinatesList .= rad2deg($lon_rad).",".rad2deg($lat_rad).",0 ";
  return $coordinatesList;

$fp = fopen('e:\lele.kml', 'w');
fwrite($fp, $kml);

After KML is done, i just need to upload it to Google FusionTable and display it on my web page.


Error Apache HTTPD, No protocol handler was valid for the URL

Got weird error today when implementing apache httpd as a reverse proxy on my RHEL environment, here is the complere stacktrace

AH01144: No protocol handler was valid for the URL /url (scheme 'http'). 
If you are using a DSO version of mod_proxy, make sure the proxy submodules are included in the configuration using LoadModule.

The only module that i unremark on httpd.conf is

LoadModule proxy_module modules/

I found out that i need to unremark other module, that is mod_proxy_http

LoadModule proxy_http_module modules/

After unremarking mod_proxy_http, everything runs well.


Error java.sql.SQLException: Numeric Overflow when Connect to Oracle using Hibernate

Got weird error today, here is the complere stacktrace

2018-01-31 12:14:56 WARN  SqlException: - SQL Error: 17026, SQLState: 99999
2018-01-31 12:14:56 ERROR SqlException: - Numeric Overflow

Caused by: java.sql.SQLException: Numeric Overflow
	at oracle.jdbc.driver.NumberCommonAccessor.throwOverflow(
	at oracle.jdbc.driver.NumberCommonAccessor.getInt(
	at oracle.jdbc.driver.GeneratedStatement.getInt(
	at oracle.jdbc.driver.GeneratedScrollableResultSet.getInt(
	at oracle.jdbc.driver.GeneratedResultSet.getInt(
	at org.apache.tomcat.dbcp.dbcp.DelegatingResultSet.getInt(
	at org.apache.tomcat.dbcp.dbcp.DelegatingResultSet.getInt(
	at org.hibernate.type.descriptor.sql.IntegerTypeDescriptor$2.doExtract(
	at org.hibernate.type.descriptor.sql.BasicExtractor.extract(
	at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(
	at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(
	at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(
	at org.hibernate.type.AbstractStandardBasicType.hydrate(
	at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(
	at org.hibernate.loader.Loader.loadFromResultSet(
	at org.hibernate.loader.Loader.instanceNotYetLoaded(
	at org.hibernate.loader.Loader.getRow(
	at org.hibernate.loader.Loader.getRowFromResultSet(
	at org.hibernate.loader.Loader.processResultSet(
	at org.hibernate.loader.Loader.doQuery(
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(
	at org.hibernate.loader.Loader.doList(
	... 75 more	

And here is my java class,

public class Class {
   private Integer value;

   // other setter and getter

How to solve the issue is actually quite simple, it’s all happen because im having a data on database which is bigger than Integer’s max value. Updating my bean from Integer to BigDecimal solve the problem,

public class Class {
   private BigDecimal value;

   // other setter and getter

Create a Simple Autocomplete Textfield using Spring MVC, JQuery, EasyAutocomplete and Hibernate

Several weeks ago i got a request from one of my student, Albi Dwi Haryono. He was asking how to create an autocomplete textbox on JSP page using Spring MVC. So i create this tutorial for helping him and who knows, perhaps also helping other people.

First as usual, create a table Test

  `field1` varchar(40) NOT NULL,
  PRIMARY KEY (`field1`)

create a simple maven file which consist of Spring MVC library, json, MySql and Hibernate,

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="" xmlns:xsi="" xsi:schemaLocation="">



        <!-- Spring dependencies -->
        <!-- json request -->        
        <!-- log4j -->   
        <!-- Hibernate -->
        <!-- MySQL database driver --> 
        <!-- dbcp2 -->   


Next is create configuration file, applicationContext.xml, dispatcher-servlet.xml and registering all thos files on web.xml. I wont explain too much here, just see my github pages for a much more detailed code.

As for java files, i have a java bean Test, as representation for table Test

package com.edw.springautocomplete.bean;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Table(name = "test")
public class Test implements Serializable {

    public String field1;

    public String getField1() {
        return field1;

    public void setField1(String field1) {
        this.field1 = field1;

And finally a simple controller file, for serving default index page, and also for serving json file for autocomplete purpose.

package com.edw.springautocomplete.controller;

import com.edw.springautocomplete.bean.Test;
import java.util.List;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

public class IndexController {

    private SessionFactory sessionFactory;
    @RequestMapping(value = "/", method = RequestMethod.GET)
    public String index() {
        return "index";

    @RequestMapping(value = "/field1", method = RequestMethod.GET)
    public @ResponseBody List<Test> field1() {
        return sessionFactory.openSession()
                .createQuery("from Test")

But, the most important file is the jsp page,

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
        <link href="" rel="stylesheet" type="text/css">
        <script src="//"></script>
        <script src="" type="text/javascript" ></script>
        <input id="field1"/>
        var options = {
                url: "${pageContext.request.contextPath}/field1",
                getValue: "field1",
                list: {
                        match: {
                                enabled: true

The result would be like this,

For the complete sourcecode, can be downloaded here.