How to change a standard database query for listview

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…

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…

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?

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 a Quick Repair and Rebuild on the second system.

sure this is first step I’ve made

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.

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?

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

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