CODEWITHSUNDEEP
mysqlsql
mkb
mkb

Reputation: 25426

Is there any alternative for mysql concat with better performance?

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.

Upvotes: 2

Views: 1856

Answers (2)

David Faber
David Faber

Reputation: 12485

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 %.

Upvotes: 2

Blindy
Blindy

Reputation: 67417

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.

Upvotes: 1

Related Questions