CREATE INDEX `def_name_type` ON `travel-sample`(`name`) WHERE (`_type` = "User");

# use the index

curl -v http://Administrator:password@localhost:8093/query/service -d 'statement=EXPLAIN SELECT * FROM `travel-sample` where _type="User" and name=$name&$name="id@mail.com"'

# does not use the index

curl -v http://Administrator:password@localhost:8093/query/service -d 'statement=EXPLAIN SELECT * FROM `travel-sample` where _type=$type and name=$name&$type="User"&$name="id@mail.com"'

curl -v http://Administrator:password@localhost:8093/query/service -d 'statement=EXPLAIN SELECT * FROM `travel-sample` where _type=$1 and name=$2&args=["User","id@mail.com"]'


# IN clause

curl -v http://Administrator:password@localhost:8093/query/service -d 'statement=EXPLAIN SELECT * FROM `travel-sample` where _type=$type and name IN $name&$type="User"&$name= ["id@mail.com", "id1@mail.com"]'

curl -v http://Administrator:password@localhost:8093/query/service -d 'statement=EXPLAIN SELECT * FROM `travel-sample` where _type=$1 and name IN $2&args=["User", ["id@mail.com", "id1@mail.com"]]'

# prepared statement variant

curl -v http://Administrator:password@localhost:8093/query/service -d 'statement=PREPARE p1 FROM SELECT * FROM `travel-sample` where _type=$type and name=$name'
curl -v http://Administrator:password@localhost:8093/query/service -d 'prepared="p1"&$type="User"&$name="id@mail.com"'

curl -v http://Administrator:password@localhost:8093/query/service -d 'statement=PREPARE p2 FROM SELECT * FROM `travel-sample` where _type=$1 and name=$2'
curl -v http://Administrator:password@localhost:8093/query/service -d 'prepared="p2"&args=["User","id@mail.com"]'

# prepare with values specified

curl -v http://Administrator:password@localhost:8093/query/service -d 'statement=PREPARE p3 FROM SELECT * FROM `travel-sample` where _type=$type and name=$name&$type="User"&$name="id@mail.com"'
curl -v http://Administrator:password@localhost:8093/query/service -d 'prepared="p3"&$type="User1"&$name="id@mail.com"'

curl -v http://Administrator:password@localhost:8093/query/service -d 'statement=PREPARE p4 FROM SELECT * FROM `travel-sample` where _type=$1 and name=$2&args=["User","id@mail.com"]'
curl -v http://Administrator:password@localhost:8093/query/service -d 'prepared="p4"&$type="User1"&$name="id@mail.com"'

# update and delete

curl -v http://Administrator:password@localhost:8093/query/service -d 'statement=EXPLAIN UPDATE `travel-sample` set id = "1" where _type=$type and name=$name&$type="User"&$name="id@mail.com"'

curl -v http://Administrator:password@localhost:8093/query/service -d 'statement=EXPLAIN UPDATE `travel-sample` set id = "1" where _type=$1 and name=$2&args=["User","id@mail.com"]'

curl -v http://Administrator:password@localhost:8093/query/service -d 'statement=EXPLAIN DELETE FROM `travel-sample` where _type=$type and name=$name&$type="User"&$name="id@mail.com"'

curl -v http://Administrator:password@localhost:8093/query/service -d 'statement=EXPLAIN DELETE FROM `travel-sample`  where _type=$1 and name=$2&args=["User","id@mail.com"]'

# subqueries

curl -v http://Administrator:password@localhost:8093/query/service -d 'statement=EXPLAIN SELECT * FROM `beer-sample` WHERE type = "brewery" and city IN (SELECT city FROM `travel-sample` where _type=$type and name=$name)&$type="User"&$name="id@mail.com"'

curl -v http://Administrator:password@localhost:8093/query/service -d 'statement=EXPLAIN SELECT * FROM `beer-sample` WHERE type = "brewery" and city in (SELECT city FROM `travel-sample` where _type=$1 and name=$2)&args=["User","id@mail.com"]'

curl -v http://Administrator:password@localhost:8093/query/service -d 'statement=EXPLAIN SELECT * FROM (SELECT city FROM `travel-sample` where _type=$type and name=$name) as city&$type="User"&$name="id@mail.com"'

curl -v http://Administrator:password@localhost:8093/query/service -d 'statement=EXPLAIN SELECT * FROM  (SELECT city FROM `travel-sample` where _type=$1 and name=$2) as city&args=["User","id@mail.com"]'