Hello,
I´m using MySQL-4.1.7, and I have a query that seems,
even using where clause, an specific table isn´t being optimized
properly:
I have four tables:
real_state: cod, name, city, ag_cod, sell
agency: cod, name, ag_status
front: cod, rs_cod, ag_cod
photo: cod, rs_cod
These table have the following indexes:
real_state:
cod (pri)
city
ag_cod
agency:
cod
name
front:
cod
rs_cod
ag_cod
photo
cod
rs_cod
When I EXPLAIN the query:
EXPLAIN
SELECT front.rs_cod, photo.cod, real_state.descr
FROM real_state, agency, front, photo_foto
WHERE real_state.city = 1
AND real_state.sell = 1
AND front.rs_cod = real_state.cod
AND photo.rs_cod = front.rs_cod
AND agency.cod = real_state.ag_cod
AND ag_status = 'A'
It shows me (in a short):
table key rows
=== === ====
front rs_cod 2085
real_state cod 1
agency cod 1
photo rs_cod 1
But the trouble is: If I execute:
SELECT COUNT(*)
FROM real_state, front
WHERE real_state.city = 1
AND real_state.cod = front.rs_cod
Returns: 271
So, Why the index front.rs_cod isn´t being used?
Any help would be appreciated,
thank you,
Ronan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
