I am developing a system which generates an ID for all users that visit my website. If the user creates an account, applies for credit, buys a products... etc, it links the user information with this ID across multiple tables on the database. My problem is, combining all the information takes a long time as we have over 60,000 thousand records as of this moment. I previously created a VIEW which completely killed the performance time (taking up to 3 minutes) so I decided to instead create a query to fetch the data and combine the non-empty fields with PHP, which came up with a total of 10-15 seconds using pagination as well. I would like it to improve it more if possible. Any suggestions?
SELECT DISTINCT
u.user_id,
u.operating_system_name AS os,
u.browser_name AS browser,
u.ip_address,
u.hardware_type AS hardware,
u.city AS city,
u.state AS state,
u.country AS country,
u.date AS date,
m.id_member AS member,
s.id_supplier AS supplier,
c.id_entry AS credit,
p.id_partner AS partner,
cu.employee_ID AS careersus,
cm.empleado_ID AS careersmx,
o.id_order AS orderid,
o.recipient_name AS fn_o,
m.first_name AS fn_m,
c.first_name AS fn_c,
p.first_name AS fn_p,
s.contact_first_name AS fn_s,
cu.firstname AS fn_cu,
cm.nombre AS fn_cm,
o.last_name AS ln_o,
m.last_name AS ln_m,
c.last_name AS ln_c,
p.last_name AS ln_p,
s.contact_last_name AS ln_s,
cu.lastname AS ln_cu,
cm.apellido AS ln_cm
FROM tb_users_ids AS u
LEFT JOIN tb_orders AS o ON u.user_id = o.user_id
LEFT JOIN tb_members AS m ON u.user_id = m.user_id
LEFT JOIN financing AS c ON c.user_id = u.user_id
LEFT JOIN tb_partners AS p ON p.user_id = u.user_id
LEFT JOIN suppliers AS s ON s.user_id = u.user_id
LEFT JOIN tb_careers_us AS cu ON cu.user_id = u.user_id
LEFT JOIN tb_careers_mx AS cm ON cm.user_id = u.user_id
WHERE u.user_id<>''
ORDER BY u.date DESC
LIMIT ".$offset.", ".$rowsperpage;
