Jump to content

a.stilliard

Members
 View Profile  See their activity

  • Posts

    19

  • Joined

  • Last visited

    Never

 Content Type 

Posts posted by a.stilliard

    Recursive SQL Function / Drill down in relational MySQL Tables

    in MySQL Help

    Posted

     

    Fenway, sorry my reply is late, i am close to cracking the query now.

    The only bit slowing it down now is the part where i have 5 different category fields on the products table to check against.

    When im running it against the first category field, it works well and fast, then as i add the others (currently via UNION'S) it drasticly increases the speed !

    Each product will definetly relate a category by the "category" field, but may also may relate with the "category_2" field up to _5.

     

    The following 2 querys just list top level categories.

     

    FAST QUERY, but only one category: 

    EXPLAIN   
  SELECT distinct parent.id, parent.category_name, parent.relatedid, parent.sort_order, 
  IF((
    SELECT 1
    FROM categories sub
    WHERE sub.status != 'deleted' 
    AND sub.status != 'draft'
    AND sub.relatedid = parent.id
    LIMIT 0, 1
  ) > 0, 1, 0) AS subsFound 
  FROM categories parent
  INNER JOIN (
    SELECT node.lft 
    FROM categories node 
    INNER JOIN products p ON p.category = node.id
    WHERE 1 
    AND node.status != 'deleted' 
    AND node.status != 'draft' 
    AND p.status != 'deleted' 
    AND p.status != 'draft' 
    GROUP BY node.lft
  ) AS node
  ON node.lft BETWEEN parent.lft AND parent.rgt 
  WHERE parent.relatedid = '0'
  ORDER BY ABS(parent.sort_order), parent.sort_order, parent.category_name, parent.id

     

    New SLOW SQL, but 5 related categorys to each product : 

    EXPLAIN   
  SELECT distinct parent.id, parent.category_name, parent.relatedid, parent.sort_order, 
  IF((
    SELECT 1
    FROM categories sub
    WHERE sub.status != 'deleted' 
    AND sub.status != 'draft'
    AND sub.relatedid = parent.id
    LIMIT 0, 1
  ) > 0, 1, 0) AS subsFound 
  FROM categories parent
  INNER JOIN (
    SELECT node.lft 
    FROM categories node 
    INNER JOIN (
    	SELECT category AS cat_id, status, id FROM products
        UNION DISTINCT 
    	SELECT category_2 AS cat_id, status, id FROM products
        UNION DISTINCT 
    	SELECT category_3 AS cat_id, status, id FROM products
        UNION DISTINCT 
    	SELECT category_4 AS cat_id, status, id FROM products
        UNION DISTINCT 
    	SELECT category_5 AS cat_id, status, id FROM products
    ) p ON p.cat_id = node.id
    WHERE 1 
    AND node.status != 'deleted' 
    AND node.status != 'draft' 
    AND p.status != 'deleted' 
    AND p.status != 'draft' 
    GROUP BY node.lft
  ) AS node
  ON node.lft BETWEEN parent.lft AND parent.rgt 
  WHERE parent.relatedid = '0'
  ORDER BY ABS(parent.sort_order), parent.sort_order, parent.category_name, parent.id

     

    EXPLAIN Output:

     

    id select_type table type possible_keys key key_len ref rows Extra
    1 PRIMARY <derived3> ALL NULL NULL NULL NULL 258 Using temporary; Using filesort
    1 PRIMARY parent ALL NULL NULL NULL NULL 879 Using where
    3 DERIVED <derived4> ALL NULL NULL NULL NULL 49488 Using where; Using temporary; Using filesort
    3 DERIVED node eq_ref PRIMARY PRIMARY 4 p.cat_id 1 Using where
    4 DERIVED products ALL NULL NULL NULL NULL 24743  
    5 UNION products ALL NULL NULL NULL NULL 24743  
    6 UNION products ALL NULL NULL NULL NULL 24743  
    7 UNION products ALL NULL NULL NULL NULL 24743  
    8 UNION products ALL NULL NULL NULL NULL 24743  
    NULL UNION RESULT <union4,5,6,7,8> ALL NULL NULL NULL NULL NULL  
    2 DEPENDENT SUBQUERY sub ALL NULL NULL NULL NULL 879 Using where

     

     

    Any ideas of how to crack this last part.

    Recursive SQL Function / Drill down in relational MySQL Tables

    in MySQL Help

    Posted

    With that query i posted? yep.

    Baring in mind though there's about 1000 categories and 30,000 products, it takes from 45 seconds to several minutes to load testing in phpmyadmin, i need it to run in around a second or 2 at most.

    I have indexes set-up on the fields referenced, and i have tried to sort of optimize my query, but maybe i have missed something crucial.

     

    Recursive SQL Function / Drill down in relational MySQL Tables

    in MySQL Help

    Posted

    @fenway

     

    Because its a perfectly logical pattern, its just the mysql "adjacency list model" or parent-child model.

    When we started this application we had no intention for this facility and therefor chose not to go down the more complex mysql "nested set model".

     

    I have since posting this added support for using the nested set model into my table, using left right and level columns, and i have been able to get the script work.

    Unfortunately its now very slow, here is the SQL i have, if anyone knows how i can speed this up please let me know as I'm new to using this model.

     

     

      SELECT parent.id, parent.category_name, parent.relatedid, parent.sort_order,

      IF((

        SELECT node.id

        FROM categories node

        WHERE node.lft BETWEEN parent.lft AND parent.rgt

        AND EXISTS (

            SELECT null FROM products p

            WHERE p.category = node.id

        )

        LIMIT 0, 1

      ) > 0, 1, 0) AS productsFound

      FROM categories parent

      HAVING productsFound > 0

      ORDER BY ABS(parent.sort_order), parent.sort_order, parent.category_name, parent.id

     

     

    Request Website Page Speed / Load Times

    in PHP Coding Help

    Posted

    Hi,

    Im currently building a script to monitor / check on my sites and report back to me with their availability, HTTP Status Codes, and importantly here, the PAGE LOAD TIME.

     

    Im currently doing this with curl requests, and this works for getting status's, and finding out many other things about the pages / sites. BUT it will only give me the time it took to grab the html on the page.

     

    Is there a way to set it to download more sort of like a browser would. So it would record the total time in which it takes a user to download the html, css, JavaScript, images etc.

     

    I have thought about just using the CURL script to download the html originally, and using the multiple settings (curl_multi_*) to download each of the components to make up the page, but this seems long winded.

    I wondered if there was a better way than using curl, or maybe a pre-built set of classes ready for this but i have searched google and no such luck.

     

    I know of some online tools that request pages sort of how i want to, e.g. http://tools.pingdom.com/, and http://loadimpact.com/pageanalyzer.php but i don't need all the info they generate, just the end result page load time that the user would experience. 

     

    Not sure if this is possible in PHP, may need to switch to python or another language to do this part of the script.

    If anyone else has used/built a script capable of this please let me know!

     

    Cheers!

    Recursive SQL Function / Drill down in relational MySQL Tables

    in MySQL Help

    Posted

     

    Im aware my explainations maybe causing more confussion than help, maybe this makes sense,

     

    I need a function or proceedure to feed in the ID of a category to perform something like the following, but over an unlimited number of levels of categories.

     

    SELECT * 
FROM categories c1
WHERE EXISTS(
  SELECT null 
  FROM products p 
  WHERE p.category = c1.id 
)
OR EXISTS(
  SELECT null
  FROM categories c2
  WHERE c2.relatedid = c1.id
  AND EXISTS(
    SELECT null 
    FROM products p
    WHERE p.category   = c2.id
  )
)

     

    This SQL here was what i had before the function i built but i would need to literally have the EXISTS statement repeating for each level and i will be adding many filter params to this later so having it repeat many times will cause problems later on.

     

    Hope this extra bit sparks some more help. 

    Thanks in advance for anything of use.

     

    Recursive SQL Function / Drill down in relational MySQL Tables

    in MySQL Help

    Posted

    Just incase this helps anyone reconise my issue, i have added the following comment to the other forum where this has been posted.

     

     

    The table structure that will come into use for this function is just the primary key id and a relation to other categories via relatedid, relatedid will be 0 if top level.

     

    Ive taken out the filters to make the statement much more simple, the filters are not so easy to show examples of, but if you were to think of this as only showing categories where products exist at a lower level it makes more sense. Sorry for mentioning the filters before, thats just complicated the matter i think.

     

    The end result needs to just be able to drill down through related categories, where there are any products at the lowest category level.

     

    So the most basic table i can re-create with this would be...

     

    CREATE TABLE `categories` (
  `id` int(20) NOT NULL auto_increment,
  `category_name` varchar(100) NOT NULL,
  `relatedid` int(20) NOT NULL,
  PRIMARY KEY  (`id`) 
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE `products` (
  `id` int(20) NOT NULL auto_increment,
  `product_name` varchar(100) NOT NULL,
  `category` int(20) NOT NULL,
  PRIMARY KEY  (`id`) 
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

     

    For example, there could be a few top level categories ( categories with no related id ) each with a couple of (sub)categories relating to them, and again for several levels until one of those categories has products.

     

    So I need to build my function to be able to drill from top-level categories downwards where there are products in lower level related categories and to return a true or false value if that condition exists.

     

    SELECT by the hour!

    in MySQL Help

    Posted

    Your showtime field, how are the times formated? standard mysql time (now()) or just the hour?

     

    have a look round for things like... "mysql time differences", or "mysql time date" maybe.

     

    EDIT, i cant gues the sql without an idea of the data format.

    combining 2 resultsets

    in MySQL Help

    Posted

    Have you tried using AS?

    e.g.

    SELECT distinct(id), message, username, type AS newType, date_posted FROM table A WHERE whereclause

    UNION

    SELECT distinct(id), message, username, page_owner AS newType, date_posted FROM table B WHERE whereclause

    ORDER BY date_posted DESC LIMIT 0, 12.

    Recursive SQL Function / Drill down in relational MySQL Tables

    in MySQL Help

    Posted

     

    Hello All,

     

    I have an new issue regarding drilling down in a sql statement and wondered if anyone could offer me some help/advice on the matter.

     

    I have built an app that lists categories on our ecommerce sites, it just lists them in order of top level ones and underneath all the related (sub)categories until you get to the bottom level where the products are. Very simple. Heres the problem, we have now introduced make model and year filters onto the sites and now the lists must only display those categories which have products in them at the lowest (sub)category level which relate to the filters selected.

     

    So drawing up the select on the products inside is easy, i have the sql all written for that, but its the drilling down from top level categories whose sub categories may have further sub categories (essentially multiple other layers of sub catgeories, probably up to 5 or 6 levels) that will eventually drill down to products relating to the selected filters.

     

    I thought for this the best way would be to do this similar to my php function that loops the categories originally, which is a simple recursize function.  I've now writen a similar thing in SQL, unfortunately i did not realise you cannot have recursize functions in SQL, or at least in my version as it throws the following error "Recursive stored functions and triggers are not allowed."

     

    I'm now attempting to create a procedure to do the same and then a function that would call that procedure so i can use it in the select satement. The end result would be a function i could attach onto the where statements of my current app, which would just say if the current category (by id) contains products or sub cats with eventual  products that relate to the search criteria. Although for now im just trying to do the part without the filter, and add in the filtering part after. 

     

    Here is the sql function i wrote which demonstrates what im trying to acheive. Ive removed the filter parts because they are simple but very long and not really to do with the current issue, although they are the original cause.

     

    If you have any advise on this at all or if you can see im heading in the wrong direction with this please let me know.

    Also this "Recursive stored functions and triggers are not allowed.", is this relating only to older versions of mysql? im currently using 5.0.9, do you think its worth upgrading?

     

    DROP FUNCTION IF EXISTS `relatedSubs`;
DELIMITER $$
CREATE FUNCTION relatedSubs(catid INT(20))
RETURNS BOOLEAN 
BEGIN 
  
  DECLARE subProductsFound BOOLEAN;
  DECLARE subCatsFound BOOLEAN;
  
  SET subProductsFound = (
  	SELECT COUNT(id) 
    FROM products p 
    WHERE  p.category   = catid 
    LIMIT 0, 1
  ); 
  IF ( subProductsFound>0 ) 
  	THEN RETURN 1;
  END IF;
  
  SET subCatsFound = (
  	SELECT COUNT(id) 
    FROM categories c 
    WHERE c.relatedid=catid
    AND relatedSubs(c.id) 
    LIMIT 0, 1
  ); 
  IF ( subCatsFound>0 ) 
  	THEN RETURN 1;
  END IF;
  
  RETURN 0;
  
END$$
DELIMITER ;

# STATEMENT HERE 
SELECT * FROM categories WHERE relatedSubs(id) AND relatedid=0;

     

    Local CURL Request, or file_get_contents locally but to get the result html/xml

    in PHP Coding Help

    Posted

    Thanks for the reply, i had been trying using it with the full path but no luck.

    But i now find i had error reporting turned off, and the file_get_contents was giving this error...

     

    Warning: file_get_contents(http://domain/test2.php) [function.file-get-contents]: failed to open stream: Connection refused in /location/domain/public_html/test.php on line 8

     

    This is the error given from a test.php file which simply runs the following...

    <?php

     

    error_reporting(E_ALL|E_STRICT);

    ini_set('display_errors', 'on');

     

    $file = "http://domain/test2.php";

     

    $fileContent = file_get_contents($file);

     

    echo "Result: " . $fileContent;

     

    ?>

     

    and test2.php contains the very very simple test content of...

    <?php

    echo "hello";

    ?>

     

    So im expecting to run test.php and for "Result: hello" to be printed to my browser.

    The file definetly exists when i go directly to it in my browser, but the error remains.

    I assume this is to do with some server settings, maybe something blocking internal requests using http ?

    Local CURL Request, or file_get_contents locally but to get the result html/xml

    in PHP Coding Help

    Posted

    Hi,

    i need to find a php core function, or even one built for/into CodeIgniter, that would allow me to locally on a domain get the result of a php file.

     

    The php file generates xml, its stand alone but something i built a long time ago, what it does is great but complex and working it into the new system would take too long.

    Its on the same domain as the new system, just in a sub directly, and to make it more complex, the file called is for example file.xml, but actually its using .htaccess and an index .php page to run it.

    Now if this file was on a different domain, i could call file_get_contents and get the file, and it would give me the result xml, but its on the same domain which would typically give me the php code, the actual file content rather than its result. but even that i could solve by using eval on it. but because this file only exists via htaccess and an index.php page i cant even do that.

     

    Even the eval option would not be great as i may have some functions with the same names in both that file and the new project which would cause issues.

     

    After that ramble of an explanation basically what i need is a file_get_contetns that would work as-if its the user viewing it, or as-if its on another domain.

    I have tried curl requests but no such luck, so any help would be brilliant and if there is a set of curl params or stream params for file_get_contents i could use please point me in there direction.

     

    Thanks in advance.

    Slow advanced search on MySQL.

    in MySQL Help

    Posted

    Cheers zeodragonzord,

    I have the code limiting already when the users don't select options,

    but the INNER JOIN works much better cheers.

    And im reviewing my INDEX's on the tables thanks.

     

    Much faster query now.

     

    Also incase anyone else had similar troubles, improved the query further with help from Alex @ http://explainextended.com/

     

    SELECT  p.id
FROM    (
        SELECT  product_id
        FROM    options op
        WHERE   op.option_group = 'Tread'
                AND
                CASE op.search_type
                        WHEN 'more-than' THEN ( op.option_desc >= 25 )
                        WHEN 'less-than' THEN ( op.option_desc <= 25 )
                        WHEN 'range-array' THEN ( op.option_desc LIKE '%25%' )    
                        ELSE ( op.option_desc = '25' )
                END
        GROUP BY
                product_id
        ) opm
JOIN    products p
ON      p.id = opm.product_id
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    options op
        WHERE   op.product_id = p.id     
                AND op.option_group = 'Hole Diameter'
                AND
                CASE op.search_type
                        WHEN 'more-than' THEN ( op.option_desc >= 6.3 )
                        WHEN 'less-than' THEN ( op.option_desc <= 6.3 )
                        WHEN 'range-array' THEN ( op.option_desc LIKE '%6\.3%' )    
                        ELSE ( op.option_desc = '6.3' )
                END
        )

    Slow advanced search on MySQL.

    in MySQL Help

    Posted

    Hi all,

     

    I hope someone can help with a problem I am having with slow search on MySQL.

     

    I have a store which will hold around 50,000 products in a products table. Each product will have 14 options, giving 700,000 options in total. These are held in an options table which is joined via the product id.

     

    Users search for products based on the options via an Advanced Search menu.

     

    The users need to be able to select multiple options upon which to query.

    I would normaly use a JOIN if it was just the one option to select upon, but because its a variable number i thought it would be best to loop through the WHERE EXISTS statement.

     

    The issue i have currently is that the query is taking a minimum of 18 seconds (And that was a query when the tables only had a fraction of the total products in).

    If you can help us speed this up, or suggest an alternative idea that would be greatly appreciated.

     

    Here is the basis of the SQL code im currently using.

    (Showing an example of just 2 options selected so my code loops the sql for the EXISTS statement twice. )

     

     

    SELECT p.id FROM products p 
WHERE EXISTS (
    
    SELECT op.option_id    
    FROM options op
    WHERE op.product_id = p.id     
    AND op.option_group = 'Tread'
    
    AND CASE op.search_type
        WHEN 'more-than' THEN ( op.option_desc >= 25 )
        WHEN 'less-than' THEN ( op.option_desc <= 25 )
        WHEN 'range-array' THEN ( op.option_desc LIKE '%25%' )    
        ELSE ( op.option_desc = '25' )
    END     
    GROUP BY op.product_id
    LIMIT 0, 1
)
AND EXISTS (
    
    SELECT op.option_id    
    FROM options op
    WHERE op.product_id = p.id     
    AND op.option_group = 'Hole Diameter'
    
    AND CASE op.search_type
        WHEN 'more-than' THEN ( op.option_desc >= 6.3 )
        WHEN 'less-than' THEN ( op.option_desc <= 6.3 )        
        WHEN 'range-array' THEN ( op.option_desc LIKE '%6.3%' )            
        ELSE ( op.option_desc = '6.3' )
    END 
    GROUP BY op.product_id
    LIMIT 0, 1
)

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.