The customer wants all data encrypted in database, how to make efficient queries fields are encrypted?

Nowadays some non-technical customers ask if all data is stored (and transferred) in encrypted form. They do not like the idea that only password is hashed.

What are the best practices, e.g. how to make efficient SQL queries, if all data is crypted?
How to query for e.g. user name with partial search /JOHN/, if all fields are crypted? Crypting each field as end-user wants would cause usability issues, slowness and cpu costs, 95% of data fields are not sensitive data.

Or is it enough to assume that :

forcing to use HTTPS means that data is transferred in encrypted form between web browser client and backend.
Keeping Hard drive filesystem crypted is enough, so that the mysql database is stored in crypted form on lowest level, vs. does the Backend App really need to write each data field in crypted form?

The database and backend are running in same server, and client side is html5. Does it really give some extra security if backend php source code has the encryption key to access the sql database located on same hard disk?

Another way would be 100% security; Let's assume we would make the login+password combination some kind of unique encryption key, but then we would have severe problem if customer forgets their password, it would not be possible to access their database columns at all.

Solution

"Data is encrypted at rest" is a common security requirement. This typically means - as you indicated - encrypted on the filesystem. Most DBMS' support this feature. On the other hand, writing the data as encrypted values into database tables typically expects:

encrypting and decrypting at this level is resource cheap (it isn't)
you can continue to query on encrypted data without further effort (you can't)
a custom implementation (expensive to maintain, prone to exposure)
your implementation doesn't expose the encryption key - which requires storing it somewhere offsite

A reasonable middle ground might be to encrypt any sensitive data they are concerned about. The benefit to this is that:

when the database or its underlying data is copied off the filesystem in an unencrypted form, the sensitive data is still encrypted
breaches of the database security (eg. database username and password) doesn't necessarily lead to a leak of the sensitive data

So overall there are benefits to explicit encryption of data within the database, but there is a performance and maintenance cost that cannot be ignored.