Mailing-List: contact user-help@hive.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@hive.apache.org
MIME-Version: 1.0
From: Igor Kuzmenko <f1sherox@gmail.com>
Date: Thu, 4 Aug 2016 17:14:56 +0300
Message-ID: <CAPC1M2iei75SvPqQavSHTp=vS3eAx8Piv1aNULCm36P1xORQFA@mail.gmail.com>
Subject: Hive LIKE predicate. '_' wildcard decrease perfomance
To: user@hive.apache.org
Content-Type: multipart/alternative; boundary=94eb2c061464415f1205393f94d8
archived-at: Thu, 04 Aug 2016 14:15:22 -0000

--94eb2c061464415f1205393f94d8
Content-Type: text/plain; charset=UTF-8

I've got Hive Transactional table 'data_http' in ORC format, containing
around 100.000.000 rows.

When I execute query:

select * from data_http
where res_url like '%mts.ru%'

it completes in 10 seconds.

But executing query

select * from data_http
where res_url like '%mts_ru%'


takes more than 30 minutes.

Why '_' wildcard decrease perfomance?

--94eb2c061464415f1205393f94d8
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">I&#39;ve got Hive Transactional table &#39;data_http&#39;=
=C2=A0in ORC format, containing around 100.000.000 rows.<div><br></div><div=
>When I execute query:=C2=A0</div><blockquote style=3D"margin:0px 0px 0px 4=
0px;border:none;padding:0px"><div><div>select * from data_http</div></div><=
div><div>where res_url like &#39;%<a href=3D"http://mts.ru">mts.ru</a>%&#39=
;</div></div><div><br></div></blockquote>it completes in 10 seconds.<div><b=
r></div><div>But executing query=C2=A0</div><div><br></div><blockquote styl=
e=3D"margin:0px 0px 0px 40px;border:none;padding:0px"><div><div>select * fr=
om data_http</div></div><div><div>where res_url like &#39;%mts_ru%&#39;</di=
v></div></blockquote><br><div>takes more than 30 minutes.=C2=A0</div><div><=
br></div><div>Why &#39;_&#39; wildcard decrease perfomance?</div></div>

--94eb2c061464415f1205393f94d8--