[FX.php List] Google-type search across tables?

Thu Mar 29 11:44:20 MDT 2012

Thanks Steve!

I'll have to find out what kind of budget they'll give to this and then go from there.

Cheers,
-Joel

On Mar 29, 2012, at 12:41 AM, Steve Winter wrote:

> Morning Joel
> 
> In many respects I think either MySQL or solr are going to take a bit of work to get get up because you're going to have to come up with strategies for updating the 'remote' index, and then mechanisms to make that work in a meaningful way… one possible advantage of solr is that 'by default' it creates a concatenated 'text' field in which all of the other fields are 'indexed' for searching against and/or you can specify which fields to search…
> 
> That said, they are both going to give you similar speed enhancements over FMP, so my suggestion would be to go with what you're most familiar with and give MySQL a go… my approach would be one MySQL row for each table/field combination you wish to search across… push all the data in there, including a 'source' so that it's possible to group together all of the rows which represent the same record in the found set… then you can do some form of search term highlighting.. bear in mind that you're going to end up needing to do lots of 'like' queries so you'll need to ensure that even your MySQL db is well indexed…
> 
> Have an update script which runs every hour/night/week as required, which locates records in FM which have been modified since last index, and update those in MySQL…
> 
> Good luck…!
> 
> Cheers
> Steve
> 
>> Hey Steve
>> 
>> Thanks for the reply.  I had asked the client and was told that people are editing in the DB all day long… however I'm wondering now if maybe they won't need their web search results to be up-to-the-minute.  The MySQL table is an interesting idea.
>> 
>> As to Solr, I remember your DevCon talk about it.  It's not so quick to get up and running if I remember correctly.  Is that right?  (This search functionality probably isn't super-high priority for this client, so I likely won't be able to spend a ton of time on it.)
>> 
>> Cheers,
>> -Joel
>> 
>> 
>> On Mar 27, 2012, at 11:47 PM, Steve Winter wrote:
>> 
>>> Hey Joel
>>> 
>>> How often does much of the data change…? if it's pretty often, (x times an hour) then you're probably going to need to stick with using FM as your data source… if it's x times a day you could consider pushing each of the fields you wish to search against, from their respective tables, into a single MySQL table, which has a single record for each block of data, which includes a 'source' field, so that you know which table it came from, and therefore which category it's in…
>>> 
>>> If it's only x times a week (or month or never) then you could also look at using a 'search' tool like solr which will definitely give you the ability to do google style searches, up-to and including the ability to do type-ahead type searches across the whole lot… somewhere I've got a python indexer, which pushes specified fields from FMP into a defined solr schema (along with all the instructions to get solr running as a 'plugin' to FMS (on Apache)) if you're interested…
>>> 
>>> HTH
>>> Steve
>>> 
>>>> Thanks again, Webko.  Good to hear from someone who's tried it!
>>>> 
>>>> Anybody else?
>>>> 
>>>> Cheers,
>>>> -Joel
>>>> 
>>>> FWIW: Re: editing or creating multiple records at once, I've found it can be a *lot* faster to edit multiple records if they can be turned into related records in a portal of one "parent" record, and then just edit the one parent record.  
>>>> For creating multiple records, the fastest way by far was via an FM script:
>>>> http://blog.jsfmp.com/post/19243201571/  (the last 2 examples)
>>>> 
>>>> 
>>>> On Mar 27, 2012, at 4:47 PM, Tim 'Webko' Booth wrote:
>>>> 
>>>>> Dear Joel,
>>>>>> 
>>>>>> I've thought of concatenated fields, but the client wants to search on some "description" fields containing lots of text, and I'm concerned that that might really slow things down.
>>>>> 
>>>>> It would have to be fairly massive slabs of text to slow it down appreciably in my experience. Especially if you tune the layout to only have the search field on it.
>>>>>> 
>>>>>> I was thinking of multiple searches because they'd like the results to be filterable by category.
>>>>> 
>>>>> That's where overhead issues start coming into play - as everything is returned in the rather wordy FileMaker XML format and each search is run one after the other, lots of little searches become inefficient. I notice this particularly on one system where I can have over 50 new record or edit record requests.
>>>>>> 
>>>>>> By "extend that for related tables", do you mean have one concatenated field in the parent table including child fields/records?  Or something else?
>>>>> 
>>>>> The first. As I said, then the issue becomes identifying where the actual match came from...
>>>>> 
>>>>> Cheers
>>>>> 
>>>>> Webko
>>>> _______________________________________________
>>>> FX.php_List mailing list
>>>> FX.php_List at mail.iviking.org
>>>> http://www.iviking.org/mailman/listinfo/fx.php_list
>>> 
>>> Steve Winter
>>> +44 777 852 4776
>>> steve at bluecrocodile.co.nz
>>> 
>>> 
>>> 
>>> 
>>> _______________________________________________
>>> FX.php_List mailing list
>>> FX.php_List at mail.iviking.org
>>> http://www.iviking.org/mailman/listinfo/fx.php_list
>> 
>> _______________________________________________
>> FX.php_List mailing list
>> FX.php_List at mail.iviking.org
>> http://www.iviking.org/mailman/listinfo/fx.php_list
> 
> Steve Winter
> +44 777 852 4776
> steve at bluecrocodile.co.nz
> 
> 
> 
> 
> _______________________________________________
> FX.php_List mailing list
> FX.php_List at mail.iviking.org
> http://www.iviking.org/mailman/listinfo/fx.php_list