0

I am struggling with slow performance on an SQL search query. Hopefully somebody can help me understand the cause of this, as I have been hitting a brick wall for a few days now.

The code for the page is in VBscript. For this search I am combining two tables in the same MySQL database through a LEFT JOIN. Records in table 1 can have zero matches, one match or multiple matches in table 2.

Added complication is that I want to be able to search a combination of multiple search terms throughout the entire record row. For this I have used a CONCAT_WS to combine all fields into one searchable string.

This is my SQL query:

strSQL = "select articledata.*, oem_articlecodes.oem_articlecode FROM articledata " _
& " LEFT JOIN oem_articlecodes ON articledata.pid = oem_articlecodes.pid " _
& " WHERE (" & iLikeprod & ") ORDER BY articledata.articlecode ASC;"

This is the CONCAT_WS that I am building under the iLikeprod variable:

iLikeprod = CONCAT_WS(' ', articledata.pid, lower(articlecode), lower(brand), lower(name), lower(description)) LIKE '%" & Replace(Lcase(x), "'", "''") & "%' "

By using Split(strSearch) I am looping through all given search keywords (hence the x in the search field). For each additional search term I add an additional AND CONCAT_WS(...). So the more search terms the larger that variable becomes.

As long as I build this CONCAT_WS with only data that can be found in table 1 everything is fine and the search is instant. It finds records that have a match in table 2 and records that don't have match in table 2. Behaviour is as expected, but the search does not look for matching records in table 2.

The moment that I add one column from table 2 (not the key column) the search suddenly takes around 6 seconds. It does eventually return the correct results, but it takes absolute ages.

For now I had only changed the first of the CONCAT_WS to this (see the added 'oem_articlecode'):

CONCAT_WS(' ', articledata.pid, lower(articlecode), lower(oem_articlecode), lower(brand), lower(name), lower(description)) LIKE '%" & Replace(Lcase(x), "'", "''") & "%' "

For some reason this seems to causing a ton more processing, but I do not understand why.

At first I thought it might be caused by the inclusion inside the CONCAT_WS, but even when I take just this field outside the CONCAT it remains slow. Removing it immediately speeds up the search again. I have tried putting the relevant column in table 2 on INDEX, but that does not seem to make any difference.

Does anybody know what could be causing this and what I need to change? I have tried to keep this to the basic information, but let me know if anything else is needed. Thank you for your time.

EDIT: As per request below an example of a complete query with one search term and inclusion of oem_articlecode from table 2:

strSQL = "select articledata.*, oem_articlecodes.oem_articlecode FROM articledata LEFT JOIN oem_articlecodes ON articledata.pid = oem_articlecodes.pid WHERE (CONCAT_WS(' ', articledata.pid, lower(articlecode), lower(oem_articlecode), lower(brand), lower(name), lower(description)) LIKE '%" & Replace(Lcase(x), "'", "''") & "%' ") ORDER BY articledata.articlecode ASC;"
m4v21
  • 33
  • 7
  • Where to start, this is not how you should query a SQL database. – user692942 Jul 21 '22 at 20:11
  • 1
    Please show an example of the generated SQL (I don't know what to di with `iLikeprod`.) – Rick James Jul 22 '22 at 04:05
  • @RickJames, I did mention the iLikeprod. If you have one search term, then it would look like this: `strSQL = "select articledata.*, oem_articlecodes.oem_articlecode FROM articledata " _ & " LEFT JOIN oem_articlecodes ON articledata.pid = oem_articlecodes.pid " _ & " WHERE (CONCAT_WS(' ', articledata.pid, lower(articlecode), lower(brand), lower(name), lower(description)) LIKE '%" & Replace(Lcase(x), "'", "''") & "%' ") ORDER BY articledata.articlecode ASC;"` – m4v21 Jul 22 '22 at 07:24
  • @m4v21 Please don’t add code blocks in comments instead [edit] the question and add them there. – user692942 Jul 22 '22 at 07:54
  • @user692942 Thanks for your feedback. I won't be the first person that wants multiple search terms to produce results from multiple columns combined. If this is not the way, what method should I be looking at? As stated the query works perfectly well, but takes a perfomance hit when including a column from table 2 (oem_articlecodes). I am starting to think the problem is that there can be multiple matches in table 2. I've gone over the solutions in the thread you linked, but do not see how this would apply here. I am open to suggestions. – m4v21 Jul 22 '22 at 07:55
  • @m4v21 Thought "It won't use an index with LIKE and a leading wildcard" was pretty self explanatory in the accepted answer. As for the approach, allowing direct manipulation of the SQL instead of using a [parameterised query](https://stackoverflow.com/a/7661608/692942) is a massive security risk as it leaves the application open to the risk of [SQL Injection Attacks](https://en.m.wikipedia.org/wiki/SQL_injection). – user692942 Jul 22 '22 at 08:04
  • @user692942 thank you for your additional input and the query suggestions. It is absolutely my lack of knowledge on the subject, but I was not able to apply anything I read in that linked thread to my problem. If I am missing an obvious tweak in my original code, then please do let me know. Otherwise I will just take my losses on this and move the search in the second table to a separate query. Thanks for your time. – m4v21 Jul 22 '22 at 13:19
  • Doing multi line where you go sql = "this line" sql = sql & "next line" etc etc .. that is super slow .. you want it all on one line .. and if its possible .. put it in a stored procedure. – easleyfixed Jul 25 '22 at 18:13
  • Thank you, @easleyfixed. I will look into the stored procedure option. – m4v21 Jul 27 '22 at 10:09
  • With a suitable Collation, you don't need `LOWER()`. – Rick James Aug 01 '22 at 18:10
  • Putting function calls and expressions in `WHERE` usually leads to non-sargable queries. That is, it must scan all the rows with no help from INDEXes. – Rick James Aug 01 '22 at 18:11
  • Thanks for your comments @RickJames. I think I will need to start from scratch and first try again to find working examples for MySQL search pages. Searching multiple columns with multiple keywords must have been done a million times before. – m4v21 Aug 03 '22 at 07:07
  • @m4v21 - A FULLTEXT index can include multiple columns. It will search for "words", not arbitrary strings. -- Still this may be sufficient for your needs. – Rick James Aug 11 '22 at 17:20

0 Answers0