Emanuel Calvo Franco escribió:
>
> Crees que se indices? mmmh IMHO no es eso, debido a que con inner le
> tarda menos,
> podría pasar por otra cosa.
>
> Jose, el EXPLAIN nos dará la luz ;)
He aquí los Explain, primero para la consulta original (LEFT JOIN) y luego para
la
consulta modificada (INNER JOIN)
********************************************************************************
mysql> EXPLAIN SELECT leads.id, leads_cstm.web_c,
leads_cstm.potencialproducto_c,
leads_cstm.rubro_c, leads_cstm.subrubro_c, leads_cstm.interesado_en_c,
leads_cstm.encuesta_c, CONCAT( IFNULL( leads.first_name, '' ) , ' ', IFNULL(
leads.last_name, '' ) ) AS name, leads.first_name, leads.last_name,
leads.salutation,
leads.status, leads.account_name, leads.account_id, jt0.user_name
assigned_user_name,
jt0.created_by assigned_user_name_owner, 'Users'assigned_user_name_mod,
leads.date_entered, leads.assigned_user_id
-> FROM leads
-> LEFT JOIN leads_cstm ON leads.id = leads_cstm.id_c
-> LEFT JOIN users jt0 ON jt0.id = leads.assigned_user_id
-> AND jt0.deleted =0
-> AND jt0.deleted =0
-> WHERE leads.deleted =0
-> ORDER BY name ASC
-> LIMIT 0 , 51;
+----+-------------+------------+--------+---------------+--------------+---------+------------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref
| rows | Extra |
+----+-------------+------------+--------+---------------+--------------+---------+------------------------------+------+----------------------------------------------+
| 1 | SIMPLE | leads | range | idx_del_user | idx_del_user | 2
| NULL
| 7810 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | leads_cstm | ALL | NULL | NULL | NULL
| NULL
| 8129 | |
| 1 | SIMPLE | jt0 | eq_ref | PRIMARY | PRIMARY | 108
|
sugar.leads.assigned_user_id | 1 |
|
+----+-------------+------------+--------+---------------+--------------+---------+------------------------------+------+----------------------------------------------+
3 rows in set (0,00 sec)
********************************************************************************
mysql> EXPLAIN SELECT leads.id, leads_cstm.web_c,
leads_cstm.potencialproducto_c,
leads_cstm.rubro_c, leads_cstm.subrubro_c, leads_cstm.interesado_en_c,
leads_cstm.encuesta_c, CONCAT( IFNULL( leads.first_name, '' ) , ' ', IFNULL(
leads.last_name, '' ) ) AS name, leads.first_name, leads.last_name,
leads.salutation,
leads.status, leads.account_name, leads.account_id, jt0.user_name
assigned_user_name,
jt0.created_by assigned_user_name_owner, 'Users'assigned_user_name_mod,
leads.date_entered, leads.assigned_user_id
-> FROM leads
-> INNER JOIN leads_cstm ON leads.id = leads_cstm.id_c
-> INNER JOIN users jt0 ON jt0.id = leads.assigned_user_id
-> AND jt0.deleted =0
-> AND jt0.deleted =0
-> WHERE leads.deleted =0
-> ORDER BY name ASC
-> LIMIT 0 , 51;
+----+-------------+------------+--------+----------------------------------------+---------+---------+------------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys
| key
| key_len | ref | rows | Extra
|
+----+-------------+------------+--------+----------------------------------------+---------+---------+------------------------------+------+---------------------------------+
| 1 | SIMPLE | leads_cstm | ALL | NULL
| NULL
| NULL | NULL | 8129 | Using temporary; Using
filesort |
| 1 | SIMPLE | leads | eq_ref |
PRIMARY,idx_del_user,idx_lead_assigned |
PRIMARY | 108 | func | 1 | Using where
|
| 1 | SIMPLE | jt0 | eq_ref | PRIMARY
|
PRIMARY | 108 | sugar.leads.assigned_user_id | 1 | Using where
|
+----+-------------+------------+--------+----------------------------------------+---------+---------+------------------------------+------+---------------------------------+
3 rows in set (0,00 sec)
********************************************************************************
Por lo que veo, con INNER JOIN (y con RIGHT JOIN también), MySQL utiliza los
índices:
"PRIMARY,idx_del_user,idx_lead_assigned" para la tabla "leads", pero con LEFT
JOIN utiliza
sólo el índice "idx_del_user".
Ahora estoy viendo qué índice crear para que el LEFT JOIN lo tome...
Abrazos
--
José C. Massón
.: Rebeldes Stereotipos
Sábados 15 a 17 hs FM La Tribu 88.7 MHz
rebeldes.org.ar
--
Para desuscribirte tenés que visitar la página
https://listas.linux.org.ar/mailman/listinfo/lugar-gral/
Usuarios Software Libre Argentina (USLA)
