Reputation
Top tag
Next privilege 200 Rep.
See reduced ads
Badges
1 7
Newest
 Scholar
Impact
~4k people reached

  • 0 posts edited
  • 0 helpful flags
  • 2 votes cast
Jan
18
awarded  Scholar
Jan
18
accepted Multi level hierarchy relationship
Jan
17
comment Multi level hierarchy relationship
Creating custom function as mentioned by RolandoMySQLDBA worked. I have mentioned below the answer.
Jan
17
answered Multi level hierarchy relationship
Jan
15
comment Multi level hierarchy relationship
I don't know exactly what the query does. Just copied it from another solution, modified it to adapt to my scenario. It works, if a parent has only one child, but fails in one to many relation, as the sub query in that case has more than one row
Jan
15
asked Multi level hierarchy relationship
Jan
4
comment Checking existence and syncing data with millions of rows
That won't be efficient solution in my opinion. What if the server crashes after syncing 10 million records. That means rolling back 10 million and starting again. Can't we use some counter to keep track?
Jan
4
comment Checking existence and syncing data with millions of rows
Another approach I was thinking of, was to partition the table based on starting number, in this case (7, 8, 9) which will result in 1/3 times data in each table.
Jan
4
comment Checking existence and syncing data with millions of rows
I tried another approach with Redis by storing the rows in hash buckets of size 1000. That resulted in around 8 GB of data size (memory) for 250000000 records. Lookup was pretty fast, but not feasible for now, due to its memory footprint.
Jan
4
comment Checking existence and syncing data with millions of rows
I did the same and tested with around 100000000 rows in db for lookup. The lookup time is pretty good with a simple single lookup coming to 0.001 sec and group lookup with WHERE in coming around 0.02 sec. Your syn approach seems pretty good. BTW, how can we keep track of the progress and resume back in case of mysql crashing, while sync
Jan
4
awarded  Supporter
Jan
4
comment Checking existence and syncing data with millions of rows
Thanks for the detailed db design approach. This approach would be highly efficient, with a subscriber list. The issue here is random mobile numbers gives as input to app, while sending sms. The input can be either through app interface or remotely via API. So the lookup... There is nothing like subscriber list.
Jan
3
revised Checking existence and syncing data with millions of rows
added 1294 characters in body
Jan
3
asked Checking existence and syncing data with millions of rows
Dec
3
awarded  Notable Question
Jun
30
awarded  Student
May
17
awarded  Popular Question
Jan
10
awarded  Commentator
Jan
10
comment Relation between multiple hierarchical taxonomies linked to a single entity
Thanks for your inputs. I have edited my question to embrace another approached as suggested by another fellow. I will appreciate your suggestions on that.
Jan
10
revised Relation between multiple hierarchical taxonomies linked to a single entity
added 509 characters in body