Sql Optimization

[oarecare]

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 November 17, 2012 by oarecare

[Christian F.]

If you add EXPLAIN in front of that query, and post the result here (if you don't understand it), then I'm sure someone will be able to help you.

Without the results of the EXPLAINed query, we're reduced to guesswork. Which I'm sure you agree isn't really productive.

[oarecare]

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

[jazzman1]

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;

[jazzman1]

EDIT: The first table is pf (productFilters) in your sql explanation, sorry about that.

mysql> DESCRIBE productFilters;

mysql> SHOW INDEX FROM productFilters;

Edited November 19, 2012 by jazzman1

[oarecare]

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

....

[jazzman1]

You don't show us yet, describe of productFilters.

 

mysql> DESCRIBE productFilters;

 

Take a look at this guide -> http://hackmysql.com/case1

 

Should i rethink the database structure or the query?

 

We need to know your tables' structure.

[oarecare]

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 

[jazzman1]

Well, you can add an index on the idProduct and idFilter.

After that, explain the query from your first post again.

 

Try,

ALTER TABLE productFilters ADD INDEX index_on_ids(idProduct,idFilter)

[oarecare]

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  

[jazzman1]

Every time you post different queries.

That's a bad idea to use multiple IN operator like in examples.

If you're new to databases disign, you should take some time study how these tables are comming together.

[DavidAM]

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.

[oarecare]

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.

[jazzman1]

You have to start from db scheme. Take a look at this.

[oarecare]

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

[s4surbhi2218]

EDIT: The first table is pf (productFilters) in your sql explanation, sorry about that.

mysql> DESCRIBE productFilters;

mysql> SHOW INDEX FROM productFilters;

 

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

[jazzman1]

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.

[oarecare]

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.

[jazzman1]

@oarecare, indexes are not a panacea!

Re-read David's reply #12.

[oarecare]

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