Re: help with query to count rows while excluding certain rows

On Fri, Jan 1, 2016 at 9:31 PM, Peter Brawley wrote: > On 1/1/2016 19:24, Larry Martell wrote: >> >> On Fri, Jan 1, 2016 at 2:12 PM, Peter Brawley >> wrote: >>> >>> On 12/31/2015 0:51, Larry Martell wrote: I need to count the

Re: help with query to count rows while excluding certain rows

On Fri, Jan 1, 2016 at 2:12 PM, Peter Brawley wrote: > On 12/31/2015 0:51, Larry Martell wrote: >> >> I need to count the number of rows in a table that are grouped by a >> list of columns, but I also need to exclude rows that have more then >> some count when grouped

Re: help with query to count rows while excluding certain rows

On 12/31/2015 0:51, Larry Martell wrote: I need to count the number of rows in a table that are grouped by a list of columns, but I also need to exclude rows that have more then some count when grouped by a different set of columns. Conceptually, this is not hard, but I am having trouble doing

Re: Help improving query performance

Hi Larry, On 2/1/2015 4:49 PM, Larry Martell wrote: I have 2 queries. One takes 4 hours to run and returns 21 rows, and the other, which has 1 additional where clause, takes 3 minutes and returns 20 rows. The main table being selected from is largish (37,247,884 rows with 282 columns). Caching

Re: Help improving query performance

Hi Larry, On 2/4/2015 3:18 PM, Larry Martell wrote: On Wed, Feb 4, 2015 at 2:56 PM, shawn l.green shawn.l.gr...@oracle.com wrote: Hi Larry, On 2/1/2015 4:49 PM, Larry Martell wrote: I have 2 queries. One takes 4 hours to run and returns 21 rows, and the other, which has 1 additional where

Re: Help improving query performance

On Wed, Feb 4, 2015 at 3:25 PM, shawn l.green shawn.l.gr...@oracle.com wrote: Hi Larry, On 2/4/2015 3:18 PM, Larry Martell wrote: On Wed, Feb 4, 2015 at 2:56 PM, shawn l.green shawn.l.gr...@oracle.com wrote: Hi Larry, On 2/1/2015 4:49 PM, Larry Martell wrote: I have 2 queries. One

Re: Help improving query performance

On Wed, Feb 4, 2015 at 2:56 PM, shawn l.green shawn.l.gr...@oracle.com wrote: Hi Larry, On 2/1/2015 4:49 PM, Larry Martell wrote: I have 2 queries. One takes 4 hours to run and returns 21 rows, and the other, which has 1 additional where clause, takes 3 minutes and returns 20 rows. The

Re: Help improving query performance

Hello Larry, On 2/4/2015 3:37 PM, Larry Martell wrote: On Wed, Feb 4, 2015 at 3:25 PM, shawn l.green shawn.l.gr...@oracle.com wrote: Hi Larry, On 2/4/2015 3:18 PM, Larry Martell wrote: On Wed, Feb 4, 2015 at 2:56 PM, shawn l.green shawn.l.gr...@oracle.com wrote: Hi Larry, On 2/1/2015

Re: Help optimize query.

