complex slow and or statments

timgolding · 12-06-2012, 10:19 AM

I have an complex mysql statement that works but is very slow. About 6 secs to complete on the development server. Here it is

Code:

SELECT SITES_PRODUCT.PRODUCT_ID AS PROD_ID, SITES_RANGE.RANGE_ID, COUNT( DISTINCT PRODUCT_ID ) AS NUM_PRODUCTS, SITES_RANGE.NAME AS RANGE_NAME, SITES_RANGE.URL_NAME AS RANGE_URL, SITES_PRODUCT.URL_NAME AS PRODUCT_URL, SITES_RANGE.ORDERING, SITES_RANGE.SHRT_DESC, SITES_RANGE.LONG_DESC, SITES_RANGE.CAT_OR_LINK, SITES_RANGE.URL_LINK, FILENAME, TITLE, ALT, CAPTION, SITES_CATEGORY.LIVE AS PRODUCT_CATEGORY_LIVE, SITES_CATEGORY.BLOCK_LIST AS BLOCK_LIST, SITES_CATEGORY.LONG_DESC AS CATEGORY_DESC, SITES_CATEGORY.NAME AS CATEGORY_NAME
FROM SITES_RANGE, SITES_PRODUCT, SITES_CATEGORY, RIL, IMAGE
WHERE (
  SITES_RANGE.LIVE =1
  AND SITES_PRODUCT.LIVE =1
  AND RIL.LIVE =1
  AND IMAGE.LIVE =1
  AND SITES_RANGE.RANGE_ID = SITES_PRODUCT.RANGE_ID
  AND SITES_RANGE.RANGE_ID = RIL.RANGE_ID
  AND RIL.IMAGE_ID = IMAGE.IMAGE_ID
  AND SITES_PRODUCT.CATEGORY_ID = SITES_CATEGORY.CATEGORY_ID
  AND SITES_CATEGORY.SITE_ID =15
  AND SITES_CATEGORY.URL_NAME = 'Electric'
)
OR (
  SITES_RANGE.LIVE =1
  AND SITES_RANGE.CAT_OR_LINK =1
  AND RIL.LIVE =1
  AND IMAGE.LIVE =1
  AND SITES_RANGE.RANGE_ID = RIL.RANGE_ID
  AND RIL.IMAGE_ID = IMAGE.IMAGE_ID
  AND SITES_RANGE.CATEGORY_ID = SITES_CATEGORY.CATEGORY_ID
  AND SITES_CATEGORY.SITE_ID =15
  AND SITES_CATEGORY.URL_NAME = 'Electric'
)
GROUP BY SITES_RANGE.RANGE_ID
ORDER BY SITES_RANGE.ORDERING DESC , SITES_RANGE.NAME ASC
LIMIT 0 , 30

What i am trying to achieve is using the foreign fields RANGE_ID and CATEGORY_ID in SITES_PRODUCT (all the products) select all the ranges that belong to that category that either a have live products in them. It gets the image information at the same time RIL means Range image list and is a many to many relationship. The first part of the where clause achieves this nicely. However some ranges in the database are just URL links to other pages such as an offer or a link to another site. Therefore these ranges don't have any products with that range id and that category id. But they have a flag CAT_OR_LINK set to 1 and have a reference to their parent category. So for instance SITES_RANGE.CATEGORY_ID is the reference to the category this range belongs to for links. SITES_RANGE.CATEGORY_ID is null where CAT_OR_LINK=0 (standard ranges).

I need to figure out some way of running a single query that takes all this in to consideration and outputs to a single result in a small amount of time, if it is possible. I'm trying to avoid making separate queries for this. Hope this makes sense.

Old Pedant · 12-06-2012, 06:02 PM

So what happens when you ask MySQL to EXPLAIN that query? What do the results of EXPLAIN say?

timgolding · 12-07-2012, 09:22 AM

EXPLAIN gives me

Code:

id |	select_type 	| table 	       | type 	| possible_keys     | key 	   | key_len 	| ref 	                                    | rows          | Extra 
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1  |	SIMPLE 	        | SITES_CATEGORY       | ALL 	| PRIMARY 	    | NULL 	   | NULL 	| NULL 	                                    | 174        | Using where; Using temporary; Using filesort
1  |	SIMPLE 	        | SITES_PRODUCT        | ALL 	| NULL 	            | NULL 	   | NULL       | NULL 	                                    | 2725       | Using where
1  |	SIMPLE 	        | RIL 	               | ALL 	| PRIMARY 	    | NULL 	   | NULL 	| NULL 	                                    | 423 	 | Using where
1  |	SIMPLE 	        | SITES_RANGE 	       | eq_ref | PRIMARY 	    | PRIMARY      | 2 	        | toolsonline_central.RIL.RANGE_ID 	    | 1 	 | Using where
1  |	SIMPLE 	        | IMAGE 	       | eq_ref | PRIMARY 	    | PRIMARY      | 4 	        | toolsonline_central.RIL.IMAGE_ID 	    | 1 	 | Using where

