[SOLVED] to chop a DB in chunks to speed up search

[argumentum]

I was thinking but I don't have the experience, so you may have the experience.
I was thinking to chop a DB with 100.000 ( 20 columns ) in 10.000 DB chunks ( 10 DBs ) and query concurrently all of them to speed up a search, then add the results. Is that a sound idea ? Or will I run in trouble down the road. Should a DB better be keep in one piece ?

The DB is now in MySQL. I wanna do all this chopping and use SQLite. 

Thanks

[solved]

Edited March 2, 2020 by argumentum
[SOLVED]

[Earthshine]

Now I would look up everything I needed based on indexes and put them in temporary tables and do it that way

[Nine]

Yes 100,000 rows is a quite small DB.  Put your table in a memory SQLite DB.  Recently we made it work with a 100k table, and response time for a single row was about 0.30 ms.  Of course it will depends on how many rows you want to fetch each single query.  You may need to add indexes to accelerate the query, but I would personally not divide the DB like you intend to.

[jchd]

Post the full schema of your DB and the queries you find slow.

Posting a sample of rows would help fine-tunning.

In general, sliptting a DB is almost always a very bad idea.

[Earthshine]

right, you need to get the tables indexed properly and optimize your queries first and foremost

i worked for an energy company with many millions of customers (every customer was a complex entity, and one customer could have many accounts)... they partitioned and segmented their DB for optimal performance across the organization. you should never need to if the db is designed properly for your needs.

Edited March 2, 2020 by Earthshine

[argumentum]

2 hours ago, Nine said:

Recently we made it work with a 100k table, and response time for a single row was about 0.30 ms.

 

2 hours ago, jchd said:

In general, sliptting a DB is almost always a very bad idea.

 

1 hour ago, Earthshine said:

you need to get the tables indexed properly and optimize your queries first and foremost

 

Ok, an overwhelming "don't spit the DB". That answers the question regarding a faster search.

Reviewing the timings to continue this post, ...I'll have to go deeper in the PHP.
The search takes 1~4 ms. to return the query, but the browser say that it took 1~3 sec. waiting (TTFB) . The transfer of the JSON (content download) took 80 ms.
So I come to conclude the the "ArrayToJSON()" in PHP is the slowdown and is obviously unrelated to the DB search.

A good question would then be: can I have the DB engine return the query as JSON ? and that I did not researched.

As far as the OP, the question is clearly answer. Thanks y'all