How to use CONCAT in JOIN

Question

I have optimized for mysql the question of using CONCAT in JOIN. I have 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 content using 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 is working fine, but it is very slow with lots of entries. How can I speed this up? Should I do this differently?
Unfortunately, I cannot change the datebase schema or syspath field.

Any help would be great, thanks.

Answer 1

Well, my advice would be to create a column in pagesand store the integer value there as a duplicate of the information. This will require additional space and logic, but will save you a lot of resources on queries, especially with the corresponding 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 connection 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 did not see that you cannot change the circuit first. Guess this won't work for you :(

Answer 2

ON, MySQL - . , .