Is there a difference in performance when querying the database, depending on the number of characters entered in the query?
For example, if a query is made for any record that has the 'a' character, will it spend more processing than searching for a larger word, example 'test'?
I have this doubt, because currently the customers typing only one letter can already carry out the query, if this impacts the response time, I think of a minimum threshold of digits.
I'm using PostgreSQL.
The search itself is to make a difference close to zero. Of course, an analysis of more characters may make the search a bit slower, but this is almost theoretical because the bulk of the search job is not the comparison of the characters. I would say the difference should be less than 0.1%.
But looking for a single letter should generate a huge amount of results, and you will have to convey all of this, obviously that to convey more information will take longer. So for indirect reasons looking for a character may be a little, or even much slower in the final result, unless you do not find anything, which is unlikely.
The biggest problem is being able to search anywhere. This prevents the use of a normal index . It does not make much difference if you are looking for one or a bunch of characters together. But do not think it will be a tragedy to do so.
In fact it's up to the usual recommendation. Test in your case.
If you think it's not good maybe it's the case of using inverted index , also called full text index . PostgreSQL has this feature , but I can not guarantee it will be useful for your case. Not always the gain is great.
If you are using a web pain client or something, it is possible that network latency and intermediate processing affect more than database access. Especially if the accessions are interactive to the database (each character typed searches). It works best locally, if possible on databases embedded in the application.
The performance of the search result will depend on how many records it will return. For example, if you want only 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 the 1000 records instead of 100.
Depends on the amount of information recorded. If you allow the search for just 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 pagination. By typing just one character, only the 10, 20 or the number that you set from initial results will be displayed. As soon as the user enters 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 rows, try SELECT * FROM produtos WHERE nome LIKE '%a% LIMIT 100 OFFSET 0 that will always return a maximum of 100 results.
