Jump to content

oarecare

Members
  • Posts

    12
  • Joined

  • Last visited

Profile Information

  • Gender
    Not Telling

oarecare's Achievements

Newbie

Newbie (1/5)

0

Reputation

  1. I am not a sql guru, that's why i asked my question. What i want to accomplish is: Have products that have different filters selected (non-exclusive) (color can be both pink and metallic) Be able to filter by saying 'a (laptop) that has a (nvidia or ati) video card, (3G or 4G) memory, AMD processor, (pink or blue) color, price between ($220 and $540) etc. Show the number of filtered products that match the remaining filters (made by hp - 7, made by sony - 9, 15,6 led display - 12 etc) so that i can add those as filters as well. I can change the table structure to accomodate a speed improvement. Was using left join because i didn't know what else to use to accomplish that. Regards
  2. so what is the best approach for combining indexes in order to have lightning-speed responses from the sql server? Or how should i go about doing that (even table-structure-wise)? thanks in advance.
  3. if i select color:black, i should be presented with totally black laptops,black+pink, black+white, black+grey, black+pink+grey and so on for use, a laptop can be home use, gaming and office all-in-one (multi-select) The scheme won't do me much good unless i resort to full text search...
  4. I want to show all the available filters for 'pink laptops made by sony that have a dedicated video card and ssd drive' for example Then the user might see: processor: amd (10), intel (7) battery: 4-cell (3), 8-cell (14) operating system: win8 (12), linux (5) hdd size: 128GB(5), 500Gb(12) and so on I'm not sure how to approach it. I've made a ton of entries to check on database performance.
  5. there was an index on idProduct and idFilter. (primary). Removed it, changed it to yours... no speed change select[...] Showing rows 0 - 18 ( 19 total, Query took 2.9600 sec) EXPLAIN SELECT f.id, f.name, f.type, count( pf.idProduct ) AS cnt FROM _filters f LEFT JOIN productFilters pf ON pf.idFilter = f.id WHERE 1 AND idProduct IN ( SELECT idProduct FROM productFilters WHERE idFilter =77 AND idProduct IN ( SELECT idProduct FROM productFilters WHERE idFilter =76 AND idProduct IN ( SELECT idProduct FROM productFilters WHERE idFilter =16 AND idProduct IN ( SELECT idProduct FROM productFilters WHERE idFilter =11 ) ) ) ) GROUP BY f.id ORDER BY f.name ASC [ Edit ] [ Skip Explain SQL ] [ Create PHP Code ] + Options id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY f ALL PRIMARY NULL NULL NULL 115 Using temporary; Using filesort 1 PRIMARY a ref filter filter 4 database.f.id 4791 Using where 2 DEPENDENT SUBQUERY productFilters index_subquery filter,index_on_ids index_on_ids 8 func,const 1 Using index; Using where 3 DEPENDENT SUBQUERY productFilters index_subquery filter,index_on_ids index_on_ids 8 func,const 1 Using index; Using where 4 DEPENDENT SUBQUERY productFilters index_subquery filter,index_on_ids index_on_ids 8 func,const 1 Using index; Using where 5 DEPENDENT SUBQUERY productFilters index_subquery filter,index_on_ids index_on_ids 8 func,const 1 Using index; Using where describe productFilters Full texts Field Type Null Key Default Extra Edit Edit Edit Inline Edit Copy Copy Delete Delete idProduct int(11) NO MUL NULL Edit Edit Edit Inline Edit Copy Copy Delete Delete idFilter int(11) NO MUL NULL show index from productFilters productFilters 1 filter 1 idFilter A 115 NULL NULL BTREE productFilters 1 index_on_ids 1 idProduct A 68871 NULL NULL BTREE productFilters 1 index_on_ids 2 idFilter A 550971 NULL NULL BTREE
  6. describe productFilters: Field Type Null Key Default Extra idProduct int(11) NO PRI NULL idFilter int(11) NO PRI NULL show index from productFilters Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment productFilters 0 PRIMARY 1 idProduct A NULL NULL NULL BTREE productFilters 0 PRIMARY 2 idFilter A 550971 NULL NULL BTREE productFilters 1 filter 1 idFilter A 115 NULL NULL BTREE productFilters 1 product 1 idProduct A 68871 NULL NULL BTREE
  7. I can't understand a darn thing. Woke up today to see it working (shared hosting). the indexes have always been: Keyname Type Unique Packed Column Cardinality Collation Null Comment PRIMARY BTREE Yes No idProduct 0 A idFilter 550971 A filter BTREE No No idFilter 115 A Could it have been a weekend glitch on the hosting side? Now i have another issue (don't know if i should create another thread for this) list number of already filtered products is slow. Should i rethink the database structure or the query? SELECT f.name, count( pf.idProduct ) AS cnt FROM filters f LEFT JOIN productFilters pf ON pf.idFilter = f.idFilter WHERE f.type = 'c' AND pf.idProduct IN ( SELECT idProduct FROM productFilters WHERE idFilter =5 AND idProduct IN ( SELECT idProduct FROM productFilters WHERE idFilter =7 ) ) GROUP BY f.idFilter ORDER BY f.name ASC LIMIT 0 , 30 Return is: Showing rows 0 - 14 ( 15 total, Query took 0.9599 sec) [name: BLACK - YELLOW] Black 374 Blue 1509 ....
  8. 1 SIMPLE pf index NULL PRIMARY 8 NULL 550971 Using index; Using temporary; Using filesort 1 SIMPLE f eq_ref PRIMARY PRIMARY 4 database.pf.idFilter 1 Using where
  9. I am having a hard time optimizing a simple query... i have 2 tables: filters (id, name, filterType) and productFilters (idProduct, idFilter) The query i am trying to optimize is: SELECT f.name, count( pf.idProduct) AS cnt FROM filters f JOIN productFilters pf ON pf.idFilter = f.id WHERE f.type = 'u' GROUP BY f.id (should return the filter name and the number of products associated) (2+ seconds query) please help! tia
  10. it's not my script. actually it worked like this: //check how many months have passed (mysql) for($i=0;$i<$months;$i++) { //blabla $i=0; } Of course it worked for the first month... it's not cli, it's cgi Is there any workaround to get the server to drop it after x seconds??? I hate having to debug someone else's script.
  11. it is set to 5... a reasonable amount apparently, it was a loop that php does not resume from, even though max_execution_time passes for($i=0;$i<5;$i++) { //some sort of database query //calculations $i=0; } strange, i thought max_execution_time would kill the script, but infact it won't.
  12. i have some bad-written php scripts that, when run, make apache use over 90% cpu, under 1% memory, and not die (i have changed the timeouts to 4 seconds). The process will show in 'top' for hours, until a manual apache stop - apache start. Which part of the configuration files have i written wrong?
×
×
  • 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.