Re: query not using index
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.