not sure what to make of that though

Old Pedant · 12-07-2012, 07:18 PM

Well, that says it is *NOT* using any keys (indexes) except for the joins to SITES_RANGE and IMAGE.

So now your goal is to figure out why and if there is a way to make MySQL use the keys and whether using the keys would help.

My gut feeling is that, as a first level of attack, you should create a multi-key on SITES_CATEGORY:

Code:

CREATE INDEX category_site_url ON SITES_CATEGORY( site_id, url_name );

Do that, run EXPLAIN again, and then look for the next likely candidate.

timgolding · 12-10-2012, 10:06 AM

Well that index got it down to about 4 seconds. Which is an improvement, but still needs some work

Code:

id  select_type  table           type       possible_keys              key                key_len   ref 	                           rows 	Extra
1   SIMPLE       SITES_CATEGORY  ref        PRIMARY,CATEGORY_SITE_URL  CATEGORY_SITE_URL  259       const,const                        1        Using where; Using temporary; Using filesort
1   SIMPLE       SITES_PRODUCT   ALL        NULL                       NULL               NULL      NULL                               2725     Using where
1   SIMPLE       RIL             ALL        PRIMARY                    NULL               NULL      NULL                               423      Using where
1   SIMPLE       SITES_RANGE     eq_ref     PRIMARY                    PRIMARY            2         toolsonline_central.RIL.RANGE_ID   1        Using where
1   SIMPLE       IMAGE           eq_ref     PRIMARY                    PRIMARY            4         toolsonline_central.RIL.IMAGE_ID   1        Using where

This is my explain results now i guess i need some sort of a key that relates to product and image now?

Old Pedant · 12-10-2012, 07:53 PM

And possibly other keys.

Look at your WHERE clauses:

Code:

  AND SITES_PRODUCT.LIVE =1
...
  AND SITES_RANGE.CAT_OR_LINK =1
...
  AND SITES_RANGE.RANGE_ID = ...

And so on. Any place you see a WHERE condition that might be forcing MySQL to do a complete table scan (and where the number of records in the table is high...in the thousands), you have a potential index candidate.

You know your data better than we do.

One thing: You can always add an index just to see if it helps. If it doesn't, remove it.

timgolding · 12-11-2012, 02:06 PM

I put an index on all foreign keys and cat_or_link and it now takes 3 seconds. I can't really think of anywhere else to put a key that would help with this particular query. Wonder if the query itself needs optimising?

Old Pedant · 12-11-2012, 08:58 PM

Show the EXPLAIN again?

It *is* possible you have reached a limit, you know.

timgolding · 12-12-2012, 11:58 AM

Code:

id  select_type  table           type      possible_keys              key                key_len   ref                               rows    Extra
1   SIMPLE       SITES_CATEGORY  ref       PRIMARY,CATEGORY_SITE_URL  CATEGORY_SITE_URL  259       const,const                       1       Using where; Using temporary; Using filesort
1   SIMPLE       SITES_PRODUCT   ALL       CATEGORY_ID,RANGE_ID       NULL               NULL      NULL                              2725    Using where
1   SIMPLE       RIL             ALL       PRIMARY                    NULL               NULL      NULL                              423     Using where
1   SIMPLE       SITES_RANGE     eq_ref    PRIMARY,CAT_INDEX          PRIMARY            2         toolsonline_central.RIL.RANGE_ID  1       Using where
1   SIMPLE       IMAGE           eq_ref    PRIMARY                    PRIMARY            4         toolsonline_central.RIL.IMAGE_ID  1       Using where

I split the query into three and then used php do do the sorting and i get a microtime of about half a second. Maybe i should leave it as three queries. I just don't understand why three queries take less time than the one, that's why i wondered if the query is the problem. I have a hunch it is something to do with the or but don't see how i can use joins or anything in this example.

P.S the indexes i have added have improved the speed of the site in general...

Old Pedant · 12-12-2012, 09:27 PM

Lines 2 and 3 showing NULL key used tell me there is something not happening right.

MySQL *DOES* occasionally ignore keys when it shouldn't. You can force an index to be used, but even that will sometime be overridden.

I'd leave it like it is for now, with 3 queries, and then play with it in the background as you have time.

timgolding · 12-13-2012, 12:09 PM

Ok thanks for the help