Need some help with big tables

  • Filter
  • Time
  • Show
Clear All
new posts

  • 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'
    ORDER BY ends ASC
    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