![]() SELECT CONCAT('fooa','bfoo') with LIKE '%ab%' would work, while 'fooa' LIKE '%ab% OR 'bfoo' LIKE '%ab% OR wouldn't.įor 500 rows, i don't think you would have resource issues, so i would have just gone with what 'has the advance of being easier to read and maintain'. The main problem i see is if CONCAT could create wrong matches : I'm not that good with MySQL resource verification, so i didn't go further on this, guess it should still give an idea. It seems indeed multiple LIKE is much more resourcefull. ![]() ![]() SELECT * FROM adress WHERE road1 LIKE '%test%' OR road2 LIKE '%test%' 0.0710 sec SELECT * FROM adress WHERE CONCAT(road1,road2) LIKE '%test%' 0.0503 sec Well, after your edit, it seems you don't have that much data, but i'll still post the result :ĮDIT : This was my first try over MySQL testing, and, as Do said, those results "may be just fluctuation".įirst query (on 90000 rows) road and road2 are VARCHAR(100): Since i tought this was an interesting point, i tought the best would be good to give it a try. (note that there are only a few thousands (less than 5000) rows to search) I wish select all rows from my database where 1 of the words occurs in one of multiple colmuns and then sort them by relevance (or even add extra importance to some columns). Maby it would be easier to just explain my final wish: In php i have an array of single words. (note: I am also planning on having it sort as stated in this stackoverflow question: mysql SORT BY amount of unique word matches, maby there are problems while using a method with this?) I was wondering what is the correct and most efficient way to achieve this search. This has the advance of being easier to read and maintainīecause I do not yet understand the full use of CONCAT I do not know or this is a correct way to use it. MySQL uses just two functions CONCAT () and CONCATWS () and returns the concatenated version of the value of the columns. This seems to allow me to make one search after concatenating the columns (right?): SELECT * FROM table WHERE CONCAT(column1,column2.) LIKE %term1% OR CONCAT(column 1,column2.) LIKE %term2%. column concatenation in MySQL is a very easy way in comparison to other RDBMS like Microsoft SQL server and PostgreSQL because those RDBMS use + and operator to add strings. In my search I came across the CONCATfunction in MYSQL. ![]() Yet I though this made the query quite long, especially when there are multiple terms Ofcourse an option would be to add multiple OR's in the query: SELECT * FROM table WHERE column1 LIKE %term1% OR column2 LIKE %term1% OR. I was trying to find the best way to search multiple columns for the occurence of a word. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |