Mysql join query issue

Question

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') );

yeah... I've tested using 3 indexes on that.. colour_code, alias and a composite colour_code_alias index. — Martin
– Martin, Commented Apr 5, 2013 at 12:46

Randy · Accepted Answer · 2013-04-05 12:50:33Z

1

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)

edited Apr 5, 2013 at 12:50

answered Apr 5, 2013 at 12:45

Randy

16.7k2 gold badges40 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Martin Over a year ago

I have the indexes set up on the product and the alias table ok.. hmm.. I could try adding the original to the alias table. - Although, If the product table gets a new product, this won't be searchable until I populate the alias table with the new colour code.

Martin Over a year ago

I can't use numberic keys as you say there, I can't change the table. a colour_code can be the same for multiple products too.

Hackerman · Accepted Answer · 2013-04-05 13:17:05Z

1

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')

answered Apr 5, 2013 at 13:17

Hackerman

12.3k2 gold badges37 silver badges47 bronze badges

5 Comments

Martin Over a year ago

Hi, thanks for the suggestion.. it does seem to return the id, but I was simplifying my question, if I was to say I needed all the columns for the product this would change. I updated my query and changed the where clause to include:

where product.colour_code = 'ABC 'or product.colour_code IN(select colour_code from colour_code_alias where alias = 'XYZ')

Seems to be a bit better, but still a bit slow

Hackerman Over a year ago

Change the IN for an = ....it seems to be an index problem on your tables....declare product.colour_code and colour_code_alias.alias as indexes

Martin Over a year ago

Thanks for the suggestion. I've changed my IN for an '=' and it seems to be running faster ok. I have already indexes set up on both of those table/fields. The explain query still shows no key for the product.colour_code.

Hackerman Over a year ago

Try rebuilding your tables with the actual structures and indexes....with a mysql dump and recreate the tables on your database...to rebuild your indexes and getting rid of fragmentation of your data on disk.

Martin Over a year ago

I'm unable to rebuild the product table as I can't change it unfortunately. When I say the query is running slow I've got it down to about 1 second without the query being cached so It's ok for the time being I guess. I will revisit it when I get the chance. Thanks for all the help!

Collectives™ on Stack Overflow

Mysql join query issue

2 Answers 2

2 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related