Does this cause overhead ? Select name from User where user_name LIKE '%alex%'
Maybe, but maybe it's not worth worrying about. Depending on how many rows we're talking about, you might not be able to avoid a scan. How many rows in your table? 

An index on the username column won't help this query. At all. Think about a phone book: Very easy to find all the people with the last name starting with Sm, right? Not so easy to find all the people with last names that contains ith (or the first name John, for that matter).

An index might help if you constraint your allowed searches to "starts with" instead of "contains." Which might be a better answer in the long run. 

Who's searching for usernames that contain alex, anyway? Is this a common use case? If so you might want to look into alternative mechanisms. In SQL Server we have full-text search; see this StackOverflow question for some ideas on how to solve the same problem in MySQL (but again, keep in mind that it might be a "problem" you don't need to solve).