Hello Mimko, Sorry for the late reply. I had a bunch of work to take care of before vacation, then there was the vacation itself. :) On 11/13/2014 2:34 PM, Mimiko wrote: Hello. I have this table: show create table cc_agents_tier_status_log: CREATE TABLE cc_agents_tier_status_log ( id

Re: Help optimize query.

On 15.11.2014 01:06, Peter Brawley wrote: Let's see the results of Explain Extended this query, result of Show Create Table cc_member_queue_end_log. cc_member_queue_end_log is not of interest, it is used just as a series of numbers. It may be any table with ids. I've changed a bit the

Re: Help optimize query.

Let's see the results of Explain Extended this query, result of Show Create Table cc_member_queue_end_log. PB - On 2014-11-13 1:34 PM, Mimiko wrote: Hello. I have this table: show create table cc_agents_tier_status_log: CREATE TABLE cc_agents_tier_status_log ( id int(10) unsigned

Re: Help with query.

Hi Paul! Paul Halliday wrote: I have a query (thanks to this list) that uses a join to add country information to an IP. It looks like this: SELECT COUNT(signature) AS count, INET_NTOA(src_ip), map1.cc as src_cc, INET_NTOA(dst_ip), map2.cc as dst_cc FROM event LEFT JOIN mappings AS map1

Re: Help with query.

On Tue, 1 Feb 2011 14:46:39 -0400 Paul Halliday paul.halli...@gmail.com wrote: I have a query (thanks to this list) that uses a join to add country information to an IP. It looks like this: SELECT COUNT(signature) AS count, INET_NTOA(src_ip), map1.cc as src_cc, INET_NTOA(dst_ip), map2.cc as

Re: help with query

Thank you, that did the trick. Simon On 11 January 2011 12:09, Steve Meyers steve-mysql-l...@spamwiz.com wrote: On 1/11/11 9:31 AM, Simon Wilkinson wrote: select users.id from users where users.id in (select newletters.user_id from newletters left join articles on newletters.id =

Re: help with query

On 1/11/11 9:31 AM, Simon Wilkinson wrote: select users.id from users where users.id in (select newletters.user_id from newletters left join articles on newletters.id = articles.newsletter_id where articles.newsletter_id is null); I think this would do what you require: SELECT u.id AS

Re: help refactoring query

b wrote: I'm having some difficulty getting my head around a particular query. I'd like to make this a view once I get something working. However, all I've been able to come up with uses a sub-query. So, no view on the horizon. I have 3 tables: users id, (etc. the usual) disciplines

Re: Help with query

Are you sure those are the results to those queries? ¢,m Your second query will return more because it includes values outside of the date range specified in the first query. In the second result set you have 2009 and 'invalid dates' that would not be picked up by the first query. Regards

Re: Help with query

Am I totally missing something? Why do you believe the two queries should return the same # of rows? First one has a qualification of proj_adv_date '2008-12-16' whilst the second one does not... On Mon, Dec 15, 2008 at 12:12 PM, Néstor rot...@gmail.com wrote: I have a char fiel where I am

Re: Help with query

Sorry!!! I apoligized for being blind. Yes, in my small mind I was thinking that I do not have records biggeer than 2008 but I do and my mind refused to see the records for 2009 and 2010. Sorry again for being so blind to the obvious. :-( On Mon, Dec 15, 2008 at 9:50 AM, Phil

Re: Help with query to remove all records where foreign key doesn't have corresponding records

Hi Daevid If you are using a foreign key you can set the reference as cascade and when a row is deleted from feed it will be deleted from feed_tag. http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html If you don't like it you can delete it easy with a query like this

Re: Help with query to remove all records where foreign key doesn't have corresponding records

Just do a left join with the delete query. DELETE feed_tag FROM feed_tag LEFT JOIN feed ON feed_tag.feed_id=feed.id WHERE feed.id IS NULL That should do it. You can change DELETE feed_tag to SELECT and test it first. -- Brent Baisley On Aug 13, 2008, at 4:51 PM, Daevid Vincent wrote:

Re: Help with query MySQL and PHP

This question is strictly related to the mysql query not the php code. I need to either create a new table from the old one or add columns. The thing is don't know how to do it. let me simplify things up: I need a query to retrieve values from the table PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS to a

Re: Help with query MySQL and PHP

you can do some thing like this. create table new_table(id int, hair varchar(50),eyes varchar(50)) select column1,column2,...from old_table; On 7/3/08, axis [EMAIL PROTECTED] wrote: This question is strictly related to the mysql query not the php code. I need to either create a new table

Re: help wit query optimization (cont'd)

Perrin Harkins schrieb: On Jan 4, 2008 5:51 PM, Eben [EMAIL PROTECTED] wrote: The resultset is paginated on the front end using the SQL_CALC_FOUND_ROWS functionality... Usually a bad idea: http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/

Re: help wit query optimization (cont'd)

On Jan 9, 2008 4:33 AM, Sebastian Mendel [EMAIL PROTECTED] wrote: usually ... but i do not know of any index capable of having FULLTEXT and 'normal' fields in one index Does that matter? It would have to be doing a full scan for SQL_CALC_FOUND_ROWS to work out well. - Perrin -- MySQL

Re: help wit query optimization (cont'd)

Perrin Harkins schrieb: On Jan 9, 2008 4:33 AM, Sebastian Mendel [EMAIL PROTECTED] wrote: usually ... but i do not know of any index capable of having FULLTEXT and 'normal' fields in one index Does that matter? yes, as written in the mentioned article the test is only relevant with correct

Re: help wit query optimization (cont'd)

On Jan 9, 2008 8:34 AM, Sebastian Mendel [EMAIL PROTECTED] wrote: yes, as written in the mentioned article the test is only relevant with correct used indexes, but MySQL does not use more than one index, so this query cannot all be done with indexes Well, first of all, MySQL 5 does use more

Re: help with query optimization

Your biggest problem is probably the subquery/IN your are performing. You should change that to a join. And I don't know about using SQL_CALC_FOUND_ROWS in a full text query that's not boolean, and you shouldn't use it unless you have a LIMIT clause. SELECT SQL_CALC_FOUND_ROWS

Re: help wit query optimization (cont'd)

On Jan 4, 2008 5:51 PM, Eben [EMAIL PROTECTED] wrote: The resultset is paginated on the front end using the SQL_CALC_FOUND_ROWS functionality... Usually a bad idea: http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/ - Perrin -- MySQL General

Re: help wit query optimization (cont'd)

I've never used this feature before on my application. On Jan 5, 2008 7:06 AM, Perrin Harkins [EMAIL PROTECTED] wrote: On Jan 4, 2008 5:51 PM, Eben [EMAIL PROTECTED] wrote: The resultset is paginated on the front end using the SQL_CALC_FOUND_ROWS functionality... Usually a bad idea:

Re: Help with query, (question simplified as last mail was very complicated to understand :))

Richard a écrit : Sorry about my last email which was long and not clear. This is what I want to do Join two tables on code table1 = code table3 where messageid = for example 28 table 1 contains : message from messageid --

RE: Help with query, (question simplified as last mail was very complicated to understand :))

not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. Date: Sun, 30 Dec 2007 13:54:32 +0100 From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Re: Help with query, (question simplified as last

Re: Help with query...

Anders Norrbring wrote: I'm trying to set up a query, but I don't really get the result I expected, so can someone please help me out here? The query I've built is this: SELECT a1.username FROM accountuser AS a1 LEFT JOIN (freeaccounts AS f1, payments AS p1) ON (a1.username = p1.username AND

Re: Help with Query

Hi Ed, Ed Curtis wrote: I need to get some duplicate record information from a table and I haven't found any way to do it yet. I figured there might be some type of query I could do using a for each type command. What I have is a table with names and companies. Some people have multiple

RE: help with query: select customers that ARO NOT in orders table

, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Waldemar Jankowski [mailto:[EMAIL PROTECTED] Sent: Friday, October 20, 2006 1:53 PM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: help with query: select customers that ARO NOT in orders table

RE: help with query: select customers that ARO NOT in orders t

To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: help with query: select customers that ARO NOT in orders table On Fri, 20 Oct 2006, [EMAIL PROTECTED] wrote: hi to all, I have table customers (PK cust_id) I have table orders (PK order_id, FK cust_id) I need query that will selecct all

Re: help with query: select customers that ARO NOT in orders table

On Fri, 20 Oct 2006, [EMAIL PROTECTED] wrote: hi to all, I have table customers (PK cust_id) I have table orders (PK order_id, FK cust_id) I need query that will selecct all customers from 'customers' they don't have any order, there is not their cust_id in 'orders'. couls somebody help me?

Re: help with query: select customers that ARO NOT in orders table

Ok. Just found I gave wrong info. To make my life easier, the person who created db named cust_id in 'orders' table as SoldTo [EMAIL PROTECTED] in this case, select cust_id from customers where cust_id not in (select Soldto from orders); will not work :( On Fri, 20 Oct 2006, [EMAIL

Re: help with query: select customers that ARO NOT in orders table

On Fri, 20 Oct 2006, [EMAIL PROTECTED] wrote: Ok. Just found I gave wrong info. To make my life easier, the person who created db named cust_id in 'orders' table as SoldTo [EMAIL PROTECTED] in this case, select cust_id from customers where cust_id not in (select Soldto from orders); will

Re: Help with query

Hi, Try with FULLTEXT search. Alter the table to make the search columns as FULLTEXT columns, with MyISAM engine and retrieve the records using MATCH keyword. Ref:http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html Thanks, ViSolve DB Team. - Original Message - From: Ed

RE: Help for query

: 860.674.8341 -Original Message- From: Xiaobo Chen [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 26, 2006 10:09 AM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: Help for query I found if I divided into 2 steps, I will find the record in table B: Ta - the given

Re: Help for query

I found if I divided into 2 steps, I will find the record in table B: Ta - the given timestamp from table A; 1) select min(abs(Ta - timestamp)) as min_t from B; 2) select * from B where (timestamp + min_t = Ta) or (timestamp - min_t = Ta); But, how can I make these 2 steps into 1 query?

RE: Help for query

. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Xiaobo Chen [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 26, 2006 10:09 AM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: Help for query I found if I divided into 2 steps, I will find the record

RE: Help with query

Have you dumped out your variables to make sure none of them is a zero-length string? That would surely cause your problem. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Ed

Re: Help with query

I think you are trying to use a regular expression in the like phrase. I wasn't aware that MS SQL can do regular expressions at all? I've been using 2000 - perhaps the newer version can? In MySQL, instead of saying: LA.LastName LIKE '[A-E]%' try this: LA.LastName REGEXP '^[A-E]' You can

Re: Help with query

appreciate your help. Jesse - Original Message - From: Douglas Sims To: Jesse Cc: MySQL List Sent: Monday, August 21, 2006 12:07 PM Subject: Re: Help with query I think you are trying to use a regular expression in the like phrase. I wasn't aware that MS SQL can do regular

Re: Help with query: Row number / Rank from a query...

Thanks Peter, That looks pretty good to me. I never would have figured that out on my own. Dan T On Jun 1, 2006, at 4:06 PM, Peter Brawley wrote: Dan, I want to get a particular users 'rank' or row number from the query. SELECT 1+COUNT(*) AS Rank FROM results r1 INNER JOIN results r2

Re: Help with query: Row number / Rank from a query...

Dan wrote: I have a regular query lets say: Better to show the real query, rather than a simplified version. Simplified requests get you simplified answers. What seems like a simplification to you, may in fact hide a crucial piece of information, thus preventing anyone from giving you a

Re: Help with query: Row number / Rank from a query...

Dan, I want to get a particular users 'rank' or row number from the query. SELECT 1+COUNT(*) AS Rank FROM results r1 INNER JOIN results r2 ON r1.pointsr2.points WHERE r1.user='Steve'; PB - Dan wrote: I have a regular query lets say: SELECT user, points FROM results ORDER BY points DESC

Re: Help with Query Optimization

Kishore, We use Vbulletin for our message boards, and I have a query which takes approximately 1.2 - 1.5 seconds to execute, on a table ('thread') having around 130,000 rows, it is as follows SELECT threadid FROM thread LEFT JOIN deletionlog ON ( thread.threadid = deletionlog.primaryid AND

Re: help with query

how to achieve this? select table1.*, table2.*, table3.*, sum(table2.field3), sum(table2.field4) from table1, table2, table3 where table1.field1 = table2.field1 and table2.field1 = table3.field1 Your question as formulated has no answer. If you query aggregate values like Sum on a table, you

Re: Help with query optimization query SUM

Dear Reynier, You can use JOIN on your both, The JOIN have to run on the same feilds i.e IDA. SELECT * FROM carro_de_compras LEFT JOIN os_articulo ON carro_de_compras.IDA = os_articulo.IDA This query returns all your users with their articles if any and you can iterate on it. but one note: Use

Re: Help with query

Jay Paulson [EMAIL PROTECTED] wrote on 01/25/2006 10:09:36 AM: From the result set below I have 22 rows and the only difference is the date. I was wondering if there was a way to get all of these results using GROUP BY instead of having to use LIMIT?? As this table grows I'm going to want

Re: help with query

[EMAIL PROTECTED] wrote on 10/22/2005 06:24:07 PM: I have a table of votes with four fields, a primary key, and userID, that are just there for tracking purposes. But then I have questionID and vote fields. For each question, a user could pick a number of it's importance from 1 to 5.

Re: help with query

ja, Your question is a little cryptic. If a questionid column value identifies a particular question, and a vote column value contains a user's vote (1,2,3, c), can't you just write... SELECT questionid, vote,count(vote) FROM ... GROUP BY questionid, vote PB - [EMAIL PROTECTED] wrote:

Re: Help with query

Shawn, - Original Message - From: [EMAIL PROTECTED] To: Dušan Pavlica Cc: Michael Stassen ; list mysql Sent: Wednesday, October 12, 2005 4:45 PM Subject: Re: Help with query Dušan Pavlica [EMAIL PROTECTED] wrote on 10/12/2005 10:00:53 AM: Thanks, Michal, for your

Re: Help with query

Dušan Pavlica wrote: Hello, could someone help me please to construct correct query or tell me what I'm doing wrong? I have three tables: table products table products_codes where some products could have assigned another additional codes table products_prices I want to get all rows from

Re: Help with query

querries for each DB system separately. Now I see that I will have to. Dusan - Original Message - From: Michael Stassen [EMAIL PROTECTED] To: Dušan Pavlica [EMAIL PROTECTED] Cc: list mysql mysql@lists.mysql.com Sent: Wednesday, October 12, 2005 2:54 PM Subject: Re: Help with query Dušan

Re: Help with query

Sent: Wednesday, October 12, 2005 2:54 PM Subject: Re: Help with query Dušan Pavlica wrote: Hello, could someone help me please to construct correct query or tell me what I'm doing wrong? snip Kind regards, Dusan Pavlica snip In any case, does this do what you want

Re: Help with query using IN()

On 04/10/2005, Jasper Bryant-Greene wrote: Kishore Jalleda wrote: Could you kindly advice if this query can be made to run faster SELECT title, template FROM template WHERE templateid IN (608,2072,2073,1888,1898,1895,1897,1894,1896,1901,1903,1889,1900,189

Re: Help with query using IN()

Kishore Jalleda wrote: Could you kindly advice if this query can be made to run faster SELECT title, template FROM template WHERE templateid IN

Re: Help with query

Hi, look at group by ... with rollup at : http://dev.mysql.com/doc/mysql/en/group-by-modifiers.html Mathias Selon Ronan Lucio [EMAIL PROTECTED]: Hello, I have a table where is saved all site´s access: access id year month day weekday hour minute ip Any column has multiple

Re: Help with query

Mathias, Hi, look at group by ... with rollup at : http://dev.mysql.com/doc/mysql/en/group-by-modifiers.html Thank you very much for your help. My needs aren´t this, exactly. GROUP BY WITH ROLLUP, returns me several lines of the same day (one per IP), plus the total. I need that every

Re: Help with query

Ronan Lucio [EMAIL PROTECTED] wrote on 05/16/2005 04:21:17 PM: Mathias, Hi, look at group by ... with rollup at : http://dev.mysql.com/doc/mysql/en/group-by-modifiers.html Thank you very much for your help. My needs aren´t this, exactly. GROUP BY WITH ROLLUP, returns me several

Re: Help with query

Hi Ronan, I don't know if i understand your need, but your query gives something like that : mysql SELECT year, month, day, ip, COUNT(*) AS access - FROM access - WHERE year = 2005 - AND month = 5 - GROUP BY year, month, day, ip - ORDER BY year, month, day;

Re: Help with query

Mathias, To drop multiple IP, you can use distinct : mysql SELECT year, month, day, group_concat(distinct ip),count(*) AS access - FROM access - WHERE year = 2005 - AND month = 5 - GROUP BY year, month,day - ORDER BY year, month, day;

Re: help on query/group by

I wrong? Anyway, thank you for all your advices, I'm really learning a lot of things with that case! Melanie From: [EMAIL PROTECTED] To: mel list_php [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: help on query/group by Date: Fri, 18 Mar 2005 12:43:06 -0500 mel list_php [EMAIL

Re: help on query/group by

with that case! Melanie From: [EMAIL PROTECTED] To: mel list_php [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: help on query/group by Date: Fri, 18 Mar 2005 12:43:06 -0500 mel list_php [EMAIL PROTECTED] wrote on 03/18/2005 10:35:30 AM: Hi again, Thanks

Re: help on query/group by

for your help, I will give temporary tables an other chance!!! Melanie From: [EMAIL PROTECTED] To: mel list_php [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: help on query/group by Date: Wed, 16 Mar 2005 13:52:44 -0500 I have a favorite technique for improving the results of queries

Re: help on query/group by

Responses embedded below mel list_php [EMAIL PROTECTED] wrote on 03/18/2005 05:57:29 AM: Hi Shawn, Thank you very much, I'm impressed by the time you took to answer me, and the quality of the reply!!! I forwarded the answer to my friend. I'm wondering, I knew the mechanism of temporary

Re: help on query/group by

[EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: help on query/group by Date: Fri, 18 Mar 2005 09:14:02 -0500 Responses embedded below mel list_php [EMAIL PROTECTED] wrote on 03/18/2005 05:57:29 AM: Hi Shawn, Thank you very much, I'm impressed by the time you took to answer me

Re: help on query/group by

mel list_php [EMAIL PROTECTED] wrote on 03/18/2005 10:35:30 AM: Hi again, Thanks for the explanation about the join and the group by. I wanted to test your query (almost a simple copy/paste :-)) ). The first 2 queries are ok, but the third one still is too long : mysql CREATE

Re: help on query/group by

I have a favorite technique for improving the results of queries , like this, which involve fairly large JOINed tables. It's a form of divide-and-conquer in that you pre-compute what you can then make the JOINS you need to finish up the results. I agree that the AND s2.syn LIKE '%' in the ON

Re: !! Help with query: Where Date = MAX(Date)

Graham Cossey wrote: Could someone please offer a little help. I have a table like: Year, Month, Start_date 20041020041102 20041120041203 20041220050104 20050120050204 20050220050303 I need to get the latest Year,Month for a given date, so for example today

RE: !! Help with query: Where Date = MAX(Date)

snip As I'm using 4.0.20 I can't use subqueries so how can I create a query that does this? SELECT year, month FROM `dc_months` WHERE start_date = (SELECT MAX(start_date) from dc_months where start_date = '20050204') Any help

Re: !! Help with query: Where Date = MAX(Date)

On Fri, 2005-02-04 at 09:19, Graham Cossey wrote: Could someone please offer a little help. I have a table like: Year, Month, Start_date 20041020041102 20041120041203 20041220050104 20050120050204 20050220050303 I need to get the latest

Re: Help optimizing query

* Jesse Sheidlower [...] CREATE TABLE `citation_subject` ( `id` int(11) NOT NULL auto_increment, `citation_id` int(11) NOT NULL default '0', `subject_id` int(11) NOT NULL default '0', PRIMARY KEY (`id`), KEY `citation_id` (`citation_id`,`subject_id`) ) Try adding an index with

Re: Help optimizing query

On Tue, Nov 23, 2004 at 09:55:15PM +0100, [EMAIL PROTECTED] wrote: * Jesse Sheidlower [...] CREATE TABLE `citation_subject` ( `id` int(11) NOT NULL auto_increment, `citation_id` int(11) NOT NULL default '0', `subject_id` int(11) NOT NULL default '0', PRIMARY KEY (`id`),

RE: Help with query performance anomaly

Thanks for the advice Steven, I'll bear it in mind and do some reading. Graham -Original Message- From: Steven Roussey [mailto:[EMAIL PROTECTED] Sent: 13 November 2004 02:52 To: 'Graham Cossey' Cc: [EMAIL PROTECTED] Subject: RE: Help with query performance anomaly For production

RE: Help with query performance anomaly (SOLVED)

It turns out that it appears to be a data discrepancy that caused the query optimiser to, well, not optimise. I thought the main table (r) with 3million records would be the problem, but it was table p with 3100 records on the live server and 3082 records on my dev pc that caused the problem.

RE: Help with query performance anomaly

For production systems, I would never let the mysql optimizer guess a query plan when there are joins of big tables and you know exactly how it should behave. Once you think a query is finished, you should optimize it yourself. Use STRAIGHT_JOIN and USE INDEX as found here in the manual:

Re: Help with query performance anomaly

What does EXPLAIN show for the query on both systems? (I am wondering if you may have an index on your development system that you do not have on your production server.) Shawn Green Database Administrator Unimin Corporation - Spruce Pine Graham Cossey [EMAIL PROTECTED] wrote on 11/11/2004

RE: Help with query performance anomaly

like this? Maybe something in the configs? Thanks Graham -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 11 November 2004 16:28 To: Graham Cossey Cc: [EMAIL PROTECTED] Subject: Re: Help with query performance anomaly What does EXPLAIN show for the query

RE: Help with query performance anomaly

PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 11 November 2004 16:28 To: Graham Cossey Cc: [EMAIL PROTECTED] Subject: Re: Help with query performance anomaly What does EXPLAIN show for the query on both systems? (I am wondering if you may have an index on your development system

RE: Help with query performance anomaly

Thanks Shaun EXPLAIN shows the same 'possible keys' for each table but 'key' and 'key-len' columns are different, as are the 'rows' as well of course. I guess this points to a probable difference in key definitions? Can 2 installations with the same table definitions produce different

RE: Help with query performance anomaly

Response at end Graham Cossey [EMAIL PROTECTED] wrote on 11/11/2004 12:19:17 PM: Thanks Shaun EXPLAIN shows the same 'possible keys' for each table but 'key' and 'key-len' columns are different, as are the 'rows' as well of course. I guess this points to a probable difference in

RE: Help with query performance anomaly

[big snip] These are two different plans. Your development machine is using the index yr_mn_pc on the r table and is joining that table last. On your production server, the r table is joined second and is joined by the index PRIMARY. Let me know how the ANALYZE TABLE I suggested in a

Re: Help with query performance anomaly

How do the OS statistics look on both boxes. Do top, sar, vmstat or iostat show any CPU, memory or I/O performance issues? Does anything odd appear in the /var/log/messages file? -Jamie On Thu, 11 Nov 2004 18:42:48 -, Graham Cossey [EMAIL PROTECTED] wrote: [big snip] These are

RE: Help with query performance anomaly

!! Comments? Advice? Thanks Graham -Original Message- From: Jamie Kinney [mailto:[EMAIL PROTECTED] Sent: 11 November 2004 19:25 To: Graham Cossey Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Help with query performance anomaly How do the OS statistics look on both boxes. Do

RE: Help with query performance anomaly

[snip] Have just run 'top' on the live server... Before running the query I get: 13:56:09 up 45 days, 11:47, 1 user, load average: 0.00, 0.28, 0.44 24 processes: 23 sleeping, 1 running, 0 zombie, 0 stopped CPU0 states: 0.0% user 0.0% system0.0% nice 0.0% iowait 100.0% idle

Re: Help with query

SELECT `TABLE_1`.* FROM `TABLE_1` JOIN `TABLE_2` USING (`id`) WHERE `TABLE_2`.`id` IS NULL Asuming that the reference is the id field... Regards, Jigal. - Original Message - From: Ronan Lucio [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, October 27, 2004 3:12 PM Subject: Help

Re: Help with query

This is a very FAQ: SELECT t1.* FROM TABLE_1 t1 LEFT JOIN TABLE_2 t2 ON t1.id = t2.table1_id WHERE t2.id is null Shawn Green Database Administrator Unimin Corporation - Spruce Pine Ronan Lucio [EMAIL PROTECTED] wrote on 10/27/2004 10:12:42 AM: Hi, I have two tables: TABLE_1

Re: Help with query

Shawn, Thank you very for your answer. Actually, I thought that a main SELECT couldn´t be filtered by the WHERE clause refered to a field in a LEFT JOIN. Now, looking better in the JOIN documentation I see this issue. Thank´s, Ronan This is a very FAQ: SELECT t1.* FROM TABLE_1 t1

Re: Help with query

SELECT CityName, Count(ClientID) as ClientCount FROM City INNER JOIN Client on City.CityID = Client.CityID GROUP BY CityName HAVING ClientCount 30; Shawn Green Database Administrator Unimin Corporation - Spruce Pine Ronan Lucio [EMAIL PROTECTED] wrote on 09/10/2004 11:14:37 AM:

Re: Help with query

Shawn SELECT CityName, Count(ClientID) as ClientCount FROM City INNER JOIN Client on City.CityID = Client.CityID GROUP BY CityName HAVING ClientCount 30; Thank you very much, It should solve by problem... :-) Ronan -- MySQL General Mailing List For list archives:

RE: Help with query

You will need parentheses around the 'or' clauses of your where clause. You also don't seem to join the categories table with any other tables. If you don't join tables you will create what is called a 'cross product' query. If table A has 10 rows and table B has 20 rows then querying A

RE: Help with query

, February 06, 2004 8:53 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Help with query You will need parentheses around the 'or' clauses of your where clause. You also don't seem to join the categories table with any other tables. If you don't join tables you will create what

RE: Help with query

] Sent: Fri 2/6/2004 9:00 AM To: [EMAIL PROTECTED] Cc: Subject: RE: Help with query Hi Evelyn, How would I do that - would something like this be what you had in mind? left join categories o on o.CategoryID

RE: Help with query

] Sent: Friday, February 06, 2004 8:53 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Help with query You will need parentheses around the 'or' clauses of your where clause. You also don't seem to join the categories table with any

Re: Help with query

On 6 Feb 2004, at 14:38, Erich Beyrent wrote: This seems really efficient, since the only large number of rows to search against is the main listings table, if I read this right. Is there any further optimization that I can do, or this as good as it gets? Believe me, I am NOT complaining!!! Yes,

  1   2   >