0

I currently have the following query and it works because I have a column in t2 that contains the same ikey as t1:

$sql = "
    SELECT 
        us_mkr.*,
        t1.ikey,
        t1.tag,
        t2.events
    FROM t1 
    LEFT JOIN us_mkr ON (us_mkr.mkr_key = t1.ikey)
    LEFT JOIN t1 ON (t1.ikey = t2.ikey)
    WHERE t1.slug = '" . $_GET['title'] . "'
      AND us_mkr.county LIKE '%" . addslashes($county) . "%'";

However, I would like to change that column in t2 to store a string instead of a single key value. So I was thinking that all I could do is to change my JOIn to INSTR, like this:

$sql = "
    SELECT 
        us_mkr.*,
        t1.ikey,
        t1.tag,
        t2.events
    FROM t1 
    LEFT JOIN us_mkr ON (us_mkr.mkr_key = t1.ikey)
    LEFT JOIN t1 ON INSTR(t2.ikeys, t1.ikey) > 0
    WHERE t1.slug = '" . $_GET['title'] . "'
      AND us_mkr.county LIKE '%" . addslashes($county) . "%'";

But it doesn't seem to work so I wonder what am I missing.

santa
  • 12,234
  • 49
  • 155
  • 255
  • 3
    Join operations can make use of indices when using single-valued fields. By choosing to use `INSTR`, you are likely to be worsening the performance of this query. What's the purpose of this change? – lemon May 18 '23 at 20:51
  • It sound like you should be using a intersection* table to record the many to many relationship. (* Or it can go by various other names https://stackoverflow.com/questions/3045034/whats-the-correct-name-for-an-association-table-a-many-to-many-relationship/40623379#40623379) – Dijkgraaf May 18 '23 at 21:09
  • @lemon I was just trying to reuse the content from t2 in several cases. – santa May 18 '23 at 21:16
  • 3
    *change that column in t2 to store a string instead of a single key value* - No! don't do that! Unless of course the goal is to prevent efficient data access and decrease your performance and concurrency? – Stu May 18 '23 at 21:17
  • @stu Sorry, was that a suggestion or sarcasm? – santa May 18 '23 at 21:19
  • @Dijkgraaf I think I will explore adding the intersection table. I assume it'll contain t1.key = t2.id what would my query look like? Sounds like select with select? It's largely outside of my skill level. – santa May 18 '23 at 21:22
  • @santa lol, well possibly maybe a little, just making the point that joining data via parsing strings is not how a relational database is designed to work - at least that's what I assumed you are saying you want to do - data you need to filter on, sort on, join on etc should be *atomic* otherwise the optimiser cannot seek on or benefit from the sorted nature if an index since it must examine each row every time to perform the *unsargable* operation. – Stu May 18 '23 at 21:26
  • Suggested reading: https://stackoverflow.com/a/3653574/20860 – Bill Karwin May 19 '23 at 02:42

0 Answers0