Jump to content

Sql Optimization


oarecare

Recommended Posts

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

Edited by oarecare
Link to comment
Share on other sites

You have a problem with the first table - filters.

To optimize this query we need to know a little about the structure of that table and the current indexes.

Give us your results, please!

 

mysql> DESCRIBE filters;

mysql> SHOW INDEX FROM filters;

Link to comment
Share on other sites

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

....

Link to comment
Share on other sites

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 

Link to comment
Share on other sites

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  

Link to comment
Share on other sites

What exactly are you trying to do with that query? Your previous post called it "list number of already filtered products", but that phrase does not seem to fit the query.

 

The way I read it, that query is returning the filter data and a count of products that have filters 77, 76, 16, AND 11.

 

Dependent Subqueries are harsh on performance, and when you nest them like that, it compounds the problem.

 

By the way, the LEFT JOIN is negated by the use of pf.idProduct in the WHERE clause. That WHERE clause will only be TRUE for rows that successfully JOIN so the extra filters (that don't match the JOIN condition) will not be returned.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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...

Link to comment
Share on other sites

How did you come to know taht problem is with this table ??? what did u check in the explain query??

 

Type of the "index" and extra "Using filesort" .

 

The type of the second one "eq_ref" is used during joins, and in fact, is the best possible value of type for joins.

 

If you look carefully at explanation of query of post #10, you will see that a type is "ALL".

PRIMARY f ALL PRIMARY NULL NULL NULL 115 Using temporary; Using filesort

 

The "All" means a full table scan is needed for each combination. You should endeavor to avoid this situation in all queries that are run with any frequency.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • 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.