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, ordered_at, delivered_at) Given this query: SELECT * FROM orders WHERE item_id = 9602 AND customer_id = 5531 AND status_id IN (1,2) Then the key chosen is index_b. Same happens if I use (status_id = 1 OR status_id = 2). If I only check against one status_id, then the correct index_a gets picked with ref const,const,const. I'm not even doing a range scan on status_id and even if I were, it's the last column in index_a. Since ordered_at and delivered_at are both dates then index_b will have a very high selectivity. In reality, index_b may make little sense, but I still don't understand why MySQL would ever pick that when 3 columns in the query can use the covering index_a Can anyone give me some input on how to make sense of this? Thanks, Morten select count(*) from orders where item_id = 9602 - 4534 records select count(*) from orders where item_id = 9602 and status_id IN (1,2) - 4181 records select count(*) from orders where item_id = 9602 and customer_id = 5531 - 1226 records select count(*) from orders where item_id = 9602 and customer_id = 5531 and status_id IN (1,2) - 1174 records -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
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 =2 On Tue, Jul 21, 2009 at 4:52 AM, Morten my.li...@mac.com wrote: 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, ordered_at, delivered_at) Given this query: SELECT * FROM orders WHERE item_id = 9602 AND customer_id = 5531 AND status_id IN (1,2) Then the key chosen is index_b. Same happens if I use (status_id = 1 OR status_id = 2). If I only check against one status_id, then the correct index_a gets picked with ref const,const,const. I'm not even doing a range scan on status_id and even if I were, it's the last column in index_a. Since ordered_at and delivered_at are both dates then index_b will have a very high selectivity. In reality, index_b may make little sense, but I still don't understand why MySQL would ever pick that when 3 columns in the query can use the covering index_a Can anyone give me some input on how to make sense of this? Thanks, Morten select count(*) from orders where item_id = 9602 - 4534 records select count(*) from orders where item_id = 9602 and status_id IN (1,2) - 4181 records select count(*) from orders where item_id = 9602 and customer_id = 5531 - 1226 records select count(*) from orders where item_id = 9602 and customer_id = 5531 and status_id IN (1,2) - 1174 records -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net -- - Johnny Withers 601.209.4985 joh...@pixelated.net
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 orders USE INDEX (index_a) WHERE ... Brent Baisley On Tue, Jul 21, 2009 at 5:52 AM, Mortenmy.li...@mac.com wrote: 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, ordered_at, delivered_at) Given this query: SELECT * FROM orders WHERE item_id = 9602 AND customer_id = 5531 AND status_id IN (1,2) Then the key chosen is index_b. Same happens if I use (status_id = 1 OR status_id = 2). If I only check against one status_id, then the correct index_a gets picked with ref const,const,const. I'm not even doing a range scan on status_id and even if I were, it's the last column in index_a. Since ordered_at and delivered_at are both dates then index_b will have a very high selectivity. In reality, index_b may make little sense, but I still don't understand why MySQL would ever pick that when 3 columns in the query can use the covering index_a Can anyone give me some input on how to make sense of this? Thanks, Morten select count(*) from orders where item_id = 9602 - 4534 records select count(*) from orders where item_id = 9602 and status_id IN (1,2) - 4181 records select count(*) from orders where item_id = 9602 and customer_id = 5531 - 1226 records select count(*) from orders where item_id = 9602 and customer_id = 5531 and status_id IN (1,2) - 1174 records -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=brentt...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
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 below quote is from the book, and the multiple equality condition refers to an IN (...) expression. ... we draw a distinction between ranges of values and multiple equality conditions.The second query is a multiple equality condition, in our terminology. We’re not just being picky: these two kinds of index accesses perform differently. The range condition makes MySQL ignore any further columns in the index, but the multiple equality condition doesn’t have that limitation. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
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 | +---+---+-+ which not groups correctly. Seems it's a hard query. - Original Message - From: Darryle Steplight dstepli...@gmail.com To: Elim PDT e...@pdtnetworks.net Cc: mysql@lists.mysql.com Sent: Wednesday, July 15, 2009 11:50 PM Subject: Re: Hard? query to with group order by group head's name Hi Elim, I didn't test it out but it sounds like you want to do this SELECT * FROM group_members GROUP BY head_id, member_id ORDER BY name ASC . 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 | +---+---+-+ Record with null head_id means the member is a group head. Record with head_id k are in the group with head whoes id equals k. I like to fetch the rows in the following ordaer | 3 | David | NULL | | 4 | John | 3 | | 5 | Jane | 3 | | 1 | Elim | NULL | | 2 | Ann | 1 | That is (1) A head-row follewed by the group members with that head (2)head rows are ordered alphabetically by name. What the query looks like? Thanks -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
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 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 orders USE INDEX (index_a) WHERE ... Brent Baisley On Tue, Jul 21, 2009 at 5:52 AM, Mortenmy.li...@mac.com wrote: 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, ordered_at, delivered_at) Given this query: SELECT * FROM orders WHERE item_id = 9602 AND customer_id = 5531 AND status_id IN (1,2) Then the key chosen is index_b. Same happens if I use (status_id = 1 OR status_id = 2). If I only check against one status_id, then the correct index_a gets picked with ref const,const,const. I'm not even doing a range scan on status_id and even if I were, it's the last column in index_a. Since ordered_at and delivered_at are both dates then index_b will have a very high selectivity. In reality, index_b may make little sense, but I still don't understand why MySQL would ever pick that when 3 columns in the query can use the covering index_a Can anyone give me some input on how to make sense of this? Thanks, Morten select count(*) from orders where item_id = 9602 - 4534 records select count(*) from orders where item_id = 9602 and status_id IN (1,2) - 4181 records select count(*) from orders where item_id = 9602 and customer_id = 5531 - 1226 records select count(*) from orders where item_id = 9602 and customer_id = 5531 and status_id IN (1,2) - 1174 records -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=brentt...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
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 query X IN (1,2,3). That's easy enough (relatively speaking) but VFP also has stuff like EMPTY(X) where any of Null, the Empty String (for Char), -00-00 (or the VFP equivalent anyways for dates), False (for Boolean), 0 (for Numeric) are considered empty without needing to know the data type. So that starts getting a lot more complex since I'd need to check the data type of the field in the right table... to be able to convert it to something like (X is null OR X=) or (X is null OR x=0) etc... These are for customer stored queries... I've already manually converted system queries and I'm frustrated to the point of giving up and adding a column untested and let the end user figure it out but that seems bad from the standpoint of lazy and poor customer experience. Thanks! Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
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 the query again and see if the other index is used! 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 below quote is from the book, and the multiple equality condition refers to an IN (...) expression. ... we draw a distinction between ranges of values and multiple equality conditions.The second query is a multiple equality condition, in our terminology. We’re not just being picky: these two kinds of index accesses perform differently. The range condition makes MySQL ignore any further columns in the index, but the multiple equality condition doesn’t have that limitation. John Daisley Email: john.dais...@butterflysystems.co.uk Mobile: +44 (0)7812 451238 MySQL Certified Database Administrator (CMDBA) MySQL Certified Developer (CMDEV) MySQL Certified Associate (CMA) Comptia A+ Certified Professional IT Technician --- Life's journey is not to arrive at the grave safely in a well preserved body, but rather to slide in sideways, thoroughly used up, totally worn out and screaming Wow! what a ride!
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 | +---+---+-+ Record with null head_id means the member is a group head. Record with head_id k are in the group with head whoes id equals k. I like to fetch the rows in the following ordaer | 3 | David | NULL | | 4 | John | 3 | | 5 | Jane | 3 | | 1 | Elim | NULL | | 2 | Ann | 1 | That is (1) A head-row follewed by the group members with that head (2)head rows are ordered alphabetically by name. What the query looks like? Thanks You need to create your own sort values, and link to the head name. So really you are sorting on head name + head_id. Since sometimes the head name is the current record, sometimes it's a parent record, you need to conditional check which type of record it is and built the sort value. SELECT tablename.*, IF(tablename.head_id=NULL, CONCAT(tablename.name, tablename.member_id), CONCAT(heads.name, tablename.head_id) ) AS SortValue FROM tablename LEFT JOIN tablename AS heads ON tablename.head_id=heads.member_id ORDER BY SortValue Brent Baisley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
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 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 The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
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 | +---+---+-+ Record with null head_id means the member is a group head. Record with head_id k are in the group with head whoes id equals k. I like to fetch the rows in the following ordaer | 3 | David | NULL | | 4 | John | 3 | | 5 | Jane | 3 | | 1 | Elim | NULL | | 2 | Ann | 1 | That is (1) A head-row follewed by the group members with that head (2)head rows are ordered alphabetically by name. What the query looks like? Thanks I hope this is not a school assignment. What I came up with was to create a new order column that I populated with the name of the HEAD. Then I can order by the head, head_id, and the member_id mysql select t1.member_id, t1.name, t1.head_id from ( select m1.*, IF ( m2.name IS NULL, m1.name, m2.name) as groupName from group_member as m1 left outer join group_member as m2 ON ( m1.head_id = m2.member_id ) order by groupName, m1.head_id, m1.member_id ) AS t1; +---+---+-+ | member_id | name | head_id | +---+---+-+ | 3 | David | NULL| | 4 | John | 3 | | 5 | Jane | 3 | | 1 | Elim | NULL| | 2 | Ann | 1 | +---+---+-+ 5 rows in set (0.01 sec) It seemed to work without the order by member_id but I'll assume that is a fact of the small sample size. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
