Dto. Sistemas de Unitel wrote: [...]
something like /indexes.id=productos.prod_id and indexes.id_termino=terminos.id_termino and termino="computer" and termino=”intel”/ I know that is impossible, but maybe there is another way to make that).
Yes, there is another way. You _can_ join the same table multiple times, but you need to use table aliases. In this case you need to join two tables two times, both the terminos table and the indexes table.
SELECT STRAIGHT_JOIN
Why do you use the STRAIGHT_JOIN? It is often best to let MySQL optimize how to solve the query (order of joins), STRAIGHT_JOIN prevents this.
terminos.id_termino,productos.prod_descripcion,indexes.id,terminos.termino,
sum(indexes.rank) as ordenate,productos.prod_unitel_id FROM terminos,indexes,productos where indexes.id=productos.prod_id and indexes.id_termino=terminos.id_termino and termino="computer" or indexes.id=productos.prod_id and indexes.id_termino=terminos.id_termino and termino="intel" group by (indexes.id) order by ordenate desc
In general, when you use both AND and OR in an expression, you should use parantheses to make it clear what happens. I have no idea how MySQL resolves the above WHERE clause. The parantheses on the GROUP BY clause are not needed, they are ignored.
Try something like this:
SELECT productos.prod_descripcion, sum(i1.rank+i2.rank) as ordenate, productos.prod_unitel_id FROM terminos t1,terminos t2,indexes i1,indexes i2,productos WHERE i1.id=productos.prod_id and i1.id_termino=t1.id_termino and t1.termino="computer" AND i2.id=productos.prod_id and i2.id_termino=t2.id_termino and t2.termino="intel" GROUP BY productos.prod_descripcion, productos.prod_unitel_id order by ordenate desc
This is almost the same statment, but your OR is replaced with an AND, it should result in a faster query.
In general, for a GROUP BY query, you should not select columns you are not using in the GROUP BY clause, except for when aggregate functions (like SUM(),AVG(),MIN(),MAX()...) are used on the column(s).
<URL: http://dev.mysql.com/doc/mysql/en/group-by-functions.html > <URL: http://dev.mysql.com/doc/mysql/en/group-by-hidden-fields.html >
Use EXPLAIN to check what join order the MySQL optmizer chooses for you, post the result if it is still too slow. If everything is indexed correctly, it should read t1/t2 first, then i1/i2 and finally productos.
<URL: http://dev.mysql.com/doc/mysql/en/explain.html >
-- Roger
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
