Re: Can't get my query to return wanted data

[Chris Roy-Smith]

On Fri, 19 Jan 2018 11:25:42 -0500, shawn l.green wrote:

> Hello Chris,
> 
> On 1/19/2018 12:50 AM, Chris Roy-Smith wrote:
>> Hi I am running mysql 5.7.20 in ubuntu linux 17.10
>>
>> I have 2 tables, member and status with contents like
>>
>> member:
>> ident,   given,  surname 1   fredjones 2 johnhoward 
3   henry   wales 4
>> jennybrown
>>
>> status:
>> identyear 1  2017 2  2017 3  2017 4  2017 1  2018 3  2018
>>
>> I want my query to return the name and ident from the member table for
>> all members that has not got an entry in status with year=2018
>>
>> I have been working on the following query to achieve this, but it only
>> returns data when there is no `year` entries for a selected year.
>>
>> select details.ident, given, surname from details left join status on
>> details.ident = status.ident where NOT EXISTS (select year from status
>> where (status.year = 2018) and (details.ident = status.ident) )
>>
>>
>> Thank you for looking at this.
>> regards, Chris Roy-Smith
>>
>>
>>
> try this...
> 
> SELECT
>d.ident, d.given, d.surname
> FROM details d LEFT JOIN (
>SELECT DISTINCT ident FROM status WHERE year=2018
> ) s
>ON s.ident = d.ident
> WHERE
>s.ident is NULL;
> 
> How it works
> #
> Start by building a list of unique `ident` values that match the
> condition you do NOT want to find. (you will see why in a moment)
> 
> LEFT JOIN that list to your list of members (with your list on the right
> side of the LEFT JOIN).  Where that join's ON condition is satisfied, a
> value for the column s.ident will exist. Where it isn't satisfied, there
> will be a NULL value in s.ident.
> 
> Finally, filter the combination of the s and d tables (I'm referring to
> their aliases) to find all the rows where s.ident was not given a value
> because it did not satisfy the ON condition of your outer join.
> #
> 
> Yours,
> --
> Shawn Green MySQL Senior Principal Technical Support Engineer Oracle
> USA, Inc. - Integrated Cloud Applications & Platform Services Office:
> Blountville, TN
> 
> Become certified in MySQL! Visit https://www.mysql.com/certification/
> for details.

Hi Shawn,
That works a treat! Thank you for a solution with an explanation, which I 
appreciate, because it helps me learn.
Regards, Chris Roy-Smith


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Can't get my query to return wanted data

[shawn l.green]

Hello Chris,

On 1/19/2018 12:50 AM, Chris Roy-Smith wrote:

Hi
I am running mysql 5.7.20 in ubuntu linux 17.10

I have 2 tables, member and status with contents like

member:
ident,  given,  surname
1   fredjones
2   johnhoward
3   henry   wales
4   jenny   brown

status:
ident   year
1   2017
2   2017
3   2017
4   2017
1   2018
3   2018

I want my query to return the name and ident from the member table for all
members that has not got an entry in status with year=2018

I have been working on the following query to achieve this, but it only
returns data when there is no `year` entries for a selected year.

select details.ident, given, surname from details left join status on
details.ident = status.ident where NOT EXISTS (select year from status
where (status.year = 2018) and (details.ident = status.ident) )


Thank you for looking at this.
regards, Chris Roy-Smith




try this...

SELECT
  d.ident, d.given, d.surname
FROM details d
LEFT JOIN (
  SELECT DISTINCT ident
  FROM status
  WHERE year=2018
) s
  ON s.ident = d.ident
WHERE
  s.ident is NULL;

How it works
#
Start by building a list of unique `ident` values that match the 
condition you do NOT want to find. (you will see why in a moment)


LEFT JOIN that list to your list of members (with your list on the right 
side of the LEFT JOIN).  Where that join's ON condition is satisfied, a 
value for the column s.ident will exist. Where it isn't satisfied, there 
will be a NULL value in s.ident.


Finally, filter the combination of the s and d tables (I'm referring to 
their aliases) to find all the rows where s.ident was not given a value 
because it did not satisfy the ON condition of your outer join.

#

Yours,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit https://www.mysql.com/certification/ 
for details.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: adding column breaks query

[Larry Martell]

On Mon, Jan 28, 2013 at 8:56 AM, Shawn Green shawn.l.gr...@oracle.com wrote:
 Hello Larry,


 On 1/28/2013 10:42 AM, Larry Martell wrote:

 I have a query that works fine:


 SELECT data_ppl.name as 'P/P/L', data_recipe.name as Recipe,
 data_cstmeta.date_time as 'Last Run'
 FROM data_recipe, data_ppl, data_cstmeta
 INNER JOIN (SELECT id,recipe_id, MAX(date_time) as MaxDateTime
 FROM data_cstmeta
 GROUP BY recipe_id) grouped
 ON data_cstmeta.id = grouped.id
 AND data_cstmeta.date_time = grouped.MaxDateTime
 WHERE data_cstmeta.ppl_id = data_ppl.id
 AND data_cstmeta.recipe_id = data_recipe.id
 ORDER BY data_cstmeta.date_time desc;


 Now I need to add one more column to it, data_tool.name, so I did this:


 SELECT data_ppl.name as 'P/P/L', data_tool.name as Tool,
 data_recipe.name as Recipe, data_cstmeta.date_time as 'Last Run'
 FROM data_recipe, data_ppl, data_cstmeta, data_tool
 INNER JOIN (SELECT id,tool_id,recipe_id, MAX(date_time) as MaxDateTime
 FROM data_cstmeta
 GROUP BY recipe_id) grouped
 ON data_cstmeta.id = grouped.id
 AND data_cstmeta.date_time = grouped.MaxDateTime
 WHERE data_cstmeta.ppl_id = data_ppl.id
 AND data_cstmeta.recipe_id = data_recipe.id
 AND data_cstmeta.tool_id = data_tool.id
 ORDER BY data_cstmeta.date_time desc;

 And now it fails with:

 ERROR 1054 (42S22): Unknown column 'data_cstmeta.id' in 'on clause'

 I've messed around with this for a day and I can't figure out what
 stupid mistake I'm making.


 You didn't add just one column, you added an entire table to the FROM
 clause.  Also, you used a comma-join instead of an ANSI JOIN clause.

 Because of changes we made in 5.0.12 to make MySQL behave more like the SQL
 standard requires, the priority of the comma operator in join operations was
 demoted. This means that when the explicit JOIN declaration is being
 evaluated (which has a higher order of precedence) only the derived table
 (grouped) and the base table `data_tool` are visible to the ON clause. The
 `data_cstmeta` implicit join will be evaluated later as it is based on a
 comma-join.

 I suggest you rewrite your queries to only use the explicit JOIN ... ON
 ... syntax for all of your joins to avoid these problems in the future.
 http://dev.mysql.com/doc/refman/5.5/en/join.html

 Regards,
 --
 Shawn Green
 MySQL Principal Technical Support Engineer
 Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
 Office: Blountville, TN



 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql


Thanks. I know the JOIN clause is preferred over the comma join, just
surprised this query worked until I added a new table. In any case I
changed it as shown below, and it works now. Thanks!


SELECT data_ppl.name as 'P/P/L',
data_tool.name as Tool,
data_recipe.name as Recipe,
data_cstmeta.date_time as 'Last Run'
FROM data_cstmeta
INNER JOIN (SELECT id,tool_id,recipe_id, MAX(date_time) as MaxDateTime
   FROM data_cstmeta
   GROUP BY recipe_id) grouped
ON data_cstmeta.id = grouped.id AND data_cstmeta.date_time =
grouped.MaxDateTime
INNER JOIN data_ppl ON data_cstmeta.ppl_id = data_ppl.id
INNER JOIN data_tool ON data_cstmeta.tool_id = data_tool.id
INNER JOIN data_recipe ON data_cstmeta.recipe_id = data_recipe.id
ORDER BY data_cstmeta.date_time desc;

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: adding column breaks query

[Shawn Green]

Hello Larry,

On 1/28/2013 10:42 AM, Larry Martell wrote:

I have a query that works fine:


SELECT data_ppl.name as 'P/P/L', data_recipe.name as Recipe,
data_cstmeta.date_time as 'Last Run'
FROM data_recipe, data_ppl, data_cstmeta
INNER JOIN (SELECT id,recipe_id, MAX(date_time) as MaxDateTime
FROM data_cstmeta
GROUP BY recipe_id) grouped
ON data_cstmeta.id = grouped.id
AND data_cstmeta.date_time = grouped.MaxDateTime
WHERE data_cstmeta.ppl_id = data_ppl.id
AND data_cstmeta.recipe_id = data_recipe.id
ORDER BY data_cstmeta.date_time desc;


Now I need to add one more column to it, data_tool.name, so I did this:


SELECT data_ppl.name as 'P/P/L', data_tool.name as Tool,
data_recipe.name as Recipe, data_cstmeta.date_time as 'Last Run'
FROM data_recipe, data_ppl, data_cstmeta, data_tool
INNER JOIN (SELECT id,tool_id,recipe_id, MAX(date_time) as MaxDateTime
FROM data_cstmeta
GROUP BY recipe_id) grouped
ON data_cstmeta.id = grouped.id
AND data_cstmeta.date_time = grouped.MaxDateTime
WHERE data_cstmeta.ppl_id = data_ppl.id
AND data_cstmeta.recipe_id = data_recipe.id
AND data_cstmeta.tool_id = data_tool.id
ORDER BY data_cstmeta.date_time desc;

And now it fails with:

ERROR 1054 (42S22): Unknown column 'data_cstmeta.id' in 'on clause'

I've messed around with this for a day and I can't figure out what
stupid mistake I'm making.



You didn't add just one column, you added an entire table to the FROM 
clause.  Also, you used a comma-join instead of an ANSI JOIN clause.


Because of changes we made in 5.0.12 to make MySQL behave more like the 
SQL standard requires, the priority of the comma operator in join 
operations was demoted. This means that when the explicit JOIN 
declaration is being evaluated (which has a higher order of precedence) 
only the derived table (grouped) and the base table `data_tool` are 
visible to the ON clause. The `data_cstmeta` implicit join will be 
evaluated later as it is based on a comma-join.


I suggest you rewrite your queries to only use the explicit JOIN ... ON 
... syntax for all of your joins to avoid these problems in the future.

http://dev.mysql.com/doc/refman/5.5/en/join.html

Regards,
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: adding column breaks query

[Larry Martell]

Not sure what you mean.

On Mon, Jan 28, 2013 at 8:49 AM, Douglas Nelson
douglas.nel...@oracle.com wrote:
 two different fields??

 Thanks Doug

 SELECT data_ppl.name as 'P/P/L', data_recipe.name as Recipe,
 data_cstmeta.date_time as 'Last Run'
 FROM data_recipe, data_ppl, data_cstmeta
INNER JOIN (SELECT id,recipe_id, MAX(date_time) as MaxDateTime
FROM data_cstmeta
GROUP BY recipe_id) grouped

 ON data_cstmeta.id = grouped.id
AND data_cstmeta.date_time = grouped.MaxDateTime
 WHERE data_cstmeta.ppl_id = data_ppl.id
 AND data_cstmeta.recipe_id = data_recipe.id
 ORDER BY data_cstmeta.date_time desc;


 Now I need to add one more column to it, data_tool.name, so I did this:


 SELECT data_ppl.name as 'P/P/L', data_tool.name as Tool,
 data_recipe.name as Recipe, data_cstmeta.date_time as 'Last Run'
 FROM data_recipe, data_ppl, data_cstmeta, data_tool
INNER JOIN (SELECT id,tool_id,recipe_id, MAX(date_time) as MaxDateTime
FROM data_cstmeta
GROUP BY recipe_id) grouped
ON data_cstmeta.id = grouped.id
AND data_cstmeta.date_time = grouped.MaxDateTime
 WHERE data_cstmeta.ppl_id = data_ppl.id
 AND data_cstmeta.recipe_id = data_recipe.id
 AND data_cstmeta.tool_id = data_tool.id
 ORDER BY data_cstmeta.date_time desc;

 And now it fails with:

 ERROR 1054 (42S22): Unknown column 'data_cstmeta.id' in 'on clause'

 Larry Martell wrote:

 SELECT data_ppl.name as 'P/P/L', data_recipe.name as Recipe,
 data_cstmeta.date_time as 'Last Run'
 FROM data_recipe, data_ppl, data_cstmeta
INNER JOIN (SELECT id,recipe_id, MAX(date_time) as MaxDateTime
FROM data_cstmeta
GROUP BY recipe_id) grouped
ON data_cstmeta.id = grouped.id
AND data_cstmeta.date_time = grouped.MaxDateTime
 WHERE data_cstmeta.ppl_id = data_ppl.id
 AND data_cstmeta.recipe_id = data_recipe.id
 ORDER BY data_cstmeta.date_time desc;


 Now I need to add one more column to it, data_tool.name, so I did this:


 SELECT data_ppl.name as 'P/P/L', data_tool.name as Tool,
 data_recipe.name as Recipe, data_cstmeta.date_time as 'Last Run'
 FROM data_recipe, data_ppl, data_cstmeta, data_tool
INNER JOIN (SELECT id,tool_id,recipe_id, MAX(date_time) as MaxDateTime
FROM data_cstmeta
GROUP BY recipe_id) grouped
ON data_cstmeta.id = grouped.id
AND data_cstmeta.date_time = grouped.MaxDateTime
 WHERE data_cstmeta.ppl_id = data_ppl.id
 AND data_cstmeta.recipe_id = data_recipe.id
 AND data_cstmeta.tool_id = data_tool.id
 ORDER BY data_cstmeta.date_time desc;

 And now it fails with:

 ERROR 1054 (42S22): Unknown column 'data_cstmeta.id' in 'on clause'


 --
 Oracle
 Douglas C. Nelson
 Advanced Customer Services Principal Advanced Support Systems Analyst 5
 Mobile - 919 760 9221


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: column aliases in query

[Rick James]

Some places allow aliases, some don't.  Some (GROUP BY, ORDER BY) even allow 
ordinals.

For performance, the optimal index would be
INDEX(factory_id, date)
and then do
   WHERE date = 2012-10-11
 AND date   2012-10-11 + INTERVAL 1 DAY


 -Original Message-
 From: Mark Haney [mailto:ma...@abemblem.com]
 Sent: Thursday, October 11, 2012 10:47 AM
 To: MySQL
 Subject: column aliases in query
 
 I know it's been a while since I wrote serious queries, but I'm sure I
 have done something like this before:
 
 SELECT SUBSTR(date,1,10) as vDate, event_id, events.mach_id,
 machine.factory_id FROM events JOIN machine ON events.mach_id =
 machine.mach_id WHERE machine.factory_id = 1 AND vDate = 2012-10-11
 
 Where I've aliased the SUBSTR of the date and then used the alias in
 the WHERE clause of the query.  I'm getting an error message now, but
 I'm almost certain I've used that syntax before.  Am I missing
 something?
 
 --
 
 Mark Haney
 Software Developer/Consultant
 AB Emblem
 ma...@abemblem.com
 Linux marius.homelinux.org 3.5.1-1.fc17.x86_64 GNU/Linux
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: column aliases in query

[hsv]

 2012/10/11 13:46 -0400, Mark Haney 
I know it's been a while since I wrote serious queries, but I'm sure I have 
done something like this before:

SELECT SUBSTR(date,1,10) as vDate, event_id, events.mach_id, machine.factory_id 
FROM events JOIN machine ON events.mach_id = machine.mach_id WHERE 
machine.factory_id = 1 AND vDate = 2012-10-11

Where I've aliased the SUBSTR of the date and then used the alias in the WHERE 
clause of the query.  I'm getting an error message now, but I'm almost certain 
I've used that syntax before.  Am I missing something?

Yes: WHERE is for already defined names. In HAVING one refers to new names:

SELECT SUBSTR(date,1,10) as vDate, event_id, events.mach_id, machine.factory_id 
FROM events JOIN machine ON events.mach_id = machine.mach_id WHERE 
machine.factory_id = 1
HAVING vDate = 2012-10-11


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: alternative to slow query

[brian]

On 12-07-16 06:57 PM, Rick James wrote:


Plan A:
Would the anti-UNION problem be solved by hiding the UNION in a subquery?  The 
outer query would simply return what the UNION found.


Of course! Yes, problem solved.


Plan B:
Insert every row twice into expression_expression -- (e1,e2) and also (e2,e1).
Then, you need only one index into that table, and you don't need to UNION (or 
the LEFT JOINs).


This is the approach that I was considering for when the app is 
rewritten. (I've inherited a disaster.)


Thank again for the tip.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: alternative to slow query

[Rick James]

Please provide SHOW CREATE TABLE for the two tables.

Plan A:
Would the anti-UNION problem be solved by hiding the UNION in a subquery?  The 
outer query would simply return what the UNION found.

Plan B:
Insert every row twice into expression_expression -- (e1,e2) and also (e2,e1).
Then, you need only one index into that table, and you don't need to UNION (or 
the LEFT JOINs).

Plan C:
Do something with a VIEW.  Caution:  Performance _may_ be even worse.

 -Original Message-
 From: brian [mailto:mysql-l...@logi.ca]
 Sent: Tuesday, July 03, 2012 12:50 PM
 To: mysql@lists.mysql.com
 Subject: Re: alternative to slow query
 
 On 12-07-03 02:18 PM, Stillman, Benjamin wrote:
  Not sure why it  wouldn't show primary as a possible key then...
 
 Yes, that seems rather strange.
 
 
   From your first email:
 
  *** 1. row ***
   id: 1
  select_type: SIMPLE
table: e
 type: ALL
  possible_keys: NULL
  key: NULL
  key_len: NULL
  ref: NULL
 rows: 95127
Extra:
 
 
  I'd be curious to see the explain from this:
 
  select id, lang, term from expression where id = (insert a random,
  valid id value here);
 
  Does it use a key then? Or at least show primary as a possible key?
 
 mysql db_lexi  EXPLAIN SELECT id, term, lang_id FROM expression WHERE
 id = 223363\G
 *** 1. row ***
 id: 1
select_type: SIMPLE
  table: expression
   type: const
 possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: const
   rows: 1
  Extra:
 1 row in set (0.00 sec)
 
 
 Here's the query again, with some of the stuff I'd removed for clarity.
 There are still some other fields missing here but they involve 2 left
 joins on other tables.
 
 mysql db_lexi  EXPLAIN SELECT e.id, e.lang_id, e.term
  - FROM (expression AS e)
  - LEFT JOIN expression_expression AS ee1
  - ON ee1.expression1_id = e.id AND ee1.deleted_at = 0
  - LEFT JOIN expression_expression AS ee2
  - ON ee2.expression2_id = e.id AND ee2.deleted_at = 0
  - WHERE (ee2.expression1_id = 223363 OR ee1.expression2_id =
 223363)
  - AND e.original_id IS NULL
  - AND e.deleted_at = 0\G
 *** 1. row ***
 id: 1
select_type: SIMPLE
  table: e
   type: ref
 possible_keys: original_id_idx,deleted_at_idx
key: original_id_idx
key_len: 9
ref: const
   rows: 60560
  Extra: Using where
 *** 2. row ***
 id: 1
select_type: SIMPLE
  table: ee1
   type: ref
 possible_keys: expression1_id_idx
key: expression1_id_idx
key_len: 8
ref: db_lexi.e.id
   rows: 1
  Extra:
 *** 3. row ***
 id: 1
select_type: SIMPLE
  table: ee2
   type: ref
 possible_keys: expression2_id_idx
key: expression2_id_idx
key_len: 8
ref: db_lexi.e.id
   rows: 1
  Extra: Using where
 3 rows in set (0.00 sec)
 
 I presume that e.id is not being used because I'm not specifically
 querying against it. Instead, I'm using expression_expression's FKs.
 
 Which gives me an idea. I can add expression a 2nd time to the FROM
 clause:
 
 mysql db_lexi  EXPLAIN SELECT e.id, e.lang_id, e.term
  - FROM (expression AS e, expression AS e_pk)
  - LEFT JOIN expression_expression AS ee1
  - ON ee1.expression1_id = e.id AND ee1.deleted_at = 0
  - LEFT JOIN expression_expression AS ee2
  - ON ee2.expression2_id = e.id AND ee2.deleted_at = 0
  - WHERE
  - (ee1.expression2_id = e_pk.id AND ee2.expression1_id = 223363)
  - OR (ee1.expression1_id = e_pk.id AND ee1.expression2_id =
 223363)
  - AND e.original_id IS NULL
  - AND e.deleted_at = 0\G
 *** 1. row ***
 id: 1
select_type: SIMPLE
  table: ee1
   type: ALL
 possible_keys: expression2_id_idx,expression1_id_idx
key: NULL
key_len: NULL
ref: NULL
   rows: 106191
  Extra: Using where
 *** 2. row ***
 id: 1
select_type: SIMPLE
  table: e
   type: eq_ref
 possible_keys: PRIMARY,original_id_idx,deleted_at_idx
key: PRIMARY
key_len: 8
ref: db_lexi.ee1.expression1_id
   rows: 1
  Extra:
 *** 3. row ***
 id: 1
select_type: SIMPLE
  table: ee2
   type: ref
 possible_keys: expression2_id_idx
key: expression2_id_idx
key_len: 8
ref

Re: alternative to slow query

[brian]

On 12-07-02 09:33 PM, yoku ts wrote:

Hello,

add index to expression1_id and expression2_id on expression_expression.

it doesn't use index,following,


WHERE
 ee2.expression1_id = $ID
OR
 ee1.expression2_id = $ID




Thank you for your reply. The table already has indexes on these 
columns, however:


mysql db_lexi  show index from expression_expression\G
*** 1. row ***
   Table: expression_expression
  Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
 Column_name: id
   Collation: A
 Cardinality: 106191
Sub_part: NULL
  Packed: NULL
Null:
  Index_type: BTREE
 Comment:
*** 2. row ***
   Table: expression_expression
  Non_unique: 1
Key_name: expression2_id_idx
Seq_in_index: 1
 Column_name: expression2_id
   Collation: A
 Cardinality: 106191
Sub_part: NULL
  Packed: NULL
Null:
  Index_type: BTREE
 Comment:
*** 3. row ***
   Table: expression_expression
  Non_unique: 1
Key_name: type_lien_id_idx
Seq_in_index: 1
 Column_name: type_lien_id
   Collation: A
 Cardinality: 43
Sub_part: NULL
  Packed: NULL
Null: YES
  Index_type: BTREE
 Comment:
*** 4. row ***
   Table: expression_expression
  Non_unique: 1
Key_name: expression1_id_idx
Seq_in_index: 1
 Column_name: expression1_id
   Collation: A
 Cardinality: 106191
Sub_part: NULL
  Packed: NULL
Null:
  Index_type: BTREE
 Comment:
4 rows in set (0.23 sec)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: alternative to slow query

[Stillman, Benjamin]

I don't see an index for expression.id.



-Original Message-
From: brian [mailto:mysql-l...@logi.ca]
Sent: Tuesday, July 03, 2012 12:28 PM
To: mysql@lists.mysql.com
Subject: Re: alternative to slow query

On 12-07-02 09:33 PM, yoku ts wrote:
 Hello,

 add index to expression1_id and expression2_id on expression_expression.

 it doesn't use index,following,

 WHERE
  ee2.expression1_id = $ID
 OR
  ee1.expression2_id = $ID


Thank you for your reply. The table already has indexes on these columns, 
however:

mysql db_lexi  show index from expression_expression\G
*** 1. row ***
Table: expression_expression
   Non_unique: 0
 Key_name: PRIMARY
Seq_in_index: 1
  Column_name: id
Collation: A
  Cardinality: 106191
 Sub_part: NULL
   Packed: NULL
 Null:
   Index_type: BTREE
  Comment:
*** 2. row ***
Table: expression_expression
   Non_unique: 1
 Key_name: expression2_id_idx
Seq_in_index: 1
  Column_name: expression2_id
Collation: A
  Cardinality: 106191
 Sub_part: NULL
   Packed: NULL
 Null:
   Index_type: BTREE
  Comment:
*** 3. row ***
Table: expression_expression
   Non_unique: 1
 Key_name: type_lien_id_idx
Seq_in_index: 1
  Column_name: type_lien_id
Collation: A
  Cardinality: 43
 Sub_part: NULL
   Packed: NULL
 Null: YES
   Index_type: BTREE
  Comment:
*** 4. row ***
Table: expression_expression
   Non_unique: 1
 Key_name: expression1_id_idx
Seq_in_index: 1
  Column_name: expression1_id
Collation: A
  Cardinality: 106191
 Sub_part: NULL
   Packed: NULL
 Null:
   Index_type: BTREE
  Comment:
4 rows in set (0.23 sec)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql




Notice: This communication may contain privileged and/or confidential 
information. If you are not the intended recipient, please notify the sender by 
email, and immediately delete the message and any attachments without copying 
or disclosing them. LBI may, for any reason, intercept, access, use, and 
disclose any information that is communicated by or through, or which is stored 
on, its networks, applications, services, and devices.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: alternative to slow query

[brian]

On 12-07-03 01:13 PM, Stillman, Benjamin wrote:

I don't see an index for expression.id.



mysql db_lexi  show index from expression\G
*** 1. row ***
   Table: expression
  Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
 Column_name: id
   Collation: A
 Cardinality: 96111
Sub_part: NULL
  Packed: NULL
Null:
  Index_type: BTREE
 Comment:

... and 10 other keys, 2 of which are also being used in the WHERE part. 
I left them out for clarity because while they help to narrow things 
down a bit including them doesn't speed up the query all that much.




mysql db_lexi  SHOW TABLE STATUS WHERE NAME = 'expression'\G
*** 1. row ***
   Name: expression
 Engine: InnoDB
Version: 10
 Row_format: Compact
   Rows: 100747
 Avg_row_length: 193
Data_length: 19447808
Max_data_length: 0
   Index_length: 31621120
  Data_free: 48234496
 Auto_increment: 240840
Create_time: 2012-06-27 14:18:57
Update_time: NULL
 Check_time: NULL
  Collation: utf8_general_ci
   Checksum: NULL
 Create_options:
