0

When the number of data in a table is about a billion (yes, billion), I want to select 1 to 3 hundreds of data under 20ms with SELECT val0, val1, txt FROM Table_A WHERE id in (???) while ??? contains a list of ids (size at least 100), while the 3000 of queries executed per a second.

I built a table(assume name is Table_A) with below query:

CREATE TABLE if not exists Table_A 
(
    id CHAR(32), 
    val0 VARCHAR(64) NOT NULL, 
    val1 CHAR(19) NOT NULL, 
    val2 INT NOT NULL, 
    val3 VARCHAR(64) NOT NULL, 
    val4 BIGINT NOT NULL, 
    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
    updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    txt TEXT NOT NULL, PRIMARY KEY (id)
)

The value of id is unique and txt value has long text data which contain json string (1000 length on average).

I tried basic SELECT-WHERE-IN query method and also tried the way to create temporary table and join it with Table_A but it always gives me an 50ms+ of latency.

Is anyway to reduce latency more than now? Or is it too heavy for MySQL to control this scenario? Should I change it to other DB?

4
  • Why your primary key is CHAR? Maybe it can be mapped to (BIG)INT? Why you store JSON value in TEXT column and not as JSON datatype? Does the values in the list of ids which is provided into the query looks like random ones, or they can be "patterned"?
    – Akina
    Commented May 3, 2023 at 12:21
  • What is the typical size of txt? Which Engine? (Hopfully InnoDB.)
    – Rick James
    Commented May 3, 2023 at 18:54
  • How much RAM? What is the value of innodb_buffer_pool_size?
    – Rick James
    Commented May 3, 2023 at 18:55
  • Is id some type of hash or UUID? Are the desired rows "near" each other in, say time?
    – Rick James
    Commented May 3, 2023 at 23:04

2 Answers 2

1

Sounds like you need better indexing. I would have suggested the following

CREATE INDEX IX ON Table_A
  (id, val0, val1);

Having said that, you say the id is unique, so you probably just want a clustered primary key, which you already seem to have.

ALTER TABLE Table_A
ADD CONSTRAINT PK_A PRIMARY KEY
  (id);

Given the right index, it makes little difference that you have a billion rows. A B-Tree index has O(log(n)) complexity, so it won't take more than 30 lookups in the average case.

So if you are still seeing slow performance I suggest you investigate the EXPLAIN plan and possibly invest in better hardware. There should be no reason why this query should take more than a few milliseconds.

6
  • @DevFallingstar OP, to Charlieface's point "A B-Tree index has O(log(n)) complexity, so it won't take more than 30 lookups in the average case." - This should realistically take no more than a few milliseconds with the proper index (but will vary based on your hardware, and other non-database system specific variables). So if you're still seeing 50ms or more with the proper index, then it's not a database system specific issue. "Should I change it to other DB?" - That's rarely the solution to performance problems.
    – J.D.
    Commented May 3, 2023 at 12:02
  • Having said that, you say the id is unique, so you probably just want a clustered primary key 1) OP provides table's DDL where id is PK. 2) In MySQL (more precisely - in InnoDB) the table's PK is clustered index always. I would have suggested the following TEXT column cannot be indexed completely, one must specify its prefix length. So this index won't be covering one, and the server will access the table body anycase. For shown PK (with added prefix length) this means that the index seek will be followed by index/table scan.
    – Akina
    Commented May 3, 2023 at 12:25
  • They already have PRIMARY KEY (id); the composite index will likely be ignored because of that.
    – Rick James
    Commented May 3, 2023 at 23:07
  • 1
    30 is correct for binary tree. It is more like 5 for a BTree.
    – Rick James
    Commented May 3, 2023 at 23:08
  • If the 100 ids are scattered (not consecutive), it could easily take several ms.
    – Rick James
    Commented May 3, 2023 at 23:11
0

Your performance requirement is very demanding, as you know. You have a time budget of 300-some-odd microseconds to handle each query (you said 3000 per second). In that time, each query must perform, if you have correct indexing for your query, at least 100 simple O(log(n)) index lookups. Then it must return, to your application, a VARCHAR(64), a CHAR(19) and a TEXT column for each of those index lookups. It looks like you have about 2 microseconds for each of those operations. !!

How to make this database design faster?

  1. If you can change the data type of your id primary key column to BIGINT do so. That will get you faster index lookups.

  2. You may not be able to do that because your id values contain non-integer data. If you can change the definition of that column to

    id CHAR(32) NOT NULL CHARACTER SET latin1 COLLATE latin1_bin PRIMARY KEY,
    

    your database server will no longer have to handle (expensive) case-insensitive searches on Unicode data in your columns. Your id column will become, internally, fixed-length, as well. (Columns containing utf-8 Unicode cannot be stored in fixed-length internal data structures, because individual characters may contain 1-4 bytes.)

    This only works if all the characters in your id values are eight-bit plain ASCII or western european numbers and/or letters. And if you can handle case-insensitive searching (in which, for example, DevFallingStar and DEVFALLINGSTAR are different values).

  3. Changing the CHARACTER SET of your other textual columns to latin1 will also help, because it makes them shorter. Again, you may not be able to do this, if your text contains characters from languages other than Western Europe's.

  4. TEXT data types are extra-burdensome to store and retrieve because they require jumping from the main table's data structure (a so-called clustered index in InnoDB) to another data structure to find them. Can you change that data type to VARCHAR()?

  5. This is unlikely to be enough to meet your very stringent performance requirement, even with an RDBMS server with a big enough innodb_buffer_pool_size to hold most of your table in RAM (128GiB, maybe). Servers that size are expensive.

A replicated cluster of MySQL servers may give you enough load-balanced capacity to handle this requirement, as long as your table is nearly read-only. As you can imagine, it's going to get very expensive.

You could try SQLite, with local copies of your data table on the file system of each machine running your app. SQLite saves the round-trip client server network overhead between your app and an RDBMS server. Again, this only works properly if your table is nearly read-only.

At any rate, your app design is probably not feasible as you have described it, sad to say.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.