Like query makes very slow taking 5 sec to run this query

{
      "about": null,
      "approved_point": 0,
      "blocked": 0,
      "email": "email",
      "email_verify": 0,
      "facebook_id": null,
      "gender": null,
      "google_id": null,
      "home": {
        "entry_lat": null,
        "entry_lng": null,
        "lat": null,
        "lng": null,
        "name": null,
        "place_id": null
      },
      "last_visited": "2017-11-16 11:47:21",
      "location": "",
      "mobile": null,
      "mobile_verify": 0,
      "name": "anuj",
      "new_password_key": null,
      "office": {
        "entry_lat": null,
        "entry_lng": null,
        "lat": null,
        "lng": null,
        "name": null,
        "place_id": null
      },
      "password": "pass",
      "password_requested_platform": null,
      "password_requested_timestamp": null,
      "permissions": [
        "101"
      ],
      "registered_timestamp": "2014-05-07 12:01:28",
      "social_id": 101,
      "stats": {
        "added": 0,
        "checkin": {
          "others": 0,
          "self": 0
        },
        "edited": 0,
        "favourite": 0,
        "follower": 0,
        "following": 0,
      },
      "unapproved_point": 0,
      "user_id": "usrid",
      "user_name": "anuj",
      "user_photo": "photopath"
    }

Query-

select * from users USE INDEX (registered_timestamp_user_admin_search) where TOSTRING(user_name) LIKE “anu%” OR TOSTRING(name) LIKE “anu%” OR TOSTRING(email) LIKE “anuj%” LIMIT 10;

Indexing-

CREATE INDEX registered_timestamp_user_admin_search ON bucket_name(user_name,email,name);

The above query taking 5 sec in the 1.5 lakh documents
please help @vsr1

Why do you need TO_STRING

CREATE INDEX registered_timestamp_user_admin_search ON bucket_name(DISTINCT ARRAY v FOR v IN [user_name,email,name] END);
SELECT * FROM bucket_name WHERE ANY v IN [user_name,email,name] SATISFIES v LIKE "anu%" END LIMIT 10;
1 Like

thanx @vsr1 , now it’s responding in 25ms