Re: Doubt about query optimization
Eric, Can you send us the actual show indexes from table and explain output that isn't shortend? Thank you for answer my question. Actually, the real query and tables are much more complex than the data in the previous message. A just tryed to optimize the information for you better understand the trouble. I think found what´s happening. A SELECT WHERE city = 1 returns more rows thant a full table scan in the table front (the real states that appear in the front page). So, it seems MySQL choose the second option, once it has less rows to optimize. Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Doubt about query optimization
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): tablekeyrows ====== frontrs_cod 2085 real_statecod1 agencycod1 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]
Re: Doubt about query optimization
Can you send us the actual show indexes from table and explain output that isn't shortend? Ronan Lucio wrote: 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): tablekeyrows ====== frontrs_cod 2085 real_statecod1 agencycod1 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]
Doubt about query optimization
Hello, Supposing I have 2 tables: product == - id - name price = - id - product_id - sell_price - rent_price If I want to make a SELECT for the products having the rent_price between 300,00 and 500,00, so I use the query: SELECT rent_price FROM product LEFT JOIN price ON (product.id = price.product_id) WHERE rent_price BETWEEN 300.00 and 500.00 My doubt is if the table product will be optimized. Will optimization process be done over all rows from the product table or only the rows related to the price table? In other words: If table price has other columns and LEFT JOIN is needed anyway, even that would be better to move the columns sell_price and rent_price to the product table? My table has so many columns, and, for structural and maintainance reasons it would be better to divide the columns in two tables, but I can´t compromisse the application performance because of it. Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
