RE: a little help with query.

Ok... So you have 94 Million rows in the table, and I'm assuming you have an index on profile_token. How many rows have that profile token? Is it fairly unique or is it more common? If you run the query without the 'IS NOT NULL' filter, how long does it take? Date: Mon, 6 Jul 2009 10:01:44 -0500 Subject: RE: a little help with query. From: floyd@fwellers.com To: im_gumby@hotmail.com; informix-list@iiug.org It's just an integer. ----- Original Message ----- From: "Ian Michael Gumby" <im_gumby@hotmail.com> Sent: Mon, July 6, 2009 10:59 Subject: RE: a little help with query. Silly question... What's the data type of the profile_token? Is it really a Serial column? Date: Mon, 6 Jul 2009 09:32:25 -0500 Subject: a little help with query. From: floyd@fwellers.com To: informix-list@iiug.org The below query takes a long time the first time running because it must go to disk. But it takes like 50 seconds !! the table has 94 million very wide rows. It is fragmented by a char column ( but not the column in the query ). Given the below sqexplain output, is there anything obvious that can be done to speed it up ? Does the slowness have to do with the 'not null" clause ? Thanks, Floyd =============SQEXPLAIN OUTPUT HERE ========================= select count (distinct varchar_column ) from table where varchar_column is not null and profile_token = 1234 Estimated Cost: 4104 Estimated # of Rows Returned: 1 1) owner.table: INDEX PATH Filters: owner.table.varchar_column IS NOT NULL (1) Index Keys: profile_token varchar_column (Serial, fragments: ALL) Lower Index Filter: owner.table .profile_token = 1234 ====================END SQEXPLAIN OUTPUT=================== Windows Live : Keep your life in sync. Check it out. _________________________________________________________________ Windows Live™ SkyDrive™: Get 25 GB of free online storage. http://windowslive.com/online/skydrive?ocid=TXT_TAGLM_WL_SD_25GB_062009