Set explain what does it mean?
Posted in 1996
I have two tables the first, test_tbl1, has an index on column of
varchar datatype. The second table, test_tbl2 has index on column of
char datatype. Running SET EXPLAIN ON produced the following.
-What is the different between the two queries?
-What the effect of having index on column of varchar datatype?
Thanks in advance,
Haytham
test_tbl2 is defined with data type char
QUERY:
------
select count(*) from test_tbl2
where client_id = '001' and market_id = '001'
and lname = 'ADAMS'
Estimated Cost: 1
Estimated # of Rows Returned: 1
1) test_tbl2: INDEX PATH
(1) Index Keys: client_id market_id lname (Key-Only)
Lower Index Filter: (test_prospect.client_id = '001' AND
(test_prospect.market_id = '001' AND test_prospect.lname = 'ADAMS' ) )
test_tbl2 is defined with data type varchar
QUERY:
------
select count(*) from test_tbl1
where client_id = '001' and market_id = '001'
and lname = 'ADAMS'
Estimated Cost: 1
Estimated # of Rows Returned: 1
1) test_prospect: INDEX PATH
(1) Index Keys: client_id market_id lname
Lower Index Filter: (test_prospect.client_id = '001' AND
(test_prospect.market_id = '001' AND test_prospect.lname = 'ADAMS' ) )