>>The indexes were listed at the bottom of the original post. Woops, didn’t see that far down, should have scrolled a little further :)
>>What is needed, I expect, is a >>multi-column index on those 2 columns: >> >> ALTER TABLE 31909_859552 >> ADD INDEX Tag_Created (`Call Svc Tag ID`, `Journal Create Date`); Definatly get an index across all queried fields, especially in this case where there are only 2 columns in the result set, you may be able to avoid hitting the data leaf's of the table all together and retrieve all info from the index alone, saving you the added IO on the data leaf's >>Then try again: >> >> SELECT `Call Svc Tag ID`, >> Count(*) as counter, >> `Journal Create Date` >> FROM 31909_859552 >> WHERE `Journal Create Date` between '2004-03-13' AND '2004-03-16' >> AND `Call Svc Tag ID`<>'null' >> GROUP BY `Call Svc Tag ID` >> HAVING counter > 3 >> ORDER BY counter; The "count(*)" will be causing some havoc here, as all columns in the underlying table(s) will have to be read in, negating any direct hits to the index's and causing a lot more IO than is needed. Change it to a count on one of the columns in the result set or simply a "count(1) as counter" - will give you the same result without any IO. >>Note that sorting by the count can't use an index, so it will be slower >>than >>if you had ordered by `Call Svc Tag ID`. This is something that will need testing. Ordering on a varchar(255) column (call svc tag ID) is going to be a lot more inefficient that ordering on a numeric column, especially when the ordering is happening on the reduced result set of the group by and predicate clauses, however the indexing available on the "real" column may negate any deficit. >>MySQL handles BETWEEN just fine. If you think about it, you explicitly >>set >>the range with BETWEEN, but the optimizer has to put the two inequalities >>joined with AND together to get the same range. In other words, BETWEEN >>is >>easier. I am sure that MySQL does handle the "between" just fine, but this is not to say that it will handle the between better that a gt / lt clause in this particular scenario. I know the between sounds like it might be easier, but internally the between is likely to be translated into a gt / lt clause by the optimiser anyhow as the only way to check if something is between 2 values is the check if it is greater than the min and less than the max. Just give the 2 different queries a go and see if there is any time difference. Also noticed that the table seems to be a fairly flat structure (hard to tell defiantly from looking at one sample). By this I mean that there are a lot of varchar(255)'s in the table, and looking at the naming of the columns, these seem to be "id" type fields. You might be able to gain some performance by normalising the database a little by moving some of the large varchar fields out to a related table and referencing by a numeric type id, this could greatly reduce the amount of data in your base table, especially considering the number of rows you are talking about storing and could give you an IO based performance increase. Once again, might need to test and play around a little with different models here. What kind of proportion of null values are you expecting for the Call Svc Tag ID column? Depending on this, you may or may not be better off restructuring the query to filter out the null values post grouping - eg as an extra for the having clause "having counter >3 and call svc tag id <> null) - My mind is not super clear on this one at the moment, but am not sure if / how null values get indexed in MySQL (any feedback on this one anyone?) another one that maybe only testing will show if it helps or not. Also, are you looking for null values, or the word "null" in the column? --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.659 / Virus Database: 423 - Release Date: 15/04/2004 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
