Need index suggestions to optimize the speed

[fredgmain]

Hi all,

I have the following partial criteria for the SELECT statement. It involves the following fields ("senderemailaddress","cc","to"). The query definitely not very effective because it is very slow when I created 100,000 test records. But I must use AT() or $ to match the string within the fields.

Please give me suggestions on the indexing or alter the following partial query to improve the performance.

cond = cond + "((AT('@abc.com',senderemailaddress) > 0 AND cc = 'abcoutbox@nrsint.com' AND to = 'abcinbox@nrsint.com') ;
OR ;
(AT('@abc.com',senderemailaddress) = 0 AND AT('@abc.com',to) = 0)) and " ;

Thanks in advance.
Fred

 

[markros]

If that string would be from the beginning of the query, then you could have used LIKE clause which is Rushmore optimizable, e.g.

SenderMailAddress LIKE '@abs.com%' and ...

Unfortunately, it seems like you need to search at the end of the field.

Perhaps you can create an index on last 8 characters of e-mail address and search for the last 8 chars = the string to search. This would be quick then.

 

[Mike Lewis]

Fred,

If you goal is to run a query on the domain part of an email address (that is, the part after the "@"), one possibility would be to split the address into two separate fields: the part before the "@ and the part after. You could then create an index on the second of those, and the query would be fully optimised.

The disadvantage is that, whenever you want to reference an email address elsewhere in your app, you would have to deal with two fields rather than one.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[fredgmain]

Mark & Mike,

Thanks for you suggestions. I will first try to index the last 8 characters and check the performance. If that is still slow, I have to split the field or create additional fields for performance.

Thanks and Regards,
Fred

 

[Steve Bowman]

hybrid to Mike's suggestion
retain the senderemailaddress as is and add a fromdomain field for indexing


Steve Bowman
Independent Technology, Inc.
CA, USA

 

[Olaf Doschke]

You could make that index more intelligent: Instead of indexing RGHT(field,8), index on this:

INDEX ON PADR(substr(senderemailaddress,at('@',senderemailaddress)+1),Len(senderemailaddress)) TAG senderdom
INDEX ON PADR(substr(to,at('@',to)+1),Len(to)) TAG todomain

Then you can do

SELECT ... WHERE ((PADR(substr(senderemailaddress,at('@',senderemailaddress)+1),Len(senderemailaddress)) = 'abc.com') AND cc = 'abcoutbox@nrsint.com' AND to = 'abcinbox@nrsint.com') OR (NOT (PADR(substr(senderemailaddress,at('@',senderemailaddress)+1),Len(senderemailaddress)) = 'abc.com') AND NOT (PADR(substr(to,at('@',to)+1),Len(to)) = 'abc.com'))

Bye, Olaf.