How to change a standard database query for listview

šŸ‘Øā€šŸ’» Development Help
1

Hello every one

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ā€¦

2

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ā€¦

3

thanks for your reply

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?

4

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.

5

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 )

6

Try a Quick Repair and Rebuild on the second system.

7

sure this is first step Iā€™ve made

8

I donā€™t know, then.

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.

9

thanks for assist me

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

What I did wrong?

10

Use some ā€œfind in filesā€ tool to look for the field names in files under custom directory.

For example:
grep -irn myFieldName custom

I think you will find what youā€™re looking for under custom/Extension.

Note that some of the files in custom are generated by the QR & R, you shouldnā€™t move those.

The best rule I know is:

  • donā€™t move anything under a /Ext/ subdirectory
  • except if they are under custom/Extension/.../Ext/...

This will help you find your way around:

ā€“> https://docs.suitecrm.com/developer/extension-framework/#_standard_extensions

11

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**

image
image1371Ɨ567 27.9 KB