19/02/2007, 15:36
|
 | | | Fecha de Ingreso: enero-2005 Ubicación: Perú
Mensajes: 235
Antigüedad: 20 años, 2 meses Puntos: 1 | |
| Re: Consulta: Optimizar tiempo de respuesta
Esta es la estructura de las tablas Llamada (4654 reg)
Código:
+------------+-------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+------------+----------------+
| idhelp | int(4) | | PRI | | auto_increment |
| idcomercio | varchar(5) | | MUL | | |
| codigo | varchar(8) | | | | |
| nroticket | varchar(10) | | | | |
| codaveria | varchar(10) | | | | |
| contacto | varchar(30) | | | | |
| probid | varchar(6) | | MUL | | |
| idprob | varchar(6) | | MUL | | |
| reporte | blob | | | | |
| fecha | date | | | 0000-00-00 | |
| hora | time | | | 00:00:00 | |
| idempleado | char(2) | | MUL | | |
| estado | char(2) | | | | |
| sol | char(2) | YES | | NULL | |
+------------+-------------+------+-----+------------+----------------+
(650 reg)
Código:
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| idcomercio | int(5) | | PRI | NULL | auto_increment |
| codigo | varchar(5) | YES | | 0 | |
| nombre | varchar(50) | | | | |
| direccion | varchar(80) | | | | |
| distrito | varchar(40) | | | | |
| provincia | varchar(40) | | | | |
| departamento | varchar(40) | | | | |
| telefono | varchar(30) | | | | |
| contacto | varchar(60) | | | | |
| tequipo | varchar(15) | | | | |
| estado | char(1) | | | | |
| finicio | date | YES | | NULL | |
| fretiro | date | YES | | NULL | |
| comentario | tinyblob | YES | | NULL | |
+--------------+-------------+------+-----+---------+----------------+
(674 reg)
Código:
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| inst | int(11) | | PRI | NULL | auto_increment |
| idinst | int(11) | | | 0 | |
| sec | varchar(5) | | | | |
| idcomercio | varchar(5) | | MUL | | |
| idempleado | varchar(8) | | MUL | | |
| respname | varchar(80) | YES | | NULL | |
| codinstal | varchar(10) | | | | |
| prodmodelid | char(3) | | | | |
| prodtypeid | char(3) | | | | |
| nroserie | varchar(15) | YES | | NULL | |
| inventario | varchar(15) | YES | | NULL | |
| prodmodelid2 | char(3) | | | | |
| prodtypeid2 | char(3) | | | | |
| nroserie2 | varchar(15) | | | | |
| inventario2 | varchar(15) | | | | |
| observacion | blob | | | | |
| fecha | date | YES | | NULL | |
| h_inicio | time | YES | | NULL | |
| h_fin | time | YES | | NULL | |
| estado | char(1) | | | | |
| codaveria | varchar(10) | YES | | NULL | |
+--------------+-------------+------+-----+---------+----------------+
(34 reg)
Código:
+-----------------+--------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------------------+-------+
| IdEmpleado | int(11) | | PRI | 0 | |
| codigo | varchar(10) | | | | |
| Nombres | varchar(50) | YES | | NULL | |
| ApellidoPaterno | varchar(50) | YES | | NULL | |
| ApellidoMaterno | varchar(50) | YES | | NULL | |
| Direccion | varchar(150) | YES | | NULL | |
| Telefono | varchar(50) | YES | | NULL | |
| Celular | varchar(50) | YES | | NULL | |
| email | varchar(50) | | | | |
| IdCargo | int(11) | YES | | NULL | |
| StoreCode | varchar(9) | YES | | NULL | |
| fecha | datetime | | | 0000-00-00 00:00:00 | |
| estado | tinyint(4) | | | 0 | |
+-----------------+--------------+------+-----+---------------------+-------+
(10 reg)
Código:
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| idprob | varchar(5) | | PRI | | |
| descripcion | varchar(60) | | | | |
| codigo | varchar(11) | | | | |
+-------------+-------------+------+-----+---------+-------+
(137 reg)
Código:
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| probid | int(11) | | PRI | 0 | |
| probdetail | varchar(80) | | | | |
| probdetailid | varchar(5) | | | | |
| codigo | varchar(15) | YES | | NULL | |
| categoria | varchar(50) | | | | |
| probcomments | varchar(200) | | | | |
| state | tinyint(1) | | | 0 | |
+--------------+--------------+------+-----+---------+-------+
Y esta es la consulta:
Código:
SELECT h.*,
c1.Nombres nombre, c1.codigo codigoe,
c2.nombre loca, c2.tequipo equipo, c2.codigo, c2.distrito, c2.departamento, c2.direccion, c2.telefono,
c6.estado,
c7.probdetail, c7.codigo code, c7.categoria cat,
c8.descripcion
from llamada h ,
empleado c1,
comercio c2,
instalacion c3 ,
h_desk_es c6,
detproblema c7,
problema c8
where (h.idempleado = c1.IdEmpleado)
and (h.idcomercio = c2.idcomercio and c2.idcomercio = c3.idcomercio)
and (h.estado= c6.id_hdeskes)
and (h.probid= c7.probid)
and (h.idprob= c8.idprob)
and (h.fecha >= '2007-02-01' and h.fecha <= '2007-02-31')
group by h.idhelp
order by h.idhelp desc
Asi es la estructura, recibo ayuda, criticas, consejos, de todo.
Muchas gracias.
__________________
:adios: |