Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    Senior Coder timgolding's Avatar
    Join Date
    Aug 2006
    Location
    Southampton
    Posts
    1,519
    Thanks
    114
    Thanked 110 Times in 109 Posts

    complex slow and or statments

    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.
    You can not say you know how to do something, until you can teach it to someone else.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,210
    Thanks
    75
    Thanked 4,343 Times in 4,309 Posts
    So what happens when you ask MySQL to EXPLAIN that query? What do the results of EXPLAIN say?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Senior Coder timgolding's Avatar
    Join Date
    Aug 2006
    Location
    Southampton
    Posts
    1,519
    Thanks
    114
    Thanked 110 Times in 109 Posts
    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
    Last edited by timgolding; 12-07-2012 at 09:36 AM.
    You can not say you know how to do something, until you can teach it to someone else.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,210
    Thanks
    75
    Thanked 4,343 Times in 4,309 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    timgolding (12-10-2012)

  • #5
    Senior Coder timgolding's Avatar
    Join Date
    Aug 2006
    Location
    Southampton
    Posts
    1,519
    Thanks
    114
    Thanked 110 Times in 109 Posts
    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?
    You can not say you know how to do something, until you can teach it to someone else.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,210
    Thanks
    75
    Thanked 4,343 Times in 4,309 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    timgolding (12-11-2012)

  • #7
    Senior Coder timgolding's Avatar
    Join Date
    Aug 2006
    Location
    Southampton
    Posts
    1,519
    Thanks
    114
    Thanked 110 Times in 109 Posts
    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?
    You can not say you know how to do something, until you can teach it to someone else.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,210
    Thanks
    75
    Thanked 4,343 Times in 4,309 Posts
    Show the EXPLAIN again?

    It *is* possible you have reached a limit, you know.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #9
    Senior Coder timgolding's Avatar
    Join Date
    Aug 2006
    Location
    Southampton
    Posts
    1,519
    Thanks
    114
    Thanked 110 Times in 109 Posts
    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...
    You can not say you know how to do something, until you can teach it to someone else.

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,210
    Thanks
    75
    Thanked 4,343 Times in 4,309 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    timgolding (12-13-2012)

  • #11
    Senior Coder timgolding's Avatar
    Join Date
    Aug 2006
    Location
    Southampton
    Posts
    1,519
    Thanks
    114
    Thanked 110 Times in 109 Posts
    Ok thanks for the help
    You can not say you know how to do something, until you can teach it to someone else.


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •