Clearly, since MV is smaller it can explain, why scanning through it is faster.
By full scan I mean issuing series of requests like this SELECT token(site_id,user_id), user_id, data FROM :table WHERE token(site_id, user_id) >= :start AND token(site_id, user_id) <= :stop where (start,stop) pairs span [-2^63, 2:63-1[ When preforming it on table I add " AND site_id = :site_id" On 7 September 2017 at 18:46, DuyHai Doan <doanduy...@gmail.com> wrote: > "As I described, non-filtered full scans on MV are more efficient than > filtered full scans on a table" > > --> But if your MV has the same primary key as your view, how can it be > possible ? > > Can you elaborate on what you mean by "non filtered full scan on MV" ? > Please give us some sample SELECT queries > > On Thu, Sep 7, 2017 at 5:11 PM, Alex Kotelnikov < > alex.kotelni...@diginetica.com> wrote: > >> In this example all tables and materialized views share all columns. What >> is the question? >> >> On 7 September 2017 at 17:26, sha p <shatestt...@gmail.com> wrote: >> >>> There is one more column "data" here in MView? >>> >>> On 7 Sep 2017 7:49 p.m., "DuyHai Doan" <doanduy...@gmail.com> wrote: >>> >>>> The answer of your question is in the error message. For once it's very >>>> clear. The primary key of your materialized view is EXACTLY the same as for >>>> your base table. >>>> >>>> So the question is what's the point creating this materialized view ... >>>> >>>> >>>> >>>> On Thu, Sep 7, 2017 at 4:01 PM, Alex Kotelnikov < >>>> alex.kotelni...@diginetica.com> wrote: >>>> >>>>> Hey. I have a problem creating a materialized view. >>>>> >>>>> My case is quite similar to >>>>> https://issues.apache.org/jira/browse/CASSANDRA-13564 >>>>> but discussion in comments there faded, let me describe by case. >>>>> >>>>> I have a table like >>>>> CREATE TABLE users ( >>>>> site_id int, >>>>> user_id text, >>>>> n int, >>>>> data set<frozen<text>>, >>>>> PRIMARY KEY ((site_id, user_id), n)); >>>>> >>>>> user data is updated and read by PK and sometimes I have to fetch all >>>>> user for some specific site_id. It appeared that full scan by >>>>> token(site_id,user_id) filtered by WHERE site_id = <some id> works much >>>>> slower than unfiltered full scan on >>>>> CREATE MATERIALIZED VIEW users_1 AS >>>>> SELECT site_id, user_id, n, data >>>>> FROM users >>>>> WHERE site_id = 1 AND user_id IS NOT NULL AND n IS NOT NULL >>>>> PRIMARY KEY ((site_id, user_id), n); >>>>> >>>>> yes, you have to do so for each site_id, but it makes such bulk >>>>> fetches much faster. (When I do so, I am always puzzled, why I have to put >>>>> NOT NULL for a part of a primary key). >>>>> And just in case, I tried secondary indices on site_id. For such use >>>>> they improve nothing. >>>>> >>>>> >>>>> But things are changing and we realized that we want to get rid of >>>>> clustering key, n. >>>>> >>>>> DROP MATERIALIZED VIEW users_1; >>>>> DROP TABLE users; >>>>> >>>>> CREATE TABLE users ( >>>>> site_id int, >>>>> user_id text, >>>>> data set<text>, >>>>> PRIMARY KEY ((site_id, user_id))); >>>>> >>>>> CREATE MATERIALIZED VIEW users_1 AS >>>>> SELECT site_id, user_id, data >>>>> FROM users >>>>> WHERE site_id = 1 AND user_id IS NOT NULL >>>>> PRIMARY KEY ((site_id, user_id)); >>>>> >>>>> And here I get the error I listed in the subject. >>>>> InvalidRequest: Error from server: code=2200 [Invalid query] >>>>> message="No columns are defined for Materialized View other than primary >>>>> key" >>>>> >>>>> But why? I still expect scans to be faster with MV. It appears to be >>>>> possible to create a dummy column and using as a clustering key. That's >>>>> ugly. >>>>> -- >>>>> >>>>> Best Regards, >>>>> >>>>> >>>>> *Alexander Kotelnikov* >>>>> >>>>> *Team Lead* >>>>> >>>>> DIGINETICA >>>>> Retail Technology Company >>>>> >>>>> m: +7.921.915.06.28 <+7%20921%20915-06-28> >>>>> >>>>> *www.diginetica.com <http://www.diginetica.com/>* >>>>> >>>> >>>> >> >> >> -- >> >> Best Regards, >> >> >> *Alexander Kotelnikov* >> >> *Team Lead* >> >> DIGINETICA >> Retail Technology Company >> >> m: +7.921.915.06.28 <+7%20921%20915-06-28> >> >> *www.diginetica.com <http://www.diginetica.com/>* >> > > -- Best Regards, *Alexander Kotelnikov* *Team Lead* DIGINETICA Retail Technology Company m: +7.921.915.06.28 *www.diginetica.com <http://www.diginetica.com/>*
