Welcome Guest, Not a member yet? Register   Sign In
Database query very slow with just one addition
#1

Hi,

I have this query that runs very slowly (nearly 8 seconds). When I remove a single WHERE statement it runs very fast (0.02 seconds). I think it might be something to do with the where statement being a string comparison on a joined table. Does anyone know why this would be.

Very Slow Query
PHP Code:
$products $this->db->from('products')
 
  ->select('product_id, product_model, product_url, product_short_name, product_supplier_id, product_google_feed, product_colour_variations, product_live, pcolour_full_model, mp_model, mp_leisurebench_item')
 
  ->join('brands''products.product_brand_id = brands.brand_id''LEFT')
 
  ->join('product_colours''products.product_model = product_colours.pcolour_model''LEFT')
 
  ->join('netsuite_master_product_list''(products.product_model = netsuite_master_product_list.mp_model OR  product_colours.pcolour_full_model = netsuite_master_product_list.mp_model)')
 
  ->where('mp_leisurebench_item''No' // This line appears to be the culprit
 
  ->group_start()
 
     ->where('product_supplier_id'0)
 
     ->or_where('brand_on_netsuite'1)
 
  ->group_end()
 
  ->get()
 
  ->result_array(); 
 

When I remove the line "->where('mp_leisurebench_item', 'No')" the query is very fast.

In the end I removed that line and did a foreach loop to unset the unwanted data but am still confused about why this one WHERE statement would slow this query so considerably. I would clearly use a binary TRUE/FALSE normally but the data is coming from another system that outputs a string "No" or "Yes" which I have no control over unfortunately.

Just curious if anyone knows why this would happen.

Thanks in advance,

Paul.
Reply
#2

Do you have the field indexed?

Fields you use like that should all be indexed for speed look ups.
What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply
#3

(04-28-2019, 08:13 AM)InsiteFX Wrote: Do you have the field indexed?

Fields you use like that should all be indexed for speed look ups.

Ah, good suggestion. It is not indexed, I will give that a try tomorrow and see if it fixes it.

Actually, my bad, I completely forgot about indexing. Thanks for the suggestion.

Paul.
Reply




Theme © iAndrew 2016 - Forum software by © MyBB