I have a gigantic data of more than 2500000000 records distributed among 10 tables in derby. There are two columns "floraNfauna" and "locations" common in each table. Now I have to find a particular "floraNfauna" found at particular "locations", so I use "select" query with "like" e.g. "select * from tables where floraNfauna like('%fish%') and locations like('%shallow water bodies%')"; and it takes days to finally fetch the results which count below 1000 sometimes. After searching I found that "full text search" would be the best and faster approach to this. Can you help me with an example?
Asked
Active
Viewed 843 times
0
-
What is the size of the RAM in your server / pc. Please also mention the details of the table. `desc` – mustangDC Jul 31 '15 at 06:02
-
The system has 4GB Ram with a dual core processor clocked at 2.1 GHz. The tables have only two columns each first is "floraNfauna" varchar(50) and second is "locations" varchar(100). – avinash.kashyap Jul 31 '15 at 19:43
-
2500000000 is huge.. You seriously need to consider about upgrading your RAM first and most importantly index your table... – mustangDC Jul 31 '15 at 19:45
-
How to index the table? On both columns separately or a single index for both the columns? I did try the second one to create a single index for both the columns but still it takes 2-3 days to go through the complete data. – avinash.kashyap Jul 31 '15 at 19:46
-
See the edited answer – mustangDC Jul 31 '15 at 19:58
2 Answers
0
Derby integrates nicely with Lucene, which is a full-text search engine.
Read more about that here: http://wiki.apache.org/db-derby/LuceneIntegration
Bryan Pendleton
- 16,128
- 3
- 32
- 56
-1
Firstly you must consider indexing your table. Here is an SO link which definitely would help to know more about Why to index a DB table.
More about Adding Indexes to a table.
Secondly, if you are using a centralized database, then definitely consider upgrading your server hardware configuration.
Thanks, hope it helps.
