{"action":"created","issue":{"url":"https://api.github.com/repos/orientechnologies/orientdb/issues/2706","labels_url":"https://api.github.com/repos/orientechnologies/orientdb/issues/2706/labels{/name}","comments_url":"https://api.github.com/repos/orientechnologies/orientdb/issues/2706/comments","events_url":"https://api.github.com/repos/orientechnologies/orientdb/issues/2706/events","html_url":"https://github.com/orientechnologies/orientdb/issues/2706","id":40861754,"number":2706,"title":"SELECT COUNT(field) is very slow","user":{"login":"Athelionas","id":7241355,"avatar_url":"https://avatars.githubusercontent.com/u/7241355?v=3","gravatar_id":"","url":"https://api.github.com/users/Athelionas","html_url":"https://github.com/Athelionas","followers_url":"https://api.github.com/users/Athelionas/followers","following_url":"https://api.github.com/users/Athelionas/following{/other_user}","gists_url":"https://api.github.com/users/Athelionas/gists{/gist_id}","starred_url":"https://api.github.com/users/Athelionas/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Athelionas/subscriptions","organizations_url":"https://api.github.com/users/Athelionas/orgs","repos_url":"https://api.github.com/users/Athelionas/repos","events_url":"https://api.github.com/users/Athelionas/events{/privacy}","received_events_url":"https://api.github.com/users/Athelionas/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/orientechnologies/orientdb/labels/enhancement","name":"enhancement","color":"84b6eb"}],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":5,"created_at":"2014-08-21T23:35:30Z","updated_at":"2015-10-05T13:11:19Z","closed_at":null,"body":"OrientDB 1.7.8\r\n\r\n<pre>PROPERTIES\r\n+-------------+-------------+-------------------+-----------+----------+----------+\r\n| NAME        | TYPE        | LINKED TYPE/CLASS | MANDATORY | READONLY | NOT NULL |\r\n+-------------+-------------+-------------------+-----------+----------+----------+\r\n| birthday    | DATETIME    | null              | true      | false    | true     |\r\n| firstName   | STRING      | null              | true      | false    | true     |\r\n| password    | STRING      | null              | true      | false    | true     |\r\n| salt        | STRING      | null              | true      | false    | true     |\r\n| active      | BOOLEAN     | null              | true      | false    | true     |\r\n| groups      | LINKSET     | Group             | true      | false    | true     |\r\n| email       | STRING      | null              | true      | false    | true     |\r\n| username    | STRING      | null              | true      | false    | true     |\r\n| lastName    | STRING      | null              | true      | false    | true     |\r\n+-------------+-------------+-------------------+-----------+----------+----------+\r\n\r\nINDEXES\r\n+-------------------------------+----------------+\r\n| NAME                          | PROPERTIES     |\r\n+-------------------------------+----------------+\r\n| UserEmailIndex                | email          |\r\n| UserSaltIndex                 | salt           |\r\n| UserUsernameIndex             | username       |\r\n+-------------------------------+----------------+\r\n</pre>\r\n\r\nThe following query executes very slowly:\r\n\r\n<pre>orientdb > select count(username) from User where username like '%12%'          \r\n\r\n----+-----+-----\r\n#   |@RID |count\r\n----+-----+-----\r\n0   |#-2:0|49401\r\n----+-----+-----\r\n\r\n1 item(s) found. Query executed in 7.768 sec(s).</pre>\r\n\r\n<pre>orientdb > explain select count(username) from User where username like '%12%'\r\n\r\nProfiled command '{documentReads:1000000,current:#11:999999,documentAnalyzedCompatibleClass:1000000,recordReads:1000000,projectionElapsed:4,fetchingFromTargetElapsed:7829,evaluated:1000000,groupByElapsed:0,elapsed:7829.875,resultType:collection,resultSize:1}' in 7.832000 sec(s):\r\n{\"@type\":\"d\",\"@version\":0,\"documentReads\":1000000,\"current\":\"#11:999999\",\"documentAnalyzedCompatibleClass\":1000000,\"recordReads\":1000000,\"projectionElapsed\":4,\"fetchingFromTargetElapsed\":7829,\"evaluated\":1000000,\"groupByElapsed\":0,\"elapsed\":7829.875,\"resultType\":\"collection\",\"resultSize\":1,\"@fieldTypes\":\"documentReads=l,documentAnalyzedCompatibleClass=l,recordReads=l,projectionElapsed=l,fetchingFromTargetElapsed=l,evaluated=l,groupByElapsed=l,elapsed=f\"}</pre>\r\n\r\nInterestingly, query execution time is very close to the following select, so it looks like count also fetches records it touches instead of just checking for existence.\r\n\r\n<pre>orientdb > explain select username from User where username like '%12%'\r\n\r\nProfiled command '{documentReads:1000000,current:#11:999999,documentAnalyzedCompatibleClass:1000000,recordReads:1000000,fetchingFromTargetElapsed:7911,evaluated:1000000,elapsed:7911.414,resultType:collection,resultSize:49401}' in 7.912000 sec(s):\r\n{\"@type\":\"d\",\"@version\":0,\"documentReads\":1000000,\"current\":\"#11:999999\",\"documentAnalyzedCompatibleClass\":1000000,\"recordReads\":1000000,\"fetchingFromTargetElapsed\":7911,\"evaluated\":1000000,\"elapsed\":7911.414,\"resultType\":\"collection\",\"resultSize\":49401,\"@fieldTypes\":\"documentReads=l,documentAnalyzedCompatibleClass=l,recordReads=l,fetchingFromTargetElapsed=l,evaluated=l,elapsed=f\"}</pre>\r\n\r\nResults with select * are very similar."},"comment":{"url":"https://api.github.com/repos/orientechnologies/orientdb/issues/comments/145523088","html_url":"https://github.com/orientechnologies/orientdb/issues/2706#issuecomment-145523088","issue_url":"https://api.github.com/repos/orientechnologies/orientdb/issues/2706","id":145523088,"user":{"login":"mindplay-dk","id":103348,"avatar_url":"https://avatars.githubusercontent.com/u/103348?v=3","gravatar_id":"","url":"https://api.github.com/users/mindplay-dk","html_url":"https://github.com/mindplay-dk","followers_url":"https://api.github.com/users/mindplay-dk/followers","following_url":"https://api.github.com/users/mindplay-dk/following{/other_user}","gists_url":"https://api.github.com/users/mindplay-dk/gists{/gist_id}","starred_url":"https://api.github.com/users/mindplay-dk/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mindplay-dk/subscriptions","organizations_url":"https://api.github.com/users/mindplay-dk/orgs","repos_url":"https://api.github.com/users/mindplay-dk/repos","events_url":"https://api.github.com/users/mindplay-dk/events{/privacy}","received_events_url":"https://api.github.com/users/mindplay-dk/received_events","type":"User","site_admin":false},"created_at":"2015-10-05T13:11:19Z","updated_at":"2015-10-05T13:11:19Z","body":"Same problem here. Testing with a content database of 2.5 million articles, and count queries are essentially unusable. I'm using a LUCENE index."}}