Re: Faster SQL - newbie needs help

[Rick James]

SELECT  a.*
    FROM  jos_zoo_item as a
    JOIN 
      ( SELECT  b.item_id
            FROM  jos_zoo_search_index as b
            WHERE  b.element_id = '688d9a68-5ce2-4c53-b226-a053499ef634'
              AND  b.value LIKE '%Stockholms län%') as b ON a.id = b.item_id
    JOIN 
      ( SELECT  c.item_id
            FROM  jos_zoo_search_index as c
            WHERE  c.element_id = 'b018bdde-dc6e-401a-9619-7c0edfb490a7'
              AND  c.value LIKE '%Data och IT%') as c ON a.id = c.item_id
    JOIN 
      ( SELECT  d.item_id
            FROM  jos_zoo_search_index as d
            WHERE  d.value LIKE '%Britny%') as d ON a.id = d.item_id

1. EAV design is terrible on performance.

2. UUIDs are big, hence bad on performance.

3. JOINs often work much faster than subqueries.

4. FULLTEXT (if you are searching on words) works much faster than LIKE '%...'.