Comment:
1 row in set (0.77 sec)

mysql db_lexi  SHOW TABLE STATUS WHERE NAME = 'expression_expression'\G
*** 1. row ***
   Name: expression_expression
 Engine: InnoDB
Version: 10
 Row_format: Compact
   Rows: 106191
 Avg_row_length: 103
Data_length: 11026432
Max_data_length: 0
   Index_length: 14204928
  Data_free: 48234496
 Auto_increment: 218884
Create_time: 2012-06-27 14:19:31
Update_time: NULL
 Check_time: NULL
  Collation: utf8_general_ci
   Checksum: NULL
 Create_options:
Comment:
1 row in set (0.13 sec)

I realise that I should have posted all this in the original msg.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: alternative to slow query

[Stillman, Benjamin]

Not sure why it  wouldn't show primary as a possible key then...

From your first email:

*** 1. row ***
id: 1
   select_type: SIMPLE
 table: e
  type: ALL
possible_keys: NULL
   key: NULL
   key_len: NULL
   ref: NULL
  rows: 95127
 Extra:


I'd be curious to see the explain from this:

select id, lang, term from expression where id = (insert a random, valid id 
value here);

Does it use a key then? Or at least show primary as a possible key?




-Original Message-
From: brian [mailto:mysql-l...@logi.ca]
Sent: Tuesday, July 03, 2012 1:47 PM
To: mysql@lists.mysql.com
Subject: Re: alternative to slow query

On 12-07-03 01:13 PM, Stillman, Benjamin wrote:
 I don't see an index for expression.id.


mysql db_lexi  show index from expression\G
*** 1. row ***
Table: expression
   Non_unique: 0
 Key_name: PRIMARY
Seq_in_index: 1
  Column_name: id
Collation: A
  Cardinality: 96111
 Sub_part: NULL
   Packed: NULL
 Null:
   Index_type: BTREE
  Comment:

... and 10 other keys, 2 of which are also being used in the WHERE part.
I left them out for clarity because while they help to narrow things down a bit 
including them doesn't speed up the query all that much.



mysql db_lexi  SHOW TABLE STATUS WHERE NAME = 'expression'\G
*** 1. row ***
Name: expression
  Engine: InnoDB
 Version: 10
  Row_format: Compact
Rows: 100747
  Avg_row_length: 193
 Data_length: 19447808
Max_data_length: 0
Index_length: 31621120
   Data_free: 48234496
  Auto_increment: 240840
 Create_time: 2012-06-27 14:18:57
 Update_time: NULL
  Check_time: NULL
   Collation: utf8_general_ci
Checksum: NULL
  Create_options:
 Comment:
1 row in set (0.77 sec)

mysql db_lexi  SHOW TABLE STATUS WHERE NAME = 'expression_expression'\G
*** 1. row ***
Name: expression_expression
  Engine: InnoDB
 Version: 10
  Row_format: Compact
Rows: 106191
  Avg_row_length: 103
 Data_length: 11026432
Max_data_length: 0
Index_length: 14204928
   Data_free: 48234496
  Auto_increment: 218884
 Create_time: 2012-06-27 14:19:31
 Update_time: NULL
  Check_time: NULL
   Collation: utf8_general_ci
Checksum: NULL
  Create_options:
 Comment:
1 row in set (0.13 sec)

I realise that I should have posted all this in the original msg.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql




Notice: This communication may contain privileged and/or confidential 
information. If you are not the intended recipient, please notify the sender by 
email, and immediately delete the message and any attachments without copying 
or disclosing them. LBI may, for any reason, intercept, access, use, and 
disclose any information that is communicated by or through, or which is stored 
on, its networks, applications, services, and devices.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: alternative to slow query

[brian]

On 12-07-03 02:18 PM, Stillman, Benjamin wrote:

Not sure why it  wouldn't show primary as a possible key then...


Yes, that seems rather strange.



 From your first email:

*** 1. row ***
 id: 1
select_type: SIMPLE
  table: e
   type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
   rows: 95127
  Extra:


I'd be curious to see the explain from this:

select id, lang, term from expression where id = (insert a random, valid id 
value here);

Does it use a key then? Or at least show primary as a possible key?


mysql db_lexi  EXPLAIN SELECT id, term, lang_id FROM expression WHERE 
id = 223363\G

*** 1. row ***
   id: 1
  select_type: SIMPLE
table: expression
 type: const
possible_keys: PRIMARY
  key: PRIMARY
  key_len: 8
  ref: const
 rows: 1
Extra:
1 row in set (0.00 sec)


Here's the query again, with some of the stuff I'd removed for clarity. 
There are still some other fields missing here but they involve 2 left 
joins on other tables.


mysql db_lexi  EXPLAIN SELECT e.id, e.lang_id, e.term
- FROM (expression AS e)
- LEFT JOIN expression_expression AS ee1
- ON ee1.expression1_id = e.id AND ee1.deleted_at = 0
- LEFT JOIN expression_expression AS ee2
- ON ee2.expression2_id = e.id AND ee2.deleted_at = 0
- WHERE (ee2.expression1_id = 223363 OR ee1.expression2_id = 223363)
- AND e.original_id IS NULL
- AND e.deleted_at = 0\G
*** 1. row ***
   id: 1
  select_type: SIMPLE
table: e
 type: ref
possible_keys: original_id_idx,deleted_at_idx
  key: original_id_idx
  key_len: 9
  ref: const
 rows: 60560
Extra: Using where
*** 2. row ***
   id: 1
  select_type: SIMPLE
table: ee1
 type: ref
possible_keys: expression1_id_idx
  key: expression1_id_idx
  key_len: 8
  ref: db_lexi.e.id
 rows: 1
Extra:
*** 3. row ***
   id: 1
  select_type: SIMPLE
table: ee2
 type: ref
possible_keys: expression2_id_idx
  key: expression2_id_idx
  key_len: 8
  ref: db_lexi.e.id
 rows: 1
Extra: Using where
3 rows in set (0.00 sec)

I presume that e.id is not being used because I'm not specifically 
querying against it. Instead, I'm using expression_expression's FKs.


Which gives me an idea. I can add expression a 2nd time to the FROM clause:

mysql db_lexi  EXPLAIN SELECT e.id, e.lang_id, e.term
- FROM (expression AS e, expression AS e_pk)
- LEFT JOIN expression_expression AS ee1
- ON ee1.expression1_id = e.id AND ee1.deleted_at = 0
- LEFT JOIN expression_expression AS ee2
- ON ee2.expression2_id = e.id AND ee2.deleted_at = 0
- WHERE
- (ee1.expression2_id = e_pk.id AND ee2.expression1_id = 223363)
- OR (ee1.expression1_id = e_pk.id AND ee1.expression2_id = 223363)
- AND e.original_id IS NULL
- AND e.deleted_at = 0\G
*** 1. row ***
   id: 1
  select_type: SIMPLE
table: ee1
 type: ALL
possible_keys: expression2_id_idx,expression1_id_idx
  key: NULL
  key_len: NULL
  ref: NULL
 rows: 106191
Extra: Using where
*** 2. row ***
   id: 1
  select_type: SIMPLE
table: e
 type: eq_ref
possible_keys: PRIMARY,original_id_idx,deleted_at_idx
  key: PRIMARY
  key_len: 8
  ref: db_lexi.ee1.expression1_id
 rows: 1
Extra:
*** 3. row ***
   id: 1
  select_type: SIMPLE
table: ee2
 type: ref
possible_keys: expression2_id_idx
  key: expression2_id_idx
  key_len: 8
  ref: db_lexi.ee1.expression1_id
 rows: 1
Extra: Using where
*** 4. row ***
   id: 1
  select_type: SIMPLE
table: e_pk
 type: ALL
possible_keys: PRIMARY
  key: NULL
  key_len: NULL
  ref: NULL
 rows: 121120
Extra: Range checked for each record (index map: 0x1)
4 rows in set (0.00 sec)


But this doesn't feel like an elegant solution. Regardless, I'm still 
seeing the query take ~2.5sec. I'm just looking into the Range checked 
for each record msg now. Perhaps this is the right direction but 
requires a little tweaking.



I don't understand why deleted_at_idx is also not used, though. Perhaps 
because I'm only looking for values of 0? Regardless, that doesn't seem 
to be the heart of the problem.


BTW, I inherited the DB, so can't be sure 

Re: alternative to slow query

[yoku ts]

Hello,

add index to expression1_id and expression2_id on expression_expression.

it doesn't use index,following,

 WHERE
 ee2.expression1_id = $ID
 OR
 ee1.expression2_id = $ID

regards,


2012/7/3 brian mysql-l...@logi.ca

 I have a table that joins on itself through a second table:

 table expression:

 id INT PRIMARY KEY,
 lang_id INT
 term VARCHAR(128)

 table expression_expression:

 id INT PRIMARY KEY
 expression1_id INT
 expression2_id INT

 In order to find associated records, I had originally used a UNION, which
 worked very well. However, the application is written in PHP and uses PDO.
 PDOStatement::getColumnMeta() doesn't return anything for the table name
 with a UNION and this is crucial to the application. So I've come up with
 the following substitute:

 SELECT e.id, e.lang_id, e.term
 FROM expression AS e
 LEFT JOIN expression_expression AS ee1
 ON ee1.expression1_id = e.id
 LEFT JOIN expression_expression AS ee2
 ON ee2.expression2_id = e.id
 WHERE
 ee2.expression1_id = $ID
 OR
 ee1.expression2_id = $ID

 This gives me the correct values but is rather (~2-4 sec) slow. Here's the
 EXPLAIN output:

 *** 1. row ***
id: 1
   select_type: SIMPLE
 table: e
  type: ALL
 possible_keys: NULL
   key: NULL
   key_len: NULL
   ref: NULL
  rows: 95127
 Extra:
 *** 2. row ***
id: 1
   select_type: SIMPLE
 table: ee1
  type: ref
 possible_keys: expression1_id_idx
   key: expression1_id_idx
   key_len: 8
   ref: db_lexi.e.id
  rows: 1
 Extra:
 *** 3. row ***
id: 1
   select_type: SIMPLE
 table: ee2
  type: ref
 possible_keys: expression2_id_idx
   key: expression2_id_idx
   key_len: 8
   ref: db_lexi.e.id
  rows: 1
 Extra: Using where
 3 rows in set (0.00 sec)


 Can someone suggest a better approach?

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql

RE: Within-group aggregate query help please - customers and latest subscription row

[Hal�sz S�ndor]

 2011/10/24 16:31 -0700, Daevid Vincent 
  WHERE cs.customer_id = 7
GROUP BY customer_id

Well, the latter line is now redundant.

How will you make the '7' into a parameter?


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Within-group aggregate query help please - customers and latest subscription row

[Daevid Vincent]

A kind (and shy) soul replied to me off list and suggested this solution,
however,
this takes 28 seconds (that's for a single customer_id, so this is not going
to scale). 
Got any other suggestions? :-)

SELECT 
c.customer_id,
c.email,
c.name,
c.username,
s.subscription_id,
s.`date`
FROM
customers AS c 
INNER JOIN customers_subscriptions AS s 
ON c.customer_id = s.customer_id 
INNER JOIN 
(SELECT 
MAX(`date`) AS LastDate,
customer_id 
FROM
customers_subscriptions AS cs 
GROUP BY customer_id) AS `x`
ON s.customer_id = x.customer_id 
AND s.date = x.LastDate 
WHERE c.customer_id = 7;

There are 781,270 customers (nearly 1 million) and  1,018,092
customer_subscriptions.

Our tables have many indexes on pretty much every column and for sure the
ones we use here.

EXPLAIN says:

id  select_type  table   typepossible_keys key
key_len  refrows  Extra  
--  ---  --  --    ---
---  --  ---  ---
 1  PRIMARY  c   const   PRIMARY   PRIMARY  4
const 1 
 1  PRIMARY  s   ref date,customer_id  customer_id  4
const 2 
 1  PRIMARY  derived2  ALL (NULL)(NULL)
(NULL)   (NULL)   781265  Using where
 2  DERIVED  cs  ALL (NULL)(NULL)
(NULL)   (NULL)  1018092  Using temporary; Using filesort

 -Original Message-
 From: Daevid Vincent [mailto:dae...@daevid.com]
 Sent: Monday, October 24, 2011 1:46 PM
 To: mysql@lists.mysql.com
 Subject: Within-group aggregate query help please - customers and latest
 subscription row
 
 I know this is a common problem, and I've been struggling with it for a
full
 day now but I can't get it.
 
 I also tried a few sites for examples:
 http://www.artfulsoftware.com/infotree/queries.php#101

http://forums.devarticles.com/general-sql-development-47/select-max-datetime
 -problem-10210.html
 
 Anyways, pretty standard situation:
 
 CREATE TABLE `customers` (
   `customer_id` int(10) unsigned NOT NULL auto_increment,
   `email` varchar(64) NOT NULL default '',
   `name` varchar(128) NOT NULL default '',
   `username` varchar(32) NOT NULL,
   ...
 );
 
 CREATE TABLE `customers_subscriptions` (
   `subscription_id` bigint(12) unsigned NOT NULL default '0',
   `customer_id` int(10) unsigned NOT NULL default '0',
   `date` date NOT NULL default '-00-00',
   ...
 );
 
 I want to show a table where I list out the ID, email, username, and LAST
 SUBSCRIPTION.
 
 I need this data in TWO ways:
 
 The FIRST way, is with a query JOINing the two tables so that I can easily
 display that HTML table mentioned. That is ALL customers and the latest
 subscription they have.
 
 The SECOND way is when I drill into the customer, I already know the
 customer_id and so don't need to JOIN with that table, I just want to get
 the proper row from the customers_subscriptions table itself.
 
 SELECT * FROM `customers_subscriptions` WHERE customer_id = 7 ORDER BY
 `date` DESC;
 
 subscription_id  processor  customer_id  date
 ---  -  ---  --
   134126370  chargem  7  2005-08-04
  1035167192  billme   7  2004-02-08
 
 SELECT MAX(`date`) FROM `customers_subscriptions` WHERE customer_id = 7
 GROUP BY customer_id;
 
 gives me 2005-08-04 obviously, but as you all know, mySQL completely takes
a
 crap on your face when you try what would seem to be the right query:
 
 SELECT subscription_id, MAX(`date`) FROM `customers_subscriptions` WHERE
 customer_id = 7 GROUP BY customer_id;
 
 subscription_id  MAX(`date`)
 ---  ---
  1035167192  2005-08-04
 
 Notice how I have the correct DATE, but the wrong subscription_id.
 
 In the example web sites above, they seem to deal more with finding the
 MAX(subscription_id), which in my case will not work.
 
 I need the max DATE and the corresponding row (with matching
 subscription_id).
 
 Thanks,
 
 d


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Within-group aggregate query help please - customers and latest subscription row

[Daevid Vincent]

Okay, it seems I am learning... slowly...

So there needs to be a second WHERE in the sub-select...

To get ONE customer's last subscription (0.038s):

SELECT 
c.customer_id,
c.email,
c.name,
c.username,
s.subscription_id,
s.`date`
FROM
customers AS c 
INNER JOIN customers_subscriptions AS s 
ON c.customer_id = s.customer_id 
INNER JOIN 
(SELECT 
MAX(`date`) AS LastDate,
customer_id 
FROM
customers_subscriptions AS cs 
  WHERE cs.customer_id = 7
GROUP BY customer_id
) AS `x`
ON s.customer_id = x.customer_id 
AND s.date = x.LastDate
WHERE c.customer_id = 7;

To get ALL customers and their last subscription row (1m:28s)

SELECT 
c.customer_id,
c.email,
c.name,
c.username,
s.subscription_id,
s.`date`
FROM
customers AS c 
INNER JOIN customers_subscriptions AS s 
ON c.customer_id = s.customer_id 
INNER JOIN 
(SELECT 
MAX(`date`) AS LastDate,
customer_id 
FROM
customers_subscriptions AS cs 
  GROUP BY customer_id
) AS `x`
ON s.customer_id = x.customer_id 
AND s.date = x.LastDate
ORDER BY customer_id LIMIT 10;

Thanks to you know who you are for pointing me in the right direction. 

Hopefully this helps someone else.

d.

 -Original Message-
 From: Daevid Vincent [mailto:dae...@daevid.com]
 Sent: Monday, October 24, 2011 4:06 PM
 To: mysql@lists.mysql.com
 Subject: RE: Within-group aggregate query help please - customers and
latest
 subscription row
 
 A kind (and shy) soul replied to me off list and suggested this solution,
 however,
 this takes 28 seconds (that's for a single customer_id, so this is not
going
 to scale).
 Got any other suggestions? :-)
 
 SELECT
 c.customer_id,
 c.email,
 c.name,
 c.username,
 s.subscription_id,
 s.`date`
 FROM
 customers AS c
 INNER JOIN customers_subscriptions AS s
 ON c.customer_id = s.customer_id
 INNER JOIN
 (SELECT
 MAX(`date`) AS LastDate,
 customer_id
 FROM
 customers_subscriptions AS cs
 GROUP BY customer_id) AS `x`
 ON s.customer_id = x.customer_id
 AND s.date = x.LastDate
 WHERE c.customer_id = 7;
 
 There are 781,270 customers (nearly 1 million) and  1,018,092
 customer_subscriptions.
 
 Our tables have many indexes on pretty much every column and for sure the
 ones we use here.
 
 EXPLAIN says:
 
 id  select_type  table   typepossible_keys key
 key_len  refrows  Extra
 --  ---  --  --    ---
 ---  --  ---  ---
  1  PRIMARY  c   const   PRIMARY   PRIMARY  4
 const 1
  1  PRIMARY  s   ref date,customer_id  customer_id  4
 const 2
  1  PRIMARY  derived2  ALL (NULL)(NULL)
 (NULL)   (NULL)   781265  Using where
  2  DERIVED  cs  ALL (NULL)(NULL)
 (NULL)   (NULL)  1018092  Using temporary; Using filesort
 
  -Original Message-
  From: Daevid Vincent [mailto:dae...@daevid.com]
  Sent: Monday, October 24, 2011 1:46 PM
  To: mysql@lists.mysql.com
  Subject: Within-group aggregate query help please - customers and latest
  subscription row
 
  I know this is a common problem, and I've been struggling with it for a
 full
  day now but I can't get it.
 
  I also tried a few sites for examples:
  http://www.artfulsoftware.com/infotree/queries.php#101
 

http://forums.devarticles.com/general-sql-development-47/select-max-datetime
  -problem-10210.html
 
  Anyways, pretty standard situation:
 
  CREATE TABLE `customers` (
`customer_id` int(10) unsigned NOT NULL auto_increment,
`email` varchar(64) NOT NULL default '',
`name` varchar(128) NOT NULL default '',
`username` varchar(32) NOT NULL,
  ...
  );
 
  CREATE TABLE `customers_subscriptions` (
`subscription_id` bigint(12) unsigned NOT NULL default '0',
`customer_id` int(10) unsigned NOT NULL default '0',
`date` date NOT NULL default '-00-00',
...
  );
 
  I want to show a table where I list out the ID, email, username, and
LAST
  SUBSCRIPTION.
 
  I need this data in TWO ways:
 
  The FIRST way, is with a query JOINing the two tables so that I can
easily
  display that HTML table mentioned. That is ALL customers and the latest
  subscription they have.
 
  The SECOND way

Re: Fwd: Large insert query gives MySQL server gone away

[Tompkins Neil]

Do you have any recommendations as to what size this should be increased to
?

On Tue, Oct 11, 2011 at 12:23 AM, Angela liu yyll2...@yahoo.com wrote:

 Yeah, I think adjusting max packet size may be helpful, remember change
 that value in both server and client

 --
 *From:* Johnny Withers joh...@pixelated.net
 *To:* Neil Tompkins neil.tompk...@googlemail.com
 *Cc:* [MySQL] mysql@lists.mysql.com
 *Sent:* Monday, October 10, 2011 4:13 PM
 *Subject:* Re: Fwd: Large insert query gives MySQL server gone away

 Max packet size?

 On Oct 10, 2011 6:12 PM, Neil Tompkins neil.tompk...@googlemail.com
 wrote:


  As per the subject we've a large insert query that gives up the error
 MySQL server has gone away when we try to execute it.  Any ideas why ?

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net

Re: Fwd: Large insert query gives MySQL server gone away

[Johnny Withers]

Max packet size?

On Oct 10, 2011 6:12 PM, Neil Tompkins neil.tompk...@googlemail.com
wrote:


 As per the subject we've a large insert query that gives up the error
MySQL server has gone away when we try to execute it.  Any ideas why ?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net

Re: Fwd: Large insert query gives MySQL server gone away

[Angela liu]

Yeah, I think adjusting max packet size may be helpful, remember change that 
value in both server and client



From: Johnny Withers joh...@pixelated.net
To: Neil Tompkins neil.tompk...@googlemail.com
Cc: [MySQL] mysql@lists.mysql.com
Sent: Monday, October 10, 2011 4:13 PM
Subject: Re: Fwd: Large insert query gives MySQL server gone away

Max packet size?

On Oct 10, 2011 6:12 PM, Neil Tompkins neil.tompk...@googlemail.com
wrote:


 As per the subject we've a large insert query that gives up the error
MySQL server has gone away when we try to execute it.  Any ideas why ?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=joh...@pixelated.net

Re: a lesson in query writing and (maybe) a bug report

[Jigal van Hemert]

Hi,

On 28-8-2011 4:08, shawn wilson wrote:

On Sat, Aug 27, 2011 at 17:33, Arthur Fullerfuller.art...@gmail.com  wrote:

I agree 110%. It is completely pointless to index a column with that amount
of NULLs. In practical fact I would go further: what is the point of a
NULLable column?


A NULL 'value' is special in most operations. It indicates that the 
value is undefined, unknown, uncertain. In this regard it's actually not 
a value.

SELECT 'Uncertain' = TRUE;
Result: 0
SELECT 'Uncertain' = FALSE;
Result: 1
SELECT 'Uncertain' = NULL;
Result: NULL

SELECT NULL = TRUE;
Result: NULL
SELECT NULL = FALSE;
Result: NULL
SELECT NULL = NULL;
Result: NULL

(Unfortunately someone decided to add the = operator:
SELECT NULL = NULL;
Result: 1
Even stranger is that it is documented as NULL safe !?!?)

The advantage to me for having NULL 'values' is that it is usually 
handled as a truly undefined value. (When you compare an undefined value 
with for example 2, the result cannot be TRUE or FALSE. The undefined 
value might be equal to 2, or might not be equal to 2. The result can 
only be undefined.)
To deal with NULL results inside expressions COALESCE() is a very useful 
function.



how does null effect an index? i had always assumed that, since there
is nothing there, that record wouldn't go into the index hence
wouldn't be processed when utilizing the index.


MySQL can use NULL in indexes when executing a query. If there are not 
enough different values in a column (low cardinality) it might be faster 
to do a full table search instead of first reading the index and then 
having to go through the table anyway.


--
Kind regards / met vriendelijke groet,

Jigal van Hemert.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: a lesson in query writing and (maybe) a bug report

[Jigal van Hemert]

Hi,

On 27-8-2011 1:28, Dave Dyer wrote:


Can you post the EXPLAIN EXTENDED output for your before and after queries?
also, have you recently run an ANALYZE TABLE on the tables?


What was the result of ANALYZE TABLE?

What is the engine of the tables involved?


// before


Used keys:

p2.NULL, g.player2, p1.uid

In your original post you wrote: The according to explain, the query 
used gmtdate as an index, an excellent choice.
The explain output you posted later indicated that this is not the case 
(anymore).

gmtdate isn't listed as possible index, so what has changed?

 It seems odd that the query optimizer would choose to scan a 3.5
 million entry table instead of a 20,000 entry table.

Let's see.
Before: 28653 * 41 * 1 rows to consider = 1.1 M rows
After: 15292 * 67 * 1 rows to consider = 1.0 M rows

Conclusion: the query optimizer didn't choose to scan an entire table. 
In fact it found a way to have to look at 10% less rows.


For the final order by and limit it would be great to have a (partial) 
index to work with.
It's true that planning indexes isn't always an exact science. Generally 
speaking the goal is to construct both the query and the indexes in a 
way that you rule out as many rows as possible early on in the process.


From your query it becomes evident that you want the latest fifty 
matches between two players who both have the status is_robot null.
Try to create indexes which cover as many of the columns which are 
involved in the join, where and order parts, and look at the cardinality 
of those indexes. This will determine how many records can be discarded 
in each join and keeps the number of records MySQL has to scan as low as 
possible.


Another way is a bit tricky, but can speed up queries a lot: you want 
the 50 most recent records, so analyse the data and see if you can 
predict how big your result set will be in a period of time. Let's 
assume that there are always between 10 and 50 of such records per day. 
If you want the top 50 it would be safe to limit the search for the last 
10 to 20 days.
Of course this requires an index which includes gmtdate, but it can make 
the result set before the limit a lot smaller.


--
Kind regards / met vriendelijke groet,

Jigal van Hemert.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: a lesson in query writing and (maybe) a bug report

[Jigal van Hemert]

Hi,

On 27-8-2011 22:52, Dave Dyer wrote:

The innocuous change was to add an index for is_robot which is true
for 6 out of 20,000 records and null for the rest.


Not useful to add an index for that. I also wonder why the value is null 
(meaning: unknown, not certain) for almost all records.


If you want to use such a column in an index it's best to use and index 
base on multiple columns. This makes it more useful for use in queries.



My complaint/question/observation is not how to optimize the query
that went awry, but to be alarmed that a venerable and perfectly
serviceable query, written years ago and ignored ever since, suddenly
brought the system crashing down after making a seemingly innocuous
change intended to make a marginal improvement on an unrelated query.


Adding an index will most likely trigger some maintenance actions to 
make sure the table is healthy before adding the index.

The query optimizer has an extra index to take into account.


I had previously believed that tinkering the schema by adding
indexeswas a safe activity.


A database should be left alone for a long period. It needs monitoring 
and maintenance. Changes in the schema and even changes in the data can 
lead to changes in the behaviour.
You can make suggestions for the indexes to be used and you can even 
force the use of an index if the query optimizer makes the wrong 
decisions in a case.


