1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

My SQL statement isn't using indices??

Discussion in 'Web Design & Development' started by vidyaishaya, Apr 12, 2010.

Thread Status:
Not open for further replies.
  1. vidyaishaya

    vidyaishaya Thread Starter

    Joined:
    Oct 26, 2001
    Messages:
    84
    I have a question that I don't seem to be able to solve myself.

    Here's a MySQL statement which is a slow query in our programs:

    SELECT content,id,SUM(count) AS hits FROM statistics
    WHERE date>=1271048400 AND date<=1271048400
    GROUP BY CONCAT(id,content) ORDER BY hits DESC LIMIT 0,50

    The 'statistics' table is defined as:

    CREATE TABLE statistics (
    statid INTEGER AUTO_INCREMENT PRIMARY KEY,
    id INTEGER not null,
    content CHAR(1) not null,
    count INTEGER not null,
    date INTEGER not null,
    INDEX(content),INDEX(id)
    )

    When I do an EXPLAIN on the query, it says it is not using indices at all.
    Which is a surprise to me. Since the statistics file tend to be huge, this is not good.

    (I know I could use an index on date; but when I remove date from the query,
    it still says it isn't using indices.)

    Since this is the main query we use on the statistics, how can I make indices
    so that it uses them? Any ideas?

    Thanks

    ....Vidya
    www.opednews.com
     
  2. vidyaishaya

    vidyaishaya Thread Starter

    Joined:
    Oct 26, 2001
    Messages:
    84
    No problem, thanks.

    I found the solution.

    ...Vidya
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/916496

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice