Is there any way to optimize row sorting in MemSQL?
For example, I have a 600M+ row table of customer transactional data and the first column
row is an auto_increment integer column. I’m able to run a query that filters by customer information, which drops my overall result to 18M+ rows. However, if I add “ORDER BY row DESC” the performance drastically drops.
I tested an alternate scenario by creating a new table with only the
row column and those 18M+ rows, the absolute minimum dataset I’d work on at a time, followed by “ORDER BY row DESC LIMIT 25” and performance still suffers.
The end goal is to display the latest 25 entries filtered by customer, hence applying that “ORDER BY row DESC LIMIT 25” query, yet working with anywhere from 600M to over 1B rows of data in a 1.5TB database. The query execution time is at minimum 12 seconds, not at all optimal for customers accessing the dataset through a frontend / website.