Index selection problem

Hi, I have a table orders with the columns item_id INT FK items(id) customer_id INT FK customers(id) status_id TINYINT -- Between 1 and 4 always ordered_at DATETIME delivered_at DATETIME There are indexes: index_a: (item_id, customer_id, status_id) index_b: (item_id, status_id,

Query_cache instance creation

Hi, MySQL query cache implementation is based on the Query_cache object (ref: sql_cache.cc). But I cannot find where the instance for the object is created ... (like new Query_cache qcache ...). Can anybody point me to the file please? Regards, Raja

Re: Index selection problem

MySQL is unable to use your index when you use IN and/or OR on yoru column. If the query is slow, you should switch to a union: SELECT * FROM orders WHERE item_id = 9602 AND customer_id = 5531 AND status_id =1 UNION SELECT * FROM orders WHERE item_id = 9602 AND customer_id = 5531 AND status_id

Re: Index selection problem

Try doing a SHOW INDEX FROM orders and look at the cardinality column. These are the stats MySQL uses to determine which index to use. Sometimes they aren't always update properly and you may need to run ANALYZE on the table. But, you can also tell MySQL to use the index you want. SELECT * FROM

Re: Index selection problem

On Jul 21, 2009, at 3:27 PM, Johnny Withers wrote: MySQL is unable to use your index when you use IN and/or OR on yoru column. Is this really true? I'm reading High Performance MySQL 2nd ed. these days and specifically got the impression that using IN will allow usage of the index. The

Re: Hard? query to with group order by group head's name

Hi Darryle, Your result was: +---+---+-+ | member_id | name | head_id | +---+---+-+ | 2 | Ann | 1 | | 3 | David | NULL | | 1 | Elim | NULL | | 5 | Jane | 3 | | 4 | John | 3 |

Re: Index selection problem

The other index does have a way higher cardinality, but the query is for 3 columns all of which are in the first index. I guess this is just one of the situations where MySQL makes a wrong assessment. On Jul 21, 2009, at 3:54 PM, Brent Baisley wrote: Try doing a SHOW INDEX FROM orders

Converting VFP SQL to MySQL

Does anyone have any scripts that will help convert Visual FoxPro 6.0 style WHERE clauses to MySQL... For the most part the problems are converting VFP functions to the equivalent SQL. For example, Visual FoxPro has a function inlist() that is used like inlist(X,1,2,3) which converts to the MySQL

Re: Index selection problem

On Tue, 2009-07-21 at 19:42 +0200, Morten Primdahl wrote: On Jul 21, 2009, at 3:27 PM, Johnny Withers wrote: MySQL is unable to use your index when you use IN and/or OR on yoru column. Is this really true? No its not true! Try running OPTIMIZE TABLE on the affected table, then run

Re: Hard? query to with group order by group head's name

On Thu, Jul 16, 2009 at 1:20 AM, Elim PDTe...@pdtnetworks.net wrote: My table group_member looks like this: +---+---+-+ | member_id | name  | head_id | +---+---+-+ |         1 | Elim  |    NULL | |         2 | Ann   |       1 | |         3 | David |  

RE: Query_cache instance creation

You might have better luck on the mysql-internals list -Original Message- From: Rajarshi Chowdhury [mailto:mailtorajar...@gmail.com] Sent: Tuesday, July 21, 2009 4:58 AM To: mysql@lists.mysql.com Subject: Query_cache instance creation Hi, MySQL query cache implementation is based on the

Re: Hard? query to with group order by group head's name

On Thu, Jul 16, 2009 at 1:20 AM, Elim PDTe...@pdtnetworks.net wrote: My table group_member looks like this: +---+---+-+ | member_id | name | head_id | +---+---+-+ | 1 | Elim | NULL | | 2 | Ann | 1 | | 3 | David | NULL | | 4 | John | 3 | | 5 | Jane | 3 |