2
There is some difference in performance when searching the database, depending on the number of characters reported in the query?
For example, if a query is made for any record that has the character 'a', it will cost more processing than searching for a larger word, for example "test"?
I am with this doubt, because currently customers typing only one letter can already perform the query, if it impacts the response time, I think for a minimum limit of digits.
I am using Postgresql.
4
The search itself is to give a difference close to zero. Of course, a more character analysis can make the search a little slower, but this is almost theoretical, because the bulk of the search work is not the comparison of the characters. I’d say the difference must be less than 0.1%.
Only that looking for a single letter should generate an immense amount of results, and will have to transmit (on several levels) all this, obvious that transmitting more information will take longer. So for indirect reasons looking for a character can be a little, or even a lot slower in the end result, unless you don’t find something, which is unlikely.
The biggest problem is being able to search anywhere. This prevents the use of a normal index. Then it doesn’t make much difference whether you’re looking for one or a bunch of characters together. But don’t think it’ll be a tragedy to do so.
Actually fits the usual recommendation: test in your case.
If you think it’s not right maybe it’s a case of using one inverted index, also called full text index. The Postgresql has this feature, but I can’t guarantee that it will be useful for your case. Not always the win is great.
If you’re using a web client or something like that, it’s possible that network latency and intermediate processing affects more than access to the database. Especially if the accesses are interactive to the database (each character typed makes a search). There it works best locally, if possible in application embedded databases.
2
The performance of the search result will depend on the amount of records it will return. For example, if you just want records that have the word amor and if there are 1000 records that start with the letter a and there are only 100 that start with the word amor, then logically if you search for only the letter a will take longer as it will return 1000 records instead of 100.
-1
It depends on the amount of information recorded. If you allow the search for only one record, 'a' say, which is very common, in a database of considerable size, the return could be an excessive number of records. If your intention is to speed up the user’s life by showing results as soon as possible so that the interaction is more responsive, I suggest using paging. Thus typing only one character, only the 10, 20 or the number you set of initial results will be displayed. Once the user informs the rest of the word, the search will become more specific. Use the options OFFSET e LIMIT: instead of SELECT * FROM produtos WHERE nome LIKE '%a% that can return thousands of lines, try SELECT * FROM produtos WHERE nome LIKE '%a% LIMIT 100 OFFSET 0 that will always return at most 100 results.
Browser other questions tagged database postgresql query performance
You are not signed in. Login or sign up in order to post.
You say search for in
aanywhere oraonly at the beginning?– Maniero
@bigown anywhere, any record that has the character
a– Paulo
Yes the search for the second letter only starts after the first found.
– mau humor