开发者

How to create index for table having 30 columns where Select statement is generated dynamically

I have a table containing language translations. It has 30 columns for 30 languages.

My requirement is to search from all columns for a particular word (say "hello").

I dynamically create Select statement:

Select * from Languages where {English = "hello" OR French = "hello" OR German = "hello"}

Statement inside {} is generated dynamically.

If I have to index such a table, how do I do that?

Will I have to create one index for all 3开发者_如何学Go0 columns or create seperate 30 indexes for each column?

Please suggest better option.


You shouldn't have 30 columns, you should use a table with a column for language name, and a column for the word, and an id to associate the 30 rows together. Then you can simply query for WORD = 'hello'.

If you absolutely must keep 30 columns, you will need to create 30 indexes, and the performance of the query will be 30 times slower than it needs to be.


I would create a second table that contains [id, lang, value], and index it by the id and value. You can set up a view that replicates the table you are working with from this, or vice versa, and index the view


If you must have all 30 columns, you will need to separately index each column.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