A problem that bothers me for a long time is basically solved at 8:41 today.
The data volume of the database is very large, the query speed is very slow, after indexing "=" search speed problem resolution, but in the search of the SQL statements are mostly used like, but like '%xxx% ' is not using the index, and as the "XXX%" is indexed, for advice once the teacher, Senior programmers are useless, search on the site, help, and ultimately locked two ways: 1. Full-text indexing. 2. Search engine. But it is said that the Full-text indexing speed is also unsatisfactory, the support of GBK also some problems, for the time being to study search engines, but found that the search engine is not won't be can be studied thoroughly things, had to turn to Full-text index, the following is the method to establish Full-text indexing:
(Before you create a text index on a table, you must create a primary key for the table if there is no primary key on the table)
Create index text index name
On Table name (table field to which you want to build the text index)
Indextype is ctxsys.context;
It takes a long time to create a text index, wait patiently ...
Query Test after creation: SELECT * FROM table where CONTAINS (USERNAME, ' cocoa ') >0
The speed has been greatly improved, thinking that the problems of many days have finally been solved. But I did not think that when the search results are more. The speed of count accumulation slowed down, the index is all set up, the solution has not found, last night lying in bed over and over, think of a lot like the search in the forum limit the number of search keywords, this can effectively control the number of query results, you can limit the search when the keyword input at least four characters. But the blink of an eye is not feasible, because the data in the library is likely to have only two words, three word name, which will result in the search results are unreliable ...
Early this morning came to the company, open yesterday on the Internet to help the post, answered a lot of people, put forward a variety of methods, but I have tried, are not workable, perhaps this special project requirements are more special. Calm down to think carefully, I need to query the page in fact can be divided into two types, small-range search and wide-range search, the speed is qualified in the previous small search, but the recent addition of a large range of search, the following problems, but the full text index, the wide range of speed significantly increased, A small range of count is surprisingly slow. Analysis completed, it is obvious, in a small range of searches with a general search (at the same time can be restricted to only one word), and a wide range of time with Full-text indexing search. Immediately follow this plan to modify, finally ... I hope the effect basically appears. (but in a wide range of searches, the speed of a small range slightly slower, but should not be serious) mood great. I thought I was going to give up the problem, but eventually I found a solution. Although there is no "prison break" as thrilling stimulus, but the mood after the problem is indeed very good.
Then there are a small problem, it is only a matter of time to solve them ... |
|
