How to organize a search architecture on a large amount of data about 10 million in .net

Question

I have a table that stores about 10 million records (elements), each record is of type itemtype (this is a reference key to table types). On my site I have a search function based on full-text search, this was normal, but a few days ago my client wanted to show on the site not only all the elements, but also the types of elements.

I am trying to execute two parallel requests to different servers (one to the replicator and one to the main):

- the first request is the elements of the group itemtypeid (using full-text search) and the returned list itemtypeid

- second query - keywords for searching the database (full-text search)

on a web server, I aggregate the results of these queries and pop them into a web browser. Problem: the first request is not running as fast as I want :) (it is very slow), and after six (eight) months there will be more than 11 million elements, so the first request will be slower and slower.

Please show me the right way.

Answer 1

In addition to what @sonyc said, you should also consider:

• The order in which tables should be combined. How does the database management system perform the union between Item and ItemType tables? It is helpful to have a little idea of ​​what might happen, so you can make some informed decisions about adding indexes.

  . , , .

  , . ItemType , ItemID . , ItemID ItemType . ItemID "", .

• .

  . . . . , ( ItemID), .

, : . ? , , .

, . , , , , . . .

, . , . . , . , . , , , , . . , .

Answer 2

, . itemtypeid, . , . itemtypeid, - Lucene.net. .

- , . , - memcached Velocity.

lucene, , . . 100 .

Answer 3

, , : Item ItemType. Item ItemType. Item, ItemType .

, JOIN .