Jump to content


Photo

Sql Optimization


  • Please log in to reply
19 replies to this topic

#1 oarecare

oarecare

    Member

  • Members
  • PipPip
  • 12 posts

Posted 17 November 2012 - 01:29 PM

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, 17 November 2012 - 01:30 PM.


#2 Christian F.

Christian F.

    Advanced Member

  • Staff Alumni
  • 3,106 posts
  • LocationNorway

Posted 17 November 2012 - 01:53 PM

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. :)
Keeping it simple.

#3 oarecare

oarecare

    Member

  • Members
  • PipPip
  • 12 posts

Posted 17 November 2012 - 01:57 PM


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



#4 jazzman1

jazzman1

    Advanced Member

  • Gurus
  • 2,678 posts
  • LocationMississauga, Canada

Posted 18 November 2012 - 11:01 PM

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;

#5 jazzman1

jazzman1

    Advanced Member

  • Gurus
  • 2,678 posts
  • LocationMississauga, Canada

Posted 18 November 2012 - 11:48 PM

EDIT: The first table is pf (productFilters) in your sql explanation, sorry about that.
mysql> DESCRIBE productFilters;
mysql> SHOW INDEX FROM productFilters;

Edited by jazzman1, 18 November 2012 - 11:51 PM.


#6 oarecare

oarecare

    Member

  • Members
  • PipPip
  • 12 posts

Posted 19 November 2012 - 01:49 AM

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

#7 jazzman1

jazzman1

    Advanced Member

  • Gurus
  • 2,678 posts
  • LocationMississauga, Canada

Posted 19 November 2012 - 08:54 AM

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.

#8 oarecare

oarecare

    Member

  • Members
  • PipPip
  • 12 posts

Posted 19 November 2012 - 10:05 PM

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 


#9 jazzman1

jazzman1

    Advanced Member

  • Gurus
  • 2,678 posts
  • LocationMississauga, Canada

Posted 20 November 2012 - 09:35 AM

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)


#10 oarecare

oarecare

    Member

  • Members
  • PipPip
  • 12 posts

Posted 22 November 2012 - 07:22 AM

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  


#11 jazzman1

jazzman1

    Advanced Member

  • Gurus
  • 2,678 posts
  • LocationMississauga, Canada

Posted 22 November 2012 - 09:03 AM

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.

#12 DavidAM

DavidAM

    Advanced Member

  • Gurus
  • 1,974 posts
  • LocationSpring, TX USA

Posted 22 November 2012 - 11:15 AM

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.
-- I haven't lost my mind, it's backed up on tape ... somewhere!

#13 oarecare

oarecare

    Member

  • Members
  • PipPip
  • 12 posts

Posted 22 November 2012 - 03:25 PM

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.

#14 jazzman1

jazzman1

    Advanced Member

  • Gurus
  • 2,678 posts
  • LocationMississauga, Canada

Posted 22 November 2012 - 05:58 PM

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

Attached Files



#15 oarecare

oarecare

    Member

  • Members
  • PipPip
  • 12 posts

Posted 22 November 2012 - 07:28 PM

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

#16 s4surbhi2218

s4surbhi2218

    Advanced Member

  • Members
  • PipPipPip
  • 54 posts

Posted 23 November 2012 - 02:49 AM

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??
Have a great 2013!!

#17 jazzman1

jazzman1

    Advanced Member

  • Gurus
  • 2,678 posts
  • LocationMississauga, Canada

Posted 23 November 2012 - 07:24 AM

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.

#18 oarecare

oarecare

    Member

  • Members
  • PipPip
  • 12 posts

Posted 23 November 2012 - 06:32 PM

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.

#19 jazzman1

jazzman1

    Advanced Member

  • Gurus
  • 2,678 posts
  • LocationMississauga, Canada

Posted 25 November 2012 - 01:08 PM

@oarecare, indexes are not a panacea!
Re-read David's reply #12.

#20 oarecare

oarecare

    Member

  • Members
  • PipPip
  • 12 posts

Posted 26 November 2012 - 04:19 AM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com