1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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,632
    Likes Received:
    2,384
    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:
    111
    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