[Solved] DataStax Enterprise 4.6.1 / C* Pagination in Python using cqlengine 0.21.0
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

DataStax Enterprise 4.6.1 / C* Pagination in Python using cqlengine 0.21.0

I am currently trying to paginate query results from DSE 4.6.1 (Cassandra 2.0.12.200) with Python and cqlengine 0.21.0.

My table being queried is:

CREATE TABLE tags_for_search (
  village_id int,
  tag_prefix text,
  tag text,
  time timeuuid,
  author_id int,
  tag_id uuid,
  type text,
  PRIMARY KEY ((village_id, tag_prefix), tag, time)
) WITH CLUSTERING ORDER BY (time DESC) AND
  bloom_filter_fp_chance=0.010000 AND
  caching='KEYS_ONLY' AND
  comment='' AND
  dclocal_read_repair_chance=0.100000 AND
  gc_grace_seconds=864000 AND
  index_interval=128 AND
  read_repair_chance=0.000000 AND
  populate_io_cache_on_flush='false' AND
  default_time_to_live=0 AND
  speculative_retry='99.0PERCENTILE' AND
  memtable_flush_period_in_ms=0 AND
  compaction={'class': 'SizeTieredCompactionStrategy'} AND
  compression={'sstable_compression': 'LZ4Compressor'};

Is there an alternative for paging/pagination (DataStax Enterprise / DSE 4.6.1) of results in Python (using cqlengine 0.21.0)? The documented solution (http://cqlengine.readthedocs.org/en/latest/topics/queryset.html#token-function) appears to be broken due to #7016.

My initial query for data is:

SELECT * FROM that_keyspace.tags_for_search WHERE "tag_prefix" = 'der' AND "tag_text" = '#derpy1' AND "village_id" = 1 LIMIT 10000;

Or in Python via cqlengine:

village_tags = VillageSearch.objects.filter(village_id=1, tag_prefix='der', tag_text='#derpy1')

tags_data = []
for village_tag in village_tags:
    tags_data.append(village_tag.to_dict())

first_page = village_tags
last = first_page[-1]
next_page = list(village_tags.filter(pk__token__gt=cqlengine.Token(last.pk)))

It is throwing the error:

code=2200 [Invalid query] message="Column "village_id" cannot be restricted by both an equality and an inequality relation"

Is there an alternative that I can use to avoid this bug for immediate usage?

Thank you for any assistance you are able to provide!

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Selecting Unnecessary Columns (query line: 2): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
The optimized query:
SELECT
        * 
    FROM
        that_keyspace.tags_for_search 
    WHERE
        'tag_prefix' = 'der' 
        AND 'tag_text' = '#derpy1' 
        AND 'village_id' = 1 LIMIT 10000

Related Articles



* original question posted on StackOverflow here.