--
Kind regards / met vriendelijke groet,

Jigal van Hemert.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: a lesson in query writing and (maybe) a bug report

[Arthur Fuller]

I agree 110%. It is completely pointless to index a column with that amount
of NULLs. In practical fact I would go further: what is the point of a
NULLable column? I try to design my tables such that every column is NOT
NULL. In practice this is not realistic, but I try to adhere to this
principle whenever I can. For example, it's possible to add a new Hire while
not yet having determined which department s/he will work in, and hence
which manager s/he will report to, but typically I deal with such scenarios
by creating an Undetermined value in the corresponding lookup table.

Arthur

Re: a lesson in query writing and (maybe) a bug report

[Michael Dykman]

It is a general rule that indexes for columns with low cardinality are not
worth it, often making queries more expensive than they would be without
said index.  binary columns all suffer from this.

 - michael dykman


On Sat, Aug 27, 2011 at 4:52 PM, Dave Dyer ddyer-my...@real-me.net wrote:


 The innocuous change was to add an index for is_robot which is true
 for 6 out of 20,000 records and null for the rest.

 My complaint/question/observation is not how to optimize the query
 that went awry, but to be alarmed that a venerable and perfectly
 serviceable query, written years ago and ignored ever since, suddenly
 brought the system crashing down after making a seemingly innocuous
 change intended to make a marginal improvement on an unrelated query.

 I had previously believed that tinkering the schema by adding indexes was a
 safe activity.  It's as though I add a shortcut to my regular commute
 and caused a massive traffic jam when the entire traffic flow tried to
 follow me.

 (Both tables are ok according to analyze table)


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com




-- 
 - michael dykman
 - mdyk...@gmail.com

 May the Source be with you.

Re: a lesson in query writing and (maybe) a bug report

[shawn wilson]

On Sat, Aug 27, 2011 at 17:33, Arthur Fuller fuller.art...@gmail.com wrote:
 I agree 110%. It is completely pointless to index a column with that amount
 of NULLs. In practical fact I would go further: what is the point of a
 NULLable column? I try to design my tables such that every column is NOT
 NULL. In practice this is not realistic, but I try to adhere to this
 principle whenever I can. For example, it's possible to add a new Hire while
 not yet having determined which department s/he will work in, and hence
 which manager s/he will report to, but typically I deal with such scenarios
 by creating an Undetermined value in the corresponding lookup table.


maybe this should be a new thread, but...

what's the difference between defining a null value (ie, Undetermined
in your example is the same to you as null)? it would seem that this
would take up more space and take longer to process since null is a
built in (not-)value.

how does null effect an index? i had always assumed that, since there
is nothing there, that record wouldn't go into the index hence
wouldn't be processed when utilizing the index.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: a lesson in query writing and (maybe) a bug report

[Dan Nelson]

In the last episode (Aug 26), Dave Dyer said:
 This is a cautionary tale - adding indexes is not always helpful or
 harmless.  I recently added an index to the players table to optimize a
 common query, and as a consequence this other query flipped from innocuous
 to something that takes infinite time.
 
 select 
 p1.player_name,g.score1,g.time1,g.color1,p2.player_name,g.score2,g.time2,g.color2,g.gamename,gmtdate
  
  from players as p1, players as p2, gamerecord g 
  where (p1.uid = g.player1 and p2.uid = g.player2) 
and (p1.is_robot is null and p2.is_robot is null) 
  order by gmtdate desc limit 50
 
 players is a table with 20,000 records, gamerecord is a table with 3.5
 million records, with gmtdate available as an index.  The according to
 explain, the query used gmtdate as an index, an excellent choice.  When
 I added an index to is_robot on the players table, the query flipped to
 using it, and switched from a brisk report to an infinite slog.
 
 I realize that selecting an index is an imprecise science, and I that can
 specify what index to use as a hint, but this particular flip was
 particularly disastrous.  It seems odd that the query optimizer would
 choose to scan a 3.5 million entry table instead of a 20,000 entry table.

Can you post the EXPLAIN EXTENDED output for your before and after queries? 
also, have you recently run an ANALYZE TABLE on the tables?

-- 
Dan Nelson
dnel...@allantgroup.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: Re: a lesson in query writing and (maybe) a bug report

[Dave Dyer]

Can you post the EXPLAIN EXTENDED output for your before and after queries? 
also, have you recently run an ANALYZE TABLE on the tables?

// before

mysql explain extended select 
p1.player_name,g.score1,g.time1,g.color1,p2.player_name,g.score2,g.time2,g.color2,g.gamename,gmtdate
-   from players as p1, players as p2, gamerecord g
-   where (p1.uid = g.player1 and p2.uid = g.player2)
- and (p1.is_robot is null and p2.is_robot is null)
-   order by gmtdate desc limit 50;
++-+---++-+-+-++---+--+---
---+
| id | select_type | table | type   | possible_keys   | key | key_len | ref 
   | rows  | filtered | Extra
   |
++-+---++-+-+-++---+--+---
---+
|  1 | SIMPLE  | p2| ALL| uid,uidindex| NULL| NULL| 
NULL   | 28653 |   100.00 | Using where; Using temporary; Using fi
lesort |
|  1 | SIMPLE  | g | ref| player2,player1 | player2 | 4   | 
tan2.p2.uid|41 |   100.00 |
   |
|  1 | SIMPLE  | p1| eq_ref | uid,uidindex| uid | 4   | 
tan2.g.player1 | 1 |   100.00 | Using where
   |
++-+---++-+-+-++---+--+---
---+
3 rows in set, 1 warning (0.00 sec)


// after


mysql use tantrix_tantrix;
Database changed
mysql explain extended select 
p1.player_name,g.score1,g.time1,g.color1,p2.player_name,g.score2,g.time2,g.color2,g.gamename,gmtdate
-   from players as p1, players as p2, gamerecord g
-   where (p1.uid = g.player1 and p2.uid = g.player2)
- and (p1.is_robot is null and p2.is_robot is null)
-   order by gmtdate desc limit 50;
++-+---++--+-+-+---+---+--+---
---+
| id | select_type | table | type   | possible_keys| key | 
key_len | ref   | rows  | filtered | Extra
   |
++-+---++--+-+-+---+---+--+---
---+
|  1 | SIMPLE  | p1| ref| uid,uidindex,robot_index | robot_index | 
2   | const | 15292 |   100.00 | Using where; U
sing temporary; Using filesort |
|  1 | SIMPLE  | g | ref| player2,player1  | player1 | 
4   | tantrix_tantrix.p1.uid|67 |   100.00 |
   |
|  1 | SIMPLE  | p2| eq_ref | uid,uidindex,robot_index | uid | 
4   | tantrix_tantrix.g.player2 | 1 |   100.00 | Using where
   |
++-+---++--+-+-+---+---+--+---
---+
3 rows in set, 1 warning (0.11 sec)

mysql 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: How to view Query Execution time

[Suresh Kuna]

Usually, at the end of the query running it displays the time how much it
took.

Or else enable the profiling and run the query to check the exact time it
took for execution at all levels.

On Mon, Aug 1, 2011 at 12:11 PM, Adarsh Sharma adarsh.sha...@orkash.comwrote:

 Dear all,

 I want to know how much time did it take to run a sample query.
 In postgresql, we enable timing by \timing command.

 Is there is any way to enable in Mysql

 Thanks

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?**
 unsub=sureshkumar...@gmail.comhttp://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com




-- 
Thanks
Suresh Kuna
MySQL DBA

Re: How to view Query Execution time

[Prabhat Kumar]

you can also use EXPLAIN, which will give you much more details.
http://dev.mysql.com/doc/refman/5.5/en/execution-plan-information.html
http://www.techiequest.com/mysql-visual-explain-hierarchical-view-of-query-execution-plan/

On Sun, Jul 31, 2011 at 11:45 PM, Suresh Kuna sureshkumar...@gmail.comwrote:

 Usually, at the end of the query running it displays the time how much it
 took.

 Or else enable the profiling and run the query to check the exact time it
 took for execution at all levels.

 On Mon, Aug 1, 2011 at 12:11 PM, Adarsh Sharma adarsh.sha...@orkash.com
 wrote:

  Dear all,
 
  I want to know how much time did it take to run a sample query.
  In postgresql, we enable timing by \timing command.
 
  Is there is any way to enable in Mysql
 
  Thanks
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/mysql?**
  unsub=sureshkumar...@gmail.com
 http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com
 
 


 --
 Thanks
 Suresh Kuna
 MySQL DBA




-- 
Best Regards,

Prabhat Kumar
MySQL DBA

My Blog: http://adminlinux.blogspot.com
My LinkedIn: http://www.linkedin.com/in/profileprabhat

Re: How to view Query Execution time

[Jon Siebert]

http://dev.mysql.com/doc/refman/5.1/en/query-log.html information on query
log may become useful for you in the future. Also, search the slow query
log, which also may help you in the future

On Mon, Aug 1, 2011 at 11:54 AM, Prabhat Kumar aim.prab...@gmail.comwrote:

 you can also use EXPLAIN, which will give you much more details.
 http://dev.mysql.com/doc/refman/5.5/en/execution-plan-information.html

 http://www.techiequest.com/mysql-visual-explain-hierarchical-view-of-query-execution-plan/

 On Sun, Jul 31, 2011 at 11:45 PM, Suresh Kuna sureshkumar...@gmail.com
 wrote:

  Usually, at the end of the query running it displays the time how much it
  took.
 
  Or else enable the profiling and run the query to check the exact time it
  took for execution at all levels.
 
  On Mon, Aug 1, 2011 at 12:11 PM, Adarsh Sharma adarsh.sha...@orkash.com
  wrote:
 
   Dear all,
  
   I want to know how much time did it take to run a sample query.
   In postgresql, we enable timing by \timing command.
  
   Is there is any way to enable in Mysql
  
   Thanks
  
   --
   MySQL General Mailing List
   For list archives: http://lists.mysql.com/mysql
   To unsubscribe:http://lists.mysql.com/mysql?**
   unsub=sureshkumar...@gmail.com
  http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com
  
  
 
 
  --
  Thanks
  Suresh Kuna
  MySQL DBA
 



 --
 Best Regards,

 Prabhat Kumar
 MySQL DBA

 My Blog: http://adminlinux.blogspot.com
 My LinkedIn: http://www.linkedin.com/in/profileprabhat

Re: Help with a query

[Anupam Karmarkar]

Hi Aveek,

You need to use something like union all and having to get desire result

Follow example below

select file, digest  from 
(
SELECT file, digest,Count(*)  as Cnt FROM A GROUP BY file, digest
union all
SELECT file, digest,Count(*)  as Cnt FROM B GROUP BY file, digest
) tmp 
group by file, digest
HAVING Sum(Cnt)  (Select sum(refcount) from C WHERE tmp.file = C.file and 
tmp.digest = C.digest);

--Anupam


--- On Tue, 17/5/11, Aveek Misra ave...@yahoo-inc.com wrote:

From: Aveek Misra ave...@yahoo-inc.com
Subject: Help with a query
To: mysql@lists.mysql.com mysql@lists.mysql.com
Date: Tuesday, 17 May, 2011, 1:23 PM

I have a table A and table B of the same type as

CREATE TABLE A (
     `file` varbinary(255) not null,
     `digest` binary(40) not null
)

CREATE TABLE B (
     `file` varbinary(255) not null,
     `digest` binary(40) not null
)

I have another table C of the following type 

CREATE TABLE C (
     `file` varbinary(255) not null,
     `digest` binary(40) not null,
    `refcount` bigint(20) not null
)

I need to write a query where for the same file and digest in table A and table 
B, the refcount is table C is not the same. So:

SELECT COUNT(*) as count 1 FROM A WHERE file='file1' AND digest='digest1';
SELECT COUNT(*) as count 2 FROM B WHERE file='file1' AND digest='digest1';

and then adding up the two counts from these queries and comparing it with the 
result of the following query:

SELECT refcount FROM C WHERE file='file1' AND digest='digest1';

basically (refcount == (count1 + count2)) should be true and I am interested in 
finding out all such records in table C where this is not the case.



Thanks
Aveek
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=sb_akarmar...@yahoo.com

Re: Help with a query

[Aveek Misra]

I eventually came up with a solution myself although the query is a bit 
different

SELECT C.file, C.digest, (a.cnt_A +  b.cnt_B) AS total_count, C.refcount FROM C,
(SELECT file, digest, COUNT(file) AS cnt_A FROM A GROUP BY file, digest) as a,
(SELECT file, digest, COUNT(file) AS cnt_B FROM B GROUP BY file, digest) as b
WHERE C.file = a.file and C.digest = a.digest and C.file = b.file and C.digest 
= b.digest and  C.refcount  (a.cnt_A +  b.cnt_B);

Thanks
Aveek

On May 20, 2011, at 1:52 PM, Anupam Karmarkar wrote:

Hi Aveek,

You need to use something like union all and having to get desire result

Follow example below

select file, digest  from
(
SELECT file, digest,Count(*)  as Cnt FROM A GROUP BY file, digest
union all
SELECT file, digest,Count(*)  as Cnt FROM B GROUP BY file, digest
) tmp
group by file, digest
HAVING Sum(Cnt)  (Select sum(refcount) from C WHERE tmp.file = C.file and 
tmp.digest = C.digest);

--Anupam


--- On Tue, 17/5/11, Aveek Misra 
ave...@yahoo-inc.commailto:ave...@yahoo-inc.com wrote:

From: Aveek Misra ave...@yahoo-inc.commailto:ave...@yahoo-inc.com
Subject: Help with a query
To: mysql@lists.mysql.commailto:mysql@lists.mysql.com 
mysql@lists.mysql.commailto:mysql@lists.mysql.com
Date: Tuesday, 17 May, 2011, 1:23 PM

I have a table A and table B of the same type as

CREATE TABLE A (
 `file` varbinary(255) not null,
 `digest` binary(40) not null
)

CREATE TABLE B (
 `file` varbinary(255) not null,
 `digest` binary(40) not null
)

I have another table C of the following type

CREATE TABLE C (
 `file` varbinary(255) not null,
 `digest` binary(40) not null,
`refcount` bigint(20) not null
)

I need to write a query where for the same file and digest in table A and table 
B, the refcount is table C is not the same. So:

SELECT COUNT(*) as count 1 FROM A WHERE file='file1' AND digest='digest1';
SELECT COUNT(*) as count 2 FROM B WHERE file='file1' AND digest='digest1';

and then adding up the two counts from these queries and comparing it with the 
result of the following query:

SELECT refcount FROM C WHERE file='file1' AND digest='digest1';

basically (refcount == (count1 + count2)) should be true and I am interested in 
finding out all such records in table C where this is not the case.



Thanks
Aveek
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=sb_akarmar...@yahoo.com

Re: Need help with query

[S�ndor Hal�sz]

 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

[Roy Lyseng]

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

[LAMP]

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

[S�ndor Hal�sz]

 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

[LAMP]

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

[Peter Brawley]

 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

[LAMP]

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

[LAMP]

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: Help with slow query

[Jim McNeely]

Shawn,

Thanks for the great help! It still is not working. I did an EXPLAIN on this 
query with your amended split out join statements and got this:

++-+---+---+---++-+--++-+
| id | select_type | table | type  | possible_keys | key| key_len | ref 
 | rows   | Extra   |
++-+---+---+---++-+--++-+
|  1 | SIMPLE  | a | range | apptdate  | apptdate   | 4   | 
NULL | 296148 | Using where |
|  1 | SIMPLE  | p | ALL   | NULL  | NULL   | NULL| 
NULL | 262462 | |
|  1 | SIMPLE  | t | ALL   | NULL  | NULL   | NULL| 
NULL | 311152 | |
|  1 | SIMPLE  | c | ref   | IdApptType| IdApptType | 51  | 
func |  1 | |
|  1 | SIMPLE  | af| ALL   | NULL  | NULL   | NULL| 
NULL |   5680 | |
++-+---+---+---++-+--++-+

What I'm not catching is why it says there is no key it can use for the patient 
table; here is a portion of the show create:

PRIMARY KEY (`zzk`),
  KEY `IdPatient` (`IdPatient`),
  KEY `SSN` (`SSN`),
  KEY `IdLastword` (`IdLastword`),
  KEY `DOB` (`DateOfBirth`),
  KEY `NameFirst` (`NameFirst`),
  KEY `NameLast` (`NameLast`)

So, the IdPatient is at least a POSSIBLE key, right?

On Mar 9, 2011, at 8:34 PM, Shawn Green (MySQL) wrote:

 Hi Jim,
 
 On 3/9/2011 17:57, Jim McNeely wrote:
 I am trying to set up an export query which is executing very slowly, and I 
 was hoping I could get some help. Here is the query:
 
 SELECT a.IdAppt, a.IdPatient,
 p.NameLast, p.NameFirst, p.NameMI,
 a.IdProcedure, a.ProcName, CAST(CONCAT(a.ApptDate,  , a.ApptTimeOut) AS 
 CHAR)
 ApptDateTime, a.ApptLenMin Duration,
 a.Room Primary_Resource_ID, 'R' as Primary_Resource_Type,
 t.Note, c.IdPractAffil DoctorID, af.PractName DoctorName, a.ICD9code ICD9,
 '??' Diagnosis_free_test
 
 from Appt_ a LEFT JOIN (patient_ p, today_ t, Copy_ c, Affil_ af)
 ON (a.IdPatient = p.IdPatient
 AND a.IdPatientDate = t.IdPatientDate
 AND CONCAT(a.IdAppt, '0') = c.IdApptType
 AND a.IdPriCarePhy = af.IdAffil)
 WHERE a.ApptDate= '2009-03-01';
 
 p.IdPatient, t.IdPatientDate, c.IdApptType, and af.IdAffil are all indexed. 
 Also I selectively took out join parameters until there was nothing but a 
 join on the patient table, and it was still slow, but when I took that out, 
 the query was extremely fast. What might I be doing wrong?
 
 Thanks,
 
 Jim McNeely
 
 The performance problem is with your Cartesian product. I think you meant to 
 write:
 
 from Appt_ a
 LEFT JOIN patient_ p
  ON a.IdPatient = p.IdPatient
 LEFT JOIN today_ t
  ON a.IdPatientDate = t.IdPatientDate
 LEFT JOIN Copy_ c
  ON CONCAT(a.IdAppt, '0') = c.IdApptType
 LEFT JOIN Affil_ af
  ON a.IdPriCarePhy = af.IdAffil
 
 As of 5.0.12, the comma operator for table joins was demoted in the 'order of 
 precedence' for query execution. That means that MySQL became more complaint 
 with the SQL standard but it also means that using a comma-join instead of an 
 explicit ANSI join can result in a Cartesian product more frequently.
 
 Try my style and compare how it works. If both styles are similarly slow, 
 collect the EXPLAIN plan for this query and share with the list.
 
 Yours,
 -- 
 Shawn Green
 MySQL Principal Technical Support Engineer
 Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
 Office: Blountville, TN
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=j...@newcenturydata.com
 

Re: Help with slow query

[Jim McNeely]

Rhino,

Thanks for the help and time! Actually, I thought the same thing, but what's 
weird is that is the only thing that doesn't slow it down. If I take out all of 
the join clauses EXCEPT that one the query runs virtually instantaneously. for 
some reason it will use the index in that case and it works. If I take out 
everything like this:

SELECT a.IdAppt, a.IdPatient, 
p.NameLast, p.NameFirst, p.NameMI

from Appt_ a
LEFT JOIN patient_ p
 ON a.IdPatient = p.IdPatient
WHERE a.ApptDate = '2009-03-01';

It is still utterly slow. EXPLAIN looks like this:

++-+---+---+---+--+-+--++-+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  
| rows   | Extra   |
++-+---+---+---+--+-+--++-+
|  1 | SIMPLE  | a | range | apptdate  | apptdate | 4   | NULL 
| 296166 | Using where |
|  1 | SIMPLE  | p | ALL   | NULL  | NULL | NULL| NULL 
| 262465 | |
++-+---+---+---+--+-+--++-+

But, very good try. I thought this might be it as well.

Thanks,

Jim McNeely

On Mar 10, 2011, at 9:05 AM, Rhino wrote:

 
 What I'm about to say may be completely out to lunch so don't be afraid to 
 dismiss it. I'm more a DB2 guy than a MySQL guy and I'm getting rusty on both 
 but I've always been struck by how similar the two dtabases are. Therefore, I 
 want to offer an insight on why this query would not perform terribly well in 
 DB2. I simply don't know if it is applicable to MySQL.
 
 In DB2, using functions on predicates (conditions in a WHERE clause), 
 prevents DB2 from using an index to satisfy that predicate. (Or at least it 
 used to: I'm not certain if that has been remedied in recent versions of the 
 DB2 optimizer.) Therefore, the CONCAT() function in the line
 AND CONCAT(a.IdAppt, '0') = c.IdApptType would ensure that no index on the 
 IdAppt column would be used to find the rows of the table that satisfied that 
 condition.
 
 My suggestion is that you try rewriting that condition to avoid using 
 CONCAT() - or any other function - and see if that helps the performance of 
 your query. That would require modifying your data to append a zero to the 
 end of the existing date in IdApptType column, which may or may not be a 
 reasonable thing to do. You'll have to decide about that.
 
 Again, I could be all wet here so don't have me killed if I'm wrong about 
 this :-) I'm just trying to help ;-)
 
 --
 Rhino
 
 On 2011-03-10 11:38, Jim McNeely wrote:
 Shawn,
 
 Thanks for the great help! It still is not working. I did an EXPLAIN on this 
 query with your amended split out join statements and got this:
 
 ++-+---+---+---++-+--++-+
 | id | select_type | table | type  | possible_keys | key| key_len | 
 ref  | rows   | Extra   |
 ++-+---+---+---++-+--++-+
 |  1 | SIMPLE  | a | range | apptdate  | apptdate   | 4   | 
 NULL | 296148 | Using where |
 |  1 | SIMPLE  | p | ALL   | NULL  | NULL   | NULL| 
 NULL | 262462 | |
 |  1 | SIMPLE  | t | ALL   | NULL  | NULL   | NULL| 
 NULL | 311152 | |
 |  1 | SIMPLE  | c | ref   | IdApptType| IdApptType | 51  | 
 func |  1 | |
 |  1 | SIMPLE  | af| ALL   | NULL  | NULL   | NULL| 
 NULL |   5680 | |
 ++-+---+---+---++-+--++-+
 
 What I'm not catching is why it says there is no key it can use for the 
 patient table; here is a portion of the show create:
 
 PRIMARY KEY (`zzk`),
   KEY `IdPatient` (`IdPatient`),
   KEY `SSN` (`SSN`),
   KEY `IdLastword` (`IdLastword`),
   KEY `DOB` (`DateOfBirth`),
   KEY `NameFirst` (`NameFirst`),
   KEY `NameLast` (`NameLast`)
 
 So, the IdPatient is at least a POSSIBLE key, right?
 
 On Mar 9, 2011, at 8:34 PM, Shawn Green (MySQL) wrote:
 
 Hi Jim,
 
 On 3/9/2011 17:57, Jim McNeely wrote:
 I am trying to set up an export query which is executing very slowly, and 
 I was hoping I could get some help. Here is the query:
 
 SELECT a.IdAppt, a.IdPatient,
 p.NameLast, p.NameFirst, p.NameMI,
 a.IdProcedure, a.ProcName, CAST(CONCAT(a.ApptDate,  , a.ApptTimeOut) AS 
 CHAR)
 ApptDateTime, a.ApptLenMin Duration,
 a.Room Primary_Resource_ID, 'R' as Primary_Resource_Type,
 t.Note, c.IdPractAffil DoctorID, af.PractName DoctorName, a.ICD9code ICD9,
 '??' Diagnosis_free_test
 
 from Appt_ a LEFT JOIN (patient_ p, today_ t, Copy_ c, Affil_ af)
 ON (a.IdPatient = p.IdPatient
 AND a.IdPatientDate = t.IdPatientDate
 AND CONCAT(a.IdAppt, '0') = c.IdApptType
 AND 

Re: Help with slow query

[mos]

If the optimizer chooses the wrong index, you can tell it what index to use.

SELECT a.IdAppt, a.IdPatient,
p.NameLast, p.NameFirst, p.NameMI

from Appt_ a force index(id_patient)
LEFT JOIN patient_ p
 ON a.IdPatient = p.IdPatient

WHERE a.ApptDate = '2009-03-01';

See http://dev.mysql.com/doc/refman/5.1/en/index-hints.html

Mike

At 11:32 AM 3/10/2011, Jim McNeely wrote:

Rhino,

Thanks for the help and time! Actually, I thought the same thing, but 
what's weird is that is the only thing that doesn't slow it down. If I 
take out all of the join clauses EXCEPT that one the query runs virtually 
instantaneously. for some reason it will use the index in that case and it 
works. If I take out everything like this:


SELECT a.IdAppt, a.IdPatient,
p.NameLast, p.NameFirst, p.NameMI

from Appt_ a
LEFT JOIN patient_ p
 ON a.IdPatient = p.IdPatient
WHERE a.ApptDate = '2009-03-01';

It is still utterly slow. EXPLAIN looks like this:

++-+---+---+---+--+-+--++-+
| id | select_type | table | type  | possible_keys | key  | key_len | 
ref  | rows   | Extra   |

++-+---+---+---+--+-+--++-+
|  1 | SIMPLE  | a | range | apptdate  | apptdate | 4   | 
NULL | 296166 | Using where |
|  1 | SIMPLE  | p | ALL   | NULL  | NULL | NULL| 
NULL | 262465 | |

++-+---+---+---+--+-+--++-+

But, very good try. I thought this might be it as well.

Thanks,

Jim McNeely

On Mar 10, 2011, at 9:05 AM, Rhino wrote:


 What I'm about to say may be completely out to lunch so don't be afraid 
to dismiss it. I'm more a DB2 guy than a MySQL guy and I'm getting rusty 
on both but I've always been struck by how similar the two dtabases are. 
Therefore, I want to offer an insight on why this query would not perform 
terribly well in DB2. I simply don't know if it is applicable to MySQL.


 In DB2, using functions on predicates (conditions in a WHERE clause), 
prevents DB2 from using an index to satisfy that predicate. (Or at least 
it used to: I'm not certain if that has been remedied in recent versions 
of the DB2 optimizer.) Therefore, the CONCAT() function in the line
 AND CONCAT(a.IdAppt, '0') = c.IdApptType would ensure that no index 
on the IdAppt column would be used to find the rows of the table that 
satisfied that condition.


 My suggestion is that you try rewriting that condition to avoid using 
CONCAT() - or any other function - and see if that helps the performance 
of your query. That would require modifying your data to append a zero to 
the end of the existing date in IdApptType column, which may or may not 
be a reasonable thing to do. You'll have to decide about that.


 Again, I could be all wet here so don't have me killed if I'm wrong 
about this :-) I'm just trying to help ;-)


 --
 Rhino

 On 2011-03-10 11:38, Jim McNeely wrote:
 Shawn,

 Thanks for the great help! It still is not working. I did an EXPLAIN 
