a.stilliard

anything?

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.

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.

@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

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!

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.

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

Still had nothing here so i have opened this on the mysql forums too... http://forums.mysql.com/read.php?52,371552,371552#msg-371552 Further developments may happen here in-case you have an answer or are looking for a similar issue.

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.

Im not sure if theres a way to increase this size but there might be some config for it someone else knows, Apart from that what i tend to do is split the file into multiple parts, should still work so long as the parts go in, in the correct order.

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.

You can select something as something else, so you could select the word 'luck' as f5 SELECT f1, f2, 'luck' AS f5 FROM tablename

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;

Thats what im fearing now, just loggin a ticket to my hosting company about it. Thanks for your help anyway thorpe.

i have "allow_url_fopen on" in my php.ini file. No difference if i turn it on or off though. EDIT: I have uploaded the files to 2 different servers now, a shared server where im havig this issue, and my server where i can set allow_url_fopen there, and it works on my server when its set. But on the shared server it seems to be ignoring the php.ini file allow_url_fopen .

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 ?

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.

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 )

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 )