MySQL Forums
Forum List  »  General

Re: query not using index
Posted by: Matthew Boehm
Date: January 14, 2005 09:43AM

CREATE TABLE cdr (
uniqueid varchar(32) NOT NULL default '',
userfield varchar(255) NOT NULL default '',
accountcode varchar(20) NOT NULL default '',
src varchar(80) NOT NULL default '',
dst varchar(80) NOT NULL default '',
dcontext varchar(80) NOT NULL default '',
clid varchar(80) NOT NULL default '',
channel varchar(80) NOT NULL default '',
dstchannel varchar(80) NOT NULL default '',
lastapp varchar(80) NOT NULL default '',
lastdata varchar(80) NOT NULL default '',
calldate datetime NOT NULL default '0000-00-00 00:00:00',
duration int(11) NOT NULL default '0',
billsec int(11) NOT NULL default '0',
disposition varchar(45) NOT NULL default '',
amaflags int(11) NOT NULL default '0',
KEY src (src),
KEY dst (dst),
KEY billsec (billsec),
KEY uniqueid (uniqueid),
KEY calldate (calldate),
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Jason Collison wrote:
> You'll need to move
> that to before or after the customer_id clause.

OK. Will do that.

> - Is there data in that table that does _not_
> match your stripped down criteria?

I am positive that all the calldates are no earlier than Oct 1, 2004. And most of the 40000 rows have the same calldate. Probably 50-500 rows for each date since Oct 1, 2004. The column is not "that" unique. Yet the cardinality on the index is always equal to the number of rows I have in the table. Row size is 222 Bytes.

> - Have you tried removing the sort or changing its direction?

removing the sort didn't change anything

> But assuming those have been tried, what jumps
> right out at me is where you're blindly comparing
> a (date?) column to a string. (I can only assume
> it was declared as a date coluimn.)

OK. It is a 'datetime' column. I changed the query to this:

SELECT calldate, src, dst, billsec, accountcode
FROM cdr WHERE calldate >= 20041116000000

As simple as it can be, and it still is searching thru all 40K rows.

> Does this really
> work properly in mysql?? God I'd hope not.

Aparently it does work cause I am getting the correct expected results. The 'stripped down' query only takes about 0.0016 seconds to complete.

> Not _quite_ a shot in the dark, but this board is
> slow as molasses so I figured I'd reply.

I greatly appreciate you taking the time to help with my problem.

Options: ReplyQuote


Subject
Written By
Posted
January 13, 2005 11:24AM
January 13, 2005 02:14PM
Re: query not using index
January 14, 2005 09:43AM
January 13, 2005 06:23PM
January 14, 2005 01:05PM
January 14, 2005 01:12PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.