maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

ops, i pressed enter without full message, sorry

from mysql 4.1 (when sub query started) i "ported" my queries to something
like:
SELECT XXXX
FROM table1,table2
WHERE
(joins go here) AND
(filters go here)
GROUP BY fields
ORDER BY fields
LIMIT xxxx
OFFSET xxxx

example..

SELECT
SUM( CantidadPedida ) AS SumPedida,
SUM( CantidadRecibida ) AS SumRecibida,
ArticuloCodigo, ArticuloNombre, ArticuloCosto,
ArticulosDisponibles, ArticuloUnidad

FROM ArticulosPedidos, PedidosIndex,CatArticulos
WHERE
PedidosIndex.NumDePedido = ArticulosPedidos.NumDePedido AND
ArticulosPedidos.ArticuloID = CatArticulos.ArticuloID AND

ArticulosPedidos.Fecha BETWEEN '{$_POST['MovsDelAno']}-{$_**POS
T['MovsDelMes']}-{$_POST['**MovsDelDia']}' AND '{$_POST['MovsAlAno']}-{$_**
POST['MovsAlMes']}-{$_POST['**MovsAlDia']}'

GROPU BY ArticulosPedidos.ArticuloID
ORDER BY SumPedida DESC
LIMIT 500

----
other tip, instead of full table name, *USE* alias
FROM ArticulosPedidos AS a, PedidosIndex AS b, CartArticulos AS c
b.NumDePedido=a.NumDePedido AND
a.ArticuloID=c.ArticuloID

this decrease TCP/IP or UNIX SOCKET packets... if you have a very high qps
(query per second) this can save a lot of ethernet, example
1000 queries of 1KB=1MB/second , if you can save near to 50B per query, you
will use 950*1000 = 950KB, in other words, you can send 5% more bytes over
network without changing hardware
---