CODEWITHSUNDEEP
mysqlperformancejoinindexing
Martin
Martin

Reputation: 125

Mysql join query issue

I currently have an issue with an SQL query. I have a product table which contains a colour_code field. I cannot alter the product table as it is taken in from an external source.

On the website a user can search a product by colour_code and return all the products available in this colour.

The task at hand is to have a different search term return a certain product which has a different colour code. For this, I added a new table called colour_code_alias which simply has a colour_code field which corresponds to the colour_code field in the product table and an alias field which would return this result. See table examples below.

**Product_tb**
id, colour_code
1, "ABC"

**colour_code_alias_td**
colour_code,alias
"ABC","XYZ"

So, if a user searches for XYZ, they should be returned product with the id of 1. If they search for "ABC" they should also be returned the product with the id of 1.

My problem is that the query is taking too long to execute as it isn't using an index. My simplified query is below:

Select * from product
left join colour_code_alias cca on cca.colour_code = product.colour_code
where (product.colour_code = 'XYZ' or cca.alias = "XYZ")

When I use the explain it shows its not using a key for this query. When I remove the 'or cca.alias = "XYZ"' in the where clause, the colour_code index from the product table is being used.

I'm looking for help as to increase performance of such a query, how I should index this type of query or even should I rewrite it?

Any help is appreciated.

Thanks, Martin.

just as a note as to what I've done.. I've removed the left join and added a select query into the where clause. The query looks like the following:

Select * from product
where (product.colour_code = 'XYZ' or product.colour_code = (select colour_code from colour_code_alias where alias = 'XYZ') );

Upvotes: 1

Views: 97

Answers (2)

Hackerman
Hackerman

Reputation: 12295

You can try this query:

select *,
(select id from product where colour_code = cod.colour_code ) as id_prod
from 
colour_code_alias cod
where (cod.colour_code = 'ABC' or cod.alias = 'XYZ')

Upvotes: 1

Randy
Randy

Reputation: 16673

you should have an index on product.colour_code and alias for sure.

another thought is to avoid the OR part entirely by populating the alias table with the original as well... something like:

'ABC','ABC'

that way you have one place to look.

EDIT:

one more thought - use numeric keys instead of strings - this will be more efficient also. (but may be difficult if you can't change the table)

Upvotes: 1

Related Questions