Any MySQL experts?

Discussion in 'General Programming Chat' started by lancis, Jan 3, 2014.

  1. lancis

    lancis Elite Member

    Joined:
    Jul 31, 2010
    Messages:
    1,683
    Likes Received:
    2,427
    Occupation:
    Entrepreneur
    Location:
    Milky Way
    Home Page:
    Need an advice here. Turned the Internet upside down, and didnt find any speedy solution.
    Simple query:

    Code:
    SELECT COUNT(DISTINCT x) FROM table WHERE y=somevalue AND date>=mindate AND date<=maxdate
    ..is hell slow. Any 'solutions' I found on stackoverflow only slow it down further. Obviously indices are in place.

    Any tricks to speed it up?

    Cheers,
     
  2. mypmmail

    mypmmail Junior Member

    Joined:
    Jan 31, 2008
    Messages:
    114
    Likes Received:
    27
    If there is a lot of data in your table, it will be slow.

    One of the way to speed it up is to create index.
    I would create index for the following columns
    1 - y
    2 - date
    3 - y + date

    To create an index,
    CREATE INDEX idx_index_name_1 ON table_name (y);
    CREATE INDEX idx_index_name_2 ON table_name (y, date);

    docs.oracle.com/cd/E17952_01/refman-5.6-en/create-index.html


    hth
     
    • Thanks Thanks x 1