MySQL Forums
Forum List  »  Performance

Optimizing query & table structure for huge table.
Posted by: Vijay Patel
Date: January 17, 2005 11:38PM

Hello,

I am having big "product" table with ~ 5,000,000 rows.
product.myd - 85MB
product.myi - 100 MB
Another big table, product_user_count (say PUC) , maintains FK_PRODUCT, FK_USER, COUNT.
This table is having ~ 200,000,000 rows.
puc.myd - 2 GB
puc.myi - 3 GB.
User table is having 2,000,000 rows.


My main search table is PUC, which is focused to get ~ 100 queries on product per second. Result display is based on order by COUNT from PUC.

Indexing is done on
1) FK_PRODUCT
2) FK_USER

(Note : count is not used as second key_part).

I found that following query is taking ~ 30-40 second of execution if, there are ~3000 record for that PRODUCT.
select FK_USER,COUNT from PUC where FK_PRODUCT=<constant> order by COUNT desc limit 1,10;

Explain query is showing me that it is using file sort for my query. It seems that server is trying to do file sorting for all 3000 record which is taking time. As file is of ~ 3 GB, even file sort for selected 3000 records is taking big time.


My query is very similar to example given in documentation : "How MySQL Optimises ORDER BY".......
SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2

To have key_part2 included in index, i changed index like,
1) FK_PRODUCT,COUNT
2) FK_USER,COUNT

Even after changing index, the query time is not improved.


Now to achieve this, i need comeents on......

1) Whether going for Innodb will be good option?
2) IF PUC table would have -- "primary_key auto_incr", will it make any diff. in execution of this query.
3) Which other parameter, can optimize this query,
I have configured my.cnf as my-huge.cnf (RAM : 1.5 GB).

Thank you,
Vijay.

Options: ReplyQuote


Subject
Views
Written By
Posted
Optimizing query & table structure for huge table.
2299
January 17, 2005 11:38PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.