Way around using CONCAT in a JOIN

I've for a mysql optimize question about using CONCAT in a JOIN. I've got two tables:

pages

(id, type, title, content)

1, 'front', 'Welcome', 'content text'

2, 'page', 'Page 2', 'more content'

paths

(pid, syspath, cleanpath)

98, 'front/1', '/'

99, 'page/2', '/contact'

To select the content with the path I use:

SELECT c.title, c.content, u.cleanpath

FROM pages c

LEFT JOIN paths p ON p.syspath = CONCAT(c.type, '/', c.id);

Now this query works fin开发者_开发知识库e but it is very very slow with lots of records. How can I speed this up? Should I be going about it another way?

Sadly I can't change the datebase schema or the 'syspath' field.

Any help would be great, thanks.

Well, my advice would be to create a column in pages and store the whole value there as an info duplicate. This will require additional space and logic, but will save you a lot of resources on queries, especially with proper indexes. this is called denormalization.

-- add a new column
ALTER TABLE `pages` ADD COLUMN `type_and_id` VARCHAR(255) NOT NULL;
-- index it
CREATE INDEX `someindexname` ON `pages` (`type_and_id`);
-- fill it with values
UPDATE `pages` SET
   `type_and_id` = CONCAT(`type`, '/', `id`);

And the the join will look like this:

SELECT c.title, c.content, u.cleanpath
FROM pages c
LEFT JOIN paths p ON p.syspath = c.type_and_id;

UPDATE

Sorry, I didn't see that you can't change the schema at first. Guess that will not work for you :(

Since you're using a function in ON clause, MySQL is unable to use any indexes for this join. Unless you change DB structure, there's not much you can do.