I have a custom company module (like accounts) and I make 7 million records import(into mysql)
And I canāt open listview of this module there was infinity loading page (Iāve waited all night)
Sure I make indexes for my import tables
But there was an interesting things if go to MySQL query slow log:
SELECT s7mc_seven7millioncompanies.id , s7mc_seven7millioncompanies.assigned_user_id ,
s7mc_seven7millioncompanies.name , jt0.user_name assigned_user_name , jt0.created_by assigned_user_name_owner ,
'Use>
AND jt0.deleted=0 where s7mc_seven7millioncompanies.deleted=0 ORDER BY s7mc_seven7millioncompanies.date_entered DESC LIMIT 0,101;
So my question is how I can change this query?
If I use directly MySQL SQL like
SELECT id,name,assigned_user_id
FROM s7mc_seven7millioncompanies
WHERE deleted=0
ORDER BY date_entered
DESC LIMIT 0,101;
it loads fast
Btw ājt0.user_name assigned_user_nameā what is ājt0ā in this default query? I havenāt jt0 table in my database.
I make a custom module to test how 7 million records will be performed in the system
In future I wanted to move it on accountsā¦
I donā think it will be easy to change that query, itās very dynamic - itās a query for any module, including custom ones, any filter, any set of columns (configurable from Studio + filters), with pagination, etc. Itās way more complicated than you might think.
Your first query didnāt copy-paste correctly, itās broken and incomplete. If you can get all of it I can try explaining jt0 (probably a table alias defined in a FROM or JOIN clause) and you can also try additional indices to improve the performance of the complicated query.
But I am afraid I am no expert in database optimization, I might not be able to help much thereā¦
SELECT s7mc_seven7millioncompanies.id , s7mc_seven7millioncompanies.assigned_user_id , s7mc_seven7millioncompanies.name , jt0.user_name assigned_user_name , jt0.created_by assigned_user_name_owner jt0.deleted=0
WHERE s7mc_seven7millioncompanies.deleted=0
ORDER BY s7mc_seven7millioncompanies.date_entered DESC LIMIT 0,101;
itās correct copy-paste
I think youāre right itās kinda āprobably a table alias defined in a FROM or JOIN clauseā
It sounds bad to me if you havenāt met any way of force custom queryā¦
Btw when I create indexes I copy it from accounts module ( see which indexes used account and accounts_cstm and make the same for my tables)
Could you give me some advice on index formation? (right now itās a few indexes for id, and double indexes for id_deleted, name_deleted, ā¦
ps/ @pgr From your experience, could you tell how the system works with data of 5-10 million in several modules?
That query is definitely not complete, it has no FROM clause.
I donāt have experience with big databases with SuiteCRM. But it should be possible with enough hardware and careful tweaking. Anyway, unless you have access to great hardware and expert tweakers, you might prefer to design your processes differently so you donāt add millions of accounts at once. Do it in chunks, or SELECT from them while still outside SuiteCRM and move only parts into SuiteCRM, as needed.
thanks
got it.
I have one more question can I ask here so as not to produce topicsā¦
I have 2 suitecrm system and i wanna export my custom module (simple module, no many relationship, just layouts) from first system to second system.
I go to studio-> export customization than go to second system -> module loader and loader zipā¦
It was success, but i couldnāt find my new module. I see itās in system folders, but not system not Studioā¦
Any tips for me?
p/s
second questions - is there any simple way to sync manually sync Calendar with Google Calendar. I integrate by Google calendar API but it doesnāt work. (crorntab runing but not sync )
Try looking for log messages at the time of the export (on the 1st system) or import (2nd system).
Or try moving the module without export/import, simply by moving the files from one systemās custom dir to the other. You need to be careful figuring out what to move, but you get more control over whatās happening.
I trying direct move custom folders itās move fine but havenāt custom fields in studio-> my_imported_module->field(only default fields here). and my_module hasnāt position on ALL module menu filter
Thanks, I try repeat following your guide
Do you know anything about sync Calendar with Google Calendar. I integrate by Google calendar API but it doesnāt work. (crontab running but not sync )
How I can check crontab is work fine?
I did this sudo crontab -e -u www-data
ā¦ and add the following line to the crontab file:
*** * * * * cd /var/www/suitecrm; php -f cron.php > /dev/null 2>&1**