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.
精彩评论