Asheeown Posted January 28, 2007 Author Share Posted January 28, 2007 I think this is important, I did a show index query on the table. It brought my Index up however UTCTime is first in the sequence.[quote]Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Commentrated_cdrs 0 PRIMARY 1 ID A 2888305 NULL NULL BTREE rated_cdrs 1 IdTime 1 UTCTime A 2888305 NULL NULL YES BTREE rated_cdrs 1 IdTime 2 Originating_TG A 2888305 NULL NULL YES BTREE [/quote] Link to comment https://forums.phpfreaks.com/topic/35867-working-with-large-database-tables/page/2/#findComment-171419 Share on other sites More sharing options...
shoz Posted January 28, 2007 Share Posted January 28, 2007 The order of the columns in the index has to be the other way around if you want to use the index for the ORDER BY on Originating... Link to comment https://forums.phpfreaks.com/topic/35867-working-with-large-database-tables/page/2/#findComment-171426 Share on other sites More sharing options...
fenway Posted January 30, 2007 Share Posted January 30, 2007 effigy's off to a good start... BETWEEN is definitely more logic, and easier to read, and gives the parser/optimizer a hint that the same field needs to be examined for both inequalities. However, if you're going to be sorting often, you should have a covering index on ( UTCTime, Originating_TG ), which will remove the filesort, and speed up your query. You won't be able to ever use the lone index on the latter because you're using IN(). Link to comment https://forums.phpfreaks.com/topic/35867-working-with-large-database-tables/page/2/#findComment-172929 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.