on this query with your amended split out join statements and got this:


 
++-+---+---+---++-+--++-+
 | id | select_type | table | type  | possible_keys | key| 
key_len | ref  | rows   | Extra   |
 
++-+---+---+---++-+--++-+
 |  1 | SIMPLE  | a | range | apptdate  | apptdate   | 
4   | NULL | 296148 | Using where |
 |  1 | SIMPLE  | p | ALL   | NULL  | NULL   | 
NULL| NULL | 262462 | |
 |  1 | SIMPLE  | t | ALL   | NULL  | NULL   | 
NULL| NULL | 311152 | |
 |  1 | SIMPLE  | c | ref   | IdApptType| IdApptType | 
51  | func |  1 | |
 |  1 | SIMPLE  | af| ALL   | NULL  | NULL   | 
NULL| NULL |   5680 | |
 
++-+---+---+---++-+--++-+


 What I'm not catching is why it says there is no key it can use for 
the patient table; here is a portion of the show create:


 PRIMARY KEY (`zzk`),
   KEY `IdPatient` (`IdPatient`),
   KEY `SSN` (`SSN`),
   KEY `IdLastword` (`IdLastword`),
   KEY `DOB` (`DateOfBirth`),
   KEY `NameFirst` (`NameFirst`),
   KEY `NameLast` (`NameLast`)

 So, the IdPatient is at least a POSSIBLE key, right?

 On Mar 9, 2011, at 8:34 PM, Shawn Green (MySQL) wrote:

 Hi Jim,

 On 3/9/2011 17:57, Jim McNeely wrote:
 I am trying to set up an export query which is executing very 
slowly, and I was hoping I could get some help. Here is the query:


 SELECT a.IdAppt, a.IdPatient,
 p.NameLast, p.NameFirst, p.NameMI,
 a.IdProcedure, a.ProcName, CAST(CONCAT(a.ApptDate,  , 
a.ApptTimeOut) AS CHAR)

 ApptDateTime, a.ApptLenMin Duration,
 

Re: Help with slow query

[Shawn Green (MySQL)]

On 3/10/2011 12:32, Jim McNeely wrote:

Rhino,

Thanks for the help and time! Actually, I thought the same thing, but what's 
weird is that is the only thing that doesn't slow it down. If I take out all of 
the join clauses EXCEPT that one the query runs virtually instantaneously. for 
some reason it will use the index in that case and it works. If I take out 
everything like this:

SELECT a.IdAppt, a.IdPatient,
p.NameLast, p.NameFirst, p.NameMI

from Appt_ a
LEFT JOIN patient_ p
  ON a.IdPatient = p.IdPatient
WHERE a.ApptDate= '2009-03-01';

It is still utterly slow. EXPLAIN looks like this:

++-+---+---+---+--+-+--++-+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  
| rows   | Extra   |
++-+---+---+---+--+-+--++-+
|  1 | SIMPLE  | a | range | apptdate  | apptdate | 4   | NULL 
| 296166 | Using where |
|  1 | SIMPLE  | p | ALL   | NULL  | NULL | NULL| NULL 
| 262465 | |
++-+---+---+---+--+-+--++-+

But, very good try. I thought this might be it as well.


... snip ...

According to this report, there are no indexes on the `patient_` table 
that include the column `IdPatient` as the first column. Fix that and 
this query should be much faster.


--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Help with slow query

[Jim McNeely]

Shawn,

This is the first thing that I though as well, but here is a portion from the 
show create table for patient_:


PRIMARY KEY (`zzk`),
 KEY `IdPatient` (`IdPatient`),
 KEY `SSN` (`SSN`),
 KEY `IdLastword` (`IdLastword`),
 KEY `DOB` (`DateOfBirth`),
 KEY `NameFirst` (`NameFirst`),
 KEY `NameLast` (`NameLast`)

This extremely simple join is still massively slow.

Jim

On Mar 10, 2011, at 10:00 AM, Shawn Green (MySQL) wrote:

 On 3/10/2011 12:32, Jim McNeely wrote:
 Rhino,
 
 Thanks for the help and time! Actually, I thought the same thing, but what's 
 weird is that is the only thing that doesn't slow it down. If I take out all 
 of the join clauses EXCEPT that one the query runs virtually 
 instantaneously. for some reason it will use the index in that case and it 
 works. If I take out everything like this:
 
 SELECT a.IdAppt, a.IdPatient,
 p.NameLast, p.NameFirst, p.NameMI
 
 from Appt_ a
 LEFT JOIN patient_ p
  ON a.IdPatient = p.IdPatient
 WHERE a.ApptDate= '2009-03-01';
 
 It is still utterly slow. EXPLAIN looks like this:
 
 ++-+---+---+---+--+-+--++-+
 | id | select_type | table | type  | possible_keys | key  | key_len | 
 ref  | rows   | Extra   |
 ++-+---+---+---+--+-+--++-+
 |  1 | SIMPLE  | a | range | apptdate  | apptdate | 4   | 
 NULL | 296166 | Using where |
 |  1 | SIMPLE  | p | ALL   | NULL  | NULL | NULL| 
 NULL | 262465 | |
 ++-+---+---+---+--+-+--++-+
 
 But, very good try. I thought this might be it as well.
 
 ... snip ...
 
 According to this report, there are no indexes on the `patient_` table that 
 include the column `IdPatient` as the first column. Fix that and this query 
 should be much faster.
 
 -- 
 Shawn Green
 MySQL Principal Technical Support Engineer
 Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
 Office: Blountville, TN
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=j...@newcenturydata.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: Help with slow query

[Shawn Green (MySQL)]

On 3/10/2011 13:12, Jim McNeely wrote:

Shawn,

This is the first thing that I though as well, but here is a portion from the 
show create table for patient_:


PRIMARY KEY (`zzk`),
  KEY `IdPatient` (`IdPatient`),
  KEY `SSN` (`SSN`),
  KEY `IdLastword` (`IdLastword`),
  KEY `DOB` (`DateOfBirth`),
  KEY `NameFirst` (`NameFirst`),
  KEY `NameLast` (`NameLast`)

This extremely simple join is still massively slow.

Jim

On Mar 10, 2011, at 10:00 AM, Shawn Green (MySQL) wrote:


On 3/10/2011 12:32, Jim McNeely wrote:

Rhino,

Thanks for the help and time! Actually, I thought the same thing, but what's 
weird is that is the only thing that doesn't slow it down. If I take out all of 
the join clauses EXCEPT that one the query runs virtually instantaneously. for 
some reason it will use the index in that case and it works. If I take out 
everything like this:

SELECT a.IdAppt, a.IdPatient,
p.NameLast, p.NameFirst, p.NameMI

from Appt_ a
LEFT JOIN patient_ p
  ON a.IdPatient = p.IdPatient
WHERE a.ApptDate= '2009-03-01';



1) Verify that the indexes on `patient_` haven't been disabled

SHOW INDEXES FROM `patient_`;

http://dev.mysql.com/doc/refman/5.5/en/show-index.html

2) Verify that the data types of `Appt_`.`IdPatient` and 
`patient_`.`IdPatient` are not incompatible. (for example: one is 
varchar, the other int)


Thanks,
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Help with slow query

[Andy Wallace]

On 3/10/11 10:46 AM, Shawn Green (MySQL) wrote:

On 3/10/2011 12:32, Jim McNeely wrote:

Rhino,

Thanks for the help and time! Actually, I thought the same thing, but what's 
weird is that is the only thing that doesn't slow it down.
If I take out all of the join clauses EXCEPT that one the query runs virtually 
instantaneously. for some reason it will use the index in
that case and it works. If I take out everything like this:

SELECT a.IdAppt, a.IdPatient,
p.NameLast, p.NameFirst, p.NameMI

from Appt_ a
LEFT JOIN patient_ p
ON a.IdPatient = p.IdPatient
WHERE a.ApptDate= '2009-03-01';



1) Verify that the indexes on `patient_` haven't been disabled

SHOW INDEXES FROM `patient_`;

http://dev.mysql.com/doc/refman/5.5/en/show-index.html

2) Verify that the data types of `Appt_`.`IdPatient` and `patient_`.`IdPatient` 
are not incompatible. (for example: one is varchar, the
other int)


This last one can be HUGE. I tracked a big performance issue to this exact
problem - the columns used in the join had the same name, but different
data types. Correcting to be the same type (both ints) made a terrific
performance increase.



--
Andy Wallace
iHOUSEweb, Inc.
awall...@ihouseweb.com
(866) 645-7700 ext 219
--
There are two ways to build software:
Make it so simple that there are obviously no bugs,
or make it so complex that there are no obvious bugs.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Help with slow query

[Shawn Green (MySQL)]

Hi Jim,

On 3/9/2011 17:57, Jim McNeely wrote:

I am trying to set up an export query which is executing very slowly, and I was 
hoping I could get some help. Here is the query:

SELECT a.IdAppt, a.IdPatient,
p.NameLast, p.NameFirst, p.NameMI,
a.IdProcedure, a.ProcName, CAST(CONCAT(a.ApptDate,  , a.ApptTimeOut) AS CHAR)
ApptDateTime, a.ApptLenMin Duration,
a.Room Primary_Resource_ID, 'R' as Primary_Resource_Type,
t.Note, c.IdPractAffil DoctorID, af.PractName DoctorName, a.ICD9code ICD9,
'??' Diagnosis_free_test

from Appt_ a LEFT JOIN (patient_ p, today_ t, Copy_ c, Affil_ af)
ON (a.IdPatient = p.IdPatient
AND a.IdPatientDate = t.IdPatientDate
AND CONCAT(a.IdAppt, '0') = c.IdApptType
AND a.IdPriCarePhy = af.IdAffil)
WHERE a.ApptDate= '2009-03-01';

p.IdPatient, t.IdPatientDate, c.IdApptType, and af.IdAffil are all indexed. 
Also I selectively took out join parameters until there was nothing but a join 
on the patient table, and it was still slow, but when I took that out, the 
query was extremely fast. What might I be doing wrong?

Thanks,

Jim McNeely


The performance problem is with your Cartesian product. I think you 
meant to write:


from Appt_ a
LEFT JOIN patient_ p
  ON a.IdPatient = p.IdPatient
LEFT JOIN today_ t
  ON a.IdPatientDate = t.IdPatientDate
LEFT JOIN Copy_ c
  ON CONCAT(a.IdAppt, '0') = c.IdApptType
LEFT JOIN Affil_ af
  ON a.IdPriCarePhy = af.IdAffil

As of 5.0.12, the comma operator for table joins was demoted in the 
'order of precedence' for query execution. That means that MySQL became 
more complaint with the SQL standard but it also means that using a 
comma-join instead of an explicit ANSI join can result in a Cartesian 
product more frequently.


Try my style and compare how it works. If both styles are similarly 
slow, collect the EXPLAIN plan for this query and share with the list.


Yours,
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: running a mysql query inside a loop of another without a sync error

[Shawn Green (MySQL)]

Hello Delan,

On 1/19/2011 21:54, Delan Azabani wrote:

Hi all,

I'm using MySQL with C in a CGI application. I hope this is the right
list to ask for help.

If I have this simplified code:

MYSQL_RES *res;
MYSQL_ROW row;
mysql_query(mysql, some select query);
res = mysql_use_result(mysql);
while (row = mysql_fetch_row(res)) {
 MYSQL_RES *res2;
 MYSQL_ROW row2;
 mysql_query(mysql, some other select query using an id from the
first);
 res2 = mysql_use_result(mysql);
 /* ... */
 mysql_free_result(res2);
}
mysql_free_result(res);

Whenever I run the second query, inside the loop, I get the nasty
'commands out of sync' error. How can I run a select query while in a
loop fetching rows from another select query? Or, do I have to fetch all
the rows completely first and store them in memory (which wouldn't be
very 'nice' to do)?

If someone could help me with this problem, it would be greatly appreciated.



The mysql object you are using for your connection can only have one 
active query or result on it at a time. To have two sets of results 
working, you need a second independent connection to the MySQL server


http://dev.mysql.com/doc/refman/5.5/en/c-api-data-structures.html
###
MYSQL

This structure represents a handle to one database connection. It is 
used for almost all MySQL functions. You should not try to make a copy 
of a MYSQL structure. There is no guarantee that such a copy will be 
usable.

###


http://dev.mysql.com/doc/refman/5.5/en/mysql-real-connect.html
###
The first parameter should be the address of an existing MYSQL 
structure. Before calling mysql_real_connect() you must call 
mysql_init() to initialize the MYSQL structure. You can change a lot of 
connect options with the mysql_options() call. See Section 22.9.3.49, 
“mysql_options()”.

###

http://dev.mysql.com/doc/refman/5.5/en/threaded-clients.html
###
Two threads can't send a query to the MySQL server at the same time on 
the same connection. In particular, you have to ensure that between 
calls to mysql_query() and mysql_store_result() no other thread is using 
the same connection.

###

This same rule applies to attempting to process more than one query on 
the same connection. You must complete the first query before starting 
the second or you must open a separate connection to handle the second 
query.


Yours,
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: running a mysql query inside a loop of another without a sync error

[Michael Dykman]

From 1 feet, what you are attempting to do looks like it would be
very easily accomplished with a join, leaving you with 1 result set to
process.  I realize that isn't the question, but it might be a
solution.

 - michael dykman

On Wed, Jan 19, 2011 at 9:54 PM, Delan Azabani de...@azabani.com wrote:
 Hi all,

 I'm using MySQL with C in a CGI application. I hope this is the right
 list to ask for help.

 If I have this simplified code:

 MYSQL_RES *res;
 MYSQL_ROW row;
 mysql_query(mysql, some select query);
 res = mysql_use_result(mysql);
 while (row = mysql_fetch_row(res)) {
    MYSQL_RES *res2;
    MYSQL_ROW row2;
    mysql_query(mysql, some other select query using an id from the
 first);
    res2 = mysql_use_result(mysql);
    /* ... */
    mysql_free_result(res2);
 }
 mysql_free_result(res);

 Whenever I run the second query, inside the loop, I get the nasty
 'commands out of sync' error. How can I run a select query while in a
 loop fetching rows from another select query? Or, do I have to fetch all
 the rows completely first and store them in memory (which wouldn't be
 very 'nice' to do)?

 If someone could help me with this problem, it would be greatly appreciated.

 --
 Thanks and best regards,
 Delan Azabani
 http://azabani.com/

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:    http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com





-- 
 - michael dykman
 - mdyk...@gmail.com

 May the Source be with you.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Log Mysql slow query into table

[Wagner Bianchi]

Have a look on it:

mysql show variables like '%slow%';
+-+--+
| Variable_name   | Value|
+-+--+
| log_slow_queries| OFF  |
| slow_launch_time| 2|
| slow_query_log  | OFF  |
| slow_query_log_file | /var/lib/mysql/grey-slow.log |
+-+--+
4 rows in set (0.05 sec)

mysql set global slow_query_log = 1;
Query OK, 0 rows affected (0.00 sec)

mysql show variables like '%slow%';
+-+--+
| Variable_name   | Value|
+-+--+
| log_slow_queries| ON   |
| slow_launch_time| 2|
| slow_query_log  | ON   |
| slow_query_log_file | /var/lib/mysql/grey-slow.log |
+-+--+
4 rows in set (0.06 sec)

one advise is, be aware that guide its slow queries to a tabel will impact
MySQL's performance.

Best regards.
--
WB

Skype: wbianchijr (preferred way to contact me)


2010/11/30 Cool Cool cool_r...@yahoo.com

 Hi,

  I am trying to log slow queries into both file and table.
 I had set  as  SET GLOBAL log_output =`TABLE,FILE`; But it isnt getting
 logged
 into tables.

 Can I know if I need to create table or am I missing anything ?

 Thanks.

 Regards,
 Ram

Re: Howto optimize Distinct query over 1.3mil rows?

[Johnny Withers]

BIB_ID is VARCHAR, you should probably try

WHERE BIB_ID='464' so MySQL treats the value as a string

JW


On Tue, Sep 28, 2010 at 10:02 AM, John Stoffel j...@stoffel.org wrote:


 Hi,

 I'm running MySQL 5.0.51a-24+lenny3-log  on a Debian Lenny box with
 2Gb of RAM and a pair of dual core 2.6Ghz CPUs.  I'm using a pair of
 40Gb disks mirrored using MD (Linux software RAID) for both the OS and
 the storage of the mysql DBs.

 My problem child is doing this simple query:

   mysql select distinct Call_No from Newspaper_Issues
   mysql WHERE BIB_ID = 464;
   +--+
   | Call_No  |
   +--+
   | News |
   | NewsD CT |
   +--+
   2 rows in set (2.98 sec)

 The Newspaper_Issues table has 1.3 million rows, and has a structure
 like this:

   mysql describe  Newspaper_Issues;
   ++-+--+-+-++
   | Field  | Type| Null | Key | Default | Extra  |
   ++-+--+-+-++
   | Record_No  | int(11) | NO   | PRI | NULL| auto_increment |
   | BIB_ID | varchar(38) | NO   | MUL | NULL||
   | Issue_Date | date| NO   | MUL | NULL||
   | Type_Code  | char(1) | NO   | | r   ||
   | Condition_Code | char(1) | NO   | | o   ||
   | Format_Code| char(1) | NO   | | p   ||
   | Edition_Code   | char(1) | NO   | | n   ||
   | Date_Type_Code | char(1) | NO   | | n   ||
   | Ed_Type| tinyint(1)  | NO   | | 1   ||
   | RS_Code| char(1) | NO   | | c   ||
   | Call_No| varchar(36) | YES  | MUL | NULL||
   | Printed_Date   | varchar(10) | YES  | | NULL||
   | Update_Date| date| NO   | | NULL||
   ++-+--+-+-++
   13 rows in set (0.00 sec)


 I've tried adding various indexes, and reading up on howto optimize
 DISTINCT or GROUP BY queries, but I'm hitting a wall here.  My current
 indexes are:

 mysql show index from Newspaper_Issues;

 +--++--+--+-+---+-+--++--++-+
 | Table| Non_unique | Key_name | Seq_in_index |
 Column_name | Collation | Cardinality | Sub_part | Packed | Null |
 Index_type | Comment |

 +--++--+--+-+---+-+--++--++-+
 | Newspaper_Issues |  0 | PRIMARY  |1 |
 Record_No   | A | 1333298 | NULL | NULL   |  | BTREE
  | |
 | Newspaper_Issues |  1 | BIB_ID   |1 |
 BIB_ID  | A |   14980 | NULL | NULL   |  | BTREE
  | |
 | Newspaper_Issues |  1 | Call_No  |1 |
 Call_No | A | 927 | NULL | NULL   | YES  | BTREE
  | |
 | Newspaper_Issues |  1 | Issue_Date   |1 |
 Issue_Date  | A |   49381 | NULL | NULL   |  | BTREE
  | |
 | Newspaper_Issues |  1 | BIB_ID_Issue_Date|1 |
 BIB_ID  | A |   14980 | NULL | NULL   |  | BTREE
  | |
 | Newspaper_Issues |  1 | BIB_ID_Issue_Date|2 |
 Issue_Date  | A | 1333298 | NULL | NULL   |  | BTREE
  | |
 | Newspaper_Issues |  1 | call_no_short|1 |
 Call_No | A |  30 |6 | NULL   | YES  | BTREE
  | |
 | Newspaper_Issues |  1 | BIB_ID_call_no_short |1 |
 BIB_ID  | A |   14980 | NULL | NULL   |  | BTREE
  | |
 | Newspaper_Issues |  1 | BIB_ID_call_no_short |2 |
 Call_No | A |   15503 | NULL | NULL   | YES  | BTREE
  | |
 | Newspaper_Issues |  1 | call_no_bib_id   |1 |
 Call_No | A | 927 | NULL | NULL   | YES  | BTREE
  | |
 | Newspaper_Issues |  1 | call_no_bib_id   |2 |
 BIB_ID  | A |   15503 | NULL | NULL   |  | BTREE
  | |

 +--++--+--+-+---+-+--++--++-+
 11 rows in set (0.00 sec)


 So now when I do an explain on my query I get:

mysql explain select distinct(Call_No) from Newspaper_Issues WHERE
 BIB_ID = 464;

  
 

Re: Howto optimize Distinct query over 1.3mil rows?

[Johan De Meersman]

If Cal_NO is a recurring value, then yes, that is the way it should be done
in a relational schema.

Your index cardinality of 15.000 against 1.3 million rows is reasonable,
although not incredible; is your index cache large enough to acccomodate all
your indices ?


On Tue, Sep 28, 2010 at 5:02 PM, John Stoffel j...@stoffel.org wrote:


 Hi,

 I'm running MySQL 5.0.51a-24+lenny3-log  on a Debian Lenny box with
 2Gb of RAM and a pair of dual core 2.6Ghz CPUs.  I'm using a pair of
 40Gb disks mirrored using MD (Linux software RAID) for both the OS and
 the storage of the mysql DBs.

 My problem child is doing this simple query:

   mysql select distinct Call_No from Newspaper_Issues
   mysql WHERE BIB_ID = 464;
   +--+
   | Call_No  |
   +--+
   | News |
   | NewsD CT |
   +--+
   2 rows in set (2.98 sec)

 The Newspaper_Issues table has 1.3 million rows, and has a structure
 like this:

   mysql describe  Newspaper_Issues;
   ++-+--+-+-++
   | Field  | Type| Null | Key | Default | Extra  |
   ++-+--+-+-++
   | Record_No  | int(11) | NO   | PRI | NULL| auto_increment |
   | BIB_ID | varchar(38) | NO   | MUL | NULL||
   | Issue_Date | date| NO   | MUL | NULL||
   | Type_Code  | char(1) | NO   | | r   ||
   | Condition_Code | char(1) | NO   | | o   ||
   | Format_Code| char(1) | NO   | | p   ||
   | Edition_Code   | char(1) | NO   | | n   ||
   | Date_Type_Code | char(1) | NO   | | n   ||
   | Ed_Type| tinyint(1)  | NO   | | 1   ||
   | RS_Code| char(1) | NO   | | c   ||
   | Call_No| varchar(36) | YES  | MUL | NULL||
   | Printed_Date   | varchar(10) | YES  | | NULL||
   | Update_Date| date| NO   | | NULL||
   ++-+--+-+-++
   13 rows in set (0.00 sec)


 I've tried adding various indexes, and reading up on howto optimize
 DISTINCT or GROUP BY queries, but I'm hitting a wall here.  My current
 indexes are:

 mysql show index from Newspaper_Issues;

 +--++--+--+-+---+-+--++--++-+
 | Table| Non_unique | Key_name | Seq_in_index |
 Column_name | Collation | Cardinality | Sub_part | Packed | Null |
 Index_type | Comment |

 +--++--+--+-+---+-+--++--++-+
 | Newspaper_Issues |  0 | PRIMARY  |1 |
 Record_No   | A | 1333298 | NULL | NULL   |  | BTREE
  | |
 | Newspaper_Issues |  1 | BIB_ID   |1 |
 BIB_ID  | A |   14980 | NULL | NULL   |  | BTREE
  | |
 | Newspaper_Issues |  1 | Call_No  |1 |
 Call_No | A | 927 | NULL | NULL   | YES  | BTREE
  | |
 | Newspaper_Issues |  1 | Issue_Date   |1 |
 Issue_Date  | A |   49381 | NULL | NULL   |  | BTREE
  | |
 | Newspaper_Issues |  1 | BIB_ID_Issue_Date|1 |
 BIB_ID  | A |   14980 | NULL | NULL   |  | BTREE
  | |
 | Newspaper_Issues |  1 | BIB_ID_Issue_Date|2 |
 Issue_Date  | A | 1333298 | NULL | NULL   |  | BTREE
  | |
 | Newspaper_Issues |  1 | call_no_short|1 |
 Call_No | A |  30 |6 | NULL   | YES  | BTREE
  | |
 | Newspaper_Issues |  1 | BIB_ID_call_no_short |1 |
 BIB_ID  | A |   14980 | NULL | NULL   |  | BTREE
  | |
 | Newspaper_Issues |  1 | BIB_ID_call_no_short |2 |
 Call_No | A |   15503 | NULL | NULL   | YES  | BTREE
  | |
 | Newspaper_Issues |  1 | call_no_bib_id   |1 |
 Call_No | A | 927 | NULL | NULL   | YES  | BTREE
  | |
 | Newspaper_Issues |  1 | call_no_bib_id   |2 |
 BIB_ID  | A |   15503 | NULL | NULL   |  | BTREE
  | |

 +--++--+--+-+---+-+--++--++-+
 11 rows in set (0.00 sec)


 So now when I do an explain on my query I get:

Re: Howto optimize Distinct query over 1.3mil rows?

[John Stoffel]

Johnny BIB_ID is VARCHAR, you should probably try
Johnny WHERE BIB_ID='464' so MySQL treats the value as a string

Wow!  What a difference that makes!  Time to A) update my queries, or
B) fix the DB schema to NOT use varchar there.  

mysql select SQL_NO_CACHE distinct(Call_No) from Newspaper_Issues
mysql WHERE BIB_ID = 464;
+--+
| Call_No  |
+--+
| News | 
| NewsD CT | 
+--+
2 rows in set (3.06 sec)

