Need some help with big tables


I ve a big table with 13 Millions records ( 8,5GB ) associated to a 4GB index.

For optimizing my MySQL Queries, I worked with a second table with only 2 Millions of rows…

These tables are storing product catalogs where I ve informations like title, description, price, picture URL, category…

I d like to browse all products from a category. With The 2M rows table, there is now problem… It goes fast…

With the larger table (12GB), I ve a problem ( it is very very slow ) when I execute this query :

SELECT SQL_CALC_FOUND_ROWS id,title,category,a80,buy_now,minimum_bid,current_bid,subtit le,photo_uploaded,auction_type,pict_url FROM SQL_table
WHERE category=‘11995’
AND starts<=‘NOW()’
AND closed=‘0’
AND private=‘n’
AND suspended =‘0’
LIMIT 0,10

The 11995 category has 2 Millions items.

I put indexes on starts, ends, category, and id is the primary index ( Unique ).

SQL_CALC_FOUND_ROWS is here because I need to know how many results/products in order to calculate how many pages I would browse…

Do you have any advices ? I am blocked with that…

Thanks in advance !

Best Regards
Luc Michalski