Re: Need help with query
2011/03/15 17:51 -0500, LAMP Let's say there is a table orders (simplified, of course) CREATE TABLE orders ( item_id int, org_id int, ) ENGINE=MyISAM Need to select all (distinct) org_id they have item_id 34, 36, 58 and 63. All of them, not only some of them. Result is org_id=2607 and org_id=1520 I can have it by select org_id from orders where item_id in (34, 36, 58, 63) group by org_id having count(org_id)=4 I now noticed the aggregate function GROUP_CONCAT: select org_id,GROUP_CONCAT(DISTINCT item_id, ORDER BY item_id) AS itemset from orders where item_id in (34, 36, 58, 63) group by org_id having itemset = '34,36,58,63' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Need help with query
Hi! I think that the query that you have proposed is the best possible for the problem. However, if there are duplicates in the orders table, then HAVING COUNT(item_id) = 4 should be replaced with HAVING COUNT(DISTINCT item_id) = 4 (I assume that you meant item_id and not org_id in the COUNT function). Thanks, Roy On 17.03.11 18.00, LAMP wrote: Yes, that was my question. Though, since English is not my first language, let me try to post it again: There is a list of all orgs and items org bough, from table called orders item_id org_id 34 2607 34 2607 34 1520 36 2607 36 1520 36 8934 38 28 38 15 38 5 38 13 58 2607 58 2607 58 7295 58 1649 58 7295 58 1520 63 2607 63 2607 63 8871 63 7295 63 1520 65 15 65 20 95 1520 95 1520 95 7295 98 1520 98 7295 select org_id from orders where item_id in (34. 36. 58. 63) will give me a result 5 13 15 28 1520 1649 2607 7295 8871 8934 This is the list of ALL orgs they bought ANY of items (34. 36. 58. 63). Agree? What I need is a list of orgs they bought all of items 34, 36, 58, 63. every of them. Result should be only orgs 2607 and 1520. I hope it's more clear now. On Mar 15, 2011, at 10:47 PM, Rhino wrote: Your original question said: Need to select all (distinct) org_id they have item_id 34, 36, 58 and 63. All of them, not only some of them. That's the question I answered with my suggested query. It sounds like that is not what you meant after all but I'm not sure what you DO want with your query. Why are 2607 and 1520 the only right answers? Because they are. I look at the database and manually found the result I have to get. What's wrong with my statement? Based on your own query, it looks like you only want an org_id for item_ids 34, 36, 58, and 63 if there are exactly 4 occurrences of that org_id amongst the desired item_ids actually, there is mistake in my query, it should say having count(org_id) = 4 and, yes, that's what I want. I can get the correct list using the query I posted but I was hoping there is BETTER way. but that wasn't in your statement of the problem. So please clarify EXACTLY what you want. Giving an incomplete or contradictory description of you want only wastes both your time and mine. As I stated earlier, English is not my first language and I was trying to do my best. Sorry for confusing you. -- Rhino On 2011-03-15 20:35, LAMP wrote: On Mar 15, 2011, at 6:18 PM, Rhino wrote: All you should need is this: select distinct org_id from orders where item_id in (34, 36, 58, 63) I'm assuming that the DISTINCT operator is available in the version of MySQL that you are using. I don't currently have any version of MySQL installed so I can't try this myself to be sure it works in your version of MySQL. -- Rhino your query will give me every org_id that has ANY of item_id., I need org_id that has ALL of item_id. right? result would be 2607 1520 8934 7295 1649 8871 On 2011-03-15 18:51, LAMP wrote: Hi, I need a help to build a query. Let's say there is a table orders (simplified, of course) CREATE TABLE orders ( `item_id` int, `org_id` int, ) ENGINE=MyISAM item_id org_id 34 2607 34 2607 34 1520 36 2607 36 1520 36 8934 38 28 38 15 38 5 38 13 58 2607 58 2607 58 7295 58 1649 58 7295 58 1520 63 2607 63 2607 63 8871 63 7295 63 1520 65 15 65 20 95 1520 95 1520 95 7295 98 1520 98 7295 Need to select all (distinct) org_id they have item_id 34, 36, 58 and 63. All of them, not only some of them. Result is org_id=2607 and org_id=1520 I can have it by select org_id from orders where item_id in (34, 36, 58, 63) group by org_id having count(org_id)=4 but, I'm sure there is better solution? Thanks for any help. LAMP -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Need help with query
On Mar 17, 2011, at 3:01 PM, Geert-Jan Brits wrote: Indeed, I don't thing there is. Just be sure that each record has an unique combination of org_id and item_id, otherwise you might end up with an org_id that, for example, references 4 times item_id 34 in 4 different records, but no other item_ids. This is obvisouly not what you want. Geert-Jan Correct. That's why I use select distinct org_id, item_id in sub- query. Is here anybody from mysql development team, to suggest to build IN ALL function? :-) 2011/3/17 LAMP l...@afan.net First I was thinking there is function IN ALL or something like that, since there are functions IN and EXISTS. And I would be able to make a query something like this select distinct org_id, item_id from orders where item_id in all (34, 36, 58, 63) order by org_id asc But, there isn't any. :-( The correct query is select r.org_id from ( select distinct a.org_id, a.item_id from orders a where a.item_id in (34, 36, 58, 63) order by a.org_id asc ) r group by r.org_id having count(*) = 4 On Mar 17, 2011, at 12:24 PM, Peter Brawley wrote: What I need is a list of orgs they bought all of items 34, 36, 58, 63. every of them. Some solutions under What else did buyers of X buy at http://www.artfulsoftware.com/infotree/queries.php . PB --- On 3/17/2011 12:00 PM, LAMP wrote: Yes, that was my question. Though, since English is not my first language, let me try to post it again: There is a list of all orgs and items org bough, from table called orders item_idorg_id 342607 342607 341520 362607 361520 368934 3828 3815 385 3813 582607 582607 587295 581649 587295 581520 632607 632607 638871 637295 631520 6515 6520 951520 951520 957295 981520 987295 select org_id from orders where item_id in (34. 36. 58. 63) will give me a result 5 13 15 28 1520 1649 2607 7295 8871 8934 This is the list of ALL orgs they bought ANY of items (34. 36. 58. 63). Agree? What I need is a list of orgs they bought all of items 34, 36, 58, 63. every of them. Result should be only orgs 2607 and 1520. I hope it's more clear now. On Mar 15, 2011, at 10:47 PM, Rhino wrote: Your original question said: Need to select all (distinct) org_id they have item_id 34, 36, 58 and 63. All of them, not only some of them. That's the question I answered with my suggested query. It sounds like that is not what you meant after all but I'm not sure what you DO want with your query. Why are 2607 and 1520 the only right answers? Because they are. I look at the database and manually found the result I have to get. What's wrong with my statement? Based on your own query, it looks like you only want an org_id for item_ids 34, 36, 58, and 63 if there are exactly 4 occurrences of that org_id amongst the desired item_ids actually, there is mistake in my query, it should say having count(org_id) = 4 and, yes, that's what I want. I can get the correct list using the query I posted but I was hoping there is BETTER way. but that wasn't in your statement of the problem. So please clarify EXACTLY what you want. Giving an incomplete or contradictory description of you want only wastes both your time and mine. As I stated earlier, English is not my first language and I was trying to do my best. Sorry for confusing you. -- Rhino On 2011-03-15 20:35, LAMP wrote: On Mar 15, 2011, at 6:18 PM, Rhino wrote: All you should need is this: select distinct org_id from orders where item_id in (34, 36, 58, 63) I'm assuming that the DISTINCT operator is available in the version of MySQL that you are using. I don't currently have any version of MySQL installed so I can't try this myself to be sure it works in your version of MySQL. -- Rhino your query will give me every org_id that has ANY of item_id., I need org_id that has ALL of item_id. right? result would be 2607 1520 8934 7295 1649 8871 On 2011-03-15 18:51, LAMP wrote: Hi, I need a help to build a query. Let's say there is a table orders (simplified, of course) CREATE TABLE orders ( `item_id` int, `org_id` int, ) ENGINE=MyISAM item_idorg_id 342607 342607 341520 362607 361520 368934 3828 3815 385 3813 582607 582607 587295 581649 587295 581520 632607 632607 638871 637295 631520 6515 6520 951520 951520 957295 981520 987295 Need to select all (distinct) org_id they have item_id 34, 36, 58 and 63. All of them, not only some of them. Result is org_id=2607 and org_id=1520 I can have it by select
Re: Need help with query
2011/03/18 08:49 -0500, LAMP Is here anybody from mysql development team, to suggest to build IN ALL function? There is a problem here: the basic operation is on the record, each record by each record, all by itself. The solution to your problem entails acting on more distinct records until enough have been encountered. If you imagine the table input to a program that checks for hits, you will see the problem. The program reads its input, for every number of the four that you want matched it holds on to its mate until that mate is matched with all four of the chosen. It is a global condition, and SQL works one record at a time. Global conditions are detected only through the summary functions. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Need help with query
Yes, that was my question. Though, since English is not my first language, let me try to post it again: There is a list of all orgs and items org bough, from table called orders item_idorg_id 342607 342607 341520 362607 361520 368934 3828 3815 385 3813 582607 582607 587295 581649 587295 581520 632607 632607 638871 637295 631520 6515 6520 951520 951520 957295 981520 987295 select org_id from orders where item_id in (34. 36. 58. 63) will give me a result 5 13 15 28 1520 1649 2607 7295 8871 8934 This is the list of ALL orgs they bought ANY of items (34. 36. 58. 63). Agree? What I need is a list of orgs they bought all of items 34, 36, 58, 63. every of them. Result should be only orgs 2607 and 1520. I hope it's more clear now. On Mar 15, 2011, at 10:47 PM, Rhino wrote: Your original question said: Need to select all (distinct) org_id they have item_id 34, 36, 58 and 63. All of them, not only some of them. That's the question I answered with my suggested query. It sounds like that is not what you meant after all but I'm not sure what you DO want with your query. Why are 2607 and 1520 the only right answers? Because they are. I look at the database and manually found the result I have to get. What's wrong with my statement? Based on your own query, it looks like you only want an org_id for item_ids 34, 36, 58, and 63 if there are exactly 4 occurrences of that org_id amongst the desired item_ids actually, there is mistake in my query, it should say having count(org_id) = 4 and, yes, that's what I want. I can get the correct list using the query I posted but I was hoping there is BETTER way. but that wasn't in your statement of the problem. So please clarify EXACTLY what you want. Giving an incomplete or contradictory description of you want only wastes both your time and mine. As I stated earlier, English is not my first language and I was trying to do my best. Sorry for confusing you. -- Rhino On 2011-03-15 20:35, LAMP wrote: On Mar 15, 2011, at 6:18 PM, Rhino wrote: All you should need is this: select distinct org_id from orders where item_id in (34, 36, 58, 63) I'm assuming that the DISTINCT operator is available in the version of MySQL that you are using. I don't currently have any version of MySQL installed so I can't try this myself to be sure it works in your version of MySQL. -- Rhino your query will give me every org_id that has ANY of item_id., I need org_id that has ALL of item_id. right? result would be 2607 1520 8934 7295 1649 8871 On 2011-03-15 18:51, LAMP wrote: Hi, I need a help to build a query. Let's say there is a table orders (simplified, of course) CREATE TABLE orders ( `item_id` int, `org_id` int, ) ENGINE=MyISAM item_idorg_id 342607 342607 341520 362607 361520 368934 3828 3815 385 3813 582607 582607 587295 581649 587295 581520 632607 632607 638871 637295 631520 6515 6520 951520 951520 957295 981520 987295 Need to select all (distinct) org_id they have item_id 34, 36, 58 and 63. All of them, not only some of them. Result is org_id=2607 and org_id=1520 I can have it by select org_id from orders where item_id in (34, 36, 58, 63) group by org_id having count(org_id)=4 but, I'm sure there is better solution? Thanks for any help. LAMP
Re: Need help with query
What I need is a list of orgs they bought all of items 34, 36, 58, 63. every of them. Some solutions under What else did buyers of X buy at http://www.artfulsoftware.com/infotree/queries.php. PB --- On 3/17/2011 12:00 PM, LAMP wrote: Yes, that was my question. Though, since English is not my first language, let me try to post it again: There is a list of all orgs and items org bough, from table called orders item_idorg_id 342607 342607 341520 362607 361520 368934 3828 3815 385 3813 582607 582607 587295 581649 587295 581520 632607 632607 638871 637295 631520 6515 6520 951520 951520 957295 981520 987295 select org_id from orders where item_id in (34. 36. 58. 63) will give me a result 5 13 15 28 1520 1649 2607 7295 8871 8934 This is the list of ALL orgs they bought ANY of items (34. 36. 58. 63). Agree? What I need is a list of orgs they bought all of items 34, 36, 58, 63. every of them. Result should be only orgs 2607 and 1520. I hope it's more clear now. On Mar 15, 2011, at 10:47 PM, Rhino wrote: Your original question said: Need to select all (distinct) org_id they have item_id 34, 36, 58 and 63. All of them, not only some of them. That's the question I answered with my suggested query. It sounds like that is not what you meant after all but I'm not sure what you DO want with your query. Why are 2607 and 1520 the only right answers? Because they are. I look at the database and manually found the result I have to get. What's wrong with my statement? Based on your own query, it looks like you only want an org_id for item_ids 34, 36, 58, and 63 if there are exactly 4 occurrences of that org_id amongst the desired item_ids actually, there is mistake in my query, it should say having count(org_id) = 4 and, yes, that's what I want. I can get the correct list using the query I posted but I was hoping there is BETTER way. but that wasn't in your statement of the problem. So please clarify EXACTLY what you want. Giving an incomplete or contradictory description of you want only wastes both your time and mine. As I stated earlier, English is not my first language and I was trying to do my best. Sorry for confusing you. -- Rhino On 2011-03-15 20:35, LAMP wrote: On Mar 15, 2011, at 6:18 PM, Rhino wrote: All you should need is this: select distinct org_id from orders where item_id in (34, 36, 58, 63) I'm assuming that the DISTINCT operator is available in the version of MySQL that you are using. I don't currently have any version of MySQL installed so I can't try this myself to be sure it works in your version of MySQL. -- Rhino your query will give me every org_id that has ANY of item_id., I need org_id that has ALL of item_id. right? result would be 2607 1520 8934 7295 1649 8871 On 2011-03-15 18:51, LAMP wrote: Hi, I need a help to build a query. Let's say there is a table orders (simplified, of course) CREATE TABLE orders ( `item_id` int, `org_id` int, ) ENGINE=MyISAM item_idorg_id 342607 342607 341520 362607 361520 368934 3828 3815 385 3813 582607 582607 587295 581649 587295 581520 632607 632607 638871 637295 631520 6515 6520 951520 951520 957295 981520 987295 Need to select all (distinct) org_id they have item_id 34, 36, 58 and 63. All of them, not only some of them. Result is org_id=2607 and org_id=1520 I can have it by select org_id from orders where item_id in (34, 36, 58, 63) group by org_id having count(org_id)=4 but, I'm sure there is better solution? Thanks for any help. LAMP -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Need help with query
First I was thinking there is function IN ALL or something like that, since there are functions IN and EXISTS. And I would be able to make a query something like this select distinct org_id, item_id from orders where item_id in all (34, 36, 58, 63) order by org_id asc But, there isn't any. :-( The correct query is select r.org_id from ( select distinct a.org_id, a.item_id from orders a where a.item_id in (34, 36, 58, 63) order by a.org_id asc ) r group by r.org_id having count(*) = 4 On Mar 17, 2011, at 12:24 PM, Peter Brawley wrote: What I need is a list of orgs they bought all of items 34, 36, 58, 63. every of them. Some solutions under What else did buyers of X buy at http://www.artfulsoftware.com/infotree/queries.php . PB --- On 3/17/2011 12:00 PM, LAMP wrote: Yes, that was my question. Though, since English is not my first language, let me try to post it again: There is a list of all orgs and items org bough, from table called orders item_idorg_id 342607 342607 341520 362607 361520 368934 3828 3815 385 3813 582607 582607 587295 581649 587295 581520 632607 632607 638871 637295 631520 6515 6520 951520 951520 957295 981520 987295 select org_id from orders where item_id in (34. 36. 58. 63) will give me a result 5 13 15 28 1520 1649 2607 7295 8871 8934 This is the list of ALL orgs they bought ANY of items (34. 36. 58. 63). Agree? What I need is a list of orgs they bought all of items 34, 36, 58, 63. every of them. Result should be only orgs 2607 and 1520. I hope it's more clear now. On Mar 15, 2011, at 10:47 PM, Rhino wrote: Your original question said: Need to select all (distinct) org_id they have item_id 34, 36, 58 and 63. All of them, not only some of them. That's the question I answered with my suggested query. It sounds like that is not what you meant after all but I'm not sure what you DO want with your query. Why are 2607 and 1520 the only right answers? Because they are. I look at the database and manually found the result I have to get. What's wrong with my statement? Based on your own query, it looks like you only want an org_id for item_ids 34, 36, 58, and 63 if there are exactly 4 occurrences of that org_id amongst the desired item_ids actually, there is mistake in my query, it should say having count(org_id) = 4 and, yes, that's what I want. I can get the correct list using the query I posted but I was hoping there is BETTER way. but that wasn't in your statement of the problem. So please clarify EXACTLY what you want. Giving an incomplete or contradictory description of you want only wastes both your time and mine. As I stated earlier, English is not my first language and I was trying to do my best. Sorry for confusing you. -- Rhino On 2011-03-15 20:35, LAMP wrote: On Mar 15, 2011, at 6:18 PM, Rhino wrote: All you should need is this: select distinct org_id from orders where item_id in (34, 36, 58, 63) I'm assuming that the DISTINCT operator is available in the version of MySQL that you are using. I don't currently have any version of MySQL installed so I can't try this myself to be sure it works in your version of MySQL. -- Rhino your query will give me every org_id that has ANY of item_id., I need org_id that has ALL of item_id. right? result would be 2607 1520 8934 7295 1649 8871 On 2011-03-15 18:51, LAMP wrote: Hi, I need a help to build a query. Let's say there is a table orders (simplified, of course) CREATE TABLE orders ( `item_id` int, `org_id` int, ) ENGINE=MyISAM item_idorg_id 342607 342607 341520 362607 361520 368934 3828 3815 385 3813 582607 582607 587295 581649 587295 581520 632607 632607 638871 637295 631520 6515 6520 951520 951520 957295 981520 987295 Need to select all (distinct) org_id they have item_id 34, 36, 58 and 63. All of them, not only some of them. Result is org_id=2607 and org_id=1520 I can have it by select org_id from orders where item_id in (34, 36, 58, 63) group by org_id having count(org_id)=4 but, I'm sure there is better solution? Thanks for any help. LAMP -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Need help with query
On Mar 15, 2011, at 6:18 PM, Rhino wrote: All you should need is this: select distinct org_id from orders where item_id in (34, 36, 58, 63) I'm assuming that the DISTINCT operator is available in the version of MySQL that you are using. I don't currently have any version of MySQL installed so I can't try this myself to be sure it works in your version of MySQL. -- Rhino your query will give me every org_id that has ANY of item_id., I need org_id that has ALL of item_id. right? result would be 2607 1520 8934 7295 1649 8871 On 2011-03-15 18:51, LAMP wrote: Hi, I need a help to build a query. Let's say there is a table orders (simplified, of course) CREATE TABLE orders ( `item_id` int, `org_id` int, ) ENGINE=MyISAM item_idorg_id 342607 342607 341520 362607 361520 368934 3828 3815 385 3813 582607 582607 587295 581649 587295 581520 632607 632607 638871 637295 631520 6515 6520 951520 951520 957295 981520 987295 Need to select all (distinct) org_id they have item_id 34, 36, 58 and 63. All of them, not only some of them. Result is org_id=2607 and org_id=1520 I can have it by select org_id from orders where item_id in (34, 36, 58, 63) group by org_id having count(org_id)=4 but, I'm sure there is better solution? Thanks for any help. LAMP -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Need help with query optimization
It may only be returning 51 rows but its having to read significantly more. Get rid of the derived table join if possible. Something like SELECT TAP.ID http://tap.id/, M.UID, TAP.FirstName, TAP.MI, TAP.LastName, TAP.State, TAP.SchoolName, TAP.StateApproved, TAP.DiplomatApproved, C.ChapterType, S.Region, S.District,Sum(Pts.Points) Total_Points FROM TorchAwardParticipants TAP JOIN Members M On M.ID http://m.id/=TAP.CurrentMemberID JOIN Chapters C On C.ID http://c.id/=M.ChapterID JOIN Schools S On S.ID http://s.id/=C.SchoolID JOIN TorchAwardSelAct Pts ON Pts.AchievementID=TAP.ID http://tap.id/ WHERE TAP.State='OH' AND TAP.Sub='1' AND TAP.SubmittedToDiplomat IS NOT NULL AND Pts.LocalApproveStatus='A' GROUP BY TAP.ID http://tap.id/, M.UID, TAP.FirstName, TAP.MI, TAP.LastName, TAP.State, TAP.SchoolName, TAP.StateApproved, TAP.DiplomatApproved, C.ChapterType, S.Region, S.District ORDER BY TAP.LastName, TAP.FirstName, Sum(Pts.Points) Regards John On Tue, Mar 16, 2010 at 6:17 PM, Jesse j...@msdlg.com wrote: I have the following query: SELECT TAP.ID http://tap.id/, M.UID, TAP.FirstName, TAP.MI, TAP.LastName, TAP.State, TAP.SchoolName, TAP.StateApproved, TAP.DiplomatApproved, C.ChapterType, S.Region, S.District,Pts.TotPoints FROM TorchAwardParticipants TAP JOIN Members M On M.ID http://m.id/=TAP.CurrentMemberID JOIN Chapters C On C.ID http://c.id/=M.ChapterID JOIN Schools S On S.ID http://s.id/=C.SchoolID JOIN (SELECT AchievementID,Sum(Points) As TotPoints FROM TorchAwardSelAct TASA WHERE LocalApproveStatus='A' GROUP BY AchievementID) Pts ON Pts.AchievementID=TAP.IDhttp://tap.id/ WHERE TAP.State='OH' AND TAP.Sub='1' AND TAP.SubmittedToDiplomat IS NOT NULL ORDER BY TAP.LastName, TAP.FirstName, Pts.TotPoints The TorchAwardParticipants table has about 84,000 records in it. The query takes almost 40 seconds to return the data, which is only 51 rows. An EXPLAIN returns the following: ++-+++-+---+-+-++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+++-+---+-+-++-+ | 1 | PRIMARY | derived2 | ALL| NULL| NULL | NULL| NULL| 4382 | Using temporary; Using filesort | | 1 | PRIMARY | TAP| eq_ref | PRIMARY,CurrentMemberID | PRIMARY | 4 | Pts.AchievementID | 1 | Using where | | 1 | PRIMARY | M | eq_ref | PRIMARY,IX_Members_3| PRIMARY | 4 | bpa.TAP.CurrentMemberID | 1 | | | 1 | PRIMARY | C | eq_ref | PRIMARY,IX_Chapters_1 | PRIMARY | 4 | bpa.M.ChapterID | 1 | | | 1 | PRIMARY | S | eq_ref | PRIMARY | PRIMARY | 4 | bpa.C.SchoolID | 1 | | | 2 | DERIVED | TASA | index | NULL| AchievementID | 5 | NULL| 161685 | Using where | ++-+++-+---+-+-++-+ What is the best way to optimize this query so that it doesn't take 40 seconds to return the dataset? Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=john.dais...@butterflysystems.co.uk
Re: Need help with query optimization
Can you please show us the indexes on both the tables. regards anandkl On Tue, Mar 16, 2010 at 11:47 PM, Jesse j...@msdlg.com wrote: I have the following query: SELECT TAP.ID http://tap.id/, M.UID, TAP.FirstName, TAP.MI, TAP.LastName, TAP.State, TAP.SchoolName, TAP.StateApproved, TAP.DiplomatApproved, C.ChapterType, S.Region, S.District,Pts.TotPoints FROM TorchAwardParticipants TAP JOIN Members M On M.ID http://m.id/=TAP.CurrentMemberID JOIN Chapters C On C.ID http://c.id/=M.ChapterID JOIN Schools S On S.ID http://s.id/=C.SchoolID JOIN (SELECT AchievementID,Sum(Points) As TotPoints FROM TorchAwardSelAct TASA WHERE LocalApproveStatus='A' GROUP BY AchievementID) Pts ON Pts.AchievementID=TAP.IDhttp://tap.id/ WHERE TAP.State='OH' AND TAP.Sub='1' AND TAP.SubmittedToDiplomat IS NOT NULL ORDER BY TAP.LastName, TAP.FirstName, Pts.TotPoints The TorchAwardParticipants table has about 84,000 records in it. The query takes almost 40 seconds to return the data, which is only 51 rows. An EXPLAIN returns the following: ++-+++-+---+-+-++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+++-+---+-+-++-+ | 1 | PRIMARY | derived2 | ALL| NULL| NULL | NULL| NULL| 4382 | Using temporary; Using filesort | | 1 | PRIMARY | TAP| eq_ref | PRIMARY,CurrentMemberID | PRIMARY | 4 | Pts.AchievementID | 1 | Using where | | 1 | PRIMARY | M | eq_ref | PRIMARY,IX_Members_3| PRIMARY | 4 | bpa.TAP.CurrentMemberID | 1 | | | 1 | PRIMARY | C | eq_ref | PRIMARY,IX_Chapters_1 | PRIMARY | 4 | bpa.M.ChapterID | 1 | | | 1 | PRIMARY | S | eq_ref | PRIMARY | PRIMARY | 4 | bpa.C.SchoolID | 1 | | | 2 | DERIVED | TASA | index | NULL| AchievementID | 5 | NULL| 161685 | Using where | ++-+++-+---+-+-++-+ What is the best way to optimize this query so that it doesn't take 40 seconds to return the dataset? Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com
Re: need help with query...
On Dec 17, 2008, at 2:56 PM, Lamp Lists wrote:
I hate when somebody put in Subject line something like I just did
but after 15 minutes to try to be specific just with one short
sentence - I gave up. So, you can hate me - I understand (though,
help with my problem too) :-)
I have let say 3 tables people, organization, addresses. and they
are linked to each other with column person_id.
to select person by specified person_id it's not big deal, really
simple. though, I wonder how can I select records from these three
tables but somehow marked which table it belongs?
e.g.
select p.first_name, p.last_name, p.status, p.date_registered,
o.org_id, o.org_name, o.org_department, a.addres1, a.address2,
a.city, a.state, a.zip
from people p, organization o, addresses a
where p.person_id=123 and o.person_id=p.person_id and
a.person_id=p.person_id
I need somehow, together with result data, info which table data
belogs?
e.g.
not exactly this way but something like this:
$data = array(
'people' = array('lamp', 'lists', 1, '2008-12-12'),
'organization' = array(56, 'Lamp List', 'web'),
'addresses' = array('123 Main St.', 'Toon Town', 'NY', '12345')
}
thanks for any help.
-afan
It may not be the best option, but when I've done that type of stuff
in the past, I add another field to the database and call it like
List and put People in the people database. and then you could
just query the field List and display it how ever you needed.
--
Jason Pruim
japr...@raoset.com
616.399.2355
Re: need help with query...
Hi Afan
Why not prefix your field names with the table name?
select
p.first_name AS person_first_name,
p.last_name AS person_last_name,
p.status AS person_status,
p.date_registered AS person_date_registered,
o.org_id AS organization_org_id,
o.org_name AS organization_org_name,
o.org_department AS organization_org_department,
a.addres1 AS addresses_address1,
a.address2 AS addresses_address2,
a.city AS addresses_city,
a.state AS addresses_state,
a.zip AS addresses_zip
from people p, organization o, addresses a
where p.person_id=123 and o.person_id=p.person_id and
a.person_id=p.person_id
Then in PHP (which I guess you're using from your example) do something
like:
// Get every record from the database ($result is your MySQL result from
mysql_query)
while ($row = mysql_fetch_assoc($result))
{
$result = Array();
// Run through each field in the row
foreach ($row as $field = $value)
{
// Split the field into 2 segments split by _
$fieldSplit = explode('_', $field, 1);
// $fieldSplit will be, for example, Array(0 = 'person', 1 =
'first_name')
$result[$fieldSplit[0]][$fieldSplit[1]] = $value;
// Now you should be able to access the person's first name using
$result['person']['first_name']
}
}
This code may not be perfect as I've just typed it out from memory so it
may take a bit of tweaking.
Thanks,
Andy
Jason Pruim wrote:
On Dec 17, 2008, at 2:56 PM, Lamp Lists wrote:
I hate when somebody put in Subject line something like I just did
but after 15 minutes to try to be specific just with one short
sentence - I gave up. So, you can hate me - I understand (though,
help with my problem too) :-)
I have let say 3 tables people, organization, addresses. and they are
linked to each other with column person_id.
to select person by specified person_id it's not big deal, really
simple. though, I wonder how can I select records from these three
tables but somehow marked which table it belongs?
e.g.
select p.first_name, p.last_name, p.status, p.date_registered,
o.org_id, o.org_name, o.org_department, a.addres1, a.address2,
a.city, a.state, a.zip
from people p, organization o, addresses a
where p.person_id=123 and o.person_id=p.person_id and
a.person_id=p.person_id
I need somehow, together with result data, info which table data belogs?
e.g.
not exactly this way but something like this:
$data = array(
'people' = array('lamp', 'lists', 1, '2008-12-12'),
'organization' = array(56, 'Lamp List', 'web'),
'addresses' = array('123 Main St.', 'Toon Town', 'NY', '12345')
}
thanks for any help.
-afan
It may not be the best option, but when I've done that type of stuff
in the past, I add another field to the database and call it like
List and put People in the people database. and then you could
just query the field List and display it how ever you needed.
--
Jason Pruim
japr...@raoset.com
616.399.2355
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: need help with query...
-Original Message- From: Lamp Lists [mailto:lamp.li...@yahoo.com] Sent: Wednesday, December 17, 2008 2:57 PM To: mysql@lists.mysql.com Subject: need help with query... ...snip... I have let say 3 tables people, organization, addresses. and they are linked to each other with column person_id. to select person by specified person_id it's not big deal, really simple. though, I wonder how can I select records from these three tables but somehow marked which table it belongs? [JS] Admittedly I'm not really good with this stuff, but I think this works: SELECT 'table1' AS tablename, person_id FROM table1 WHERE person_id = 123 UNION SELECT 'table2', person_id FROM table2 WHERE person_id = 123 UNION SELECT 'table3', person_id FROM table2 WHERE person_id = 123; That would give you all of the tables in which a particular person_id is found. I don't know if this suits your needs. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.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: need help with query...
-Original Message- From: Andy Shellam [mailto:andy-li...@networkmail.eu] Sent: Wednesday, December 17, 2008 3:29 PM To: Lamp Lists Cc: mysql@lists.mysql.com Subject: Re: need help with query... Hi Afan Why not prefix your field names with the table name? select p.first_name AS person_first_name, p.last_name AS person_last_name, p.status AS person_status, p.date_registered AS person_date_registered, o.org_id AS organization_org_id, o.org_name AS organization_org_name, o.org_department AS organization_org_department, a.addres1 AS addresses_address1, a.address2 AS addresses_address2, a.city AS addresses_city, a.state AS addresses_state, a.zip AS addresses_zip from people p, organization o, addresses a where p.person_id=123 and o.person_id=p.person_id and a.person_id=p.person_id [JS] That would only retrieve a person_id if it is all three tables. I'm not sure that's what is wanted. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: need help with query...
From: Andy Shellam andy-li...@networkmail.eu
To: Lamp Lists lamp.li...@yahoo.com
Cc: mysql@lists.mysql.com
Sent: Wednesday, December 17, 2008 2:29:08 PM
Subject: Re: need help with query...
Hi Afan
Why not prefix your field names with the table name?
select
p.first_name AS person_first_name,
p.last_name AS person_last_name,
p.status AS person_status,
p.date_registered AS person_date_registered,
o.org_id AS organization_org_id,
o.org_name AS organization_org_name,
o.org_department AS organization_org_department,
a.addres1 AS addresses_address1,
a.address2 AS addresses_address2,
a.city AS addresses_city,
a.state AS addresses_state,
a.zip AS addresses_zip
from people p, organization o, addresses a
where p.person_id=123 and o.person_id=p.person_id and a.person_id=p.person_id
Then in PHP (which I guess you're using from your example) do something like:
// Get every record from the database ($result is your MySQL result from
mysql_query)
while ($row = mysql_fetch_assoc($result))
{
$result = Array();
// Run through each field in the row
foreach ($row as $field = $value)
{
// Split the field into 2 segments split by _
$fieldSplit = explode('_', $field, 1);
// $fieldSplit will be, for example, Array(0 = 'person', 1 =
'first_name')
$result[$fieldSplit[0]][$fieldSplit[1]] = $value;
// Now you should be able to access the person's first name using
$result['person']['first_name']
}
}
This code may not be perfect as I've just typed it out from memory so it may
take a bit of tweaking.
Thanks,
Andy
Hi Andy,
the reason I can't use this because fields (columns) in select statement
(p.first_name, p.last_name,...) are actually dynamically created. In my project
different client will select different fields to be shown. 99% will select
first_name, and last_name, but some don't care about date_registered, some will
need more org data...
actually, it will be more this way:
SELECT {$selected_fields} FROM people p, organization o. addresses a
WHERE ...
where
$selected_fields = p.first_name, p.last_name, o.org_name
or
$selected_fields = p.first_name, p.last_name, o.org_name, a.address, a.city,
a.state, a.zip
or
$selected_fields = o.org_name, a.address, a.city, a.state, a.zip
I hope I'm more clear now?
Though, I can do something as you suggested while creating $selected_fields
:-)
Thanks
Jason Pruim wrote:
On Dec 17, 2008, at 2:56 PM, Lamp Lists wrote:
I hate when somebody put in Subject line something like I just did but after
15 minutes to try to be specific just with one short sentence - I gave up.
So, you can hate me - I understand (though, help with my problem too) :-)
I have let say 3 tables people, organization, addresses. and they are linked
to each other with column person_id.
to select person by specified person_id it's not big deal, really simple.
though, I wonder how can I select records from these three tables but
somehow marked which table it belongs?
e.g.
select p.first_name, p.last_name, p.status, p.date_registered, o.org_id,
o.org_name, o.org_department, a.addres1, a.address2, a.city, a.state, a.zip
from people p, organization o, addresses a
where p.person_id=123 and o.person_id=p.person_id and a.person_id=p.person_id
I need somehow, together with result data, info which table data belogs?
e.g.
not exactly this way but something like this:
$data = array(
'people' = array('lamp', 'lists', 1, '2008-12-12'),
'organization' = array(56, 'Lamp List', 'web'),
'addresses' = array('123 Main St.', 'Toon Town', 'NY', '12345')
}
thanks for any help.
-afan
It may not be the best option, but when I've done that type of stuff in the
past, I add another field to the database and call it like List and put
People in the people database. and then you could just query the field List
and display it how ever you needed.
-- Jason Pruim
japr...@raoset.com
616.399.2355
Re: need help with query...
Jerry Schwartz wrote: -Original Message- From: Andy Shellam [mailto:andy-li...@networkmail.eu] Sent: Wednesday, December 17, 2008 3:29 PM To: Lamp Lists Cc: mysql@lists.mysql.com Subject: Re: need help with query... Hi Afan Why not prefix your field names with the table name? select p.first_name AS person_first_name, p.last_name AS person_last_name, p.status AS person_status, p.date_registered AS person_date_registered, o.org_id AS organization_org_id, o.org_name AS organization_org_name, o.org_department AS organization_org_department, a.addres1 AS addresses_address1, a.address2 AS addresses_address2, a.city AS addresses_city, a.state AS addresses_state, a.zip AS addresses_zip from people p, organization o, addresses a where p.person_id=123 and o.person_id=p.person_id and a.person_id=p.person_id [JS] That would only retrieve a person_id if it is all three tables. I'm not sure that's what is wanted. That was the original query as specified by the original poster - I just added the AS xxx to each field he'd selected. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: need help with query...
Hi,
Hi Andy,
the reason I can't use this because fields (columns) in select
statement (p.first_name, p.last_name,...) are actually dynamically
created. In my project different client will select different fields
to be shown. 99% will select first_name, and last_name, but some don't
care about date_registered, some will need more org data...
actually, it will be more this way:
SELECT {$selected_fields} FROM people p, organization o. addresses a
WHERE ...
where
$selected_fields = p.first_name, p.last_name, o.org_name
or
$selected_fields = p.first_name, p.last_name, o.org_name, a.address,
a.city, a.state, a.zip
or
$selected_fields = o.org_name, a.address, a.city, a.state, a.zip
So just tag AS table_field_name to each field when you're building
your list of $selected_fields - e.g.
$selected_fields = p.first_name AS person_first_name, p.last_name AS
person_last_name, o.org_name AS organization_org_name
You don't have to use the full table name either - for example in the
following statement, you would then access the data using
$result['p']['first_name'];
$selected_fields = p.first_name AS p_first_name, p.last_name AS
p_last_name, o.org_name AS o_org_name
This approach is actually easier if you're creating the query
dynamically, because you don't have to manually type a load of AS xxx
statements after every field.
I've recently done something similar in one of my applications to wrap
date/time fields in either FROM_UNIXTIME() or UNIX_TIMESTAMP() functions.
Andy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: need help with query...
From: Andy Shellam andy-li...@networkmail.eu
To: Lamp Lists lamp.li...@yahoo.com
Cc: mysql@lists.mysql.com
Sent: Wednesday, December 17, 2008 2:48:31 PM
Subject: Re: need help with query...
Hi,
Hi Andy,
the reason I can't use this because fields (columns) in select statement
(p.first_name, p.last_name,...) are actually dynamically created. In my
project different client will select different fields to be shown. 99% will
select first_name, and last_name, but some don't care about date_registered,
some will need more org data...
actually, it will be more this way:
SELECT {$selected_fields} FROM people p, organization o. addresses a
WHERE ...
where
$selected_fields = p.first_name, p.last_name, o.org_name
or
$selected_fields = p.first_name, p.last_name, o.org_name, a.address, a.city,
a.state, a.zip
or
$selected_fields = o.org_name, a.address, a.city, a.state, a.zip
So just tag AS table_field_name to each field when you're building your list
of $selected_fields - e.g.
$selected_fields = p.first_name AS person_first_name, p.last_name AS
person_last_name, o.org_name AS organization_org_name
You don't have to use the full table name either - for example in the following
statement, you would then access the data using $result['p']['first_name'];
$selected_fields = p.first_name AS p_first_name, p.last_name AS p_last_name,
o.org_name AS o_org_name
This approach is actually easier if you're creating the query dynamically,
because you don't have to manually type a load of AS xxx statements after
every field.
I've recently done something similar in one of my applications to wrap
date/time fields in either FROM_UNIXTIME() or UNIX_TIMESTAMP() functions.
Andy
Yup! That'll do it!
Thanks Andy
;-)
Re: Need help to query with timestamp in C++
Hi Saul, I need to use C++ and I'm not writing a web application. Thanks anyway. Kandy I have done queries to the database in PHP with variables like month but easily can select from a range of time and data to produce the same results, the output goes directly to the web so if that is what you are seeking for, I can help with PHP. Saul Kandy Wong wrote: Hi, I need to write a C++ program in a Linux environment to query with a timestamp. The user will only provide with an approximate time so I'd like to know how can I write a program or a query to return the closest data. The followings are the timestamp in the MySQL database: | 2008-08-05 03:56:09 | 1217933769 | | 2008-08-05 03:56:19 | 1217933779 | | 2008-08-05 03:56:29 | 1217933789 | | 2008-08-05 03:59:39 | 1217933979 | | 2008-08-05 03:59:49 | 1217933989 | | 2008-08-05 03:59:59 | 1217933999 | | 2008-08-05 04:02:39 | 1217934159 | | 2008-08-05 04:02:49 | 1217934169 | | 2008-08-05 04:02:59 | 1217934179 | For example, '2008-08-05 04:01:39' is the time provided by the user which does not exist in the database. So, how can I return the closest data? I know I can make use of 'LIKE' but this will return more than one data. What is the best method to get the closest one? And what is the good connector (C++ to MySQL) to use? Any suggestion? Thank you. Kandy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Kandy Wong Scientific Programmer Analyst TRIUMF (Canada's National Laboratory for Particle and Nuclear Physics) 4004 Wesbrook Mall Vancouver, BC, Canada, V6T 2A3 Phone: (604) 222- 1047 ext. 6193 Email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help to query with timestamp in C++
Hi Kandy,
this could be the query you are looking for. It should return record
with the closest timestamp to your required time:
(SELECT TIMEDIFF('20080815091907', timestamp_column) AS diff, t.* FROM
table1 t
WHERE timestamp_column = '20080815091907'
ORDER BY timestamp_column DESC LIMIT 1
)
UNION
(SELECT TIMEDIFF(timestamp_column, '20080815091907') AS diff, t.* FROM
table1 t
WHERE timestamp_column = '20080815091907'
ORDER BY timestamp_column LIMIT 1
)
ORDER BY diff LIMIT 1
HTH,
Dusan
Kandy Wong napsal(a):
Hi,
I need to write a C++ program in a Linux environment to query with a
timestamp.
The user will only provide with an approximate time so I'd like to know
how can I write a program or a query to return the closest data.
The followings are the timestamp in the MySQL database:
| 2008-08-05 03:56:09 | 1217933769 |
| 2008-08-05 03:56:19 | 1217933779 |
| 2008-08-05 03:56:29 | 1217933789 |
| 2008-08-05 03:59:39 | 1217933979 |
| 2008-08-05 03:59:49 | 1217933989 |
| 2008-08-05 03:59:59 | 1217933999 |
| 2008-08-05 04:02:39 | 1217934159 |
| 2008-08-05 04:02:49 | 1217934169 |
| 2008-08-05 04:02:59 | 1217934179 |
For example, '2008-08-05 04:01:39' is the time provided by the user which
does not exist in the database. So, how can I return the closest data? I
know I can make use of 'LIKE' but this will return more than one data.
What is the best method to get the closest one?
And what is the good connector (C++ to MySQL) to use?
Any suggestion?
Thank you.
Kandy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help to query with timestamp in C++
Kandy Wong wrote: Hi Saul, I need to use C++ and I'm not writing a web application. Thanks anyway. you can do something like: select min(abs(timediff(targettime,timestamp))) from table where condition ; if you use the libmysql you can get the result as strings back (the method i prefer) and convert them in what ever you need. re, wh The followings are the timestamp in the MySQL database: | 2008-08-05 03:56:09 | 1217933769 | | 2008-08-05 03:56:19 | 1217933779 | | 2008-08-05 03:56:29 | 1217933789 | | 2008-08-05 03:59:39 | 1217933979 | | 2008-08-05 03:59:49 | 1217933989 | | 2008-08-05 03:59:59 | 1217933999 | | 2008-08-05 04:02:39 | 1217934159 | | 2008-08-05 04:02:49 | 1217934169 | | 2008-08-05 04:02:59 | 1217934179 | For example, '2008-08-05 04:01:39' is the time provided by the user which does not exist in the database. So, how can I return the closest data? I know I can make use of 'LIKE' but this will return more than one data. What is the best method to get the closest one? And what is the good connector (C++ to MySQL) to use? Any suggestion? Thank you. Kandy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help to query with timestamp in C++
Kandy Wong wrote: And what is the good connector (C++ to MySQL) to use? MySQL++ (http://tangentsoft.net/mysql++/) has native Date, Time, and DateTime data types. You can convert to these types implicitly: mysqlpp::DateTime dt = row[my_column]; Row::operator[] doesn't return DateTime, it returns a stringish type, which can convert itself to lots of different C++ data types. This is useful because the MySQL C API normally returns results in string form, so you need a natural way to convert these values to the native C++ types for processing. In this particular case, it saves you from having to do the timestamp string parsing yourself. -- Warren Young, maintainer of MySQL++ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help to query with timestamp in C++
I'm puzzled by the layout of your table, if that's what you're showing us. Is the timestamp in the table truly associated with the time at which the user put in his approximate time? If, for example, the user types in 04:05:07 at 04:03:02, and then types in 04:02:59 at 04:03:03, what is it that you want to return? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Kandy Wong [mailto:[EMAIL PROTECTED] Sent: Friday, August 15, 2008 2:36 AM To: Saul Bejarano Cc: mysql@lists.mysql.com Subject: Re: Need help to query with timestamp in C++ Hi Saul, I need to use C++ and I'm not writing a web application. Thanks anyway. Kandy I have done queries to the database in PHP with variables like month but easily can select from a range of time and data to produce the same results, the output goes directly to the web so if that is what you are seeking for, I can help with PHP. Saul Kandy Wong wrote: Hi, I need to write a C++ program in a Linux environment to query with a timestamp. The user will only provide with an approximate time so I'd like to know how can I write a program or a query to return the closest data. The followings are the timestamp in the MySQL database: | 2008-08-05 03:56:09 | 1217933769 | | 2008-08-05 03:56:19 | 1217933779 | | 2008-08-05 03:56:29 | 1217933789 | | 2008-08-05 03:59:39 | 1217933979 | | 2008-08-05 03:59:49 | 1217933989 | | 2008-08-05 03:59:59 | 1217933999 | | 2008-08-05 04:02:39 | 1217934159 | | 2008-08-05 04:02:49 | 1217934169 | | 2008-08-05 04:02:59 | 1217934179 | For example, '2008-08-05 04:01:39' is the time provided by the user which does not exist in the database. So, how can I return the closest data? I know I can make use of 'LIKE' but this will return more than one data. What is the best method to get the closest one? And what is the good connector (C++ to MySQL) to use? Any suggestion? Thank you. Kandy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Kandy Wong Scientific Programmer Analyst TRIUMF (Canada's National Laboratory for Particle and Nuclear Physics) 4004 Wesbrook Mall Vancouver, BC, Canada, V6T 2A3 Phone: (604) 222- 1047 ext. 6193 Email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help to query with timestamp in C++
Hi Dusan,
Thank you so much. It works!
Kandy
Hi Kandy,
this could be the query you are looking for. It should return record
with the closest timestamp to your required time:
(SELECT TIMEDIFF('20080815091907', timestamp_column) AS diff, t.* FROM
table1 t
WHERE timestamp_column = '20080815091907'
ORDER BY timestamp_column DESC LIMIT 1
)
UNION
(SELECT TIMEDIFF(timestamp_column, '20080815091907') AS diff, t.* FROM
table1 t
WHERE timestamp_column = '20080815091907'
ORDER BY timestamp_column LIMIT 1
)
ORDER BY diff LIMIT 1
HTH,
Dusan
Kandy Wong napsal(a):
Hi,
I need to write a C++ program in a Linux environment to query with a
timestamp.
The user will only provide with an approximate time so I'd like to know
how can I write a program or a query to return the closest data.
The followings are the timestamp in the MySQL database:
| 2008-08-05 03:56:09 | 1217933769 |
| 2008-08-05 03:56:19 | 1217933779 |
| 2008-08-05 03:56:29 | 1217933789 |
| 2008-08-05 03:59:39 | 1217933979 |
| 2008-08-05 03:59:49 | 1217933989 |
| 2008-08-05 03:59:59 | 1217933999 |
| 2008-08-05 04:02:39 | 1217934159 |
| 2008-08-05 04:02:49 | 1217934169 |
| 2008-08-05 04:02:59 | 1217934179 |
For example, '2008-08-05 04:01:39' is the time provided by the user
which
does not exist in the database. So, how can I return the closest data?
I
know I can make use of 'LIKE' but this will return more than one data.
What is the best method to get the closest one?
And what is the good connector (C++ to MySQL) to use?
Any suggestion?
Thank you.
Kandy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help to query with timestamp in C++
I have done queries to the database in PHP with variables like month but easily can select from a range of time and data to produce the same results, the output goes directly to the web so if that is what you are seeking for, I can help with PHP. Saul Kandy Wong wrote: Hi, I need to write a C++ program in a Linux environment to query with a timestamp. The user will only provide with an approximate time so I'd like to know how can I write a program or a query to return the closest data. The followings are the timestamp in the MySQL database: | 2008-08-05 03:56:09 | 1217933769 | | 2008-08-05 03:56:19 | 1217933779 | | 2008-08-05 03:56:29 | 1217933789 | | 2008-08-05 03:59:39 | 1217933979 | | 2008-08-05 03:59:49 | 1217933989 | | 2008-08-05 03:59:59 | 1217933999 | | 2008-08-05 04:02:39 | 1217934159 | | 2008-08-05 04:02:49 | 1217934169 | | 2008-08-05 04:02:59 | 1217934179 | For example, '2008-08-05 04:01:39' is the time provided by the user which does not exist in the database. So, how can I return the closest data? I know I can make use of 'LIKE' but this will return more than one data. What is the best method to get the closest one? And what is the good connector (C++ to MySQL) to use? Any suggestion? Thank you. Kandy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help creating query statement
Guys, just wanted to thank you again for helping me with the sql statement that I needed. I was able to sorted using php and I was able to display the correct result. Thanks again!!1 Nestor :-) On Nov 6, 2007 7:37 AM, Néstor [EMAIL PROTECTED] wrote: You guys are correct, that is exactly what happened. I must thing of this in the future. At this moment I have a lot of other projects to take care, that it is eaiser for me to read the information into an associative array with the columns and the values and sort the array and then print the top 5 values within each array. Thanks, Nestor :-) On 11/6/07, Enrique Sanchez Vela [EMAIL PROTECTED] wrote: --- [EMAIL PROTECTED] wrote: Néstor wrote: I think you'd best begin by normalising your database. Something along these lines: very true indeed, that would save you major headaches when right after finishing the demo, someone would say, can we add a 18 gallon pledge? what about a 25? as of the sort of columns per row, I believe it is not possible nor in the goals of MySQL to make it possible/easy. best regards, enrique. -- What you have been obliged to discover by yourself leaves a path in your mind which you can use again when the need arises.--G. C. Lichtenberg http://themathcircle.org/ __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help creating query statement
--- [EMAIL PROTECTED] wrote: Néstor wrote: I think you'd best begin by normalising your database. Something along these lines: very true indeed, that would save you major headaches when right after finishing the demo, someone would say, can we add a 18 gallon pledge? what about a 25? as of the sort of columns per row, I believe it is not possible nor in the goals of MySQL to make it possible/easy. best regards, enrique. -- What you have been obliged to discover by yourself leaves a path in your mind which you can use again when the need arises.--G. C. Lichtenberg http://themathcircle.org/ __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help creating query statement
You guys are correct, that is exactly what happened. I must thing of this in the future. At this moment I have a lot of other projects to take care, that it is eaiser for me to read the information into an associative array with the columns and the values and sort the array and then print the top 5 values within each array. Thanks, Nestor :-) On 11/6/07, Enrique Sanchez Vela [EMAIL PROTECTED] wrote: --- [EMAIL PROTECTED] wrote: Néstor wrote: I think you'd best begin by normalising your database. Something along these lines: very true indeed, that would save you major headaches when right after finishing the demo, someone would say, can we add a 18 gallon pledge? what about a 25? as of the sort of columns per row, I believe it is not possible nor in the goals of MySQL to make it possible/easy. best regards, enrique. -- What you have been obliged to discover by yourself leaves a path in your mind which you can use again when the need arises.--G. C. Lichtenberg http://themathcircle.org/ __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fwd: Re: Need help creating query statement
--- Enrique Sanchez Vela [EMAIL PROTECTED] wrote: Date: Mon, 5 Nov 2007 15:01:59 -0800 (PST) From: Enrique Sanchez Vela [EMAIL PROTECTED] Subject: Re: Need help creating query statement To: Néstor [EMAIL PROTECTED] --- Néstor [EMAIL PROTECTED] wrote: I can do simple select statements but I need your brains to create query statment. I am using mysql 4.0 in my 1and1 site. I have a table that has 8 fields, one is the agency field and the other 7 are *tip* values on saving water and the value of this field is either 0 or an amount. -- |agency| 5g | 8g | 10g | 12g | 15g | 20g | 40g | -- so the record could be |Helix | 0 | 8 | 10 | 12 | 15 | 0 | 40 | |Corp| 5 | 0 | 0 | 12 | 15 | 0 | 0 | |Helix | 0 | 8 | 10 | 0 | 15 | 0 | 40 | I need to get the to count *tips* per *agency *so in this case of 3 records I would have: |Helix | 0 | 2 | 2 |1 | 2 | 0 | 2 | |Corp |1 | 0 | 0 |1 |1 |0 | 0 | let's see if I got the point CREATE TABLE `agency` ( `name` varchar(11) collate latin1_bin NOT NULL default '', `5g` int(11) NOT NULL default '0', `8g` int(11) NOT NULL default '0', `10g` int(11) NOT NULL default '0', `12g` int(11) NOT NULL default '0', `15g` int(11) NOT NULL default '0', `20g` int(11) NOT NULL default '0', `40g` int(11) NOT NULL default '0' ) ENGINE=MyISAM ; select * from agency; +---+++-+-+-+-+-+ | name | 5g | 8g | 10g | 12g | 15g | 20g | 40g | +---+++-+-+-+-+-+ | Helix | 0 | 0 | 0 | 12 | 0 | 0 | 0 | | Helix | 0 | 8 | 10 | 0 | 15 | 0 | 40 | | Corp | 5 | 0 | 0 | 12 | 15 | 0 | 40 | | Helix | 0 | 8 | 10 | 0 | 15 | 0 | 40 | +---+++-+-+-+-+-+ 4 rows in set (0.00 sec) select `name`, sum(if(5g0, 1,0)) as 5g , sum(if(8g0, 1,0)) as 8g, sum(if(10g0,1,0)) as 10g, sum(if(12g0,1,0))as 12g ,sum(if(15g0,1,0)) as 15g , sum(if (20g0,1,0)) as 20g, sum(if(40g0,1,0)) as 40g from agency group by `name`; produces... +---+--+--+--+--+--+--+--+ | name | 5g | 8g | 10g | 12g | 15g | 20g | 40g | +---+--+--+--+--+--+--+--+ | Corp |1 |0 |0 |1 |1 |0 | 1 | | Helix |0 |2 |2 |1 |2 |0 | 2 | +---+--+--+--+--+--+--+--+ 2 rows in set (0.00 sec) regards, esv. -- What you have been obliged to discover by yourself leaves a path in your mind which you can use again when the need arises.--G. C. Lichtenberg http://themathcircle.org/ __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- What you have been obliged to discover by yourself leaves a path in your mind which you can use again when the need arises.--G. C. Lichtenberg http://themathcircle.org/ __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help creating query statement
Enrique that is pretty good and close to what I need. On top of what you have generously provide, I guess I can just put each gallon field into an array an then sort the array to display the first 5 gallon fields per agency. Is there an easier way? Thanks, Nestor :-) On 11/5/07, Enrique Sanchez Vela [EMAIL PROTECTED] wrote: --- Néstor [EMAIL PROTECTED] wrote: I can do simple select statements but I need your brains to create query statment. I am using mysql 4.0 in my 1and1 site. I have a table that has 8 fields, one is the agency field and the other 7 are *tip* values on saving water and the value of this field is either 0 or an amount. -- |agency| 5g | 8g | 10g | 12g | 15g | 20g | 40g | -- so the record could be |Helix | 0 | 8 | 10 | 12 | 15 | 0 | 40 | |Corp| 5 | 0 | 0 | 12 | 15 | 0 | 0 | |Helix | 0 | 8 | 10 | 0 | 15 | 0 | 40 | I need to get the to count *tips* per *agency *so in this case of 3 records I would have: |Helix | 0 | 2 | 2 |1 | 2 | 0 | 2 | |Corp |1 | 0 | 0 |1 |1 |0 | 0 | let's see if I got the point CREATE TABLE `agency` ( `name` varchar(11) collate latin1_bin NOT NULL default '', `5g` int(11) NOT NULL default '0', `8g` int(11) NOT NULL default '0', `10g` int(11) NOT NULL default '0', `12g` int(11) NOT NULL default '0', `15g` int(11) NOT NULL default '0', `20g` int(11) NOT NULL default '0', `40g` int(11) NOT NULL default '0' ) ENGINE=MyISAM ; select * from agency; +---+++-+-+-+-+-+ | name | 5g | 8g | 10g | 12g | 15g | 20g | 40g | +---+++-+-+-+-+-+ | Helix | 0 | 0 | 0 | 12 | 0 | 0 | 0 | | Helix | 0 | 8 | 10 | 0 | 15 | 0 | 40 | | Corp | 5 | 0 | 0 | 12 | 15 | 0 | 40 | | Helix | 0 | 8 | 10 | 0 | 15 | 0 | 40 | +---+++-+-+-+-+-+ 4 rows in set (0.00 sec) select `name`, sum(if(5g0, 1,0)) as 5g , sum(if(8g0, 1,0)) as 8g, sum(if(10g0,1,0)) as 10g, sum(if(12g0,1,0))as 12g ,sum(if(15g0,1,0)) as 15g , sum(if (20g0,1,0)) as 20g, sum(if(40g0,1,0)) as 40g from agency group by `name`; produces... +---+--+--+--+--+--+--+--+ | name | 5g | 8g | 10g | 12g | 15g | 20g | 40g | +---+--+--+--+--+--+--+--+ | Corp |1 |0 |0 |1 |1 |0 | 1 | | Helix |0 |2 |2 |1 |2 |0 | 2 | +---+--+--+--+--+--+--+--+ 2 rows in set (0.00 sec) regards, esv. -- What you have been obliged to discover by yourself leaves a path in your mind which you can use again when the need arises.--G. C. Lichtenberg http://themathcircle.org/ __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: Need help creating query statement
Néstor wrote:
I can do simple select statements but I need your brains to create query
statment.
I am using mysql 4.0 in my 1and1 site.
I have a table that has 8 fields, one is the agency field and the other 7
are
*tip* values on saving water and the value of this field is either 0 or an
amount.
--
|agency| 5g | 8g | 10g | 12g | 15g | 20g | 40g |
--
so the record could be
|Helix | 0 | 8 | 10 | 12 | 15 | 0 | 40 |
|Corp| 5 | 0 | 0 | 12 | 15 | 0 |0 |
|Helix | 0 | 8 | 10 | 0 | 15 | 0 | 40 |
I need to get the to count *tips* per *agency
*so in this case of 3 records I would have:
|Helix | 0 | 2 | 2 |1 | 2 | 0 | 2 |
|Corp |1 | 0 | 0 |1 |1 |0 |0 |
and then I need to come out with top 5 *tips *per agency
|Helix | 8g = 2 | 10g = 2 | 15g = 2 | 8g = 2 | 40g = 2|
|Corp| 8g = 1 | 12g = 1 | 15g= 1 |
Is there an easy way to do this?
I think you'd best begin by normalising your database. Something along
these lines:
CREATE TABLE agency (
id tinyint(3) unsigned NOT NULL auto_increment,
name varchar(64)NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM;
CREATE TABLE pledge (
id tinyint(3) unsigned NOT NULL auto_increment,
agency_id tinyint(3) unsigned NOT NULL,
gallons enum('5','8','10','12','15','20','40') DEFAULT NULL,
PRIMARY KEY (id),
FOREIGN KEY (agency_id) REFERENCES agency (id)
) ENGINE=MyISAM;
You could create a separate 'gallons' table, with the '5','8','10', etc.
as rows but, since this is MySQL you might as well go with the ENUM.
Once you have your data normalised you'll be able to things like joining
across both tables and grouping by agency, etc.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with query
Hi Jesse, Jesse wrote: The following query works just fine: SELECT B.BusNum, COALESCE(CampCount.Cnt,0) As Kids, B.CamperCapacity, COALESCE(CounselorCount.Cnt,0) As Adults, B.AdultCapacity FROM Buses B LEFT JOIN (SELECT BusID,Count(*) As Cnt FROM Campers GROUP BY BusID) AS CampCount ON CampCount.BusID=B.ID LEFT JOIN (SELECT BusID,Count(*) As Cnt FROM Counselors GROUP BY BusID) AS CounselorCount On CounselorCount.BusID=B.ID ORDER BY B.BusNum However, I would like to add the to have a total of the adults and kids on the bus, so I change my query as follows: SELECT B.BusNum, COALESCE(CampCount.Cnt,0) As Kids, B.CamperCapacity, COALESCE(CounselorCount.Cnt,0) As Adults, B.AdultCapacity, Kids + Adults As GT FROM Buses B LEFT JOIN (SELECT BusID,Count(*) As Cnt FROM Campers GROUP BY BusID) AS CampCount ON CampCount.BusID=B.ID LEFT JOIN (SELECT BusID,Count(*) As Cnt FROM Counselors GROUP BY BusID) AS CounselorCount On CounselorCount.BusID=B.ID ORDER BY B.BusNum When I try to execute this, I get the error, Unknown column 'Kids' in 'field list' How do I properly add these together to get a total column? Column aliases can't be referred to later in the select list as far as I know. The only way I know to do this is simply duplicate the expressions: COALESCE(CampCount.Cnt,0) + COALESCE(CounselorCount.Cnt,0) as GT Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with query
COALESCE(CampCount.Cnt,0) + COALESCE(CounselorCount.Cnt,0) as GT Duuuh. Why didn't I think of that. What is MySQL's issue with referring to the variables (As assignments, whatever you want to call them)? I've had issues like this in situations like this one, when trying to use them in the ORDER BY clause, and other places. Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with query
Jesse wrote: COALESCE(CampCount.Cnt,0) + COALESCE(CounselorCount.Cnt,0) as GT Duuuh. Why didn't I think of that. What is MySQL's issue with referring to the variables (As assignments, whatever you want to call them)? I've had issues like this in situations like this one, when trying to use them in the ORDER BY clause, and other places. They simply don't 'exist' at that point in time. All of the rows are read at one point in time (according to a WHERE clause if applicable), then the expressions within the column lists are done, then grouping, order by etc., and finally HAVING clauses are done (where one can use a column alias, when not available within a WHERE clause). However, you should be able to use them in an ORDER BY - as they do exist at that point - so I'm not sure that issue would have been related to this. Best regards Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help optimize query
Your original query (implicit INNER JOINs): SELECT teu.name, eca.owner_id, ece.value FROM typed_enterprise_unit teu, e_contact_association eca, e_contact_entry ece WHERE teu.unit_id=eca.owner_id and eca.entry_id=ece.entry_id and eca.type_id=68 and (teu.type_path like '%/66/%' or teu.type_id=66) ORDER BYeca.owner_id LIMIT 22; The same query reformatted to demonstrate explicit INNER JOINS SELECT teu.name , eca.owner_id , ece.value FROM typed_enterprise_unit teu INNER JOIN e_contact_association eca ON teu.unit_id=eca.owner_id INNER JOIN e_contact_entry ece ON eca.entry_id=ece.entry_id WHERE eca.type_id=68 AND (teu.type_path like '%/66/%' or teu.type_id=66) ORDER BYeca.owner_id LIMIT 22; I have two immediate suggestions. 1) If you had not attached your information but included it in your message as text, you would have probably already received an answer. Next time, please inline your information. Please don't use an attachment as many people WILL NOT OPEN THEM unless they know you. 2) Be very, very careful when you use a comma delimited list of tables to imply INNER JOIN that you actually include the necessary join conditions in your WHERE clause. You did do that this time. However, it is a well known hazard of that particular query format that those terms can be accidentally omitted and you can very easily wind up with an unintentional Cartesian product of your tables. The explain from your query (extracted from your attachment and included below) tells me exactly why your query takes so long (heavily trimmed to avoid excessive message wrapping). +--+---+--++-++-+--+ |id|select_type|table |type|possible_keys|key | | Extra| +--+---+--++-++-+--+ | 1| PRIMARY |eca |ALL |NULL |NULL| | Using where; Using temporary; Using filesort | | 1| PRIMARY |ece |ALL |NULL |NULL| | Using where| | 1| PRIMARY |derived2|ALL |NULL |NULL| | Using where| | 2| DERIVED |tp|ALL |NULL |NULL| || | 2| DERIVED |eu|ALL |NULL |NULL| | Using where| +--+---+--++-++-++ Look at the column possible_keys. Every entry is NULL. That means that you have no indexes on your tables that could have been used to respond to this query. This worries me as tables that are involved in relationships with other tables should at a MINIMUM contain a primary key. Your slow performance is due to the fact that the query engine had to perform complete table scans of all tables involved in this query. May I strongly suggest some reading for you. If you don't understand any part of it, come back to the list with your questions and we can help explain it in other ways. These articles describe ways to implement indexes(keys) in your database to speed up your queries. http://dev.mysql.com/doc/mysql/en/CREATE_INDEX.html http://dev.mysql.com/doc/mysql/en/ALTER_TABLE.html http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html This article describes how to interpret the output of the EXPLAIN command http://dev.mysql.com/doc/mysql/en/EXPLAIN.html These articles help to explain how queries are helped by indexes http://dev.mysql.com/doc/mysql/en/MySQL_indexes.html http://dev.mysql.com/doc/mysql/en/SELECT_speed.html There are also MANY articles on the web about query optimization and index usage. Use your favorite search engine to help you find them. Nearly all of the principles and techniques that work for the other database engines (Oracle, MS SQL Server, Informix, etc) will also work for your queries with MySQL so don't necessarily limit yourself to just MySQL articles. To solve your speed problem, you need to create an appropriate set of keys(indexes) on your tables. Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Elim Qiu [EMAIL PROTECTED] wrote on 11/18/2004 07:44:01 PM: Dear list, i have some small tables but for some reason the mysql took very long to find the results. my query looks like below and mysql'e explain is attached for better format. Thanks for your help! select teu.name, eca.owner_id, ece.value fromtyped_enterprise_unit teu, e_contact_association eca, e_contact_entry ece where teu.unit_id=eca.owner_id and eca.entry_id=ece.entry_id and eca.type_id=68 and (teu.type_path like '%/66/%' or teu.type_id=66) and eca.owner_id 45 order by eca.owner_id limit 50; [attachment need_help_query.txt deleted by Shawn Green/Unimin] No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.289 / Virus Database: 265.3.1 - Release Date: 11/15/2004 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help optimizing query
On 15 Jul 2004 at 6:27, Patrick Drouin [EMAIL PROTECTED] wrote: I'm having a hard time with the following query. It retrieves about 3K rows from a few tables. One of them contains over 40M rows. When run on a 3Ghz server with 1G of RAM it returns the rows in more than 1 mini. I don't think that's normal. Your indexes look good, but I see that you have some varchar fields. Maybe you could run an optimize table on these tables? Also, you are talking about a 40M rows table. If it is a read only MyISAM table, I might try compression. Arnaud -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help optimizing query
Bonjour Arnaud, --- Arnaud [EMAIL PROTECTED] wrote: On 15 Jul 2004 at 6:27, Patrick Drouin Your indexes look good, but I see that you have some varchar fields. Maybe you could run an optimize table on these tables? I'm running it at the moment, I will follow-up on the list when it's done. It could take a while I guess. Also, you are talking about a 40M rows table. If it is a read only MyISAM table, I might try compression. It's mainly read-only, I sometimes batch load some data but users don't update at all. I'll look into compression and see what it is about. Thanks, Patrick __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help optimizing query
What version of MySQL are you using? Have you checked the cardinality on these tables? -Original Message- From: Patrick Drouin To: [EMAIL PROTECTED] Sent: 7/15/04 8:27 AM Subject: Need help optimizing query Hello everyone, I'm having a hard time with the following query. It retrieves about 3K rows from a few tables. One of them contains over 40M rows. When run on a 3Ghz server with 1G of RAM it returns the rows in more than 1 mini. I don't think that's normal. Here's the output of EXPLAIN: mysql explain SELECT ti.posi, ti.docid, d.filename, ti.id, c.name FROM corpus_documents cd, corpus c, documents d, tokens_ins ti, tokens t WHERE c.corpusid=4 AND cd.corpusid=c.corpusid AND cd.docid=d.docid AND t.docid=d.docid AND ti.id=t.id AND t.word='police' AND t.docid=ti.docid\g; He +---++-- +---+-+---+--+-- +| table | type | possible_keys | key | key_len | ref | rows | Extra |+---++- -+---+-+---+--+- -+| c | ref | PRIMARY,corpus_corpusid | PRIMARY | 3 | const | 1 | Using where; Using index || t | ref | PRIMARY,tokens_id,tokens_docid,tokens_word,tokens_word_docid | PRIMARY | 30 | const | 24 | Using where || ti | ref | PRIMARY,tokens_ins_id,tokens_ins_docid | tokens_ins_id | 4 | t.id | 96 | Using where || d | ref | PRIMARY,documents_docid | PRIMARY | 3 | t.docid | 3 | Using index || cd | eq_ref | PRIMARY | PRIMARY | 6 | const,d.docid | 1 | Using where; Using index |+---++- -+---+-+---+--+- -+ 5 rows in set (0.00 sec) It seems to be using indexes as expected and it does not seem to look at that many rows. Here's tthe query chewed up and nicely displayed: SELECT ti.posi, ti.docid, d.filename, ti.id, c.name FROM corpus_documents cd, corpus c, documents d, tokens_ins ti, tokens t WHERE c.corpusid=4 AND cd.corpusid=c.corpusid AND cd.docid=d.docid AND t.docid=d.docid AND ti.id=t.id AND t.word='police' AND t.docid=ti.docid; ... 3791 rows in set (1 min 29.78 sec) Here are descriptions of the tables at play : mysql desc tokens_ins; +--+---+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +--+---+--+-+-+---+ | id | int(11) | | MUL | 0 | | | posi | int(11) | | PRI | 0 | | | docid | mediumint(20) | | PRI | 0 | | | originalspelling | varchar(30) | | | | | +--+---+--+-+-+---+ 4 rows in set (0.02 sec) mysql desc tokens; +-++--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-++--+-+-+---+ | id | int(11) | | MUL | 0 | | | docid | mediumint(20) | | PRI | 0 | | | word | varchar(30) binary | | PRI | | | | pos | varchar(10) | | PRI | 0 | | | absfreq | mediumint(20) | | MUL | 0 | | +-++--+-+-+---+ 5 rows in set (0.00 sec) mysql desc corpus; +--+---+--+-+-++ | Field | Type | Null | Key | Default | Extra | +--+---+--+-+-++ | corpusid | mediumint(20) | | PRI | NULL | auto_increment | | name | varchar(30) | | PRI |+-+-+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+-+--+-+-+---+ | docid | mediumint(20) | | PRI | 0 | | | filename | varchar(30) | | PRI | | | | language | char(3) | | | | | | description | varchar(255) binary | YES | | NULL | | +-+-+--+-+-+---+ 4 rows in set (0.00 sec) mysql desc corpus_documents; +--+---+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +--+---+--+-+-+---+ | corpusid | mediumint(20) | | PRI | 0 | | | docid | mediumint(20) | | PRI | 0 | | +--+---+--+-+-+---+ 2 rows in set (0.00 sec) Can anybody give me a hand speeding up this ting? I'm running out of ideas. Thanks, P | | | language | char(3) | | MUL | | | +--+---+--+-+-++ 3 rows in set (0.00 sec) mysql desc documents; __ Do you Yahoo!? Yahoo! Mail Address AutoComplete - You start. We finish. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql
RE: Need help optimizing query
Hello Victor, What version of MySQL are you using? Have you checked the cardinality on these tables? Problem solved! Optimizing the table brought the query time down to 17 secs Wow! Thanks for the input Victor and merci to Arnaud for the quick fix. Patrick __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with query. Please!
Try: SELECT C.company_id, C.company_name FROM companies C LEFT JOIN company_group_intersect CG ON (C.company_id=CG.company_id AND CG.group_id='1') WHERE C.status='1' AND CG.company_id IS NULL -- Diana Soares On Fri, 2003-12-05 at 15:08, Robert Hughes wrote: I have the following 3 tables: table 1: companies fields 1: company_id, company name, status Sample data: 1 , company 1 , 0 2 , company 2 , 1 3 , company 3 , 1 4 , company 4 , 0 5 , company 5 , 1 table 2: groups fields 2: group_id, group_name Sample data: 1 , Group 1 2 , Group 2 3 , Group 3 4 , Group 4 5 , Group 5 table 3: company_group_intersect table 3: group_id, company_id Sample data: 1 , 2 1 , 3 2 , 2 2 , 3 2 , 5 As you can see, Group 1 consists of Companies 2 and 3. And Group 2 consists of Companies 2, 3 and 5. The query I'm having trouble with is trying to get a result set of (status=1) companies that AREN'T in a particular group. group_id is my only available variable passed in from the script. I need a results set that has: * companies.company_name, companies.company_id where companies.status='1' and companies.company_id is not in intersect table next to the variable group_id. If I pass in group_id 1 the result set should be: 5 , company 5 since it's the only status='1' company that's not in group 1 Thanks in advance for your help. Robert --- At Executive Performance Group we take security very seriously. All emails and attachments are scanned for viruses prior to sending. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.544 / Virus Database: 338 - Release Date: 11/25/2003 -- Diana Soares -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help with query. Please!
That worked perfectly!!! Thanks so much :-) -Original Message- From: Diana Soares [mailto:[EMAIL PROTECTED] Sent: Friday, December 05, 2003 10:31 AM To: Robert Hughes Cc: [EMAIL PROTECTED] Subject: Re: Need help with query. Please! Try: SELECT C.company_id, C.company_name FROM companies C LEFT JOIN company_group_intersect CG ON (C.company_id=CG.company_id AND CG.group_id='1') WHERE C.status='1' AND CG.company_id IS NULL -- Diana Soares On Fri, 2003-12-05 at 15:08, Robert Hughes wrote: I have the following 3 tables: table 1: companies fields 1: company_id, company name, status Sample data: 1 , company 1 , 0 2 , company 2 , 1 3 , company 3 , 1 4 , company 4 , 0 5 , company 5 , 1 table 2: groups fields 2: group_id, group_name Sample data: 1 , Group 1 2 , Group 2 3 , Group 3 4 , Group 4 5 , Group 5 table 3: company_group_intersect table 3: group_id, company_id Sample data: 1 , 2 1 , 3 2 , 2 2 , 3 2 , 5 As you can see, Group 1 consists of Companies 2 and 3. And Group 2 consists of Companies 2, 3 and 5. The query I'm having trouble with is trying to get a result set of (status=1) companies that AREN'T in a particular group. group_id is my only available variable passed in from the script. I need a results set that has: * companies.company_name, companies.company_id where companies.status='1' and companies.company_id is not in intersect table next to the variable group_id. If I pass in group_id 1 the result set should be: 5 , company 5 since it's the only status='1' company that's not in group 1 Thanks in advance for your help. Robert --- At Executive Performance Group we take security very seriously. All emails and attachments are scanned for viruses prior to sending. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.544 / Virus Database: 338 - Release Date: 11/25/2003 -- Diana Soares -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.544 / Virus Database: 338 - Release Date: 11/25/2003 --- At Executive Performance Group we take security very seriously. All emails and attachments are scanned for viruses prior to sending. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.544 / Virus Database: 338 - Release Date: 11/25/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help constructing query ...
* John Kelly
I have a table of full URLs and IPs and am using the following
query to return
distinct web requests by domain. Using SUBSTRING_INDEX it only returns the
domain part of the URL:
SELECT SUBSTRING_INDEX(url, '/', 3) as topsites, count(distinct
ip) as count
from tablename WHERE SUBSTRING_INDEX(url, '/', 3) LIKE
'%mydomain%' group by
topsites order by count
Example output:
topsitescount
http://www.mydomain.com5
http://mydomain.com 3
My question is how do I modify the query to get it to merge
requests for the
same domain by ignoring the www. so that the above would return:
http://mydomain.com 8
I think it has something to do with adding
REPLACE('url', 'www.', '')
but I can't figure out where to put it to make it work.
Try either
SUBSTRING_INDEX(REPLACE(url, 'www.', ''), '/', 3)
or
REPLACE(SUBSTRING_INDEX(url, '/', 3),'www.', '')
You don't need it in the WHERE clause, only in the field list and GROUP BY:
SELECT REPLACE(SUBSTRING_INDEX(url, '/', 3),'www.', '') as topsites,
count(distinct ip) as count from tablename
WHERE SUBSTRING_INDEX(url, '/', 3) LIKE '%mydomain%'
group by topsites order by count
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help constructing query ...
Try the following: SELECT ip_address, url, count(distinct ip_address) FROM tablename GROUP BY ip_address, url At 09:48 PM 10/21/2003, John Kelly wrote: : -Original Message- : From: John Kelly [mailto:[EMAIL PROTECTED] : Sent: Tuesday, October 21, 2003 3:45 PM : To: [EMAIL PROTECTED] : Cc: [EMAIL PROTECTED] : Subject: Re: Need help constructing query ... : : : - Original Message - : From: Daniel Clark [EMAIL PROTECTED] : To: [EMAIL PROTECTED] : Cc: [EMAIL PROTECTED] : Sent: Tuesday, October 21, 2003 2:33 PM : Subject: Re: Need help constructing query ... : : : : Hi, I have a table full of logged urls and ip addresses. : The following : : query returns all the urls and the number of requests. How would I : : modify it to return unique requests based on distinct ip : addresses? : : : : select url, count(*) as pageviews from table group by url order by : : pageviews desc : : : : How about: : : : : SELECT ip_address, url, count(*) : : FROM tablename : : GROUP BY ip_adress, url : : : Thanks but I could not get that to work. It does not appear : to count the number of page requests by distinct IPs anyway : does it? Don't you need something like a : count(distinct(ip_address)) somewhere in there? : : -- - Original Message - From: Kevin Fries [EMAIL PROTECTED] To: 'John Kelly' [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, October 21, 2003 3:57 PM Subject: RE: Need help constructing query ... : Then I think you want : SELECT url, COUNT(DISTINCT ip_address) : FROM tablename : GROUP BY url; Thanks, this must be a resource intensive query as it works in a few seconds on a small table but takes 6+ minutes when done on a table with just 100,000 records. Anyway, thanks again. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or888-638-6963 Fax: 724-489-4386 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help constructing query ...
Hi, I have a table full of logged urls and ip addresses. The following query returns all the urls and the number of requests. How would I modify it to return unique requests based on distinct ip addresses? select url, count(*) as pageviews from table group by url order by pageviews desc How about: SELECT ip_address, url, count(*) FROM tablename GROUP BY ip_adress, url -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help constructing query ...
- Original Message - From: Daniel Clark [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, October 21, 2003 2:33 PM Subject: Re: Need help constructing query ... : Hi, I have a table full of logged urls and ip addresses. The following : query returns all the urls and the number of requests. How would I : modify it to return unique requests based on distinct ip addresses? : : select url, count(*) as pageviews from table group by url order by : pageviews desc : : How about: : : SELECT ip_address, url, count(*) : FROM tablename : GROUP BY ip_adress, url : Thanks but I could not get that to work. It does not appear to count the number of page requests by distinct IPs anyway does it? Don't you need something like a count(distinct(ip_address)) somewhere in there? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help constructing query ...
Then I think you want SELECT url, COUNT(DISTINCT ip_address) FROM tablename GROUP BY url; -Original Message- From: John Kelly [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 21, 2003 3:45 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Need help constructing query ... - Original Message - From: Daniel Clark [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, October 21, 2003 2:33 PM Subject: Re: Need help constructing query ... : Hi, I have a table full of logged urls and ip addresses. The following : query returns all the urls and the number of requests. How would I : modify it to return unique requests based on distinct ip addresses? : : select url, count(*) as pageviews from table group by url order by : pageviews desc : : How about: : : SELECT ip_address, url, count(*) : FROM tablename : GROUP BY ip_adress, url : Thanks but I could not get that to work. It does not appear to count the number of page requests by distinct IPs anyway does it? Don't you need something like a count(distinct(ip_address)) somewhere in there? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help constructing query ...
: -Original Message- : From: John Kelly [mailto:[EMAIL PROTECTED] : Sent: Tuesday, October 21, 2003 3:45 PM : To: [EMAIL PROTECTED] : Cc: [EMAIL PROTECTED] : Subject: Re: Need help constructing query ... : : : - Original Message - : From: Daniel Clark [EMAIL PROTECTED] : To: [EMAIL PROTECTED] : Cc: [EMAIL PROTECTED] : Sent: Tuesday, October 21, 2003 2:33 PM : Subject: Re: Need help constructing query ... : : : : Hi, I have a table full of logged urls and ip addresses. : The following : : query returns all the urls and the number of requests. How would I : : modify it to return unique requests based on distinct ip : addresses? : : : : select url, count(*) as pageviews from table group by url order by : : pageviews desc : : : : How about: : : : : SELECT ip_address, url, count(*) : : FROM tablename : : GROUP BY ip_adress, url : : : Thanks but I could not get that to work. It does not appear : to count the number of page requests by distinct IPs anyway : does it? Don't you need something like a : count(distinct(ip_address)) somewhere in there? : : -- - Original Message - From: Kevin Fries [EMAIL PROTECTED] To: 'John Kelly' [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, October 21, 2003 3:57 PM Subject: RE: Need help constructing query ... : Then I think you want : SELECT url, COUNT(DISTINCT ip_address) : FROM tablename : GROUP BY url; Thanks, this must be a resource intensive query as it works in a few seconds on a small table but takes 6+ minutes when done on a table with just 100,000 records. Anyway, thanks again. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help writing query
Patrick Shoaf [EMAIL PROTECTED] wrote: I have a table name product defined as follows: Item_Code Item_Size Item_Color Item_img Description Cost Retail_Price Category and other non-essential items, such as qty based on code,size,color The data is as follows: j2400 S BLK j2400blk.jpgBlack Jacket12.00 24.00 Jacket j2400 M BLK j2400blk.jpgBlack Jacket12.00 24.00 Jacket j2400 L BLK j2400blk.jpgBlack Jacket12.00 24.00 Jacket j2400 4XL BLK j2400blk.jpgBlack Jacket18.00 36.00 Jacket p2400 S BLK p2400blk.jpgBlack Pants 16.00 32.00 Pants p2400 M BLK p2400blk.jpgBlack Pants 16.00 32.00 Pants p2400 L BLK p2400blk.jpgBlack Pants 16.00 32.00 Pants p2400 4XL BLK p2400blk.jpgBlack Pants 24.00 48.00 Pants I need the result to show: j2400 S,M,L,4XL BLK j2400blk.jpgBlack Jacket12,12,12,18 p2400 S,M,L,4XL BLK p2400blk.jpgBlack Pants 16,16,16,24 How would you write this query? I used initially SELECT Item_Code,Item_Size,Item_Color,Item_Img,Description,Cost FROM product WHERE Category=Jacket or Category=Pants GROUP BY Item_code This resulted in only retrieving the first item in the list for size price info. There is a GROUP_CONCAT() function, that is available from version 4.1: http://www.mysql.com/doc/en/GROUP-BY-Functions.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help writing query
I loaded MySQL 4.1, but now the text is gibberish in the Group_Concat field SELECT Cat_Item_Img,Cat_Price,Product.Item_Code,GROUP_CONCAT(Item_Size SEPARATOR ;) as sizes,Item_Img, Description,Category,Retail_Price,Short_Desc,Product.Item_Color FROM Cat_Items,Product WHERE cat_code =BoltTech and Cat_Items.Item_Code = Product.Item_Code and Category =Casual Wear GROUP BY Product.Item_Code RESULTS: | Cat_Item_Img | Cat_Price | Item_Code | sizes | Item_Img | Description | Category| | | 19.00 | 288 | ?L.MYI | 288 | bPromenade/b Blended Knit Shirt | Casual Wear | | | 28.00 | 71080 | ?L.MYI | 71080| bLee Denim Shirt/bbr | Casual Wear | | | 24.00 | 77123 | ?L.MYI | 77123| bLee Golf Shirt/bbr | Casual Wear | The data should read S;M;L;XL;2XL;3XL not ?L.MYI (?L.MYI is translation when using copy/paste, there were other characters in sizes field.) Is this a 4.1 Bug? I loaded from the 4.1.0-0 Linux x86 RPM files. So far nothing else appears broken. Any suggestions? Patrick At 09:11 AM 9/18/2003, you wrote: Patrick Shoaf [EMAIL PROTECTED] wrote: I have a table name product defined as follows: Item_Code Item_Size Item_Color Item_img Description Cost Retail_Price Category and other non-essential items, such as qty based on code,size,color The data is as follows: j2400 S BLK j2400blk.jpgBlack Jacket12.00 24.00 Jacket j2400 M BLK j2400blk.jpgBlack Jacket12.00 24.00 Jacket j2400 L BLK j2400blk.jpgBlack Jacket12.00 24.00 Jacket j2400 4XL BLK j2400blk.jpgBlack Jacket18.00 36.00 Jacket p2400 S BLK p2400blk.jpgBlack Pants 16.00 32.00 Pants p2400 M BLK p2400blk.jpgBlack Pants 16.00 32.00 Pants p2400 L BLK p2400blk.jpgBlack Pants 16.00 32.00 Pants p2400 4XL BLK p2400blk.jpgBlack Pants 24.00 48.00 Pants I need the result to show: j2400 S,M,L,4XL BLK j2400blk.jpgBlack Jacket12,12,12,18 p2400 S,M,L,4XL BLK p2400blk.jpgBlack Pants 16,16,16,24 How would you write this query? I used initially SELECT Item_Code,Item_Size,Item_Color,Item_Img,Description,Cost FROM product WHERE Category=Jacket or Category=Pants GROUP BY Item_code This resulted in only retrieving the first item in the list for size price info. There is a GROUP_CONCAT() function, that is available from version 4.1: http://www.mysql.com/doc/en/GROUP-BY-Functions.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or888-638-6963 Fax: 724-489-4386 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help optimizing query, awfully slow on only 20000 records
Are your tables indexed? http://www.mysql.com/doc/en/MySQL_indexes.html Saqib Ali - http://www.xml-dev.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help optimizing query, awfully slow on only 20000 records
Absolutely! I have multiple indexes. I think it might be a problem with ODBC Are your tables indexed? http://www.mysql.com/doc/en/MySQL_indexes.html Saqib Ali - http://www.xml-dev.com - Visit CARMEL MUSIC ENTERTAINMENT website http://carmelme.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help optimizing query, awfully slow on only 20000 records
When following query is pulled up, it takes about whole 1-2 minutes to come up. inquiries table has 17000 records, contacts has about 7000, individual_contacts has about 16000. It has gotten worse once I upgraded to 4.0 and latest MyODBC. Clients are separate machines (mix of Win98 and WinXP). Those 20K records is what feeds the Access97 form, pull down list filters out some and pulls up about 3K and people just start typing a name and then (since there multiple inquiries for some clients) pull down the list to pick inquiry they want. What are you defining as a huge performance hit? Is the result set 20K records, or the base tables? How big are the base tables? Are the client and server on the same machine? Pulling 20K records across the network could take some time. Formatting 20K records into a pull -down list in access will also take a long time. Anyway who reads a 20K list? Which parts of the process are slow? How does the query perform from the mysql command line? -Original Message- From: Apollo (Carmel Entertainment) [mailto:[EMAIL PROTECTED] Sent: 19 August 2003 17:29 To: [EMAIL PROTECTED] Subject: Need help optimizing query, awfully slow on only 2 records 1.2Ghz Pentium, with 1/2Gig of ram, 4.0.14MySQL, RedHat9.0 I have about 20K records that result from the following query. Front end for the database is ACCESS97 and pulling up 20K records makes a huge performance hit. For the form in question I am using PASSTHROUGH type query (the one that just passes everything straight to server without ODBC). NOTE: souce_for_inquiries_form is the join table and is searchable in the from (it feeds a pull-down list). SELECT inquiries.inquiry_id, inquiries.contact_id, inquiries.indiv_contact_id, inquiries.phone, inquiries.fax, inquiries.agent_id, inquiries.inquiry_date, inquiries.event_type, inquiries.letter_type, inquiries.event_date, inquiries.event_date_general, inquiries.event_location, inquiries.guests, inquiries.hours, inquiries.budget, inquiries.event_description, inquiries.talent_description, inquiries.past_use, inquiries.referred_by, inquiries.date_sent, inquiries.end_user, inquiries.event_id, inquiries.notes, source_for_inquiries_form.organization, source_for_inquiries_form.mailing_address_1, source_for_inquiries_form.mailing_address_2, source_for_inquiries_form.city, source_for_inquiries_form.state, source_for_inquiries_form.zip, source_for_inquiries_form.contact_type, individual_contacts.contact_name_first, individual_contacts.contact_name_last, individual_contacts.contact_prefix, individual_contacts.contact_title, individual_contacts.email FROM inquiries LEFT JOIN individual_contacts ON inquiries.indiv_contact_id = individual_contacts.indiv_contact_id INNER JOIN contacts AS source_for_inquiries_form ON inquiries.contact_id = source_for_inquiries_form.contact_id ORDER BY inquiries.inquiry_id DESC; - - Visit CARMEL MUSIC ENTERTAINMENT website http://carmelme.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help optimizing query, awfully slow on only 20000 records
Result of EXPLAIN is: table|type|possible_keys|key|key_len|ref|rows|Extra inquiries|ALL|contact_id| | | |8253|Using filesort individual_contacts|eq_ref|PRIMARY,indiv_contact_id|PRIMARY|3|inquiries.indiv_contact_id|1 source_for_inquiries_form|eq_ref|PRIMARY,contact_id|PRIMARY|3|inquiries.contact_id|1 What does EXPLAIN SELECT query show? Have you read the chapter in the manual on optimizing queries? Do you have all the proper indices set up? --Michael -Original Message- From: Apollo (Carmel Entertainment) [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 20, 2003 2:52 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: Need help optimizing query, awfully slow on only 2 records When following query is pulled up, it takes about whole 1-2 minutes to come up. inquiries table has 17000 records, contacts has about 7000, individual_contacts has about 16000. It has gotten worse once I upgraded to 4.0 and latest MyODBC. Clients are separate machines (mix of Win98 and WinXP). Those 20K records is what feeds the Access97 form, pull down list filters out some and pulls up about 3K and people just start typing a name and then (since there multiple inquiries for some clients) pull down the list to pick inquiry they want. What are you defining as a huge performance hit? Is the result set 20K records, or the base tables? How big are the base tables? Are the client and server on the same machine? Pulling 20K records across the network could take some time. Formatting 20K records into a pull -down list in access will also take a long time. Anyway who reads a 20K list? Which parts of the process are slow? How does the query perform from the mysql command line? -Original Message- From: Apollo (Carmel Entertainment) [mailto:[EMAIL PROTECTED] Sent: 19 August 2003 17:29 To: [EMAIL PROTECTED] Subject: Need help optimizing query, awfully slow on only 2 records 1.2Ghz Pentium, with 1/2Gig of ram, 4.0.14MySQL, RedHat9.0 I have about 20K records that result from the following query. Front end for the database is ACCESS97 and pulling up 20K records makes a huge performance hit. For the form in question I am using PASSTHROUGH type query (the one that just passes everything straight to server without ODBC). NOTE: souce_for_inquiries_form is the join table and is searchable in the from (it feeds a pull-down list). SELECT inquiries.inquiry_id, inquiries.contact_id, inquiries.indiv_contact_id, inquiries.phone, inquiries.fax, inquiries.agent_id, inquiries.inquiry_date, inquiries.event_type, inquiries.letter_type, inquiries.event_date, inquiries.event_date_general, inquiries.event_location, inquiries.guests, inquiries.hours, inquiries.budget, inquiries.event_description, inquiries.talent_description, inquiries.past_use, inquiries.referred_by, inquiries.date_sent, inquiries.end_user, inquiries.event_id, inquiries.notes, source_for_inquiries_form.organization, source_for_inquiries_form.mailing_address_1, source_for_inquiries_form.mailing_address_2, source_for_inquiries_form.city, source_for_inquiries_form.state, source_for_inquiries_form.zip, source_for_inquiries_form.contact_type, individual_contacts.contact_name_first, individual_contacts.contact_name_last, individual_contacts.contact_prefix, individual_contacts.contact_title, individual_contacts.email FROM inquiries LEFT JOIN individual_contacts ON inquiries.indiv_contact_id = individual_contacts.indiv_contact_id INNER JOIN contacts AS source_for_inquiries_form ON inquiries.contact_id = source_for_inquiries_form.contact_id ORDER BY inquiries.inquiry_id DESC; - - Visit CARMEL MUSIC ENTERTAINMENT website http://carmelme.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] Apolinaras Apollo Sinkevicius Carmel Music Entertainment, LLC 701 Main Street Evanston, IL 60202 Phone: (847) 864-5969 X110 Fax: (847) 864-6149 Toll-free: 800-276-5969 X110 e-mail: [EMAIL PROTECTED] web-site: http://carmelme.com Having an event in Chicago, or would you like to bring Chicago entertainment to your event? Give Carmel Music Entertainment a call for the finest entertainment available in Chicago. - Visit CARMEL MUSIC ENTERTAINMENT website http://carmelme.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL
Re: Need help optimizing query, awfully slow on only 20000 records
Query takes 3.4 seconds to run on the server, but it takes 1-2minutes to run via MyODBC 3.51.06 using passthrough (Access97 is the front end, but it has query type that allows bypass of Access interpretation. Two Questions: Is the same query running directly on the linux server thru mysql is also very slow ? Have you done a explain plan on the query ? Marc. - Visit CARMEL MUSIC ENTERTAINMENT website http://carmelme.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help optimizing query, awfully slow on only 20000 records
When I send the query throuh comman line, it works perfect 3-4 seconds, but when I do Access97 pass-through query, that is when it runs into 1-2 minutes. It is almost as slow as using Access97 native query that goes through MyODBC, so... That doesn't look too bad. Is the query that slow when you use the command-line client alone (connected directly to the server), or is it just when accessing the database through the ODBC tunnel? --Michael -Original Message- From: Apollo (Carmel Entertainment) [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 20, 2003 3:14 PM To: Michael S. Fischer Cc: [EMAIL PROTECTED] Subject: RE: Need help optimizing query, awfully slow on only 2 records Result of EXPLAIN is: table|type|possible_keys|key|key_len|ref|rows|Extra inquiries|ALL|contact_id| | | |8253|Using filesort individual_contacts|eq_ref|PRIMARY,indiv_contact_id|PRIMARY|3| inquiries.indiv_contact_id|1 source_for_inquiries_form|eq_ref|PRIMARY,contact_id|PRIMARY|3| inquiries.contact_id|1 What does EXPLAIN SELECT query show? Have you read the chapter in the manual on optimizing queries? Do you have all the proper indices set up? --Michael -Original Message- From: Apollo (Carmel Entertainment) [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 20, 2003 2:52 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: Need help optimizing query, awfully slow on only 2 records When following query is pulled up, it takes about whole 1-2 minutes to come up. inquiries table has 17000 records, contacts has about 7000, individual_contacts has about 16000. It has gotten worse once I upgraded to 4.0 and latest MyODBC. Clients are separate machines (mix of Win98 and WinXP). Those 20K records is what feeds the Access97 form, pull down list filters out some and pulls up about 3K and people just start typing a name and then (since there multiple inquiries for some clients) pull down the list to pick inquiry they want. What are you defining as a huge performance hit? Is the result set 20K records, or the base tables? How big are the base tables? Are the client and server on the same machine? Pulling 20K records across the network could take some time. Formatting 20K records into a pull -down list in access will also take a long time. Anyway who reads a 20K list? Which parts of the process are slow? How does the query perform from the mysql command line? -Original Message- From: Apollo (Carmel Entertainment) [mailto:[EMAIL PROTECTED] Sent: 19 August 2003 17:29 To: [EMAIL PROTECTED] Subject: Need help optimizing query, awfully slow on only 2 records 1.2Ghz Pentium, with 1/2Gig of ram, 4.0.14MySQL, RedHat9.0 I have about 20K records that result from the following query. Front end for the database is ACCESS97 and pulling up 20K records makes a huge performance hit. For the form in question I am using PASSTHROUGH type query (the one that just passes everything straight to server without ODBC). NOTE: souce_for_inquiries_form is the join table and is searchable in the from (it feeds a pull-down list). SELECT inquiries.inquiry_id, inquiries.contact_id, inquiries.indiv_contact_id, inquiries.phone, inquiries.fax, inquiries.agent_id, inquiries.inquiry_date, inquiries.event_type, inquiries.letter_type, inquiries.event_date, inquiries.event_date_general, inquiries.event_location, inquiries.guests, inquiries.hours, inquiries.budget, inquiries.event_description, inquiries.talent_description, inquiries.past_use, inquiries.referred_by, inquiries.date_sent, inquiries.end_user, inquiries.event_id, inquiries.notes, source_for_inquiries_form.organization, source_for_inquiries_form.mailing_address_1, source_for_inquiries_form.mailing_address_2, source_for_inquiries_form.city, source_for_inquiries_form.state, source_for_inquiries_form.zip, source_for_inquiries_form.contact_type, individual_contacts.contact_name_first, individual_contacts.contact_name_last, individual_contacts.contact_prefix, individual_contacts.contact_title, individual_contacts.email FROM inquiries LEFT JOIN individual_contacts ON inquiries.indiv_contact_id = individual_contacts.indiv_contact_id INNER JOIN contacts AS source_for_inquiries_form ON inquiries.contact_id = source_for_inquiries_form.contact_id ORDER BY inquiries.inquiry_id DESC; - - Visit
RE: Need help optimizing query, awfully slow on only 20000 records
That doesn't look too bad. Is the query that slow when you use the command-line client alone (connected directly to the server), or is it just when accessing the database through the ODBC tunnel? --Michael -Original Message- From: Apollo (Carmel Entertainment) [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 20, 2003 3:14 PM To: Michael S. Fischer Cc: [EMAIL PROTECTED] Subject: RE: Need help optimizing query, awfully slow on only 2 records Result of EXPLAIN is: table|type|possible_keys|key|key_len|ref|rows|Extra inquiries|ALL|contact_id| | | |8253|Using filesort individual_contacts|eq_ref|PRIMARY,indiv_contact_id|PRIMARY|3| inquiries.indiv_contact_id|1 source_for_inquiries_form|eq_ref|PRIMARY,contact_id|PRIMARY|3| inquiries.contact_id|1 What does EXPLAIN SELECT query show? Have you read the chapter in the manual on optimizing queries? Do you have all the proper indices set up? --Michael -Original Message- From: Apollo (Carmel Entertainment) [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 20, 2003 2:52 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: Need help optimizing query, awfully slow on only 2 records When following query is pulled up, it takes about whole 1-2 minutes to come up. inquiries table has 17000 records, contacts has about 7000, individual_contacts has about 16000. It has gotten worse once I upgraded to 4.0 and latest MyODBC. Clients are separate machines (mix of Win98 and WinXP). Those 20K records is what feeds the Access97 form, pull down list filters out some and pulls up about 3K and people just start typing a name and then (since there multiple inquiries for some clients) pull down the list to pick inquiry they want. What are you defining as a huge performance hit? Is the result set 20K records, or the base tables? How big are the base tables? Are the client and server on the same machine? Pulling 20K records across the network could take some time. Formatting 20K records into a pull -down list in access will also take a long time. Anyway who reads a 20K list? Which parts of the process are slow? How does the query perform from the mysql command line? -Original Message- From: Apollo (Carmel Entertainment) [mailto:[EMAIL PROTECTED] Sent: 19 August 2003 17:29 To: [EMAIL PROTECTED] Subject: Need help optimizing query, awfully slow on only 2 records 1.2Ghz Pentium, with 1/2Gig of ram, 4.0.14MySQL, RedHat9.0 I have about 20K records that result from the following query. Front end for the database is ACCESS97 and pulling up 20K records makes a huge performance hit. For the form in question I am using PASSTHROUGH type query (the one that just passes everything straight to server without ODBC). NOTE: souce_for_inquiries_form is the join table and is searchable in the from (it feeds a pull-down list). SELECT inquiries.inquiry_id, inquiries.contact_id, inquiries.indiv_contact_id, inquiries.phone, inquiries.fax, inquiries.agent_id, inquiries.inquiry_date, inquiries.event_type, inquiries.letter_type, inquiries.event_date, inquiries.event_date_general, inquiries.event_location, inquiries.guests, inquiries.hours, inquiries.budget, inquiries.event_description, inquiries.talent_description, inquiries.past_use, inquiries.referred_by, inquiries.date_sent, inquiries.end_user, inquiries.event_id, inquiries.notes, source_for_inquiries_form.organization, source_for_inquiries_form.mailing_address_1, source_for_inquiries_form.mailing_address_2, source_for_inquiries_form.city, source_for_inquiries_form.state, source_for_inquiries_form.zip, source_for_inquiries_form.contact_type, individual_contacts.contact_name_first, individual_contacts.contact_name_last, individual_contacts.contact_prefix, individual_contacts.contact_title, individual_contacts.email FROM inquiries LEFT JOIN individual_contacts ON inquiries.indiv_contact_id = individual_contacts.indiv_contact_id INNER JOIN contacts AS source_for_inquiries_form ON inquiries.contact_id = source_for_inquiries_form.contact_id ORDER BY inquiries.inquiry_id DESC; - - Visit CARMEL MUSIC ENTERTAINMENT website http://carmelme.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] Apolinaras Apollo Sinkevicius Carmel Music Entertainment, LLC 701 Main Street Evanston, IL 60202
RE: Need help optimizing query, awfully slow on only 20000 records
What does EXPLAIN SELECT query show? Have you read the chapter in the manual on optimizing queries? Do you have all the proper indices set up? --Michael -Original Message- From: Apollo (Carmel Entertainment) [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 20, 2003 2:52 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: Need help optimizing query, awfully slow on only 2 records When following query is pulled up, it takes about whole 1-2 minutes to come up. inquiries table has 17000 records, contacts has about 7000, individual_contacts has about 16000. It has gotten worse once I upgraded to 4.0 and latest MyODBC. Clients are separate machines (mix of Win98 and WinXP). Those 20K records is what feeds the Access97 form, pull down list filters out some and pulls up about 3K and people just start typing a name and then (since there multiple inquiries for some clients) pull down the list to pick inquiry they want. What are you defining as a huge performance hit? Is the result set 20K records, or the base tables? How big are the base tables? Are the client and server on the same machine? Pulling 20K records across the network could take some time. Formatting 20K records into a pull -down list in access will also take a long time. Anyway who reads a 20K list? Which parts of the process are slow? How does the query perform from the mysql command line? -Original Message- From: Apollo (Carmel Entertainment) [mailto:[EMAIL PROTECTED] Sent: 19 August 2003 17:29 To: [EMAIL PROTECTED] Subject: Need help optimizing query, awfully slow on only 2 records 1.2Ghz Pentium, with 1/2Gig of ram, 4.0.14MySQL, RedHat9.0 I have about 20K records that result from the following query. Front end for the database is ACCESS97 and pulling up 20K records makes a huge performance hit. For the form in question I am using PASSTHROUGH type query (the one that just passes everything straight to server without ODBC). NOTE: souce_for_inquiries_form is the join table and is searchable in the from (it feeds a pull-down list). SELECT inquiries.inquiry_id, inquiries.contact_id, inquiries.indiv_contact_id, inquiries.phone, inquiries.fax, inquiries.agent_id, inquiries.inquiry_date, inquiries.event_type, inquiries.letter_type, inquiries.event_date, inquiries.event_date_general, inquiries.event_location, inquiries.guests, inquiries.hours, inquiries.budget, inquiries.event_description, inquiries.talent_description, inquiries.past_use, inquiries.referred_by, inquiries.date_sent, inquiries.end_user, inquiries.event_id, inquiries.notes, source_for_inquiries_form.organization, source_for_inquiries_form.mailing_address_1, source_for_inquiries_form.mailing_address_2, source_for_inquiries_form.city, source_for_inquiries_form.state, source_for_inquiries_form.zip, source_for_inquiries_form.contact_type, individual_contacts.contact_name_first, individual_contacts.contact_name_last, individual_contacts.contact_prefix, individual_contacts.contact_title, individual_contacts.email FROM inquiries LEFT JOIN individual_contacts ON inquiries.indiv_contact_id = individual_contacts.indiv_contact_id INNER JOIN contacts AS source_for_inquiries_form ON inquiries.contact_id = source_for_inquiries_form.contact_id ORDER BY inquiries.inquiry_id DESC; - - Visit CARMEL MUSIC ENTERTAINMENT website http://carmelme.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
