Database search for product - advanced
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
0
I have a database filled with product names, eg: HD 265;HD265;HD 265DT. My problem is I need to return all 3 variants when I enter the search query HD 265DT. How does search engines solves this problem without defining all 3 variants in all rows for these products?
mysql
elasticsearch search edited Nov 26 '18 at 19:42
Shadow
26.3k93046
asked Nov 26 '18 at 19:20
Zsolt OroszlányZsolt Oroszlány
3719
|
show 8 more comments
0
I have a database filled with product names, eg: HD 265;HD265;HD 265DT. My problem is I need to return all 3 variants when I enter the search query HD 265DT. How does search engines solves this problem without defining all 3 variants in all rows for these products?
mysql
elasticsearch search edited Nov 26 '18 at 19:42
Shadow
26.3k93046
asked Nov 26 '18 at 19:20
Zsolt OroszlányZsolt Oroszlány
3719
Did you really use;separated values? I advice you to read Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Raymond Nijland
Nov 26 '18 at 19:44
What you can do is make a loop in the application code where you split unique parts out off the search stringHD 265DTmeaningHD,265andDTas a part. And query more or less likeSELECT DISTINCT table.* FROM (SELECT 'HD' AS searchword UNION SELECT '256' AS searchword ....) AS search_filter INNER JOIN table ON table.column LIKE CONCAT('%', search_filter.searchword, '%')... Ofcource this approah can't make use of indexes.. The beter approach would be to create a search_part table based on the product names index that and query those records..
– Raymond Nijland
Nov 26 '18 at 19:52
@RaymondNijland;I used only to visualize variations, of course these values stored inproduct_namecolumn row. :) When I got to realize this is some advanced search I went almost the same route you suggested except I split search query at space than used this waySELECT * FROM table WHERE name LIKE '%HD%' AND name LIKE '%265%' AND name LIKE '%265DT%'this approach doesn't use indexes either. Full text search is no option sadly.
– Zsolt Oroszlány
Nov 26 '18 at 20:07
1
The only way i can think off after seeing the data is splitting the product_data into digits and query it.. db-fiddle.com/f/qE8Vk59fMAZdHEbmS2ZGMy/3 the only problem is you need to generate the search table and update it when inserting, updating and deleting meaning you have to need triggers.. Besides you store the data duplicated sure mine approach could have been more optimizated it's just a example.. Besides it needs to have a update db-fiddle.com/f/qE8Vk59fMAZdHEbmS2ZGMy/4 that is really uses the position..
– Raymond Nijland
Nov 26 '18 at 21:25
1
It is a really interesting approach also I think I can re-use your solution to everywhere else and make the search even faster, easier. I'm thinking to replacesource_idwith product EAN this way I could reduce the amount of data stored in lookup table. Thank you @RaymondNijland!
– Zsolt Oroszlány
Nov 27 '18 at 9:24
|
show 8 more comments
0
0
0
I have a database filled with product names, eg: HD 265;HD265;HD 265DT. My problem is I need to return all 3 variants when I enter the search query HD 265DT. How does search engines solves this problem without defining all 3 variants in all rows for these products?
mysql
elasticsearch search edited Nov 26 '18 at 19:42
Shadow
26.3k93046
asked Nov 26 '18 at 19:20
Zsolt OroszlányZsolt Oroszlány
3719
I have a database filled with product names, eg: HD 265;HD265;HD 265DT. My problem is I need to return all 3 variants when I enter the search query HD 265DT. How does search engines solves this problem without defining all 3 variants in all rows for these products?
mysql
elasticsearch search mysql
elasticsearch search edited Nov 26 '18 at 19:42
Shadow
26.3k93046
asked Nov 26 '18 at 19:20
Zsolt OroszlányZsolt Oroszlány
3719
edited Nov 26 '18 at 19:42
Shadow
26.3k93046
asked Nov 26 '18 at 19:20
Zsolt OroszlányZsolt Oroszlány
3719
edited Nov 26 '18 at 19:42
Shadow
26.3k93046
edited Nov 26 '18 at 19:42
Shadow
26.3k93046
edited Nov 26 '18 at 19:42
Shadow
26.3k93046
26.3k93046
asked Nov 26 '18 at 19:20
Zsolt OroszlányZsolt Oroszlány
3719
asked Nov 26 '18 at 19:20
Zsolt OroszlányZsolt Oroszlány
3719
asked Nov 26 '18 at 19:20
Zsolt OroszlányZsolt Oroszlány
3719
3719
Did you really use;separated values? I advice you to read Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Raymond Nijland
Nov 26 '18 at 19:44
What you can do is make a loop in the application code where you split unique parts out off the search stringHD 265DTmeaningHD,265andDTas a part. And query more or less likeSELECT DISTINCT table.* FROM (SELECT 'HD' AS searchword UNION SELECT '256' AS searchword ....) AS search_filter INNER JOIN table ON table.column LIKE CONCAT('%', search_filter.searchword, '%')... Ofcource this approah can't make use of indexes.. The beter approach would be to create a search_part table based on the product names index that and query those records..
– Raymond Nijland
Nov 26 '18 at 19:52
@RaymondNijland;I used only to visualize variations, of course these values stored inproduct_namecolumn row. :) When I got to realize this is some advanced search I went almost the same route you suggested except I split search query at space than used this waySELECT * FROM table WHERE name LIKE '%HD%' AND name LIKE '%265%' AND name LIKE '%265DT%'this approach doesn't use indexes either. Full text search is no option sadly.
– Zsolt Oroszlány
Nov 26 '18 at 20:07
1
The only way i can think off after seeing the data is splitting the product_data into digits and query it.. db-fiddle.com/f/qE8Vk59fMAZdHEbmS2ZGMy/3 the only problem is you need to generate the search table and update it when inserting, updating and deleting meaning you have to need triggers.. Besides you store the data duplicated sure mine approach could have been more optimizated it's just a example.. Besides it needs to have a update db-fiddle.com/f/qE8Vk59fMAZdHEbmS2ZGMy/4 that is really uses the position..
– Raymond Nijland
Nov 26 '18 at 21:25
1
It is a really interesting approach also I think I can re-use your solution to everywhere else and make the search even faster, easier. I'm thinking to replacesource_idwith product EAN this way I could reduce the amount of data stored in lookup table. Thank you @RaymondNijland!
– Zsolt Oroszlány
Nov 27 '18 at 9:24
|
show 8 more comments
Did you really use;separated values? I advice you to read Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Raymond Nijland
Nov 26 '18 at 19:44
What you can do is make a loop in the application code where you split unique parts out off the search stringHD 265DTmeaningHD,265andDTas a part. And query more or less likeSELECT DISTINCT table.* FROM (SELECT 'HD' AS searchword UNION SELECT '256' AS searchword ....) AS search_filter INNER JOIN table ON table.column LIKE CONCAT('%', search_filter.searchword, '%')... Ofcource this approah can't make use of indexes.. The beter approach would be to create a search_part table based on the product names index that and query those records..
– Raymond Nijland
Nov 26 '18 at 19:52
@RaymondNijland;I used only to visualize variations, of course these values stored inproduct_namecolumn row. :) When I got to realize this is some advanced search I went almost the same route you suggested except I split search query at space than used this waySELECT * FROM table WHERE name LIKE '%HD%' AND name LIKE '%265%' AND name LIKE '%265DT%'this approach doesn't use indexes either. Full text search is no option sadly.
– Zsolt Oroszlány
Nov 26 '18 at 20:07
1
The only way i can think off after seeing the data is splitting the product_data into digits and query it.. db-fiddle.com/f/qE8Vk59fMAZdHEbmS2ZGMy/3 the only problem is you need to generate the search table and update it when inserting, updating and deleting meaning you have to need triggers.. Besides you store the data duplicated sure mine approach could have been more optimizated it's just a example.. Besides it needs to have a update db-fiddle.com/f/qE8Vk59fMAZdHEbmS2ZGMy/4 that is really uses the position..
– Raymond Nijland
Nov 26 '18 at 21:25
1
It is a really interesting approach also I think I can re-use your solution to everywhere else and make the search even faster, easier. I'm thinking to replacesource_idwith product EAN this way I could reduce the amount of data stored in lookup table. Thank you @RaymondNijland!
– Zsolt Oroszlány
Nov 27 '18 at 9:24
Did you really use
; separated values? I advice you to read Why should I provide an MCVE for what seems to me to be a very simple SQL query?– Raymond Nijland
Nov 26 '18 at 19:44
Did you really use
; separated values? I advice you to read Why should I provide an MCVE for what seems to me to be a very simple SQL query?– Raymond Nijland
Nov 26 '18 at 19:44
What you can do is make a loop in the application code where you split unique parts out off the search string
HD 265DT meaning HD, 265 and DT as a part. And query more or less like SELECT DISTINCT table.* FROM (SELECT 'HD' AS searchword UNION SELECT '256' AS searchword ....) AS search_filter INNER JOIN table ON table.column LIKE CONCAT('%', search_filter.searchword, '%') ... Ofcource this approah can't make use of indexes.. The beter approach would be to create a search_part table based on the product names index that and query those records..– Raymond Nijland
Nov 26 '18 at 19:52
What you can do is make a loop in the application code where you split unique parts out off the search string
HD 265DT meaning HD, 265 and DT as a part. And query more or less like SELECT DISTINCT table.* FROM (SELECT 'HD' AS searchword UNION SELECT '256' AS searchword ....) AS search_filter INNER JOIN table ON table.column LIKE CONCAT('%', search_filter.searchword, '%') ... Ofcource this approah can't make use of indexes.. The beter approach would be to create a search_part table based on the product names index that and query those records..– Raymond Nijland
Nov 26 '18 at 19:52
@RaymondNijland
; I used only to visualize variations, of course these values stored in product_name column row. :) When I got to realize this is some advanced search I went almost the same route you suggested except I split search query at space than used this way SELECT * FROM table WHERE name LIKE '%HD%' AND name LIKE '%265%' AND name LIKE '%265DT%' this approach doesn't use indexes either. Full text search is no option sadly.– Zsolt Oroszlány
Nov 26 '18 at 20:07
@RaymondNijland
; I used only to visualize variations, of course these values stored in product_name column row. :) When I got to realize this is some advanced search I went almost the same route you suggested except I split search query at space than used this way SELECT * FROM table WHERE name LIKE '%HD%' AND name LIKE '%265%' AND name LIKE '%265DT%' this approach doesn't use indexes either. Full text search is no option sadly.– Zsolt Oroszlány
Nov 26 '18 at 20:07
1
1
The only way i can think off after seeing the data is splitting the product_data into digits and query it.. db-fiddle.com/f/qE8Vk59fMAZdHEbmS2ZGMy/3 the only problem is you need to generate the search table and update it when inserting, updating and deleting meaning you have to need triggers.. Besides you store the data duplicated sure mine approach could have been more optimizated it's just a example.. Besides it needs to have a update db-fiddle.com/f/qE8Vk59fMAZdHEbmS2ZGMy/4 that is really uses the position..
– Raymond Nijland
Nov 26 '18 at 21:25
The only way i can think off after seeing the data is splitting the product_data into digits and query it.. db-fiddle.com/f/qE8Vk59fMAZdHEbmS2ZGMy/3 the only problem is you need to generate the search table and update it when inserting, updating and deleting meaning you have to need triggers.. Besides you store the data duplicated sure mine approach could have been more optimizated it's just a example.. Besides it needs to have a update db-fiddle.com/f/qE8Vk59fMAZdHEbmS2ZGMy/4 that is really uses the position..
– Raymond Nijland
Nov 26 '18 at 21:25
1
1
It is a really interesting approach also I think I can re-use your solution to everywhere else and make the search even faster, easier. I'm thinking to replace
source_id with product EAN this way I could reduce the amount of data stored in lookup table. Thank you @RaymondNijland!– Zsolt Oroszlány
Nov 27 '18 at 9:24
It is a really interesting approach also I think I can re-use your solution to everywhere else and make the search even faster, easier. I'm thinking to replace
source_id with product EAN this way I could reduce the amount of data stored in lookup table. Thank you @RaymondNijland!– Zsolt Oroszlány
Nov 27 '18 at 9:24
|
show 8 more comments
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53487693%2fdatabase-search-for-product-advanced%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53487693%2fdatabase-search-for-product-advanced%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Did you really use
;separated values? I advice you to read Why should I provide an MCVE for what seems to me to be a very simple SQL query?– Raymond Nijland
Nov 26 '18 at 19:44
What you can do is make a loop in the application code where you split unique parts out off the search string
HD 265DTmeaningHD,265andDTas a part. And query more or less likeSELECT DISTINCT table.* FROM (SELECT 'HD' AS searchword UNION SELECT '256' AS searchword ....) AS search_filter INNER JOIN table ON table.column LIKE CONCAT('%', search_filter.searchword, '%')... Ofcource this approah can't make use of indexes.. The beter approach would be to create a search_part table based on the product names index that and query those records..– Raymond Nijland
Nov 26 '18 at 19:52
@RaymondNijland
;I used only to visualize variations, of course these values stored inproduct_namecolumn row. :) When I got to realize this is some advanced search I went almost the same route you suggested except I split search query at space than used this waySELECT * FROM table WHERE name LIKE '%HD%' AND name LIKE '%265%' AND name LIKE '%265DT%'this approach doesn't use indexes either. Full text search is no option sadly.– Zsolt Oroszlány
Nov 26 '18 at 20:07
1
The only way i can think off after seeing the data is splitting the product_data into digits and query it.. db-fiddle.com/f/qE8Vk59fMAZdHEbmS2ZGMy/3 the only problem is you need to generate the search table and update it when inserting, updating and deleting meaning you have to need triggers.. Besides you store the data duplicated sure mine approach could have been more optimizated it's just a example.. Besides it needs to have a update db-fiddle.com/f/qE8Vk59fMAZdHEbmS2ZGMy/4 that is really uses the position..
– Raymond Nijland
Nov 26 '18 at 21:25
1
It is a really interesting approach also I think I can re-use your solution to everywhere else and make the search even faster, easier. I'm thinking to replace
source_idwith product EAN this way I could reduce the amount of data stored in lookup table. Thank you @RaymondNijland!– Zsolt Oroszlány
Nov 27 '18 at 9:24