Re: Optimize A Simple Query -- Help
At 08:29 AM 9/16/96 -0400, you wrote:
}I have a simple select count statement that takes three minutes to
}execute, yet I only have about 300,0000 rows. Here is the SQL.
}
} SELECT COUNT(*) FROM table WHERE key[1,7] = "B02800A" AND col1 = 2010
}AND col2 = 10;
}
}The "key" field is the primary key with data type char(11). "col1" and
}"col2" have a composite index. Values of the key field are evenly
}distributed, but col1 and col2 are not. The number of possible value of
}col1 and col2 do not exceed 50. Col1 and col2 have indexes just because
}there are reports searching on these two columns.
}
}If I drop the col1 and col2 clauses, the query will utilize index and
}hence run fast. But combining the substring and the composite key, the
}query is no longer using the "key" field for index search. Here is the
}output from SET EXPLAIN ON.
}
} Estimated Cost: 3
} Estimated # of Rows Returned: 1
}
} 1) table: INDEX PATH
}
} Filters: table.key[1,7] = "B02800A"
}
} (1) Index Keys: col1 col2
} Lower Index Filter: (table.col1 = 2010 AND table.col2 = 10)
}
}
}Is it possible to force the query to use index on key without dropping
}the composite index?
}
}Thanks in advance.
}
}Chao
}
}
Hi !
Try to select the first part into a temporary file and then select the rest
from the temporary file as follows:
SELECT col1,col2 FROM table WHERE key[1,7] = "B02800A" INTO TEMP TEMPY01
SELECT COUNT(*) FROM TEMPY01 WHERE col1 = 2010 AND col2 = 10;
Ciao!!
--------------------------------------------
Mario Estrada Rosa | (LESCO,S.A.)
| lesco@guate.net
Phone (502) 3318116 | Fax (502) 3348447
--------------------------------------------