How to merge two databases by xpdo?

Quote from: markh at Oct 28, 2011, 08:12 AM

Can you paste your schemas? If you are getting the right values from $db2->get('latitude'), but $single->set('latitude',$latitude) doesn't add anything, I suppose the problem is in there. Either you're setting it on a wrong column or there may be an error in there.

Did you mean the xy.schema.xml? This file contains in our setting more than 1.000 lines. Or did you mean another file?

Btw: Do I have to write:

$single->set('praefix_dbtablename_longitude','$longitude');

or:

$single->set('longitude','$longitude');

I would guess the second one but in a post before somebody thought of maybe the first version could be correct. [ed. note: lettis last edited this post 14 years, 11 months ago.]

Meanwhile we solved it by coding this procedure directly in php/mysql working now without problems.

But we now have some issues when we try to work with the values of the database 'events' which contains a list of events and the geo-position of the event-location. The goal is to show all events which are closer than 10 km/miles from users hometown which users can type into a form on our site:



The problem: Just this line takes about 7 seconds for processing:

('events' is a database of about 980 rows and 670 kb)

I also tried to make it faster by altering the getCollection-tag into:

But it was not faster and also took ~7 seconds.

Is there maybe a better way to process row by row of the whole database to find out all events which are closer than 10 km/miles to a user´s hometown?

Does anybody know this? Would be great!

Letti

Have you properly indexed the table columns you are using in your conditions there?

This is a point I am not sure about because I never cared about the indices in our databases. Accessing the database by phpmyadmin there is a section 'indices' which contains the field 'id' of our database with the type INDEX. Is there maybe anything we can optimize concerning the index?

You need proper indexes on ALL the columns you are querying and filtering on, especially latitude, longitude, and date. Your index design is a very important part of your table design and will determine how performant your queries will be.

Ok, thanks a lot. I added the indices and renew the schema-files. Unfortunately this call still takes a lot of time (>7 seconds):

$db= $modx->getCollection('events', array('date:>=' => $datetoday));

Changing the call to ( '=' instead of '>=' ):

$db= $modx->getCollection('events', array('date:=' => $datetoday));

it takes just less than 1/2 second for retrieving about 50 rows out of the 980 rows.

Is there maybe a better way to load the datas than using getCollection? As said before it would be also fine to get the whole database instead of only getting events which are in the future which will anyway count for 99 % of the datas.

Btw: Are ~7 seconds for loading a database of ~980 rows and 670 kb with the getCollection-method ok, or is this indicating a problem somewhere in the database?

Any query that takes more than a second with less than a million records is IMO broken, with exceptions. But that should definitely not even take a second to execute with proper indexing.

Thanks a lot for this estimation concerning how much time such a query normally should take.

Can you meaybe recommend a site in the internet with a checklist or something like this on how repairing the index of a mysql database or do you have an idea what we should check first when we try to fix it?

We already tried in phpmyadmin: optimaze tabele and repair table but this didn´t fasten the query.

Add the word EXPLAIN in front of your query and run it in PHPMyAdmin. This will describe the indexes used by the query...

Ok. I tested it and it looks like there is a problem with the operator `>`

While this:

leads to:


the tag:

leads to