MySQL - Help me implement a search engine

[IAmAndre]

Hi,

 

I'm working on a website and I would like to improve my search engine. I'm not actually asking for code, but I would just like to know where to look at and what to focus my research on. I have an "advanced search" page allowing to find users based on multiple criteria. For example, you could find users living in a specific country, professional in a specific domain, etc.I would like to show first the users matching all the criteria, then the ones matching 2 out of 3, then 1 out of 3. Is there any way to do it directly from MySQL or do I have to tweak the results programmatically?

The second thing is how to store these results. Let's say I have a complex query to run (more complex than the previous one, with many tables involved). I would like to find a way to store the results, or just save the server the trouble of running the full query again when the user wants to show the second or third page of results. At least I would like to reduce as much as possible the load on the server without having to spend too much time working on it.

The last thing is more of a bonus : if someone enters "Anna" in the "name" field, is there any way to show results for "Ana". This is really a bonus, I've been able to do it with the cities, by storing a list of different spellings for each one. Is there another approach?

 

Thanks

[Remixt]

32 minutes ago, IAmAndre said:

Hi,

 

I'm working on a website and I would like to improve my search engine. I'm not actually asking for code, but I would just like to know where to look at and what to focus my research on. I have an "advanced search" page allowing to find users based on multiple criteria. For example, you could find users living in a specific country, professional in a specific domain, etc.I would like to show first the users matching all the criteria, then the ones matching 2 out of 3, then 1 out of 3. Is there any way to do it directly from MySQL or do I have to tweak the results programmatically?

The second thing is how to store these results. Let's say I have a complex query to run (more complex than the previous one, with many tables involved). I would like to find a way to store the results, or just save the server the trouble of running the full query again when the user wants to show the second or third page of results. At least I would like to reduce as much as possible the load on the server without having to spend too much time working on it.

The last thing is more of a bonus : if someone enters "Anna" in the "name" field, is there any way to show results for "Ana". This is really a bonus, I've been able to do it with the cities, by storing a list of different spellings for each one. Is there another approach?

 

Thanks

SQL is really close to spoken English. https://dev.mysql.com/doc/ is a good place to look for syntax. If you are just getting started then use https://www.codecademy.com/learn/learn-sql to get your feet of the ground. Once you know the basics, it's pretty intuitive.

[sgzUk74r3T3BCGmRJ]

?