Today's Question:  What does your personal desk look like?        GIVE A SHOUT

 DATABASE


  Why (offset, limit) is slow in database select?

Starting from a problemFive years ago when I was working at Tencent, I found that MySQL request speed was very slow in the pagination scenario. With only 100,000 data, a select query on a single machine took about 2-3 seconds. I asked my mentor why, and he asked in return, "In an indexing scenario, what is the time complexity to get the nth largest number in MySQL?"The pursuit of the answerConfirming the scenarioAssuming there is an index on the "status" column, a query like "select * from table where status = xx limit 10 offset 10000" will be very slow. Even with a small amount of data, there...

2,734 0       MYSQL OFFSET LIMIT SLOW


  A guide on installing and running Clickhouse on macOS

ClickHouse is a high-performance open-source columnar database management system developed by Yandex. Here are some of the key features of ClickHouse:Columnar storage: ClickHouse uses a columnar storage format, which allows it to efficiently store and retrieve data by column, rather than by row. This results in much faster query performance, especially for analytical and aggregate queries.Real-time data processing: ClickHouse is designed to handle real-time data processing and can handle billions of rows of data with sub-second query times.Massively scalable: ClickHouse can scale to handle mas...

3,978 1       CLICKHOUSE MACOS


  What is blocking and how would you troubleshoot it?

Blocking is a common occurrence in an SQL server context, but if you are new to the world of database management you might not know what this issue entails and perhaps even fear that it is a sign of serious underlying problems.To allay your fears and clear up the mystery, here is a brief overview of blocking and the steps you can take to tackle it.Image Source: PixabaySQL blocking explainedSQL blocking according to SentryOne is an offshoot of the way that concurrent databases operate. Because processes can be executed simultaneously, it is likely that at some point more than one process will n...

1,549 0       SQL DEADLOCK


  How Query Optimizer Works in RDBMS

In a previous post, we discussed how the various relational operators are implemented in relational database systems. If you have read that post, you probably still remember that there are a few alternative implementations for every operator. Thus, how should RDBMS determine which algorithm (or implementation) to use?Obviously, to optimize the performance for any query, RDBMS has to select the correct the algorithm based on the query. It would not be desirable to always use the same algorithm. Also, SQL is a declarative language (i.e., as a programmer we only declare what we want to ...

8,034 0       DATABASE DATABASE DESIGN DATABASE OPTIMIZATION


  Understanding How is Data Stored in RDBMS

We all know that DBMS (database management system) is used to store (a massive amount of) data. However, have you ever wondered how is data stored in DBMS? In this post, we will focus on data storage in RDBMS, the most traditional relational database systems.Physical StorageData can be stored in many different kinds of medium or devices, from the fastest but costy registers to the slow but cheap hard drives, or even magnetic tapes. Nowadays, IaaS providers such as AWS even provides services such as S3 Glacier as a low-cost archiving storage solution. The...

11,910 0       RDBMS DATABASE


  CASSANDRA data model

Cassandra is an open source distributed database, it combines dynamic key/value and column oriented feature of Bigtable.Features of Cassandra are:Flexible schema, no need to design schema first, it's very convenient to add or delete stringsSupport range search on keysHigh usability, extensible. The single node error will not affect the cluster.We can think Cassandra's data model as a 4 or 5 dimensional Hash.COLUMNColumns is the smallest data unit in Cassandra, it is a 3 dimensional data type including name,value and timestamp. { // This is a column name: "逖靖寒的ä¸...

2,872 0       DATABASE SORT CASSANDRA


  SQLite C/C++ function interfaces

Some simple introduction to the SQLite function interfaces. First let's check some error codes defined in SQLite3 (They are in SQLite3.h file in the SQLite installation).#define SQLITE_OK           0   /* Successful result */  /* beginning-of-error-codes */  #define SQLITE_ERROR        1   /* SQL error or missing database */  #define SQLITE_INTERNAL     2   /* Internal logic error in SQLite */  #define SQLITE_PERM    &nbs...

4,834 0       SQLITE FUNCTION INTERFACE C/C++


  Three things you should never put in your database

As I've said in a few talks, the best way to improve your systems is by first notdoing "dumb things". I don't mean you or your development staff is "dumb", it'seasy to overlook the implications of these types of decisions and not realize howbad they are for maintainability let alone scaling. As a consultant I see this stuffall of the time and I have yet to ever see it work out well for anyone.Images, files, and binary dataYour database supports BLOBs so it must be a good idea to shove your files in thereright? No it isn't! Hell it isn't even very convenient to use with many DB languagebindings...

3,244 0       DATABASE IMAGE LOG BLOB