mysql select SQL_NO_CACHE distinct(Call_No) from Newspaper_Issues
mysql WHERE BIB_ID = '464';
+--+
| Call_No  |
+--+
| News | 
| NewsD CT | 
+--+
2 rows in set (0.02 sec)

Thanks a ton for your help, I would have never figured this out, esp
since I was looking down all the wrong rat holes.  

Thanks,
John

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Help needed on query on multiple tables

[Steven Staples]

How about this?

SELECT
`first_table`.`names`
, `first_table`.`version`
, (SELECT
   COUNT(`other_table`.`names`)
   FROM `other_table`
   WHERE `other_table`.`this_id` = `first_table`.`id`) AS 'count'
FROM `first_table`
WHERE `first_table`.`progress`  0;


Granted, you have not provided structure or names of the tables so this is
just my interpretation, but maybe something like this could give you a
starting point?

Steven Staples


 -Original Message-
 From: Michael Stroh [mailto:st...@astroh.org]
 Sent: June 3, 2010 11:24 AM
 To: MySql
 Subject: Help needed on query on multiple tables
 
 Hi everyone. I'm trying to create a certain MySQL query but I'm not sure
 how to do it. Here is a stripped down version of the result I'm aiming
for.
 I'm pretty new to queries that act on multiple tables, so apologize if
this
 is a very stupid question.
 
 I have one table (data) that has two columns (names and progress). I have
a
 second table (items) that has two columns (names and version). I'd like to
 do a query that produces the name of every record in data that has
progress
 set to 0 and the number of records in the items table that have the same
 value in each table.names field.
 
 I can perform this by using two sets of queries, one that queries the data
 table and then loop through the names to do a count(names) query, but I'm
 not sure if I can somehow do it in one query.
 
 Thanks in advance!
 Michael
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net
 
 No virus found in this incoming message.
 Checked by AVG - www.avg.com
 Version: 9.0.829 / Virus Database: 271.1.1/2895 - Release Date: 06/03/10
 02:25:00


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Help needed on query on multiple tables

[Michael Stroh]

Thanks! That did it perfectly!

Michael


On Jun 3, 2010, at 11:45 AM, Steven Staples wrote:

 How about this?
 
 SELECT
`first_table`.`names`
, `first_table`.`version`
, (SELECT
   COUNT(`other_table`.`names`)
   FROM `other_table`
   WHERE `other_table`.`this_id` = `first_table`.`id`) AS 'count'
 FROM `first_table`
 WHERE `first_table`.`progress`  0;
 
 
 Granted, you have not provided structure or names of the tables so this is
 just my interpretation, but maybe something like this could give you a
 starting point?
 
 Steven Staples
 
 
 -Original Message-
 From: Michael Stroh [mailto:st...@astroh.org]
 Sent: June 3, 2010 11:24 AM
 To: MySql
 Subject: Help needed on query on multiple tables
 
 Hi everyone. I'm trying to create a certain MySQL query but I'm not sure
 how to do it. Here is a stripped down version of the result I'm aiming
 for.
 I'm pretty new to queries that act on multiple tables, so apologize if
 this
 is a very stupid question.
 
 I have one table (data) that has two columns (names and progress). I have
 a
 second table (items) that has two columns (names and version). I'd like to
 do a query that produces the name of every record in data that has
 progress
 set to 0 and the number of records in the items table that have the same
 value in each table.names field.
 
 I can perform this by using two sets of queries, one that queries the data
 table and then loop through the names to do a count(names) query, but I'm
 not sure if I can somehow do it in one query.
 
 Thanks in advance!
 Michael
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net
 
 No virus found in this incoming message.
 Checked by AVG - www.avg.com
 Version: 9.0.829 / Virus Database: 271.1.1/2895 - Release Date: 06/03/10
 02:25:00
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=st...@astroh.org
 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Help needed on query on multiple tables

[Steven Staples]

I am glad that I was able to help someone finally :)

There may be other ways to do this, but that was what first came to mind.
I would maybe run an explain on that query to ensure that it is using
indexes.

Steven Staples


 -Original Message-
 From: Michael Stroh [mailto:st...@astroh.org]
 Sent: June 3, 2010 11:55 AM
 To: Steven Staples
 Cc: 'MySql'
 Subject: Re: Help needed on query on multiple tables
 
 Thanks! That did it perfectly!
 
 Michael
 
 
 On Jun 3, 2010, at 11:45 AM, Steven Staples wrote:
 
  How about this?
 
  SELECT
 `first_table`.`names`
 , `first_table`.`version`
 , (SELECT
COUNT(`other_table`.`names`)
FROM `other_table`
WHERE `other_table`.`this_id` = `first_table`.`id`) AS 'count'
  FROM `first_table`
  WHERE `first_table`.`progress`  0;
 
 
  Granted, you have not provided structure or names of the tables so this
 is
  just my interpretation, but maybe something like this could give you a
  starting point?
 
  Steven Staples
 
 
  -Original Message-
  From: Michael Stroh [mailto:st...@astroh.org]
  Sent: June 3, 2010 11:24 AM
  To: MySql
  Subject: Help needed on query on multiple tables
 
  Hi everyone. I'm trying to create a certain MySQL query but I'm not
sure
  how to do it. Here is a stripped down version of the result I'm aiming
  for.
  I'm pretty new to queries that act on multiple tables, so apologize if
  this
  is a very stupid question.
 
  I have one table (data) that has two columns (names and progress). I
 have
  a
  second table (items) that has two columns (names and version). I'd like
 to
  do a query that produces the name of every record in data that has
  progress
  set to 0 and the number of records in the items table that have the
same
  value in each table.names field.
 
  I can perform this by using two sets of queries, one that queries the
 data
  table and then loop through the names to do a count(names) query, but
 I'm
  not sure if I can somehow do it in one query.
 
  Thanks in advance!
  Michael
 
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net
 
  No virus found in this incoming message.
  Checked by AVG - www.avg.com
  Version: 9.0.829 / Virus Database: 271.1.1/2895 - Release Date:
06/03/10
  02:25:00
 
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/mysql?unsub=st...@astroh.org
 
 
 No virus found in this incoming message.
 Checked by AVG - www.avg.com
 Version: 9.0.829 / Virus Database: 271.1.1/2895 - Release Date: 06/03/10
 02:25:00


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Question on http query

[Michael Dykman]

MySQL is a tradition Relational DataBase System.  It underlays
something like 80% (somebody correct me if I'm out-of-date here) of
the http applications populating the internet.  While some RDBMSs
offer extensions for RESP-like HTTP implementations, MySQL does not
support this directly.  It can be used in conjunction with a multitude
of languages and frameworks.

If you are just getting started on this path, you might want to look
at something like python or ruby or PHP, they all can handle HTTP
requests very efficiently and have nice interfaces to MySQL.

Best of Luck.

 - michael dykman

On Wed, Jun 2, 2010 at 7:15 PM, Kandy Wong kan...@triumf.ca wrote:
 Hi,

 I'd like to know if MySQL server supports http queries?
 Thanks.

 Kandy

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:    http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com





-- 
 - michael dykman
 - mdyk...@gmail.com

 May the Source be with you.

--
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

[John Daisley]

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

[Ananda Kumar]

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: Why does this query take so long?

[Joerg Bruehe]

Hi René, everybody!


René Fournier wrote:
 [[...]] However, even if the Index can't fit in memory (4GB of RAM, lots 
 free), just reading it from disk should allow sub-millisecond response, no?

No chance!
Of course, performance of disk drives varies, but AFAIK typical values
are in the range of 7 - 10 ms per random access.
Assuming the (very unlikely) optimum case of one index access and one
data access, this would put you into the range of 15 - 20 ms just for
fetching the stuff from disk, not including any CPU time to traverse the
data structures etc.

Just do some math:
A disk with 7,200 rpm has 120 revolutions per second, so it needs a bit
more than 8 milliseconds per revolution.
Random access means you have to wait (on average) for half a revolution
(4 ms) until the desired block passes the disk head, and before that the
head needs to be positioned at the proper cylinder (the drive's data
sheet might give that time).

I guess that even with SSD you will not reach sub-millisecond response
times if the data is not in RAM.

 
 Strange thing is that I've used my laptop for benchmarking for the past five 
 years and it's always produced results fairly typical or at least consistent 
 in relation to our servers. This new thing is... new.

IMO, the most influential factor in single-user database benchmarks are
- disk performance
- RAM size for caches, cache replacement
- history, cache preloading

Their relative importance will vary, depending especially on data size.
As long as your data size is small enough that RAM differences between
server and laptop don't matter too much, performance on the laptop may
be a good prediction of that on the server.

With multi-user benchmarks, CPU performance, number of cores etc becomes
another important factor, again the relative weights will vary.


Regards,
Jörg

-- 
Joerg Bruehe,  MySQL Build Team,  joerg.bru...@sun.com
Sun Microsystems GmbH,   Komturstraße 18a,   D-12099 Berlin
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel
Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Why does this query take so long?

[René Fournier]

Even weirder, I came back to my laptop a couple hours later. And now the same 
queries are taking 3-10 seconds instead of 0.01 seconds. What could be causing 
this?

On 2009-12-28, at 1:19 PM, René Fournier wrote:

 Hmm, weird. I just re-imported the data (after drop/create table, etc.), and 
 now the spatial queries run fast. 
 Has anyone seen this sort of thing happen? Maybe the Index got corrupted 
 somehow, and then MySQL had to do a full table scan (even though EXPLAIN 
 indicated it would use the Spatial Index)?
 
 
 
 On 2009-12-28, at 9:28 AM, René Fournier wrote:
 
 So just to clarify (hello?), the index which *should* be used (EXPLAIN says 
 so) and *should* make the query run faster than 4 seconds either isn't used 
 (why?) or simply doesn't speed up the query (again, why?).
 
 ++-+---+---+---+---+-+--+--+-+
 | id | select_type | table | type  | possible_keys | key   | key_len | ref  
 | rows | Extra   |
 ++-+---+---+---+---+-+--+--+-+
 |  1 | SIMPLE  | qs| range | coord | coord | 27  | NULL 
 | 5260 | Using where | 
 ++-+---+---+---+---+-+--+--+-+
 
 SELECT id, province, latitude, longitude, AsText(coordinates), s_ts_r_m, 
 quartersection FROM qs WHERE MBRContains(GeomFromText('POLYGON((51.62582589 
 -114.82248918,51.65126254 -114.82248918,51.65126254 
 -114.78150333,51.62582589 -114.78150333,51.62582589 -114.82248918))'), 
 coordinates)
 
 8 rows in set (3.87 sec)
 
 
 On 2009-12-27, at 3:59 PM, René Fournier wrote:
 
 So... there is an index, and it's supposedly used:
 
 mysql EXPLAIN SELECT id, province, latitude, longitude, 
 AsText(coordinates), s_ts_r_m, quartersection FROM qs WHERE 
 MBRContains(GeomFromText('POLYGON((51.62582589 -114.82248918,51.65126254 
 -114.82248918,51.65126254 -114.78150333,51.62582589 
 -114.78150333,51.62582589 -114.82248918))'), coordinates);
 ++-+---+---+---+---+-+--+--+-+
 | id | select_type | table | type  | possible_keys | key   | key_len | ref  
 | rows | Extra   |
 ++-+---+---+---+---+-+--+--+-+
 |  1 | SIMPLE  | qs| range | coord | coord | 27  | NULL 
 | 5260 | Using where | 
 ++-+---+---+---+---+-+--+--+-+
 1 row in set (0.00 sec)
 
 But when I run the query:
 
 mysql SELECT id, province, latitude, longitude, AsText(coordinates), 
 s_ts_r_m, quartersection FROM qs WHERE 
 MBRContains(GeomFromText('POLYGON((51.62582589 -114.82248918,51.65126254 
 -114.82248918,51.65126254 -114.78150333,51.62582589 
 -114.78150333,51.62582589 -114.82248918))'), coordinates)
  - ;
 ++--+-+---+--+--++
 | id | province | latitude| longitude | AsText(coordinates) 
  | s_ts_r_m | quartersection |
 ++--+-+---+--+--++
 | 444543 | AB   | 51.63495228 | -114.79282412 | POINT(51.63495228 
 -114.79282412) | 04-031-06 W5 | N4 | 
 | 444564 | AB   | 51.64941120 | -114.79283278 | POINT(51.6494112 
 -114.79283278)  | 09-031-06 W5 | N4 | 
 | 444548 | AB   | 51.63497789 | -114.81645649 | POINT(51.63497789 
 -114.81645649) | 05-031-06 W5 | N4 | 
 | 444561 | AB   | 51.64943119 | -114.81643801 | POINT(51.64943119 
 -114.81643801) | 08-031-06 W5 | N4 | 
 | 444547 | AB   | 51.62775680 | -114.80475858 | POINT(51.6277568 
 -114.80475858)  | 05-031-06 W5 | E4 | 
 | 444549 | AB   | 51.63498028 | -114.80479925 | POINT(51.63498028 
 -114.80479925) | 05-031-06 W5 | NE | 
 | 444560 | AB   | 51.64220442 | -114.80478262 | POINT(51.64220442 
 -114.80478262) | 08-031-06 W5 | E4 | 
 | 444562 | AB   | 51.64942854 | -114.80476596 | POINT(51.64942854 
 -114.80476596) | 08-031-06 W5 | NE | 
 ++--+-+---+--+--++
 8 rows in set (3.87 sec)
 
 So, there are ~2.6 million rows in the table, and coordinates is 
 spatially-indexed. Yet the query requires nearly 4 seconds. What am I doing 
 wrong?
 
 ...REne
 
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub...@renefournier.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: Why does this query take so long?

[Gavin Towey]

It sounds like your laptop might be paging mysql's memory to disk or something 
like that.  Your laptop may not be the most reliable source for benchmarks.

Regards,
Gavin Towey

-Original Message-
From: René Fournier [mailto:m...@renefournier.com]
Sent: Monday, December 28, 2009 2:16 AM
To: René Fournier
Cc: mysql
Subject: Re: Why does this query take so long?

Even weirder, I came back to my laptop a couple hours later. And now the same 
queries are taking 3-10 seconds instead of 0.01 seconds. What could be causing 
this?

On 2009-12-28, at 1:19 PM, René Fournier wrote:

 Hmm, weird. I just re-imported the data (after drop/create table, etc.), and 
 now the spatial queries run fast.
 Has anyone seen this sort of thing happen? Maybe the Index got corrupted 
 somehow, and then MySQL had to do a full table scan (even though EXPLAIN 
 indicated it would use the Spatial Index)?



 On 2009-12-28, at 9:28 AM, René Fournier wrote:

 So just to clarify (hello?), the index which *should* be used (EXPLAIN says 
 so) and *should* make the query run faster than 4 seconds either isn't used 
 (why?) or simply doesn't speed up the query (again, why?).

 ++-+---+---+---+---+-+--+--+-+
 | id | select_type | table | type  | possible_keys | key   | key_len | ref  
 | rows | Extra   |
 ++-+---+---+---+---+-+--+--+-+
 |  1 | SIMPLE  | qs| range | coord | coord | 27  | NULL 
 | 5260 | Using where |
 ++-+---+---+---+---+-+--+--+-+

 SELECT id, province, latitude, longitude, AsText(coordinates), s_ts_r_m, 
 quartersection FROM qs WHERE MBRContains(GeomFromText('POLYGON((51.62582589 
 -114.82248918,51.65126254 -114.82248918,51.65126254 
 -114.78150333,51.62582589 -114.78150333,51.62582589 -114.82248918))'), 
 coordinates)

 8 rows in set (3.87 sec)


 On 2009-12-27, at 3:59 PM, René Fournier wrote:

 So... there is an index, and it's supposedly used:

 mysql EXPLAIN SELECT id, province, latitude, longitude, 
 AsText(coordinates), s_ts_r_m, quartersection FROM qs WHERE 
 MBRContains(GeomFromText('POLYGON((51.62582589 -114.82248918,51.65126254 
 -114.82248918,51.65126254 -114.78150333,51.62582589 
 -114.78150333,51.62582589 -114.82248918))'), coordinates);
 ++-+---+---+---+---+-+--+--+-+
 | id | select_type | table | type  | possible_keys | key   | key_len | ref  
 | rows | Extra   |
 ++-+---+---+---+---+-+--+--+-+
 |  1 | SIMPLE  | qs| range | coord | coord | 27  | NULL 
 | 5260 | Using where |
 ++-+---+---+---+---+-+--+--+-+
 1 row in set (0.00 sec)

 But when I run the query:

 mysql SELECT id, province, latitude, longitude, AsText(coordinates), 
 s_ts_r_m, quartersection FROM qs WHERE 
 MBRContains(GeomFromText('POLYGON((51.62582589 -114.82248918,51.65126254 
 -114.82248918,51.65126254 -114.78150333,51.62582589 
 -114.78150333,51.62582589 -114.82248918))'), coordinates)
  - ;
 ++--+-+---+--+--++
 | id | province | latitude| longitude | AsText(coordinates) 
  | s_ts_r_m | quartersection |
 ++--+-+---+--+--++
 | 444543 | AB   | 51.63495228 | -114.79282412 | POINT(51.63495228 
 -114.79282412) | 04-031-06 W5 | N4 |
 | 444564 | AB   | 51.64941120 | -114.79283278 | POINT(51.6494112 
 -114.79283278)  | 09-031-06 W5 | N4 |
 | 444548 | AB   | 51.63497789 | -114.81645649 | POINT(51.63497789 
 -114.81645649) | 05-031-06 W5 | N4 |
 | 444561 | AB   | 51.64943119 | -114.81643801 | POINT(51.64943119 
 -114.81643801) | 08-031-06 W5 | N4 |
 | 444547 | AB   | 51.62775680 | -114.80475858 | POINT(51.6277568 
 -114.80475858)  | 05-031-06 W5 | E4 |
 | 444549 | AB   | 51.63498028 | -114.80479925 | POINT(51.63498028 
 -114.80479925) | 05-031-06 W5 | NE |
 | 444560 | AB   | 51.64220442 | -114.80478262 | POINT(51.64220442 
 -114.80478262) | 08-031-06 W5 | E4 |
 | 444562 | AB   | 51.64942854 | -114.80476596 | POINT(51.64942854 
 -114.80476596) | 08-031-06 W5 | NE |
 ++--+-+---+--+--++
 8 rows in set (3.87 sec)

 So, there are ~2.6 million rows in the table, and coordinates is 
 spatially-indexed. Yet the query requires nearly 4 seconds. What am I doing 
 wrong?

 ...REne



 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com

Re: Why does this query take so long?

[René Fournier]

I think you might be right. The good-to-poor performance I'm seeing is so 
intermittent. And I see now that it's also with other queries, though not as 
extremely obvious as the spatial queries. However, even if the Index can't fit 
in memory (4GB of RAM, lots free), just reading it from disk should allow 
sub-millisecond response, no?

Strange thing is that I've used my laptop for benchmarking for the past five 
years and it's always produced results fairly typical or at least consistent in 
relation to our servers. This new thing is... new.

On 2009-12-29, at 3:18 AM, Gavin Towey wrote:

 It sounds like your laptop might be paging mysql's memory to disk or 
 something like that.  Your laptop may not be the most reliable source for 
 benchmarks.
 
 Regards,
 Gavin Towey
 
 -Original Message-
 From: René Fournier [mailto:m...@renefournier.com]
 Sent: Monday, December 28, 2009 2:16 AM
 To: René Fournier
 Cc: mysql
 Subject: Re: Why does this query take so long?
 
 Even weirder, I came back to my laptop a couple hours later. And now the same 
 queries are taking 3-10 seconds instead of 0.01 seconds. What could be 
 causing this?
 
 On 2009-12-28, at 1:19 PM, René Fournier wrote:
 
 Hmm, weird. I just re-imported the data (after drop/create table, etc.), and 
 now the spatial queries run fast.
 Has anyone seen this sort of thing happen? Maybe the Index got corrupted 
 somehow, and then MySQL had to do a full table scan (even though EXPLAIN 
 indicated it would use the Spatial Index)?
 
 
 
 On 2009-12-28, at 9:28 AM, René Fournier wrote:
 
 So just to clarify (hello?), the index which *should* be used (EXPLAIN says 
 so) and *should* make the query run faster than 4 seconds either isn't used 
 (why?) or simply doesn't speed up the query (again, why?).
 
 ++-+---+---+---+---+-+--+--+-+
 | id | select_type | table | type  | possible_keys | key   | key_len | ref  
 | rows | Extra   |
 ++-+---+---+---+---+-+--+--+-+
 |  1 | SIMPLE  | qs| range | coord | coord | 27  | NULL 
 | 5260 | Using where |
 ++-+---+---+---+---+-+--+--+-+
 
 SELECT id, province, latitude, longitude, AsText(coordinates), s_ts_r_m, 
 quartersection FROM qs WHERE MBRContains(GeomFromText('POLYGON((51.62582589 
 -114.82248918,51.65126254 -114.82248918,51.65126254 
 -114.78150333,51.62582589 -114.78150333,51.62582589 -114.82248918))'), 
 coordinates)
 
 8 rows in set (3.87 sec)
 
 
 On 2009-12-27, at 3:59 PM, René Fournier wrote:
 
 So... there is an index, and it's supposedly used:
 
 mysql EXPLAIN SELECT id, province, latitude, longitude, 
 AsText(coordinates), s_ts_r_m, quartersection FROM qs WHERE 
 MBRContains(GeomFromText('POLYGON((51.62582589 -114.82248918,51.65126254 
 -114.82248918,51.65126254 -114.78150333,51.62582589 
 -114.78150333,51.62582589 -114.82248918))'), coordinates);
 ++-+---+---+---+---+-+--+--+-+
 | id | select_type | table | type  | possible_keys | key   | key_len | ref 
  | rows | Extra   |
 ++-+---+---+---+---+-+--+--+-+
 |  1 | SIMPLE  | qs| range | coord | coord | 27  | 
 NULL | 5260 | Using where |
 ++-+---+---+---+---+-+--+--+-+
 1 row in set (0.00 sec)
 
 But when I run the query:
 
 mysql SELECT id, province, latitude, longitude, AsText(coordinates), 
 s_ts_r_m, quartersection FROM qs WHERE 
 MBRContains(GeomFromText('POLYGON((51.62582589 -114.82248918,51.65126254 
 -114.82248918,51.65126254 -114.78150333,51.62582589 
 -114.78150333,51.62582589 -114.82248918))'), coordinates)
 - ;
 ++--+-+---+--+--++
 | id | province | latitude| longitude | AsText(coordinates)
   | s_ts_r_m | quartersection |
 ++--+-+---+--+--++
 | 444543 | AB   | 51.63495228 | -114.79282412 | POINT(51.63495228 
 -114.79282412) | 04-031-06 W5 | N4 |
 | 444564 | AB   | 51.64941120 | -114.79283278 | POINT(51.6494112 
 -114.79283278)  | 09-031-06 W5 | N4 |
 | 444548 | AB   | 51.63497789 | -114.81645649 | POINT(51.63497789 
 -114.81645649) | 05-031-06 W5 | N4 |
 | 444561 | AB   | 51.64943119 | -114.81643801 | POINT(51.64943119 
 -114.81643801) | 08-031-06 W5 | N4 |
 | 444547 | AB   | 51.62775680 | -114.80475858 | POINT(51.6277568 
 -114.80475858)  | 05-031-06 W5 | E4 |
 | 444549 | AB   | 51.63498028 | -114.80479925 | POINT(51.63498028 
 -114.80479925) | 05-031-06 W5 | NE

Re: Why does this query take so long?

[René Fournier]

So just to clarify (hello?), the index which *should* be used (EXPLAIN says so) 
and *should* make the query run faster than 4 seconds either isn't used (why?) 
or simply doesn't speed up the query (again, why?).

++-+---+---+---+---+-+--+--+-+
| id | select_type | table | type  | possible_keys | key   | key_len | ref  | 
rows | Extra   |
++-+---+---+---+---+-+--+--+-+
|  1 | SIMPLE  | qs| range | coord | coord | 27  | NULL | 
5260 | Using where | 
++-+---+---+---+---+-+--+--+-+

SELECT id, province, latitude, longitude, AsText(coordinates), s_ts_r_m, 
quartersection FROM qs WHERE MBRContains(GeomFromText('POLYGON((51.62582589 
-114.82248918,51.65126254 -114.82248918,51.65126254 -114.78150333,51.62582589 
-114.78150333,51.62582589 -114.82248918))'), coordinates)

8 rows in set (3.87 sec)


On 2009-12-27, at 3:59 PM, René Fournier wrote:

 So... there is an index, and it's supposedly used:
 
 mysql EXPLAIN SELECT id, province, latitude, longitude, AsText(coordinates), 
 s_ts_r_m, quartersection FROM qs WHERE 
 MBRContains(GeomFromText('POLYGON((51.62582589 -114.82248918,51.65126254 
 -114.82248918,51.65126254 -114.78150333,51.62582589 -114.78150333,51.62582589 
 -114.82248918))'), coordinates);
 ++-+---+---+---+---+-+--+--+-+
 | id | select_type | table | type  | possible_keys | key   | key_len | ref  | 
 rows | Extra   |
 ++-+---+---+---+---+-+--+--+-+
 |  1 | SIMPLE  | qs| range | coord | coord | 27  | NULL | 
 5260 | Using where | 
 ++-+---+---+---+---+-+--+--+-+
 1 row in set (0.00 sec)
 
 But when I run the query:
 
 mysql SELECT id, province, latitude, longitude, AsText(coordinates), 
 s_ts_r_m, quartersection FROM qs WHERE 
 MBRContains(GeomFromText('POLYGON((51.62582589 -114.82248918,51.65126254 
 -114.82248918,51.65126254 -114.78150333,51.62582589 -114.78150333,51.62582589 
 -114.82248918))'), coordinates)
- ;
 ++--+-+---+--+--++
 | id | province | latitude| longitude | AsText(coordinates)   
