Question: I am trying to apply join over two table, the column on which join needs to be applied values for them are not identical due to which i need to used concat but the problem is its taking very long time to run. So here is the example:

I have two tables:

1. Table: MasterEmployee 
Fields: varchar(20) id, varchar(20) name, Int age, varchar(20) status
2. Table: Employee
Fields: varchar(20) id, varchar(20) designation, varchar(20) name, varchar(20) status

I have constant prefix: 08080

Postfix of constant length 1 char but value is random.

id in Employee = 08080 + {id in MasterEmployee} +{1 char random value}

**Sample data:**
----------------

MasterEmployee:

999, John, 24, approved

888, Leo, 26, pending

Employee:

080809991, developer, John, approved

080808885, Tester, Leo, approved

Here is the query that i am using:
----------------------------------

```
select * from Employee e  inner join MasterEmployee me
    on e.id like concat('%',me.id,'%')
 where e.status='approved' and me.status='approved';

```

Is there any better way to do the same ?? because i need to run same kind of query over very large dataset.

Answer: 
It would certainly be better to use the static prefix `08080` so that the DBMS can use an index. It won't use an index with `LIKE` and a leading wildcard:

```
SELECT * FROM Employee e INNER JOIN MasterEmployee me
    ON e.id LIKE CONCAT('08080', me.id, '_')
   AND e.status = me.status
 WHERE e.status = 'approved';

```

Note that I added `status` to the JOIN condition since you want `Employee.status` to match `MasterEmployee.status`.

Also, since you only have *one* postfix character you can use the single-character wildcard `_` instead of `%`.
It's not `concat` that's the issue, scalar operations are extremely cheap. The problem is using `like` like you are. Anything of the form `field like '%...'` automatically skips the index, resuling in a scan operation -- for what I think are obvious reasons.

If you have to have this code, then that's that, there's nothing you can do and you have to be resigned to the large performance hit you'll take. If at all possible though, I'd rethink either your database scheme or the way you address it.

Edit: Rereading it, what you want is to concatenate the prefix so your query takes the form `field like '08080...'`. This will make use of any indices you might have.