Select with partial length index??

[Bruce D]

I have a table, table1, with the field 'areacodepluste lephone'. This field
is of length 10. I created an index called 'areacode' that only uses the
first three characters.
Can I use this index in my select statement?
I tried this: select name from table1 where areacodeplustel ephone =
330' -- no results
I tried this: select name from table1 where left(areacodepl ustelephone,3) =
'330' -- got the results but slow...id didn't use the index

I'm I going about this the wrong way? Is there a better way than using the
"left" function?

Any help is greatly appreciated.

-bruce duncan

[Aggro]

Bruce D wrote:

Can I use this index in my select statement?
Yes
I tried this: select name from table1 where areacodeplustel ephone =
330' -- no results
This would return only rows where areacodeplustel ephone is exactly 330,
if row contains any other characters it won't be returned.
I tried this: select name from table1 where left(areacodepl ustelephone,3) =
'330' -- got the results but slow...id didn't use the index

Yes, because by using that function, you force the MySQL to use the
function for all the rows.

How about:
select name from table1 where areacodeplustel ephone like '330%';

[Bruce D]

"Aggro" <sp**********@y ahoo.com> wrote in message
news:aH******** *******@read3.i net.fi...

How about:
select name from table1 where areacodeplustel ephone like '330%';

Which is the "proper" way to code this SQL statement?:
1) select name from table1 where left(areacodepl ustelephone,3) = '330'
2) select name from table1 where areacodeplustel ephone like '330%'

Is there a lot of overhead on the DB using the left() function?
I thought MySQL wouldn't use indexes on a "like" clause?
Or should I just run a bunch of tests to see which is faster? I will be
searching 200 million rows...so performance is key.

-bruce

[Aggro]

Bruce D wrote:

Which is the "proper" way to code this SQL statement?:
1) select name from table1 where left(areacodepl ustelephone,3) = '330'
2) select name from table1 where areacodeplustel ephone like '330%'
In this case I suggest using the 2)
Is there a lot of overhead on the DB using the left() function?
Like I said, if you use it, MySQL has to check all rows in the table, so
yes, there is a lot of overhead. I don't know if you are a programmer,
but as I am, I think it is pretty obvious why MySQL doesn't use indexes
if the function is used. MySQL would require a lot of more if-sentences
to slow down queries, if it would have to add logic for detecting the if
parameter for cuntion is a string from column, and also detecting if the
result of that function is left part of the column.

But when you use "like", the MySQL instantly knows that the left part of
the if is a string from column. So there is no problem with that part.
I thought MySQL wouldn't use indexes on a "like" clause?
It doesn't, if you have like '%something', but in your case the % is at
the end, so MySQL can use indexes.
Or should I just run a bunch of tests to see which is faster? I will be
searching 200 million rows...so performance is key.

I think one try of each should give you a pretty good image which is
faster. If the like doesn't use indexes either, you either don't have
index on that column, or you have something else which is not allowing
the MySQL to use indexes.

[Bruce D]

"Aggro" <sp**********@y ahoo.com> wrote in message
news:FA******** *******@read3.i net.fi...

I think one try of each should give you a pretty good image which is
faster. If the like doesn't use indexes either, you either don't have
index on that column, or you have something else which is not allowing
the MySQL to use indexes.

Thanks for the info. I assumed the left() function had a lot of overhead,
but I wanted to hear it from someone else.

I've tried both queries running against 1 million records.

select name from table1 where areacodeplustel ephone like '330%' works the
best.

MySQL was not using the index I had created...and I have no idea why. So, I
issue the "force index (areacode)" and it speeds up the query by almost 40%.

Looks like this is the best solution for this situation. Thanks again for
your input...it was very helpful!

-bruce duncan