| s_ts_r_m | quartersection |
 ++--+-+---+--+--++
 | 444543 | AB   | 51.63495228 | -114.79282412 | POINT(51.63495228 
 -114.79282412) | 04-031-06 W5 | N4 | 
 | 444564 | AB   | 51.64941120 | -114.79283278 | POINT(51.6494112 
 -114.79283278)  | 09-031-06 W5 | N4 | 
 | 444548 | AB   | 51.63497789 | -114.81645649 | POINT(51.63497789 
 -114.81645649) | 05-031-06 W5 | N4 | 
 | 444561 | AB   | 51.64943119 | -114.81643801 | POINT(51.64943119 
 -114.81643801) | 08-031-06 W5 | N4 | 
 | 444547 | AB   | 51.62775680 | -114.80475858 | POINT(51.6277568 
 -114.80475858)  | 05-031-06 W5 | E4 | 
 | 444549 | AB   | 51.63498028 | -114.80479925 | POINT(51.63498028 
 -114.80479925) | 05-031-06 W5 | NE | 
 | 444560 | AB   | 51.64220442 | -114.80478262 | POINT(51.64220442 
 -114.80478262) | 08-031-06 W5 | E4 | 
 | 444562 | AB   | 51.64942854 | -114.80476596 | POINT(51.64942854 
 -114.80476596) | 08-031-06 W5 | NE | 
 ++--+-+---+--+--++
 8 rows in set (3.87 sec)
 
 So, there are ~2.6 million rows in the table, and coordinates is 
 spatially-indexed. Yet the query requires nearly 4 seconds. What am I doing 
 wrong?
 
 ...REne


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Why does this query take so long?

[René Fournier]

Hmm, weird. I just re-imported the data (after drop/create table, etc.), and 
now the spatial queries run fast. 
Has anyone seen this sort of thing happen? Maybe the Index got corrupted 
somehow, and then MySQL had to do a full table scan (even though EXPLAIN 
indicated it would use the Spatial Index)?



On 2009-12-28, at 9:28 AM, René Fournier wrote:

 So just to clarify (hello?), the index which *should* be used (EXPLAIN says 
 so) and *should* make the query run faster than 4 seconds either isn't used 
 (why?) or simply doesn't speed up the query (again, why?).
 
 ++-+---+---+---+---+-+--+--+-+
 | id | select_type | table | type  | possible_keys | key   | key_len | ref  | 
 rows | Extra   |
 ++-+---+---+---+---+-+--+--+-+
 |  1 | SIMPLE  | qs| range | coord | coord | 27  | NULL | 
 5260 | Using where | 
 ++-+---+---+---+---+-+--+--+-+
 
 SELECT id, province, latitude, longitude, AsText(coordinates), s_ts_r_m, 
 quartersection FROM qs WHERE MBRContains(GeomFromText('POLYGON((51.62582589 
 -114.82248918,51.65126254 -114.82248918,51.65126254 -114.78150333,51.62582589 
 -114.78150333,51.62582589 -114.82248918))'), coordinates)
 
 8 rows in set (3.87 sec)
 
 
 On 2009-12-27, at 3:59 PM, René Fournier wrote:
 
 So... there is an index, and it's supposedly used:
 
 mysql EXPLAIN SELECT id, province, latitude, longitude, 
 AsText(coordinates), s_ts_r_m, quartersection FROM qs WHERE 
 MBRContains(GeomFromText('POLYGON((51.62582589 -114.82248918,51.65126254 
 -114.82248918,51.65126254 -114.78150333,51.62582589 
 -114.78150333,51.62582589 -114.82248918))'), coordinates);
 ++-+---+---+---+---+-+--+--+-+
 | id | select_type | table | type  | possible_keys | key   | key_len | ref  
 | rows | Extra   |
 ++-+---+---+---+---+-+--+--+-+
 |  1 | SIMPLE  | qs| range | coord | coord | 27  | NULL 
 | 5260 | Using where | 
 ++-+---+---+---+---+-+--+--+-+
 1 row in set (0.00 sec)
 
 But when I run the query:
 
 mysql SELECT id, province, latitude, longitude, AsText(coordinates), 
 s_ts_r_m, quartersection FROM qs WHERE 
 MBRContains(GeomFromText('POLYGON((51.62582589 -114.82248918,51.65126254 
 -114.82248918,51.65126254 -114.78150333,51.62582589 
 -114.78150333,51.62582589 -114.82248918))'), coordinates)
   - ;
 ++--+-+---+--+--++
 | id | province | latitude| longitude | AsText(coordinates)  
 | s_ts_r_m | quartersection |
 ++--+-+---+--+--++
 | 444543 | AB   | 51.63495228 | -114.79282412 | POINT(51.63495228 
 -114.79282412) | 04-031-06 W5 | N4 | 
 | 444564 | AB   | 51.64941120 | -114.79283278 | POINT(51.6494112 
 -114.79283278)  | 09-031-06 W5 | N4 | 
 | 444548 | AB   | 51.63497789 | -114.81645649 | POINT(51.63497789 
 -114.81645649) | 05-031-06 W5 | N4 | 
 | 444561 | AB   | 51.64943119 | -114.81643801 | POINT(51.64943119 
 -114.81643801) | 08-031-06 W5 | N4 | 
 | 444547 | AB   | 51.62775680 | -114.80475858 | POINT(51.6277568 
 -114.80475858)  | 05-031-06 W5 | E4 | 
 | 444549 | AB   | 51.63498028 | -114.80479925 | POINT(51.63498028 
 -114.80479925) | 05-031-06 W5 | NE | 
 | 444560 | AB   | 51.64220442 | -114.80478262 | POINT(51.64220442 
 -114.80478262) | 08-031-06 W5 | E4 | 
 | 444562 | AB   | 51.64942854 | -114.80476596 | POINT(51.64942854 
 -114.80476596) | 08-031-06 W5 | NE | 
 ++--+-+---+--+--++
 8 rows in set (3.87 sec)
 
 So, there are ~2.6 million rows in the table, and coordinates is 
 spatially-indexed. Yet the query requires nearly 4 seconds. What am I doing 
 wrong?
 
 ...REne
 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: results of the query as a table

[Steve Edberg]

At 2:59 PM + 10/27/09, Olga Lyashevska wrote:

Dear all,

I run a query and I try to save results as a table. On the results 
of this first query I want to run yet another query (perhaps a few).
I have been trying to use CREATE VIEW statement, which works fine, 
except for the fact that fields are not indexed because as I 
understand it indices cannot be created on views. It really affects 
the performance, making it nearly impossible to run any further 
queries.


I am aware that it is a rather trivial problem, but still I did not 
manage to find a solution which would meet my requirements.


So my question is: are there any other possibilities to save results 
of the query as a table so that they will be re-used to run yet 
another query?


Thanks in advance,
Olga



CREATE TABLE ... SELECT

should do what you want. For example

	CREATE TABLE foo SELECT thing1,thing2,concat(thing3,thing4) 
as thing5 from bar where thing4 like 'baz%' order by thing1 desc


You could create a TEMPORARY table if needed (CREATE TEMPORARY 
TABLE...). Assuming version 5.0:


http://dev.mysql.com/doc/refman/5.0/en/create-table.html

- steve

--
++
| Steve Edberg  edb...@edberg-online.com |
| Programming/Database/SysAdminhttp://www.edberg-online.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: results of the query as a table

[Olga Lyashevska]

On 27.10.2009, at 15:11, Steve Edberg wrote:


At 2:59 PM + 10/27/09, Olga Lyashevska wrote:

Dear all,

I run a query and I try to save results as a table. On the results  
of this first query I want to run yet another query (perhaps a few).
I have been trying to use CREATE VIEW statement, which works fine,  
except for the fact that fields are not indexed because as I  
understand it indices cannot be created on views. It really affects  
the performance, making it nearly impossible to run any further  
queries.


I am aware that it is a rather trivial problem, but still I did not  
manage to find a solution which would meet my requirements.


So my question is: are there any other possibilities to save  
results of the query as a table so that they will be re-used to run  
yet another query?


Thanks in advance,
Olga



CREATE TABLE ... SELECT

should do what you want. For example

	CREATE TABLE foo SELECT thing1,thing2,concat(thing3,thing4) as  
thing5 from bar where thing4 like 'baz%' order by thing1 desc


You could create a TEMPORARY table if needed (CREATE TEMPORARY  
TABLE...). Assuming version 5.0:


Thanks Steve. It is solved! Shall I add indices manually to speed up  
query?


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: results of the query as a table

[Steve Edberg]

At 3:29 PM + 10/27/09, Olga Lyashevska wrote:

On 27.10.2009, at 15:11, Steve Edberg wrote:


At 2:59 PM + 10/27/09, Olga Lyashevska wrote:

Dear all,

I run a query and I try to save results as a table. On the results 
of this first query I want to run yet another query (perhaps a 
few).
I have been trying to use CREATE VIEW statement, which works fine, 
except for the fact that fields are not indexed because as I 
understand it indices cannot be created on views. It really 
affects the performance, making it nearly impossible to run any 
further queries.


I am aware that it is a rather trivial problem, but still I did 
not manage to find a solution which would meet my requirements.


So my question is: are there any other possibilities to save 
results of the query as a table so that they will be re-used to 
run yet another query?


Thanks in advance,
Olga



CREATE TABLE ... SELECT

should do what you want. For example

	CREATE TABLE foo SELECT thing1,thing2,concat(thing3,thing4) 
as thing5 from bar where thing4 like 'baz%' order by thing1 desc


You could create a TEMPORARY table if needed (CREATE TEMPORARY 
TABLE...). Assuming version 5.0:


Thanks Steve. It is solved! Shall I add indices manually to speed up query?




It would probably help, yes. As it mentions near the bottom of the 
CREATE TABLE documentation page, you can override column definitions 
and create indexes in the same statement, something like:


	CREATE TABLE foo (a TINYINT NOT NULL),  c, unique(c) SELECT 
b+1 AS a, c FROM bar;


(never tried that myself). Or you could do an ALTER TABLE afterwards 
to add appropriate indexes. And are you familiar with the EXPLAIN 
command to help optimize queries/decide what indexes to add?


http://dev.mysql.com/doc/refman/5.0/en/create-table.html
http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

http://dev.mysql.com/doc/refman/5.0/en/query-speed.html
http://dev.mysql.com/doc/refman/5.0/en/explain.html

- steve

--
++
| Steve Edberg  edb...@edberg-online.com |
| Programming/Database/SysAdminhttp://www.edberg-online.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: results of the query as a table

[Olga Lyashevska]

Dear Steve,

On 27.10.2009, at 16:19, Steve Edberg wrote:
 Or you could do an ALTER TABLE afterwards to add appropriate  
indexes. And are you familiar with the EXPLAIN command to help  
optimize queries/decide what indexes to add?


Thanks for this! I have added indicies with ALTER TABLE.
And using EXPLAIN  and ANALYZE TABLE I found out that in fact I am  
creating a huge Cartesian product joining fields of two tables which  
are not indexed!
No wonder it took ages to get this query done, I used up 99% of CPU.  
Definitely it can and it should be optimized.

Thanks for your tips again.

Cheers,
Olga


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: [OT] Suggestion of query manager

[Michael Dykman]

http://dev.mysql.com/downloads/gui-tools/5.0.html

On Mon, Oct 12, 2009 at 7:23 PM, Marcelo de Assis saloma...@gmail.com wrote:
 Hi people!

 Can anyone suggest a query manager on linux environment - like Heidisql?

 I using MySQL Navigator:
 http://www.bookofjesus.org/images/fl8ze90wpgyt87bkp5.png

 Thanks!

 --
 Marcelo de Assis

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:    http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com





-- 
 - michael dykman
 - mdyk...@gmail.com

Don’t worry about people stealing your ideas. If they’re any good,
you’ll have to ram them down their throats!

   Howard Aiken

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: [OT] Suggestion of query manager

[Walter Heck - OlinData.com]

The GUI tools are horrible, and I probably wouldn't recommend them to
my worst enemy :)
Take a look at workbench. It is getting better with every release,
especially now that they added SSH tunneling into it. It is still
beta-status though, but it might work for you:
http://dev.mysql.com/downloads/workbench/5.1.html

Walter

On Tue, Oct 13, 2009 at 12:59, Michael Dykman mdyk...@gmail.com wrote:
 http://dev.mysql.com/downloads/gui-tools/5.0.html

 On Mon, Oct 12, 2009 at 7:23 PM, Marcelo de Assis saloma...@gmail.com wrote:
 Hi people!

 Can anyone suggest a query manager on linux environment - like Heidisql?

 I using MySQL Navigator:
 http://www.bookofjesus.org/images/fl8ze90wpgyt87bkp5.png

 Thanks!

 --
 Marcelo de Assis

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:    http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com





 --
  - michael dykman
  - mdyk...@gmail.com

 Don’t worry about people stealing your ideas. If they’re any good,
 you’ll have to ram them down their throats!

   Howard Aiken

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:    http://lists.mysql.com/mysql?unsub=li...@olindata.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: [PHP] mysql cache query as xml

[Daniel Brown]

Chris;

From my understanding of your question, your message (included
below in its entirety) is better sent to the MySQL General list, which
I've CC'd on this reply.  If you haven't yet, please subscribe there
at mysql-subscr...@lists.mysql.com to follow the thread for responses.

If I'm misunderstanding and you're asking a PHP-related question,
please rephrase your question.

[Full original message follows.]


