Hi all,

We'll have around 300,000 products in our SF implementation, and need to search those. At the moment they're in MySQL and we use fairly complex SQL including reg exp's and scoring and stuff.

So more of a general question here - can we form such queries in PHP, have them run over multiple related objects, and get results back, with reasonable performance?
I'm new to SF and haven't delved into SOQL and such things very much at all.

Or what you do, in overview, to build a search...? I'm just after a rough idea of what basic method(s) to chase,

cheers all.

edit: We're on PHP 5.1.2

I know that SOQL is a fairly limited subset of SQL, so you won't be able do certain types of joins and other things. I haven't looked into too much, but when I was working with certain data types, I couldn't join tables. I have a feeling there is some overhead limitation in play.

Cheers. Sorry for delay replying, been away.
I need to look hard at this. It just seems that SF won't give us the power we need, compared to a MySQL/PHP setup. Using SF would make sense in principle, but in practice ... not so sure.

Hate to say it but seems that staying with the MySQL application would be the best bet Bill!

The SOQL is limited indeed and needs a lot of work

Thanks Mike. Shame, that. Would make a lot of sense strategically but we live by our search so SOQL is a major issue. We build regexp's to create ranking as part of the content search. Off to dig deep into manuals and things ...
cheers
Bill

If you were planning on using Apex at all, there is one other consideration when it comes to record sets of that size. As of the spring '10 release they have removed the restrictions on list/map/set/array sizes. However, they haven't removed heap size limitations. They also have very low limits on things like the number of SOQL and DML statements. Then, if you can get all of your data into a list or map you still have to worry about things like the number of lines of code they allow you to execute in a class or trigger while processing your data, and cpu utilization. Also, if you update the data in that big array and are ready to write it back, you might not be able to because of the limited batch size for DML statements like insert. The bottom line is that Apex is good for smallish data sets only.

Thanks - that's very useful info.