Slow Performance of Sql Query

Question

I am writing a SQL query which gives me a slow performance. Because of that it gives me 504 gateway timeout problem. Please help me to remake this query so that my output results faster. I will put the query below.

select 
  r.c1,
  parent_item.c2,
  parent_item.c3,
  parent_item.c4,
  parent_item.c5,
  parent_item.c6,
  parent_item.c7,
  pt.c8,
  child_item.c9,
  t.c10,
  child_item.c11,
from
  table1 child_item,
  table2 t,
  table3 r,
  table1 parent_item,
  table4 pt
where
  r.col1 = child_item.id and
  t.id=child_item.typeid and
  parent_item.id = r.parent_itemid and
  pt.id = parent_item.typeid  and parent_item.id=800 and 
  parent_item.id = (select
                      itemid
                    from
                      table5
                    where
                      itemid=parent_item.id  
                     ((10!= 1) ?  and (holder_itemid in (10,100) and level > 0): "")) and
  child_item.id = (select
                     itemid
                   from
                     table5
                   where
                     itemid=child_item.id  
                     ((10 != 1) ?  and (holder_itemid in (10,100) and level > 0) : ""))
order by
  r.parent_itemid,
  r.relation_typeid,
  r.ordinal

Answer 1

It’s very difficult to accurately point out performance problems if we don’t know your database schema. (The database schema means your table definitions, indexes etc.)

Also, what is this bit supposed to do?

((10!= 1) ?  and (holder_itemid in (10,100) and level > 0): ""))

AFAICS, this is not a valid SQL query, and will result in a syntax error.

Answer 2

It's likely the two sub-queries, but we don't have enough information about your schema.

You should run your query though EXPLAIN and see what it says.

JOINs might help, but again, we can't tell for sure.