On Fri, Jul 10, 2009 at 12:22,
workerho...@studysite.euworkerho...@studysite.eu wrote:
 hi guys, i need some help by optimize the performance.
 my problem is that i need a lot of rows the whole site (don't ask i need the
 rows really :-) )
 this is about ~4000 rows it will be loaded from mysql database in 0.3
 seconds
 my idea was to cache this rows in a xml file like for example:

 category
   idsome hash id/id
   titlecategory title /title
 /category
 ..

 also load query from mysql first, save to xml using 6 hours, erase the
 cached file, load query against
 but to load the same num rows from xml during more then 3 seconds in
 comparison mysql need just 0.3 seconds.

 how can i optimize the reading from xml faster?

 server design:
 2 mysql server (Master  Slave with Replication  )
 8 Applikation Server with connect to the 2 mysql server

 this i the reason why i want to cache this query anyway! other querys just
 need about 0.0004 seconds, but this is the slowest query!
 i hope someone can help me or had a better ideas to solve this problem!

 thanks chris


 --
 PHP General Mailing List (http://www.php.net/)
 To unsubscribe, visit: http://www.php.net/unsub.php





-- 
/Daniel P. Brown
daniel.br...@parasane.net || danbr...@php.net
http://www.parasane.net/ || http://www.pilotpig.net/
Check out our great hosting and dedicated server deals at
http://twitter.com/pilotpig

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: [PHP] mysql cache query as xml

[Gavin Towey]

This is more of an application architecture question than a mysql question.  
Though I'll offer my two cents:

What are you going to do with the XML file?  Write it to disk, so you can later 
read it, and parse it just to get your result set back?  That sounds a lot 
slower than fetching it from mysql again.  You can test it though -- write a 
script, time a bunch of iterations and prove to yourself if it's a good idea or 
not.

Generally the way to optimize reading xml faster is to not do it.

IMO if you want caching then use something that was meant for it like memcached.

Regards,
Gavin Towey


-Original Message-
From: paras...@gmail.com [mailto:paras...@gmail.com] On Behalf Of Daniel Brown
Sent: Friday, July 10, 2009 9:37 AM
To: workerho...@studysite.eu
Cc: PHP; MYSQL General List
Subject: Re: [PHP] mysql cache query as xml

Chris;

From my understanding of your question, your message (included
below in its entirety) is better sent to the MySQL General list, which
I've CC'd on this reply.  If you haven't yet, please subscribe there
at mysql-subscr...@lists.mysql.com to follow the thread for responses.

If I'm misunderstanding and you're asking a PHP-related question,
please rephrase your question.

[Full original message follows.]


On Fri, Jul 10, 2009 at 12:22,
workerho...@studysite.euworkerho...@studysite.eu wrote:
 hi guys, i need some help by optimize the performance.
 my problem is that i need a lot of rows the whole site (don't ask i need the
 rows really :-) )
 this is about ~4000 rows it will be loaded from mysql database in 0.3
 seconds
 my idea was to cache this rows in a xml file like for example:

 category
   idsome hash id/id
   titlecategory title /title
 /category
 ..

 also load query from mysql first, save to xml using 6 hours, erase the
 cached file, load query against
 but to load the same num rows from xml during more then 3 seconds in
 comparison mysql need just 0.3 seconds.

 how can i optimize the reading from xml faster?

 server design:
 2 mysql server (Master  Slave with Replication  )
 8 Applikation Server with connect to the 2 mysql server

 this i the reason why i want to cache this query anyway! other querys just
 need about 0.0004 seconds, but this is the slowest query!
 i hope someone can help me or had a better ideas to solve this problem!

 thanks chris


 --
 PHP General Mailing List (http://www.php.net/)
 To unsubscribe, visit: http://www.php.net/unsub.php





--
/Daniel P. Brown
daniel.br...@parasane.net || danbr...@php.net
http://www.parasane.net/ || http://www.pilotpig.net/
Check out our great hosting and dedicated server deals at
http://twitter.com/pilotpig

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com


The information contained in this transmission may contain privileged and 
confidential information. It is intended only for the use of the person(s) 
named above. If you are not the intended recipient, you are hereby notified 
that any review, dissemination, distribution or duplication of this 
communication is strictly prohibited. If you are not the intended recipient, 
please contact the sender by reply email and destroy all copies of the original 
message.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?

[Matt Neimeyer]

On Fri, Jun 19, 2009 at 11:27 AM, Brent Baisleybrentt...@gmail.com wrote:
 It sounds like you want to use spatial indexes, but they only became
 available in v4.1
 http://dev.mysql.com/doc/refman/5.0/en/create-index.html
 http://dev.mysql.com/doc/refman/5.0/en/using-a-spatial-index.html

That feels like the right thing (spatial calculations = spatial
indexes?) but I looked at the docs and my head exploded. Can anyone
recommend a good book that takes me through it gently?

That said I'm intreged by the MBRContains and the Polygon functions...
If I read those right I could create a simplified circle (probably
just an octogon) to help eliminate false positives in the corners
when using a plain square as the enclosure.

 You don't have to do any re-architecture to change you subquery to a join:
 SELECT custzip FROM customers
 JOIN
 (SELECT zip FROM
 zipcodes WHERE degrees(acos(sin(radians(39.0788994))*sin(radians(latitude))+
 cos(radians(39.0788994))*cos(radians(latitude))*cos(radians(-77.1227036-longitude*60*1.1515
  5) AS zips
 ON custzip=zip

Will that work after a where clause? Multiple Times? For example...
(pseudo-code...)

SELECT * FROM customers WHERE saleslastyear  10
   JOIN (SELECT zip FROM etc) AS zips ON custzip=zip
   JOIN (SELECT MAX(date) FROM phonecalls) AS LastCalledOn ON custid=custid

Just from thinking about that... I assume that the only limitation is
that in a subselect you can do something like WHERE NOT IN (select
etc) but with a JOIN you are assuming a positive relationship? For
example using the JOIN methods above there isn't a way to simply do
AND custid NOT IN (SELECT custid FROM ordersplacedthisyear) other
than doing exactly that and adding this clause to the saleslastyear
clause. (In this particular case a column lastorderdate in customer
that was programatically updated on ordering would also be useful but
I'm thinking examples here... ;) )

I've never seen JOIN used outside of a traditional SELECT t1.*,t2.*
FROM table1 AS t1 LEFT JOIN table2 AS t2 ON t1.id=t2.id type of
structure so I kinda feel like I have a new toy...

Thanks!

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: how to efficiently query for the next in MySQL Community Edition 5.1.34?

[Peter Brawley]

Mike

J holding the next integer that T has for S

You mean for each i, the next value of i with that s?

(U having no row for the last integer of each string).

I do not understand that at all.

PB


Mike Spreitzer wrote:
Suppose I have a table T with two column, S holding strings (say, 
VARCHAR(200)) and I holding integers.  No row appears twice.  A given 
string appears many times, on average about 100 times.  Suppose I have 
millions of rows.  I want to make a table U holding those same columns 
plus one more, J holding the next integer that T has for S (U having no 
row for the last integer of each string).  I could index T on (S,I) and 
write this query as


select t1.*, t2.I as J from T as t1, T as t2
where t1.S=t2.S and t1.I  t2.I
and not exists (select * from T as t12 where t12.S=t1.S and t1.I  t12.I 
and t12.I  t2.I)


but the query planner says this is quite expensive to run: it will 
enumerate all of T as t1, do a nested enumeration of all t2's entries for 
S=t1.S, and inside that do a further nested enumeration of t12's entries 
for S=t1.S --- costing about 10,000 times the size of T.  There has to be 
a better way!


Thanks,
Mike Spreitzer

  




No virus found in this incoming message.
Checked by AVG - www.avg.com 
Version: 8.5.364 / Virus Database: 270.12.80/2187 - Release Date: 06/19/09 06:53:00


  

Re: how to efficiently query for the next in MySQL Community Edition 5.1.34?

[Johnny Withers]

Huh???

On Saturday, June 20, 2009, Peter Brawley peter.braw...@earthlink.net wrote:
 Mike

J holding the next integer that T has for S

 You mean for each i, the next value of i with that s?

(U having no row for the last integer of each string).

 I do not understand that at all.

 PB


 Mike Spreitzer wrote:

 Suppose I have a table T with two column, S holding strings (say, 
 VARCHAR(200)) and I holding integers.  No row appears twice.  A given string 
 appears many times, on average about 100 times.  Suppose I have millions of 
 rows.  I want to make a table U holding those same columns plus one more, J 
 holding the next integer that T has for S (U having no row for the last 
 integer of each string).  I could index T on (S,I) and write this query as

 select t1.*, t2.I as J from T as t1, T as t2
 where t1.S=t2.S and t1.I  t2.I
 and not exists (select * from T as t12 where t12.S=t1.S and t1.I  t12.I and 
 t12.I  t2.I)

 but the query planner says this is quite expensive to run: it will enumerate 
 all of T as t1, do a nested enumeration of all t2's entries for S=t1.S, and 
 inside that do a further nested enumeration of t12's entries for S=t1.S --- 
 costing about 10,000 times the size of T.  There has to be a better way!

 Thanks,
 Mike Spreitzer

   


 No virus found in this incoming message.
 Checked by AVG - www.avg.com http://www.avg.com Version: 8.5.364 / Virus 
 Database: 270.12.80/2187 - Release Date: 06/19/09 06:53:00





-- 
-
Johnny Withers
601.209.4985
joh...@pixelated.net

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: how to efficiently query for the next in MySQL Community Edition 5.1.34?

[Mike Spreitzer]

Yes, for each (S, I) pair the goal is to efficiently find the next largest 
integer associated with S in T.  For the highest integer I associated with 
S in T, there is no next larger.

Thanks,
Mike Spreitzer




Peter Brawley peter.braw...@earthlink.net 
06/20/09 08:56 AM
Please respond to
peter.braw...@earthlink.net


To
Mike Spreitzer/Watson/i...@ibmus
cc
mysql@lists.mysql.com
Subject
Re: how to efficiently query for the next in MySQL Community Edition 
5.1.34?






Mike

J holding the next integer that T has for S 

You mean for each i, the next value of i with that s?

(U having no row for the last integer of each string).

I do not understand that at all.

PB


Mike Spreitzer wrote: 
Suppose I have a table T with two column, S holding strings (say, 
VARCHAR(200)) and I holding integers.  No row appears twice.  A given 
string appears many times, on average about 100 times.  Suppose I have 
millions of rows.  I want to make a table U holding those same columns 
plus one more, J holding the next integer that T has for S (U having no 
row for the last integer of each string).  I could index T on (S,I) and 
write this query as

select t1.*, t2.I as J from T as t1, T as t2
where t1.S=t2.S and t1.I  t2.I
and not exists (select * from T as t12 where t12.S=t1.S and t1.I  t12.I 
and t12.I  t2.I)

but the query planner says this is quite expensive to run: it will 
enumerate all of T as t1, do a nested enumeration of all t2's entries for 
S=t1.S, and inside that do a further nested enumeration of t12's entries 
for S=t1.S --- costing about 10,000 times the size of T.  There has to be 
a better way!

Thanks,
Mike Spreitzer

 



No virus found in this incoming message.
Checked by AVG - www.avg.com 
Version: 8.5.364 / Virus Database: 270.12.80/2187 - Release Date: 06/19/09 
06:53:00

 

Re: how to efficiently query for the next in MySQL Community Edition 5.1.34?

[Peter Brawley]

Mike,

Yes, for each (S, I) pair the goal is to efficiently find the next largest 
integer associated with S in T.  For the highest integer I associated with 
S in T, there is no next larger.


Here's a more efficient query for the next i values with matching s values:

SELECT a.i, MIN(b.i) AS j
FROM t AS a
JOIN t AS b ON b.i  a.i AND a.s = b.s
GROUP BY  a.i

To fetch the matching s values, join the above to the original table:

SELECT n.i, t.s, n.j
FROM (
 SELECT a.i, MIN(b.i) AS j
 FROM t AS a
 JOIN t AS b ON b.i  a.i AND a.s = b.s
 GROUP BY  a.i
) AS n JOIN t USING (i);

PB

-

Mike Spreitzer wrote:
Yes, for each (S, I) pair the goal is to efficiently find the next largest 
integer associated with S in T.  For the highest integer I associated with 
S in T, there is no next larger.


Thanks,
Mike Spreitzer




Peter Brawley peter.braw...@earthlink.net 
06/20/09 08:56 AM

Please respond to
peter.braw...@earthlink.net


To
Mike Spreitzer/Watson/i...@ibmus
cc
mysql@lists.mysql.com
Subject
Re: how to efficiently query for the next in MySQL Community Edition 
5.1.34?







Mike

  
J holding the next integer that T has for S 



You mean for each i, the next value of i with that s?

  

(U having no row for the last integer of each string).



I do not understand that at all.

PB


Mike Spreitzer wrote: 
Suppose I have a table T with two column, S holding strings (say, 
VARCHAR(200)) and I holding integers.  No row appears twice.  A given 
string appears many times, on average about 100 times.  Suppose I have 
millions of rows.  I want to make a table U holding those same columns 
plus one more, J holding the next integer that T has for S (U having no 
row for the last integer of each string).  I could index T on (S,I) and 
write this query as


select t1.*, t2.I as J from T as t1, T as t2
where t1.S=t2.S and t1.I  t2.I
and not exists (select * from T as t12 where t12.S=t1.S and t1.I  t12.I 
and t12.I  t2.I)


but the query planner says this is quite expensive to run: it will 
enumerate all of T as t1, do a nested enumeration of all t2's entries for 
S=t1.S, and inside that do a further nested enumeration of t12's entries 
for S=t1.S --- costing about 10,000 times the size of T.  There has to be 
a better way!


Thanks,
Mike Spreitzer

 




No virus found in this incoming message.
Checked by AVG - www.avg.com 
Version: 8.5.364 / Virus Database: 270.12.80/2187 - Release Date: 06/19/09 
06:53:00


 

  




No virus found in this incoming message.
Checked by AVG - www.avg.com 
Version: 8.5.364 / Virus Database: 270.12.81/2189 - Release Date: 06/20/09 06:15:00


  

Re: how to efficiently query for the next in MySQL Community Edition 5.1.34?

[Mike Spreitzer]

Ah, yes, the MIN should be very helpful.  Can I expect that ordering the 
storage by (S, I) or having an (S, I) index will make that MIN take O(1) 
time, for both MyISAM and InnoDB?

I do not follow why you suggested a join to get the associated S, that can 
be done in the original query (and I did NOT say a given integer I is 
associated with only one string S):

SELECT a.s, a.i, MIN(b.i) AS j 
FROM t AS a 
JOIN t AS b ON b.i  a.i AND a.s = b.s
GROUP BY  a.i

Thanks,
Mike Spreitzer




Peter Brawley peter.braw...@earthlink.net 
06/20/09 12:39 PM
Please respond to
peter.braw...@earthlink.net


To
Mike Spreitzer/Watson/i...@ibmus
cc
mysql@lists.mysql.com
Subject
Re: how to efficiently query for the next in MySQL Community Edition 
5.1.34?






Mike,
Yes, for each (S, I) pair the goal is to efficiently find the next 
largest 
integer associated with S in T.  For the highest integer I associated 
with 
S in T, there is no next larger.
Here's a more efficient query for the next i values with matching s 
values:

SELECT a.i, MIN(b.i) AS j 
FROM t AS a 
JOIN t AS b ON b.i  a.i AND a.s = b.s
GROUP BY  a.i

To fetch the matching s values, join the above to the original table:

SELECT n.i, t.s, n.j
FROM (
  SELECT a.i, MIN(b.i) AS j 
  FROM t AS a 
  JOIN t AS b ON b.i  a.i AND a.s = b.s
  GROUP BY  a.i
) AS n JOIN t USING (i);

PB

-

Mike Spreitzer wrote: 
Yes, for each (S, I) pair the goal is to efficiently find the next largest 

integer associated with S in T.  For the highest integer I associated with 

S in T, there is no next larger.

Thanks,
Mike Spreitzer




Peter Brawley peter.braw...@earthlink.net 
06/20/09 08:56 AM
Please respond to
peter.braw...@earthlink.net


To
Mike Spreitzer/Watson/i...@ibmus
cc
mysql@lists.mysql.com
Subject
Re: how to efficiently query for the next in MySQL Community Edition 
5.1.34?






Mike

 
J holding the next integer that T has for S 
 

You mean for each i, the next value of i with that s?

 
(U having no row for the last integer of each string).
 

I do not understand that at all.

PB


Mike Spreitzer wrote: 
Suppose I have a table T with two column, S holding strings (say, 
VARCHAR(200)) and I holding integers.  No row appears twice.  A given 
string appears many times, on average about 100 times.  Suppose I have 
millions of rows.  I want to make a table U holding those same columns 
plus one more, J holding the next integer that T has for S (U having no 
row for the last integer of each string).  I could index T on (S,I) and 
write this query as

select t1.*, t2.I as J from T as t1, T as t2
where t1.S=t2.S and t1.I  t2.I
and not exists (select * from T as t12 where t12.S=t1.S and t1.I  t12.I 
and t12.I  t2.I)

but the query planner says this is quite expensive to run: it will 
enumerate all of T as t1, do a nested enumeration of all t2's entries for 
S=t1.S, and inside that do a further nested enumeration of t12's entries 
for S=t1.S --- costing about 10,000 times the size of T.  There has to be 
a better way!

Thanks,
Mike Spreitzer

 



No virus found in this incoming message.
Checked by AVG - www.avg.com 
Version: 8.5.364 / Virus Database: 270.12.80/2187 - Release Date: 06/19/09 

06:53:00

 

 



No virus found in this incoming message.
Checked by AVG - www.avg.com 
Version: 8.5.364 / Virus Database: 270.12.81/2189 - Release Date: 06/20/09 
06:15:00

 

Re: how to efficiently query for the next in MySQL Community Edition 5.1.34?

[Peter Brawley]

I do not follow why you suggested a join to get the associated S,
that can be done in the original query (and I did NOT say a given
integer I is associated with only one string S):

A Group By query returns arbitrary values for a column which (i) does 
not Group By, (ii) does not aggregate, and (iii) does not have a 1:1 
relationship with the grouping expression.


PB

-

Mike Spreitzer wrote:


Ah, yes, the MIN should be very helpful.  Can I expect that ordering 
the storage by (S, I) or having an (S, I) index will make that MIN 
take O(1) time, for both MyISAM and InnoDB?


I do not follow why you suggested a join to get the associated S, that 
can be done in the original query (and I did NOT say a given integer I 
is associated with only one string S):


SELECT a.s, a.i, MIN(b.i) AS j
FROM t AS a
JOIN t AS b ON b.i  a.i AND a.s = b.s
GROUP BY  a.i

Thanks,
Mike Spreitzer



*Peter Brawley peter.braw...@earthlink.net*

06/20/09 12:39 PM
Please respond to
peter.braw...@earthlink.net



To
Mike Spreitzer/Watson/i...@ibmus
cc
mysql@lists.mysql.com
Subject
	Re: how to efficiently query for the next in MySQL Community Edition 
5.1.34?










Mike,
Yes, for each (S, I) pair the goal is to efficiently find the next 
largest
integer associated with S in T.  For the highest integer I associated 
with

S in T, there is no next larger.
Here's a more efficient query for the next i values with matching s 
values:


SELECT a.i, MIN(b.i) AS j
FROM t AS a
JOIN t AS b ON b.i  a.i AND a.s = b.s
GROUP BY  a.i

To fetch the matching s values, join the above to the original table:

SELECT n.i, t.s, n.j
FROM (
 SELECT a.i, MIN(b.i) AS j
 FROM t AS a
 JOIN t AS b ON b.i  a.i AND a.s = b.s
 GROUP BY  a.i
) AS n JOIN t USING (i);

PB

-

Mike Spreitzer wrote:
Yes, for each (S, I) pair the goal is to efficiently find the next 
largest
integer associated with S in T.  For the highest integer I associated 
with

S in T, there is no next larger.

Thanks,
Mike Spreitzer




Peter Brawley _peter.braw...@earthlink.net_ 
mailto:peter.braw...@earthlink.net

06/20/09 08:56 AM
Please respond to
_peter.braw...@earthlink.net_ mailto:peter.braw...@earthlink.net


To
Mike Spreitzer/Watson/i...@ibmus
cc
_my...@lists.mysql.com_ mailto:mysql@lists.mysql.com
Subject
Re: how to efficiently query for the next in MySQL Community Edition
5.1.34?






Mike

 
J holding the next integer that T has for S
   


You mean for each i, the next value of i with that s?

 
(U having no row for the last integer of each string).
   


I do not understand that at all.

PB


Mike Spreitzer wrote:
Suppose I have a table T with two column, S holding strings (say,
VARCHAR(200)) and I holding integers.  No row appears twice.  A given
string appears many times, on average about 100 times.  Suppose I have
millions of rows.  I want to make a table U holding those same columns
plus one more, J holding the next integer that T has for S (U having no
row for the last integer of each string).  I could index T on (S,I) and
write this query as

select t1.*, t2.I as J from T as t1, T as t2
where t1.S=t2.S and t1.I  t2.I
and not exists (select * from T as t12 where t12.S=t1.S and t1.I  t12.I
and t12.I  t2.I)

but the query planner says this is quite expensive to run: it will
enumerate all of T as t1, do a nested enumeration of all t2's entries for
S=t1.S, and inside that do a further nested enumeration of t12's entries
for S=t1.S --- costing about 10,000 times the size of T.  There has to be
a better way!

Thanks,
Mike Spreitzer





No virus found in this incoming message.
Checked by AVG - _www.avg.com_ http://www.avg.com/
Version: 8.5.364 / Virus Database: 270.12.80/2187 - Release Date: 
06/19/09

06:53:00



 





No virus found in this incoming message.
Checked by AVG - _www.avg.com_ http://www.avg.com/
Version: 8.5.364 / Virus Database: 270.12.81/2189 - Release Date: 
06/20/09 06:15:00


 




No virus found in this incoming message.
Checked by AVG - www.avg.com 
Version: 8.5.364 / Virus Database: 270.12.81/2189 - Release Date: 06/20/09 06:15:00


  

Re: how to efficiently query for the next in MySQL Community Edition 5.1.34?

[Mike Spreitzer]

Oops, I did not read your original query closely enough.  You actually 
meant to group by S, not I, right?  I can get S, I, and J with


SELECT a.s, a.i, MIN(b.i) AS j 
FROM t AS a 
JOIN t AS b ON b.i  a.i AND a.s = b.s
GROUP BY  a.s

Right?

My integers are not unique; a given integer can be paired with several 
strings.

Thanks,
Mike Spreitzer
SMTP: mspre...@us.ibm.com, Lotus Notes: Mike Spreitzer/Watson/IBM
Office phone: +1-914-784-6424 (IBM T/L 863-)
AOL Instant Messaging: M1k3Sprtzr



Peter Brawley peter.braw...@earthlink.net 
06/20/09 03:59 PM
Please respond to
peter.braw...@earthlink.net


To
Mike Spreitzer/Watson/i...@ibmus
cc
mysql@lists.mysql.com
Subject
Re: how to efficiently query for the next in MySQL Community Edition 
5.1.34?






I do not follow why you suggested a join to get the associated S, 
that can be done in the original query (and I did NOT say a given 
integer I is associated with only one string S): 

A Group By query returns arbitrary values for a column which (i) does not 
Group By, (ii) does not aggregate, and (iii) does not have a 1:1 
relationship with the grouping expression.

PB

-

Mike Spreitzer wrote: 

Ah, yes, the MIN should be very helpful.  Can I expect that ordering the 
storage by (S, I) or having an (S, I) index will make that MIN take O(1) 
time, for both MyISAM and InnoDB? 

I do not follow why you suggested a join to get the associated S, that can 
be done in the original query (and I did NOT say a given integer I is 
associated with only one string S): 

SELECT a.s, a.i, MIN(b.i) AS j 
FROM t AS a 
JOIN t AS b ON b.i  a.i AND a.s = b.s
GROUP BY  a.i 

Thanks, 
Mike Spreitzer



Peter Brawley peter.braw...@earthlink.net 
06/20/09 12:39 PM 

Please respond to
peter.braw...@earthlink.net



To
Mike Spreitzer/Watson/i...@ibmus 
cc
mysql@lists.mysql.com 
Subject
Re: how to efficiently query for the next in MySQL Community Edition 
5.1.34?








Mike, 
Yes, for each (S, I) pair the goal is to efficiently find the next 
largest 
integer associated with S in T.  For the highest integer I associated 
with 
S in T, there is no next larger. 
Here's a more efficient query for the next i values with matching s 
values:

SELECT a.i, MIN(b.i) AS j 
FROM t AS a 
JOIN t AS b ON b.i  a.i AND a.s = b.s
GROUP BY  a.i

To fetch the matching s values, join the above to the original table:

SELECT n.i, t.s, n.j
FROM (
 SELECT a.i, MIN(b.i) AS j 
 FROM t AS a 
 JOIN t AS b ON b.i  a.i AND a.s = b.s
 GROUP BY  a.i
) AS n JOIN t USING (i);

PB

-

Mike Spreitzer wrote: 
Yes, for each (S, I) pair the goal is to efficiently find the next largest 

integer associated with S in T.  For the highest integer I associated with 

S in T, there is no next larger.

Thanks,
Mike Spreitzer




Peter Brawley peter.braw...@earthlink.net 
06/20/09 08:56 AM
Please respond to
peter.braw...@earthlink.net


To
Mike Spreitzer/Watson/i...@ibmus
cc
mysql@lists.mysql.com
Subject
Re: how to efficiently query for the next in MySQL Community Edition 
5.1.34?






Mike

  
J holding the next integer that T has for S 


You mean for each i, the next value of i with that s?

  
(U having no row for the last integer of each string).


I do not understand that at all.

PB


Mike Spreitzer wrote: 
Suppose I have a table T with two column, S holding strings (say, 
VARCHAR(200)) and I holding integers.  No row appears twice.  A given 
string appears many times, on average about 100 times.  Suppose I have 
millions of rows.  I want to make a table U holding those same columns 
plus one more, J holding the next integer that T has for S (U having no 
row for the last integer of each string).  I could index T on (S,I) and 
write this query as

select t1.*, t2.I as J from T as t1, T as t2
where t1.S=t2.S and t1.I  t2.I
and not exists (select * from T as t12 where t12.S=t1.S and t1.I  t12.I 
and t12.I  t2.I)

but the query planner says this is quite expensive to run: it will 
enumerate all of T as t1, do a nested enumeration of all t2's entries for 
S=t1.S, and inside that do a further nested enumeration of t12's entries 
for S=t1.S --- costing about 10,000 times the size of T.  There has to be 
a better way!

Thanks,
Mike Spreitzer





No virus found in this incoming message.
Checked by AVG - www.avg.com 
Version: 8.5.364 / Virus Database: 270.12.80/2187 - Release Date: 06/19/09 

06:53:00



  



No virus found in this incoming message.
Checked by AVG - www.avg.com 
Version: 8.5.364 / Virus Database: 270.12.81/2189 - Release Date: 06/20/09 
06:15:00

  



No virus found in this incoming message.
Checked by AVG - www.avg.com 
Version: 8.5.364 / Virus Database: 270.12.81/2189 - Release Date: 06/20/09 
06:15:00

 

Re: how to efficiently query for the next in MySQL Community Edition 5.1.34?

[Peter Brawley]

Mike,

Oops, I did not read your original query closely enough. 
You actually meant to group by S, not I, right? 

No, it's a query for next i values with matching s values, so it groups 
by i.


I can get S, I, and J with

SELECT a.s, a.i, MIN(b.i) AS j
FROM t AS a
JOIN t AS b ON b.i  a.i AND a.s = b.s
GROUP BY  a.s

For this dataset ...

drop table if exists t;
create table t(i int,s char(1));
insert into t 
values(1,'a'),(2,'b'),(3,'c'),(4,'a'),(5,'a'),(6,'d'),(7,'e'),(8,'d');


are these the correct next values of i?

+--+--+
| i| j|
+--+--+
|1 |4 |
|4 |5 |
|6 |8 |
+--+--+

Your query doesn't return that.

PB

-

Mike Spreitzer wrote:


Oops, I did not read your original query closely enough.  You actually 
meant to group by S, not I, right?  I can get S, I, and J with



SELECT a.s, a.i, MIN(b.i) AS j
FROM t AS a
JOIN t AS b ON b.i  a.i AND a.s = b.s
GROUP BY  a.s

Right?

My integers are not unique; a given integer can be paired with several 
strings.


Thanks,
Mike Spreitzer
SMTP: mspre...@us.ibm.com, Lotus Notes: Mike Spreitzer/Watson/IBM
Office phone: +1-914-784-6424 (IBM T/L 863-)
AOL Instant Messaging: M1k3Sprtzr


*Peter Brawley peter.braw...@earthlink.net*

06/20/09 03:59 PM
Please respond to
peter.braw...@earthlink.net



To
Mike Spreitzer/Watson/i...@ibmus
cc
mysql@lists.mysql.com
Subject
	Re: how to efficiently query for the next in MySQL Community Edition 
5.1.34?










I do not follow why you suggested a join to get the associated S,
that can be done in the original query (and I did NOT say a given
integer I is associated with only one string S):

A Group By query returns arbitrary values for a column which (i) does 
not Group By, (ii) does not aggregate, and (iii) does not have a 1:1 
relationship with the grouping expression.


PB

-

Mike Spreitzer wrote:

Ah, yes, the MIN should be very helpful.  Can I expect that ordering 
the storage by (S, I) or having an (S, I) index will make that MIN 
take O(1) time, for both MyISAM and InnoDB?


I do not follow why you suggested a join to get the associated S, that 
can be done in the original query (and I did NOT say a given integer I 
is associated with only one string S):


SELECT a.s, a.i, MIN(b.i) AS j
FROM t AS a
JOIN t AS b ON b.i  a.i AND a.s = b.s
GROUP BY  a.i

Thanks,
Mike Spreitzer


*Peter Brawley **_peter.braw...@earthlink.net_* 
mailto:peter.braw...@earthlink.net


06/20/09 12:39 PM
Please respond to_
__peter.braw...@earthlink.net_ mailto:peter.braw...@earthlink.net


To
Mike Spreitzer/Watson/i...@ibmus
cc
_my...@lists.mysql.com_ mailto:mysql@lists.mysql.com
Subject
	Re: how to efficiently query for the next in MySQL Community Edition 
5.1.34?












Mike,
Yes, for each (S, I) pair the goal is to efficiently find the next 
largest
integer associated with S in T.  For the highest integer I associated 
with

S in T, there is no next larger.
Here's a more efficient query for the next i values with matching s 
values:


SELECT a.i, MIN(b.i) AS j
FROM t AS a
JOIN t AS b ON b.i  a.i AND a.s = b.s
GROUP BY  a.i

To fetch the matching s values, join the above to the original table:

SELECT n.i, t.s, n.j
FROM (
SELECT a.i, MIN(b.i) AS j
FROM t AS a
JOIN t AS b ON b.i  a.i AND a.s = b.s
GROUP BY  a.i
) AS n JOIN t USING (i);

PB

-

Mike Spreitzer wrote:
Yes, for each (S, I) pair the goal is to efficiently find the next 
largest
integer associated with S in T.  For the highest integer I associated 
with

S in T, there is no next larger.

Thanks,
Mike Spreitzer




Peter Brawley _peter.braw...@earthlink.net_ 
mailto:peter.braw...@earthlink.net

06/20/09 08:56 AM
Please respond to_
__peter.braw...@earthlink.net_ mailto:peter.braw...@earthlink.net


To
Mike Spreitzer/Watson/i...@ibmus
cc_
__my...@lists.mysql.com_ mailto:mysql@lists.mysql.com
Subject
Re: how to efficiently query for the next in MySQL Community Edition
5.1.34?






Mike

 
J holding the next integer that T has for S
   


You mean for each i, the next value of i with that s?

 
(U having no row for the last integer of each string).
   


I do not understand that at all.

PB


Mike Spreitzer wrote:
Suppose I have a table T with two column, S holding strings (say,
VARCHAR(200)) and I holding integers.  No row appears twice.  A given
string appears many times, on average about 100 times.  Suppose I have
millions of rows.  I want to make a table U holding those same columns
plus one more, J holding the next integer that T has for S (U having no
row for the last integer of each string).  I could index T on (S,I) and
write this query as

select t1.*, t2.I as J from T as t1, T as t2
where t1.S=t2.S and t1.I  t2.I
and not exists (select * from T as t12 where t12.S=t1.S and t1.I  t12.I
and t12.I  t2.I)

but the query planner says this is quite expensive to run: it will
enumerate all of T as t1, do a nested enumeration of all t2's entries for
S

Re: how to efficiently query for the next in MySQL Community Edition 5.1.34?

[Mike Spreitzer]

 06:56 PM
Please respond to
peter.braw...@earthlink.net


To
Mike Spreitzer/Watson/i...@ibmus
cc
mysql@lists.mysql.com
Subject
Re: how to efficiently query for the next in MySQL Community Edition 
5.1.34?






Mike,

Oops, I did not read your original query closely enough. 
You actually meant to group by S, not I, right?  

No, it's a query for next i values with matching s values, so it groups by 
i.

I can get S, I, and J with 

SELECT a.s, a.i, MIN(b.i) AS j 
FROM t AS a 
JOIN t AS b ON b.i  a.i AND a.s = b.s
GROUP BY  a.s 

For this dataset ...

drop table if exists t;
create table t(i int,s char(1));
insert into t 
values(1,'a'),(2,'b'),(3,'c'),(4,'a'),(5,'a'),(6,'d'),(7,'e'),(8,'d');

are these the correct next values of i?

+--+--+
| i| j|
+--+--+
|1 |4 |
|4 |5 |
|6 |8 |
+--+--+

Your query doesn't return that.

PB

-

Mike Spreitzer wrote: 

Oops, I did not read your original query closely enough.  You actually 
meant to group by S, not I, right?  I can get S, I, and J with 


SELECT a.s, a.i, MIN(b.i) AS j 
FROM t AS a 
JOIN t AS b ON b.i  a.i AND a.s = b.s
GROUP BY  a.s 

Right? 

My integers are not unique; a given integer can be paired with several 
strings. 

Thanks, 
Mike Spreitzer
SMTP: mspre...@us.ibm.com, Lotus Notes: Mike Spreitzer/Watson/IBM
Office phone: +1-914-784-6424 (IBM T/L 863-)
AOL Instant Messaging: M1k3Sprtzr 


Peter Brawley peter.braw...@earthlink.net 
06/20/09 03:59 PM 

Please respond to
peter.braw...@earthlink.net



To
Mike Spreitzer/Watson/i...@ibmus 
cc
mysql@lists.mysql.com 
Subject
Re: how to efficiently query for the next in MySQL Community Edition 
5.1.34?








I do not follow why you suggested a join to get the associated S, 
that can be done in the original query (and I did NOT say a given 
integer I is associated with only one string S): 

A Group By query returns arbitrary values for a column which (i) does not 
Group By, (ii) does not aggregate, and (iii) does not have a 1:1 
relationship with the grouping expression.

PB

-

Mike Spreitzer wrote: 

Ah, yes, the MIN should be very helpful.  Can I expect that ordering the 
storage by (S, I) or having an (S, I) index will make that MIN take O(1) 
time, for both MyISAM and InnoDB? 

I do not follow why you suggested a join to get the associated S, that can 
be done in the original query (and I did NOT say a given integer I is 
associated with only one string S): 

SELECT a.s, a.i, MIN(b.i) AS j 
FROM t AS a 
JOIN t AS b ON b.i  a.i AND a.s = b.s
GROUP BY  a.i 

Thanks, 
Mike Spreitzer


Peter Brawley peter.braw...@earthlink.net 
06/20/09 12:39 PM 

Please respond to
peter.braw...@earthlink.net



To
Mike Spreitzer/Watson/i...@ibmus 
cc
mysql@lists.mysql.com 
Subject
Re: how to efficiently query for the next in MySQL Community Edition 
5.1.34?










Mike, 
Yes, for each (S, I) pair the goal is to efficiently find the next 
largest 
integer associated with S in T.  For the highest integer I associated 
with 
S in T, there is no next larger. 
Here's a more efficient query for the next i values with matching s 
values:

SELECT a.i, MIN(b.i) AS j 
FROM t AS a 
JOIN t AS b ON b.i  a.i AND a.s = b.s
GROUP BY  a.i

To fetch the matching s values, join the above to the original table:

SELECT n.i, t.s, n.j
FROM (
SELECT a.i, MIN(b.i) AS j 
FROM t AS a 
JOIN t AS b ON b.i  a.i AND a.s = b.s
GROUP BY  a.i
) AS n JOIN t USING (i);

PB

-

Mike Spreitzer wrote: 
Yes, for each (S, I) pair the goal is to efficiently find the next largest 

integer associated with S in T.  For the highest integer I associated with 

S in T, there is no next larger.

Thanks,
Mike Spreitzer




Peter Brawley peter.braw...@earthlink.net 
06/20/09 08:56 AM
Please respond to
peter.braw...@earthlink.net


To
Mike Spreitzer/Watson/i...@ibmus
cc
mysql@lists.mysql.com
Subject
Re: how to efficiently query for the next in MySQL Community Edition 
5.1.34?






Mike

 
J holding the next integer that T has for S 
   

You mean for each i, the next value of i with that s?

 
(U having no row for the last integer of each string).
   

I do not understand that at all.

PB


Mike Spreitzer wrote: 
Suppose I have a table T with two column, S holding strings (say, 
VARCHAR(200)) and I holding integers.  No row appears twice.  A given 
string appears many times, on average about 100 times.  Suppose I have 
millions of rows.  I want to make a table U holding those same columns 
plus one more, J holding the next integer that T has for S (U having no 
row for the last integer of each string).  I could index T on (S,I) and 
write this query as

select t1.*, t2.I as J from T as t1, T as t2
where t1.S=t2.S and t1.I  t2.I
and not exists (select * from T as t12 where t12.S=t1.S and t1.I  t12.I 
and t12.I  t2.I)

but the query planner says this is quite expensive to run: it will 
enumerate all of T as t1, do a nested enumeration of all t2's entries for 
S=t1.S, and inside that do a further nested

Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?

[Dan Nelson]

In the last episode (Jun 18), Matt Neimeyer said:
 I'm converting a PHP app from using Visual FoxPro as the database backend
 to using MySQL as the backend.  I'm testing on MySQL 4.1.22 on Mac OSX
 10.4.  The end application will be deployed cross platform and to both 4.x
 and 5.x MySQL servers.
 
 This query returned 21 records in .27 seconds:
 
 SELECT zip FROM zipcodes WHERE
 degrees(acos(sin(radians(39.0788994))*sin(radians(latitude))+
 cos(radians(39.0788994))*cos(radians(latitude))*cos(radians(-77.1227036-longitude*60*1.1515
  5

Ouch.  You might want to calculate the rectange enclosing your target
distance, add an index on lat (and/or long), and add the rectangle check to
your where clause: WHERE latitude BETWEEN lt1 AND lt2 AND longitude BETWEEN
ln2 AND ln2.  That way mysql can use the index to pare down most of the rows
without having to call all those trig functions for every zipcode.
 
 This query returned 21442 records in 1.08 seconds:
 
SELECT custzip FROM customers
 
 This query is still running half an hour later, with a Time of 2167
 and a State of Sending Data (according to the mysql process list)
 
SELECT custzip FROM customers WHERE custzip IN (SELECT zip FROM
 zipcodes WHERE degrees(acos(sin(radians(39.0788994))*sin(radians(latitude))+
 cos(radians(39.0788994))*cos(radians(latitude))*cos(radians(-77.1227036-longitude*60*1.1515
  5)
 
 When I try to EXPLAIN the query it gives me the following...
 
 id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
 1,PRIMARY,customers,index,NULL,cw_custzip,30,NULL,21226,Using where; Using 
 index
 2,DEPENDENT SUBQUERY,zipcodes,ALL,NULL,NULL,NULL,NULL,42144,Using where

Neither mysql 4 or 5 are very smart when it comes to subqueries.  Unless
your inner query is dead simple, mysql assumes it's a dependent subquery and
runs it once per row in your outer query.  You might want to try mysql 6 and
see if it does any better.  For example, here are explain plans for mysql 5
and 6 for the following query on the famous Oracle emp sample table:

select ename from emp where mgr in 
 (select empno from emp where ename in ('scott'));
+---+
| ename |
+---+
| ADAMS | 
+---+
1 row in set (0.00 sec)

Mysql 5.1.30:
+++---+-+---+-+-+--+--+-+
| id | select_type| table | type| possible_keys | key | 
key_len | ref  | rows | Extra   |
+++---+-+---+-+-+--+--+-+
|  1 | PRIMARY| emp   | ALL | NULL  | NULL| 
NULL| NULL |   14 | Using where |
|  2 | DEPENDENT SUBQUERY | emp   | unique_subquery | PRIMARY,ENAME | PRIMARY | 
4   | func |1 | Using where |
+++---+-+---+-+-+--+--+-+

Note that it didn't use an index on the outer query, and had to examine all
14 rows.  It even used the wrong index on the inner query :)

Mysql 6.0.11:
++-+---+--+---+---+-++--+---+
| id | select_type | table | type | possible_keys | key   | key_len | ref   
 | rows | Extra |
++-+---+--+---+---+-++--+---+
|  1 | PRIMARY | emp   | ref  | PRIMARY,ENAME | ENAME | 13  | const 
 |1 | Using index condition |
|  1 | PRIMARY | emp   | ref  | MGR   | MGR   | 5   | 
test.emp.EMPNO |2 |   |
++-+---+--+---+---+-++--+---+

Note that the queries have flipped and aren't nested anymore (id is 1 on
both queries).  The first query uses the ename index and estimates it will
return one row.  The second query uses the mgr index based on the empno
value returned by the first query and estimates it will return 2 rows.  Much
better :)
 
-- 
Dan Nelson
dnel...@allantgroup.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: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?

[Matt Neimeyer]

 SELECT zip FROM zipcodes WHERE
 degrees(acos(sin(radians(39.0788994))*sin(radians(latitude))+
 cos(radians(39.0788994))*cos(radians(latitude))*cos(radians(-77.1227036-longitude*60*1.1515
  5

 Ouch.  You might want to calculate the rectange enclosing your target
 distance, add an index on lat (and/or long), and add the rectangle check to
 your where clause: WHERE latitude BETWEEN lt1 AND lt2 AND longitude BETWEEN
 ln2 AND ln2.  That way mysql can use the index to pare down most of the rows
 without having to call all those trig functions for every zipcode.

I like this idea the best (it always bothered me running a query that
involved multiple mathmatical functions).

So... Here's the scratch php code I ended up with... Anyone see any
problems with it? The only problem I see is that I think the old code
was more circular this will be a square (within the limits of a
square on a non-spherical earth... etc.. etc..) ... so there will be
more zip codes included in the corners. If there are too many
complaints about that I might look at some sort of overlapping
rectangle scheme instead of a square.

function ChangeInLatitude($Miles) { return rad2deg($Miles/3960); }
function ChangeInLongitude($Lat, $Miles) { return
rad2deg($Miles/3960*cos(deg2rad($Lat))); }

$Miles = 5;

$OriginalLat = 39.0788994;
$OriginalLon = -77.1227036;

$ChangeInLat = ChangeInLatitude($Miles);
$ChangeInLon = ChangeInLongitude($OriginalLat, $Miles);

$MinLat = $OriginalLat-$ChangeInLat;
$MaxLat = $OriginalLat+$ChangeInLat;

$MinLon = $OriginalLon-$ChangeInLon;
$MaxLon = $OriginalLon+$ChangeInLon;

My only other question is... when I explained the new query... On the
dependent subquery it says possible keys are zip, longitude and
latitude but it used zip. It seems like a better index would be
longitude or latitude? On the primary query, even though there is an
index on custzip it doesn't say it's using ANY indexes. I should
probably leave well enough alone... but I'm curious.

Thanks again!

Matt

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?

[Brent Baisley]

It sounds like you want to use spatial indexes, but they only became
available in v4.1
http://dev.mysql.com/doc/refman/5.0/en/create-index.html
http://dev.mysql.com/doc/refman/5.0/en/using-a-spatial-index.html

You would need to switch your table type from InnoDB to MyISAM, which
is fairly easy with ALTER TABLE. But that should allow you to drop all
your calculations in the query.

You don't have to do any re-architecture to change you subquery to a join:
SELECT custzip FROM customers
JOIN
(SELECT zip FROM
zipcodes WHERE degrees(acos(sin(radians(39.0788994))*sin(radians(latitude))+
cos(radians(39.0788994))*cos(radians(latitude))*cos(radians(-77.1227036-longitude*60*1.1515
 5) AS zips
ON custzip=zip

Often times that simple change speeds things up considerably in MySQL.
An explain should show it has a DERIVED TABLE if I recall correctly.

Brent Baisley

On Thu, Jun 18, 2009 at 9:06 PM, Matt Neimeyerm...@neimeyer.org wrote:
 I'm converting a PHP app from using Visual FoxPro as the database
 backend to using MySQL as the backend. I'm testing on MySQL 4.1.22 on
 Mac OSX 10.4. The end application will be deployed cross platform and
 to both 4.x and 5.x MySQL servers.

 This query returned 21 records in .27 seconds.

   SELECT zip FROM zipcodes WHERE
 degrees(acos(sin(radians(39.0788994))*sin(radians(latitude))+
 cos(radians(39.0788994))*cos(radians(latitude))*cos(radians(-77.1227036-longitude*60*1.1515
  5

 This query returned 21442 records in 1.08 seconds.

   SELECT custzip FROM customers

 This query is still running half an hour later, with a Time of 2167
 and a State of Sending Data (according to the mysql process list)

   SELECT custzip FROM customers WHERE custzip IN (SELECT zip FROM
 zipcodes WHERE degrees(acos(sin(radians(39.0788994))*sin(radians(latitude))+
 cos(radians(39.0788994))*cos(radians(latitude))*cos(radians(-77.1227036-longitude*60*1.1515
  5)

 When I try to EXPLAIN the query it gives me the following...

 id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
 1,PRIMARY,customers,index,NULL,cw_custzip,30,NULL,21226,Using where; Using 
 index
 2,DEPENDENT SUBQUERY,zipcodes,ALL,NULL,NULL,NULL,NULL,42144,Using where

 If it matters both tables are INNODB and both customers.custzip and
 zipcodes.zip are indexed. We used a program called DBConvert from
 DMSoft to convert the data so it's exactly the same on both the VFP
 side and the MySQL side. With all that in mind... VFP returns the
 exact same query in 5-10 seconds and that includes render time in the
 web browser.

 By comparison... the query WHERE id IN (SELECT id FROM phone WHERE
 phonedate = '2001-01-01' AND phonedate = '2009-06-18') returns
 almost instantly.

 I'm at a complete loss... The suggestions I've seen online for
 optimizing Dependent Subquery's basically revolve around changing it
 from a sub-query to a join but that would require more
 re-architecturing than I want to do... (Unless I'm forced) Especially
 since more than a few of those solutions suggested precalculating the
 distance between zipcodes which only works if the distances are known
 (only allow 10, 50 and 100 mile radi for example)

 Any ideas?

 Thanks in advance!

 Matt

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:    http://lists.mysql.com/mysql?unsub=brentt...@gmail.com



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?

[Peter Brawley]

Matt,


This query is still running half an hour later, with a Time of 2167
and a State of Sending Data (according to the mysql process list)
  SELECT custzip FROM customers WHERE custzip IN ( ...


For explanation  alternatives see The unbearable slowness of IN() at 
http://localhost/artful/infotree/queries.php.


PB

-

Matt Neimeyer wrote:

I'm converting a PHP app from using Visual FoxPro as the database
backend to using MySQL as the backend. I'm testing on MySQL 4.1.22 on
Mac OSX 10.4. The end application will be deployed cross platform and
to both 4.x and 5.x MySQL servers.

This query returned 21 records in .27 seconds.

   SELECT zip FROM zipcodes WHERE
degrees(acos(sin(radians(39.0788994))*sin(radians(latitude))+
cos(radians(39.0788994))*cos(radians(latitude))*cos(radians(-77.1227036-longitude*60*1.1515
 5

This query returned 21442 records in 1.08 seconds.

   SELECT custzip FROM customers

This query is still running half an hour later, with a Time of 2167
and a State of Sending Data (according to the mysql process list)

   SELECT custzip FROM customers WHERE custzip IN (SELECT zip FROM
zipcodes WHERE degrees(acos(sin(radians(39.0788994))*sin(radians(latitude))+
cos(radians(39.0788994))*cos(radians(latitude))*cos(radians(-77.1227036-longitude*60*1.1515
 5)

When I try to EXPLAIN the query it gives me the following...

id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1,PRIMARY,customers,index,NULL,cw_custzip,30,NULL,21226,Using where; Using index
2,DEPENDENT SUBQUERY,zipcodes,ALL,NULL,NULL,NULL,NULL,42144,Using where

If it matters both tables are INNODB and both customers.custzip and
zipcodes.zip are indexed. We used a program called DBConvert from
DMSoft to convert the data so it's exactly the same on both the VFP
side and the MySQL side. With all that in mind... VFP returns the
exact same query in 5-10 seconds and that includes render time in the
web browser.

By comparison... the query WHERE id IN (SELECT id FROM phone WHERE
phonedate = '2001-01-01' AND phonedate = '2009-06-18') returns
almost instantly.

I'm at a complete loss... The suggestions I've seen online for
optimizing Dependent Subquery's basically revolve around changing it
from a sub-query to a join but that would require more
re-architecturing than I want to do... (Unless I'm forced) Especially
since more than a few of those solutions suggested precalculating the
distance between zipcodes which only works if the distances are known
(only allow 10, 50 and 100 mile radi for example)

Any ideas?

Thanks in advance!

Matt

  




No virus found in this incoming message.
Checked by AVG - www.avg.com 
Version: 8.5.364 / Virus Database: 270.12.78/2185 - Release Date: 06/18/09 05:53:00


  

Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?

[Walter Heck - OlinData.com]

Peter,


On Thu, Jun 18, 2009 at 9:27 PM, Peter
Brawleypeter.braw...@earthlink.net wrote:
 For explanation  alternatives see The unbearable slowness of IN() at
 http://localhost/artful/infotree/queries.php.

you prolly meant to not post a url pointing at your local copy of your
website. This works better for most of us:
http://www.artfulsoftware.com/infotree/queries.php ;)

Walter



-- 
Walter Heck, Engineer @ Open Query (http://openquery.com)
Affordable Training and ProActive Support for MySQL  related technologies

Follow our blog at http://openquery.com/blog/
OurDelta: free enhanced builds for MySQL @ http://ourdelta.org

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?

[Johnny Withers]

I often find doing the IN (subquery...) is really slow versus doing a join:

SELECT cutzip
FROM customers
INNER JOIN zipcodes ON customers.zipcode=zipcodes.zip
WHERE
degrees(acos(sin(radians(39.0788994))
*
sin(radians(latitude))
+
cos(radians(39.0788994))
*
cos(radians(latitude))
*
cos(radians(-77.1227036-longitude))
)
)
*60
*1.1515
 5

That query may have un-matched ()'s, not sure. hard to tell =)

Try a join.

-jw

On Thu, Jun 18, 2009 at 8:06 PM, Matt Neimeyer m...@neimeyer.org wrote:

 I'm converting a PHP app from using Visual FoxPro as the database
 backend to using MySQL as the backend. I'm testing on MySQL 4.1.22 on
 Mac OSX 10.4. The end application will be deployed cross platform and
 to both 4.x and 5.x MySQL servers.

 This query returned 21 records in .27 seconds.

   SELECT zip FROM zipcodes WHERE
 degrees(acos(sin(radians(39.0788994))*sin(radians(latitude))+

 cos(radians(39.0788994))*cos(radians(latitude))*cos(radians(-77.1227036-longitude*60*1.1515
  5

 This query returned 21442 records in 1.08 seconds.

   SELECT custzip FROM customers

 This query is still running half an hour later, with a Time of 2167
 and a State of Sending Data (according to the mysql process list)

   SELECT custzip FROM customers WHERE custzip IN (SELECT zip FROM
 zipcodes WHERE
 degrees(acos(sin(radians(39.0788994))*sin(radians(latitude))+

 cos(radians(39.0788994))*cos(radians(latitude))*cos(radians(-77.1227036-longitude*60*1.1515
  5)

 When I try to EXPLAIN the query it gives me the following...

 id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
 1,PRIMARY,customers,index,NULL,cw_custzip,30,NULL,21226,Using where; Using
 index
 2,DEPENDENT SUBQUERY,zipcodes,ALL,NULL,NULL,NULL,NULL,42144,Using where

 If it matters both tables are INNODB and both customers.custzip and
 zipcodes.zip are indexed. We used a program called DBConvert from
 DMSoft to convert the data so it's exactly the same on both the VFP
 side and the MySQL side. With all that in mind... VFP returns the
 exact same query in 5-10 seconds and that includes render time in the
 web browser.

 By comparison... the query WHERE id IN (SELECT id FROM phone WHERE
 phonedate = '2001-01-01' AND phonedate = '2009-06-18') returns
 almost instantly.

 I'm at a complete loss... The suggestions I've seen online for
 optimizing Dependent Subquery's basically revolve around changing it
 from a sub-query to a join but that would require more
 re-architecturing than I want to do... (Unless I'm forced) Especially
 since more than a few of those solutions suggested precalculating the
 distance between zipcodes which only works if the distances are known
 (only allow 10, 50 and 100 mile radi for example)

 Any ideas?

 Thanks in advance!

 Matt

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net




-- 
-
Johnny Withers
601.209.4985
joh...@pixelated.net

RE: Ordering an IN query

[Martin Gainty]

Aaron-

to reorder results of a column specify 
FIELD(ColumnName,1stPosition,2ndPosition) e.g.

mysqluse information_schema;
mysql select ordinal_position,TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME
 from COLUMNS WHERE ORDINAL_POSITION=23 OR ORDINAL_POSITION=18 ORDER BY 
FIELD(ORDINAL_POSITION,23,18);

Shalom
Martin Gainty 
__ 
Note de déni et de confidentialité
 Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le 
destinataire prévu, nous te demandons avec bonté que pour satisfaire informez 
l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est 
interdite. Ce message sert à l'information seulement et n'aura pas n'importe 
quel effet légalement obligatoire. Étant donné que les email peuvent facilement 
être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité 
pour le contenu fourni.




 From: afisc...@smith.edu
 To: mysql@lists.mysql.com
 Subject: Ordering an IN query
 Date: Fri, 5 Jun 2009 09:09:24 -0400
 
 I have a query that I build dynamically, here is an example:
 
 select from (table1 as t1 left join table2 as t2 on t1.id = t2.id)  
 left join table3 as t3 on t1.id = t3.id where t1.id in ('221593',  
 '221591', 'CC3762', 'CC0059')
 
 So I build the query in the order that I want it displayed.  That is  
 display 221593, then 221591, then CC3762, etc.  However, when the  
 query is executed it looks like it automatically sorts the codes in  
 ascending order, so I get 221591, then 221593, the CC0059, etc.
 
 I want the results displayed in the order that I build the query.  Is  
 there some way of doing that?
 
 Thanks,
 
 -Aaron
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com
 

_
Hotmail® has ever-growing storage! Don’t worry about storage limits. 
http://windowslive.com/Tutorial/Hotmail/Storage?ocid=TXT_TAGLM_WL_HM_Tutorial_Storage_062009

Re: Ordering an IN query

[Perrin Harkins]

On Fri, Jun 5, 2009 at 9:09 AM, Aaron Fischerafisc...@smith.edu wrote:
 So I build the query in the order that I want it displayed.  That is display
 221593, then 221591, then CC3762, etc.  However, when the query is executed
 it looks like it automatically sorts the codes in ascending order, so I get
 221591, then 221593, the CC0059, etc.

 I want the results displayed in the order that I build the query.  Is there
 some way of doing that?

You'll have to use a function like CASE() or FIND_IN_SET() to map the
IDs to values in an ORDER BY clause.

- Perrin

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Help with mysql query, multiple list

[Scott Haneda]

What about sub selects. As I see it you only care about the highest  
and lowest order of results in each list.


Sorry, in am on a mobile so I can nit make a test case, and this will  
be pseudo SQL.


Select * from table where start = (select foo) and ( select foo) ...

Also look at the between and in keywords.

Perhaps your list of timestamps is not in a database, can you put then  
in?


Either way unless I am reading you wrong you only need the greatest  
and lowest time in your comparison, not a huge list.

--
Scott
Iphone says hello.

On May 8, 2009, at 9:26 AM, Abhishek Pratap abhishek@gmail.com  
wrote:



Hi All

I am kind of stuck with this query  , cant expand my thinking. May  
this is a

limitation.  Here it is

I have a database with many cols two of which are start and end  
position for

an event.

Now I have a list of event time stamps, I want to find all the info  
once the
current event time stamp is = start time of event and =end time of  
event.


something  like this

select * from table_name where start = ( LIST of time stamps) AND  
end =(

list of time stamps).

Clearly above query accepts only one item in the list. Is there a  
way to do
this for multiple items in the list ??? I can't think of anything at  
this

moment.


Thanks,
-Abhi


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Help with mysql query, multiple list

[Simon J Mudd]

abhishek@gmail.com (Abhishek Pratap) writes:

 I am kind of stuck with this query  , cant expand my thinking. May this is a
 limitation.  Here it is
 
 I have a database with many cols two of which are start and end position for
 an event.
 
 Now I have a list of event time stamps, I want to find all the info once the
 current event time stamp is = start time of event and =end time of event.
 
 something  like this
 
 select * from table_name where start = ( LIST of time stamps) AND end =(
 list of time stamps).
 
 Clearly above query accepts only one item in the list. Is there a way to do
 this for multiple items in the list ??? I can't think of anything at this
 moment.

You said in a later post that you have thousands of events. If they are already 
in a table then use that, otherwise put the events into a temporary table
and join the 2 tables together.

Something like this simple example:

mysql select * from events;
+-+
| event_ts|
+-+
| 2009-05-09 10:29:00 |
+-+
1 row in set (0.00 sec)

mysql select * from table_name;
++-+-+
| id | start_ts| end_ts  |
++-+-+
|  1 | 2009-05-09 10:00:00 | 2009-05-09 11:00:00 |
|  2 | 2009-05-10 10:00:00 | 2009-05-10 11:00:00 |
++-+-+
2 rows in set (0.00 sec)

mysql select t.* from table_name t, events WHERE event_ts = start_ts and 
event_ts = end_ts;
++-+-+
| id | start_ts| end_ts  |
++-+-+
|  1 | 2009-05-09 10:00:00 | 2009-05-09 11:00:00 |
++-+-+
1 row in set (0.00 sec)

Hope this helps.

Simon

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Help with mysql query, multiple list

[Abhishek Pratap]

aah okie I think I was trying to get too clever. Guess that won't work ...

Thanks,
-Abhi

On Fri, May 8, 2009 at 12:34 PM, Barney Boisvert bboisv...@gmail.comwrote:

 You'll have to iterate over your two lists of timestamps and build a
 set of ORed conditional pairs:

 sql = select ... from ... where 1 = 0
 for (i = 0; i  timestamps.length; i++) {
  sql +=  or start = + timestamps[i] +  and end =  + timestamps[i]
 }

 You'll want to use bind parameters in real life, of course.

 cheers,
 barneyb

 On Fri, May 8, 2009 at 9:26 AM, Abhishek Pratap abhishek@gmail.com
 wrote:
  Hi All
 
  I am kind of stuck with this query  , cant expand my thinking. May this
 is a
  limitation.  Here it is
 
  I have a database with many cols two of which are start and end position
 for
  an event.
 
  Now I have a list of event time stamps, I want to find all the info once
 the
  current event time stamp is = start time of event and =end time of
 event.
 
  something  like this
 
  select * from table_name where start = ( LIST of time stamps) AND end
 =(
  list of time stamps).
 
  Clearly above query accepts only one item in the list. Is there a way to
 do
  this for multiple items in the list ??? I can't think of anything at this
  moment.
 
 
  Thanks,
  -Abhi
 



 --
 Barney Boisvert
 bboisv...@gmail.com
 http://www.barneyb.com/

Re: Help with mysql query, multiple list

[Jim Lyons]

why not something like below.  Assume you have 3 pairs of start/end
timestamps and you want to find everything within those 3 time periods:

select * from table_name where start = start1 and end = end1
union
select * from table_name where start = start2 and end = end2
union
select * from table_name where start = start3 and end = end3

On Fri, May 8, 2009 at 11:26 AM, Abhishek Pratap abhishek@gmail.comwrote:

 Hi All

 I am kind of stuck with this query  , cant expand my thinking. May this is
 a
 limitation.  Here it is

 I have a database with many cols two of which are start and end position
 for
 an event.

 Now I have a list of event time stamps, I want to find all the info once
 the
 current event time stamp is = start time of event and =end time of event.

 something  like this

 select * from table_name where start = ( LIST of time stamps) AND end =(
 list of time stamps).

 Clearly above query accepts only one item in the list. Is there a way to do
 this for multiple items in the list ??? I can't think of anything at this
 moment.


 Thanks,
 -Abhi




-- 
Jim Lyons
Web developer / Database administrator
http://www.weblyons.com

Re: Help with mysql query, multiple list

[Abhishek Pratap]

Hi Jim

Unfortunately I have thousands of such points. So explicit statement calling
will be very expensive both computationally and in terms of writing..

Thanks,
-Abhi

On Fri, May 8, 2009 at 12:37 PM, Jim Lyons jlyons4...@gmail.com wrote:

 why not something like below.  Assume you have 3 pairs of start/end
 timestamps and you want to find everything within those 3 time periods:

 select * from table_name where start = start1 and end = end1
 union
 select * from table_name where start = start2 and end = end2
 union
 select * from table_name where start = start3 and end = end3


 On Fri, May 8, 2009 at 11:26 AM, Abhishek Pratap 
 abhishek@gmail.comwrote:

 Hi All

 I am kind of stuck with this query  , cant expand my thinking. May this is
 a
 limitation.  Here it is

 I have a database with many cols two of which are start and end position
 for
 an event.

 Now I have a list of event time stamps, I want to find all the info once
 the
 current event time stamp is = start time of event and =end time of
 event.

 something  like this

 select * from table_name where start = ( LIST of time stamps) AND end =(
 list of time stamps).

 Clearly above query accepts only one item in the list. Is there a way to
 do
 this for multiple items in the list ??? I can't think of anything at this
 moment.


 Thanks,
 -Abhi




 --
 Jim Lyons
 Web developer / Database administrator
 http://www.weblyons.com

Re: Help with mysql query, multiple list

[Kyong Kim]

Abhi,

I might not be understanding the problem but could you use the max 
and min timestamp values and use something like


SELECT * FROM TABLE WHERE start BETWEEN max AND min AND end BETWEEN max AND min

or

SELECT * FROM TABLE WHERE START IN (1,2,3,4,5) AND END IN(1,2,3,4,5)

I might be completely off-base here though as I don't think I fully 
comprehend what you're trying to do.


Kyong

At 09:36 AM 5/8/2009, Abhishek Pratap wrote:

aah okie I think I was trying to get too clever. Guess that won't work ...

Thanks,
-Abhi

On Fri, May 8, 2009 at 12:34 PM, Barney Boisvert bboisv...@gmail.comwrote:

 You'll have to iterate over your two lists of timestamps and build a
 set of ORed conditional pairs:

 sql = select ... from ... where 1 = 0
 for (i = 0; i  timestamps.length; i++) {
  sql +=  or start = + timestamps[i] +  and end =  + timestamps[i]
 }

 You'll want to use bind parameters in real life, of course.

 cheers,
 barneyb

 On Fri, May 8, 2009 at 9:26 AM, Abhishek Pratap abhishek@gmail.com
 wrote:
  Hi All
 
  I am kind of stuck with this query  , cant expand my thinking. May this
 is a
  limitation.  Here it is
 
  I have a database with many cols two of which are start and end position
 for
  an event.
 
  Now I have a list of event time stamps, I want to find all the info once
 the
  current event time stamp is = start time of event and =end time of
 event.
 
  something  like this
 
  select * from table_name where start = ( LIST of time stamps) AND end
 =(
  list of time stamps).
 
  Clearly above query accepts only one item in the list. Is there a way to
 do
  this for multiple items in the list ??? I can't think of anything at this
  moment.
 
 
  Thanks,
  -Abhi
 



 --
 Barney Boisvert
 bboisv...@gmail.com
 http://www.barneyb.com/




Kyong Kim
Instructional Multimedia/Web Programmer
Foothill College
12345 El Monte Rd
3601
Los Altos Hills, CA 94022
650-949-7091


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org