mysql - Sql query how to make it faster? -

i have sql query. possible change somehow query, has better performance, same result? query working, slow, , don't have idea on improving performance.

select   keyword, query      url_alias ua join     product p        on (p.manufacturer_id =              convert(substring_index(ua.query,'=',-1),unsigned integer)) join     oc_product_to_storebk ps        on (p.product_id = ps.product_id)      , ua.query 'manufacturer_id=%'      , ps.store_id= '9' group ua.keyword 

table structure:

url_alias

+-----------------------------------------------+ | url_alias_id |    query            | keyword  | +--------------+---------------------+----------+ |           1  | manufacturer_id=100 | test     | +--------------+---------------------+----------+ 

product

+-----------------+------------+ | manufacturer_id | product_id | +-----------------+------------+ |             100 |       1000 | +-----------------+------------+ 

oc_product_to_storebk

+------------+----------+ | product_id | store_id | +------------+----------+ |       1000 |        9 | +------------+----------+ 

i want keywords url_alias keyword column, when following condition met: like 'manufacturer_id=%' , ps.store_id='9'

you should avoid convert function expensive , provides no way profit indexes on url_alias table.

extend url_alias table has additional fields parts of query. hesitate go way, not regret once have done it. url_alias table should this:

create table url_alias (   url_alias_id int,   query varchar(200),   keyword varchar(100),   query_key varchar(200),   query_value_str varchar(200),   query_value_int int ); 

if don't want recreate it, add fields follows:

alter table url_alias add (   query_key varchar(200),   query_value_str varchar(200),   query_value_int int ); 

update these new columns existing records statement (only execute once):

update url_alias set query_key = substring_index(query, '=', 1),     query_value_str = substring_index(query, '=', -1),     query_value_int = nullif(         convert(substring_index(query,'=',-1),unsigned integer), 0); 

then create trigger these 3 fields updated automatically when insert new record:

create trigger ins_sum before insert on url_alias each row  set new.query_key = substring_index(new.query, '=', 1),     new.query_value_str = substring_index(new.query, '=', -1),     new.query_value_int = nullif(          convert(substring_index(new.query,'=',-1),unsigned integer), 0); 

note additional nullif() make sure last field null when value after equal sign not numerical.

if ever update such records, create similar update trigger.

with set-up, can still insert records before:

insert url_alias (url_alias_id, query, keyword)     values (1, 'manufacturer_id=100', 'test'); 

when select record, see this:

+--------------+---------------------+---------+-----------------+-----------------+-----------------+ | url_alias_id | query               | keyword | query_key       | query_value_str | query_value_int | +--------------+---------------------+---------+-----------------+-----------------+-----------------+ |            1 | manufacturer_id=100 | test    | manufacturer_id | 100             |             100 | +--------------+---------------------+---------+-----------------+-----------------+-----------------+ 

now work of extraction , conversion has been done once, , not have repeated more when select records. can rewrite original query this:

select   ua.keyword, ua.query      url_alias ua join     product p        on p.manufacturer_id = ua.query_value_int join     oc_product_to_storebk ps        on p.product_id = ps.product_id      , ua.query_key = 'manufacturer_id'      , ps.store_id = 9 group ua.keyword, ua.query 

and can improve performance creating indexes on both elements of query:

create index query_key on url_alias(query_key, query_value_int, keyword); 

you might need experiment bit order of fields right in index before gets used sql plan.

see sql fiddle.