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

[Larry Martell]

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

 I need to count the number of rows in a table that are grouped by a
 list of columns, but I also need to exclude rows that have more then
 some count when grouped by a different set of columns. Conceptually,
 this is not hard, but I am having trouble doing this efficiently.

 My first counting query would be this:

 SELECT count(*)
 FROM cst_rollup
 GROUP BY target_name_id, ep, roiname, recipe_process,
 recipe_product, recipe_layer, f_tag_bottom,
 measname, recipe_id

 But from this count I need to subtract the count of rows that have
 more then 50 rows with a different grouping:

 SELECT count(*)
 FROM cst_rollup
 GROUP BY target_name_id, ep, wafer_id
 HAVING count(*) >= 50

 As you can see, the second query has wafer_id, but the first query does
 not.

 Currently I am doing this in python, and it's slow. In my current
 implementation I have one query, and it selects the columns (i.e.
 doesn't just count), and I have added wafer_id:

 SELECT target_name_id, ep, roiname, recipe_process,
 recipe_product, recipe_layer, f_tag_bottom,
 measname, recipe_id, wafer_id
 FROM cst_rollup

 Then I go through the result set (which can be over 200k rows) and I
 count the number of rows with matching (target_name_id, ep, wafer_id).
 Then I go through the rows again and regroup them without wafer_id,
 but skipping the rows that have more then 50 rows for that row's
 (target_name_id, ep, wafer_id).

 Is this clear to everyone what I am trying to do?
>>>
>>>
>>> If I've understand this correctly, the resultset you wish to aggregate on
>>> is
>>> ...
>>>
>>> select target_name_id, ep, wafer_id
>>> from cst_rollup a
>>> left join (   -- exclude rows for which wafer_id count >= 50
>>>select name_id, ep, wafer, count(*) n
>>>from cst_rollup
>>>group by target_name_id, ep, wafer_id
>>>having n >= 50
>>> ) b using ( target_name_id, ep, wafer_id )
>>> where b.target_name is null ;
>>>
>>> If that's so, you could assemble that resultset in a temp table then run
>>> the
>>> desired aggregate query on it, or you could aggregate on it directly as a
>>> subquery.
>>
>> That query gives:
>>
>> ERROR 1137 (HY000): Can't reopen table: 'a'
>
>
> So, it's a temporary table, and you'll need to make that not so.

Yes, cst_rollup is a temp table. The underlying table is millions of
rows (with 300 columns) so for efficiency a subset of the rows and
columns are selected into the temp table based on some user input.
It's just the rows in the temp table that are of interest for the
current report.

I was able to get this working with a second temp table:

CREATE TEMPORARY TABLE rollup_exclude
SELECT target_name_id, ep, wafer_id, count(*) n
FROM cst_rollup
GROUP BY target_name_id, ep, wafer_id
HAVING n >= 50

And then:

SELECT count(*)
FROM cst_rollup
LEFT JOIN(
SELECT target_name_id, ep, wafer_id
FROM rollup_exclude) b
USING (target_name_id, ep, wafer_id)
WHERE b.target_name_id IS NULL
GROUP by target_name_id, ep, roiname, recipe_process, recipe_product,
recipe_layer, f_tag_bottom, measname, recipe_id

And the rowcount from that query gave me what I needed.

Thanks very much for the help Peter, you gave me a push toward the right path.

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

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

[Larry Martell]

On Fri, Jan 1, 2016 at 2:12 PM, Peter Brawley
 wrote:
> On 12/31/2015 0:51, Larry Martell wrote:
>>
>> I need to count the number of rows in a table that are grouped by a
>> list of columns, but I also need to exclude rows that have more then
>> some count when grouped by a different set of columns. Conceptually,
>> this is not hard, but I am having trouble doing this efficiently.
>>
>> My first counting query would be this:
>>
>> SELECT count(*)
>> FROM cst_rollup
>> GROUP BY target_name_id, ep, roiname, recipe_process,
>> recipe_product, recipe_layer, f_tag_bottom,
>> measname, recipe_id
>>
>> But from this count I need to subtract the count of rows that have
>> more then 50 rows with a different grouping:
>>
>> SELECT count(*)
>> FROM cst_rollup
>> GROUP BY target_name_id, ep, wafer_id
>> HAVING count(*) >= 50
>>
>> As you can see, the second query has wafer_id, but the first query does
>> not.
>>
>> Currently I am doing this in python, and it's slow. In my current
>> implementation I have one query, and it selects the columns (i.e.
>> doesn't just count), and I have added wafer_id:
>>
>> SELECT target_name_id, ep, roiname, recipe_process,
>> recipe_product, recipe_layer, f_tag_bottom,
>> measname, recipe_id, wafer_id
>> FROM cst_rollup
>>
>> Then I go through the result set (which can be over 200k rows) and I
>> count the number of rows with matching (target_name_id, ep, wafer_id).
>> Then I go through the rows again and regroup them without wafer_id,
>> but skipping the rows that have more then 50 rows for that row's
>> (target_name_id, ep, wafer_id).
>>
>> Is this clear to everyone what I am trying to do?
>
>
> If I've understand this correctly, the resultset you wish to aggregate on is
> ...
>
> select target_name_id, ep, wafer_id
> from cst_rollup a
> left join (   -- exclude rows for which wafer_id count >= 50
>   select name_id, ep, wafer, count(*) n
>   from cst_rollup
>   group by target_name_id, ep, wafer_id
>   having n >= 50
> ) b using ( target_name_id, ep, wafer_id )
> where b.target_name is null ;
>
> If that's so, you could assemble that resultset in a temp table then run the
> desired aggregate query on it, or you could aggregate on it directly as a
> subquery.

That query gives:

ERROR 1137 (HY000): Can't reopen table: 'a'

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

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

[Peter Brawley]

On 12/31/2015 0:51, Larry Martell wrote:

I need to count the number of rows in a table that are grouped by a
list of columns, but I also need to exclude rows that have more then
some count when grouped by a different set of columns. Conceptually,
this is not hard, but I am having trouble doing this efficiently.

My first counting query would be this:

SELECT count(*)
FROM cst_rollup
GROUP BY target_name_id, ep, roiname, recipe_process,
recipe_product, recipe_layer, f_tag_bottom,
measname, recipe_id

But from this count I need to subtract the count of rows that have
more then 50 rows with a different grouping:

SELECT count(*)
FROM cst_rollup
GROUP BY target_name_id, ep, wafer_id
HAVING count(*) >= 50

As you can see, the second query has wafer_id, but the first query does not.

Currently I am doing this in python, and it's slow. In my current
implementation I have one query, and it selects the columns (i.e.
doesn't just count), and I have added wafer_id:

SELECT target_name_id, ep, roiname, recipe_process,
recipe_product, recipe_layer, f_tag_bottom,
measname, recipe_id, wafer_id
FROM cst_rollup

Then I go through the result set (which can be over 200k rows) and I
count the number of rows with matching (target_name_id, ep, wafer_id).
Then I go through the rows again and regroup them without wafer_id,
but skipping the rows that have more then 50 rows for that row's
(target_name_id, ep, wafer_id).

Is this clear to everyone what I am trying to do?


If I've understand this correctly, the resultset you wish to aggregate 
on is ...


select target_name_id, ep, wafer_id
from cst_rollup a
left join (   -- exclude rows for which wafer_id count >= 50
  select name_id, ep, wafer, count(*) n
  from cst_rollup
  group by target_name_id, ep, wafer_id
  having n >= 50
) b using ( target_name_id, ep, wafer_id )
where b.target_name is null ;

If that's so, you could assemble that resultset in a temp table then run 
the desired aggregate query on it, or you could aggregate on it directly 
as a subquery.


PB

-


I'd like to do this all in sql with count because then I do not have
to actually return and parse the data in python.

Can anyone think of a way to do this in sql in a way that will be more
efficient then my current implementation?


Thanks!
-Larry




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

help with query to count rows while excluding certain rows

[Larry Martell]

I need to count the number of rows in a table that are grouped by a
list of columns, but I also need to exclude rows that have more then
some count when grouped by a different set of columns. Conceptually,
this is not hard, but I am having trouble doing this efficiently.

My first counting query would be this:

SELECT count(*)
FROM cst_rollup
GROUP BY target_name_id, ep, roiname, recipe_process,
recipe_product, recipe_layer, f_tag_bottom,
measname, recipe_id

But from this count I need to subtract the count of rows that have
more then 50 rows with a different grouping:

SELECT count(*)
FROM cst_rollup
GROUP BY target_name_id, ep, wafer_id
HAVING count(*) >= 50

As you can see, the second query has wafer_id, but the first query does not.

Currently I am doing this in python, and it's slow. In my current
implementation I have one query, and it selects the columns (i.e.
doesn't just count), and I have added wafer_id:

SELECT target_name_id, ep, roiname, recipe_process,
recipe_product, recipe_layer, f_tag_bottom,
measname, recipe_id, wafer_id
FROM cst_rollup

Then I go through the result set (which can be over 200k rows) and I
count the number of rows with matching (target_name_id, ep, wafer_id).
Then I go through the rows again and regroup them without wafer_id,
but skipping the rows that have more then 50 rows for that row's
(target_name_id, ep, wafer_id).

Is this clear to everyone what I am trying to do?

I'd like to do this all in sql with count because then I do not have
to actually return and parse the data in python.

Can anyone think of a way to do this in sql in a way that will be more
efficient then my current implementation?


Thanks!
-Larry

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

Re: Help improving query performance

[shawn l.green]

Hi Larry,

On 2/1/2015 4:49 PM, Larry Martell wrote:

I have 2 queries. One takes 4 hours to run and returns 21 rows, and
the other, which has 1 additional where clause, takes 3 minutes and
returns 20 rows. The main table being selected from is largish
(37,247,884 rows with 282 columns). Caching is off for my testing, so
it's not related to that. To short circuit anyone asking, these
queries are generated by python code, which is why there's an IN
clause with 1 value, as oppose to an =.

Here are the queries and their explains. The significant difference is
that the faster query has Using
intersect(data_cst_bbccbce0,data_cst_fba12377) in the query plan -
those 2 indexes are on the 2 columns in the where clause, so that's
why the second one is faster. But I am wondering what I can do to make
the first one faster.


4 hour query:

SELECT MIN(data_tool.name) as tool,
MIN(data_cst.date_time) start,
MAX(data_cst.date_time) end,
MIN(data_target.name) as target,
MIN(data_lot.name) as lot,
MIN(data_wafer.name) as wafer,
MIN(measname) as measname,
MIN(data_recipe.name) as recipe
FROM data_cst
INNER JOIN data_tool ON data_tool.id = data_cst.tool_id
INNER JOIN data_target ON data_target.id = data_cst.target_name_id
INNER JOIN data_lot ON data_lot.id = data_cst.lot_id
INNER JOIN data_wafer ON data_wafer.id = data_cst.wafer_id
INNER JOIN data_measparams ON data_measparams.id = data_cst.meas_params_name_id
INNER JOIN data_recipe ON data_recipe.id = data_cst.recipe_id
WHERE data_target.id IN (172) AND
   data_cst.date_time BETWEEN '2015-01-26 00:00:00' AND '2015-01-26 
23:59:59'
GROUP BY wafer_id, data_cst.lot_id, target_name_id



... snipped ...




Faster query:

SELECT MIN(data_tool.name) as tool,
MIN(data_cst.date_time) start,
MAX(data_cst.date_time) end,
MIN(data_target.name) as target,
MIN(data_lot.name) as lot,
MIN(data_wafer.name) as wafer,
MIN(measname) as measname,
MIN(data_recipe.name) as recipe
FROM data_cst
INNER JOIN data_tool ON data_tool.id = data_cst.tool_id
INNER JOIN data_target ON data_target.id = data_cst.target_name_id
INNER JOIN data_lot ON data_lot.id = data_cst.lot_id
INNER JOIN data_wafer ON data_wafer.id = data_cst.wafer_id
INNER JOIN data_measparams ON data_measparams.id = data_cst.meas_params_name_id
INNER JOIN data_recipe ON data_recipe.id = data_cst.recipe_id
WHERE data_target.id IN (172) AND
   data_recipe.id IN (148) AND
   data_cst.date_time BETWEEN '2015-01-26 00:00:00' AND '2015-01-26 
23:59:59'
GROUP BY wafer_id, data_cst.lot_id, target_name_id


... snip ...


Thanks for taking the time to read this, and for any help or pointers
you can give me.



The biggest difference is the added selectivity generated by the WHERE 
term against the data_recipe table.


Compare the two EXPLAINS, in the faster query you see that data_recipe 
is listed second. This allows the additional term a chance to reduce the 
number of row combinations for the entire query.


To really get at the logic behind how the Optimizer chooses its 
execution plan, get an optimizer trace. Look at the cost estimates for 
each phase being considered.

http://dev.mysql.com/doc/refman/5.6/en/optimizer-trace-table.html
http://dev.mysql.com/doc/internals/en/optimizer-tracing.html

Yours,
--
Shawn Green
MySQL Senior 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: Help improving query performance

[shawn l.green]

Hi Larry,

On 2/4/2015 3:18 PM, Larry Martell wrote:

On Wed, Feb 4, 2015 at 2:56 PM, shawn l.green shawn.l.gr...@oracle.com wrote:

Hi Larry,


On 2/1/2015 4:49 PM, Larry Martell wrote:


I have 2 queries. One takes 4 hours to run and returns 21 rows, and
the other, which has 1 additional where clause, takes 3 minutes and
returns 20 rows. The main table being selected from is largish
(37,247,884 rows with 282 columns). Caching is off for my testing, so
it's not related to that. To short circuit anyone asking, these
queries are generated by python code, which is why there's an IN
clause with 1 value, as oppose to an =.

Here are the queries and their explains. The significant difference is
that the faster query has Using
intersect(data_cst_bbccbce0,data_cst_fba12377) in the query plan -
those 2 indexes are on the 2 columns in the where clause, so that's
why the second one is faster. But I am wondering what I can do to make
the first one faster.


4 hour query:

SELECT MIN(data_tool.name) as tool,
 MIN(data_cst.date_time) start,
 MAX(data_cst.date_time) end,
 MIN(data_target.name) as target,
 MIN(data_lot.name) as lot,
 MIN(data_wafer.name) as wafer,
 MIN(measname) as measname,
 MIN(data_recipe.name) as recipe
FROM data_cst
INNER JOIN data_tool ON data_tool.id = data_cst.tool_id
INNER JOIN data_target ON data_target.id = data_cst.target_name_id
INNER JOIN data_lot ON data_lot.id = data_cst.lot_id
INNER JOIN data_wafer ON data_wafer.id = data_cst.wafer_id
INNER JOIN data_measparams ON data_measparams.id =
data_cst.meas_params_name_id
INNER JOIN data_recipe ON data_recipe.id = data_cst.recipe_id
WHERE data_target.id IN (172) AND
data_cst.date_time BETWEEN '2015-01-26 00:00:00' AND '2015-01-26
23:59:59'
GROUP BY wafer_id, data_cst.lot_id, target_name_id



... snipped ...




Faster query:

SELECT MIN(data_tool.name) as tool,
 MIN(data_cst.date_time) start,
 MAX(data_cst.date_time) end,
 MIN(data_target.name) as target,
 MIN(data_lot.name) as lot,
 MIN(data_wafer.name) as wafer,
 MIN(measname) as measname,
 MIN(data_recipe.name) as recipe
FROM data_cst
INNER JOIN data_tool ON data_tool.id = data_cst.tool_id
INNER JOIN data_target ON data_target.id = data_cst.target_name_id
INNER JOIN data_lot ON data_lot.id = data_cst.lot_id
INNER JOIN data_wafer ON data_wafer.id = data_cst.wafer_id
INNER JOIN data_measparams ON data_measparams.id =
data_cst.meas_params_name_id
INNER JOIN data_recipe ON data_recipe.id = data_cst.recipe_id
WHERE data_target.id IN (172) AND
data_recipe.id IN (148) AND
data_cst.date_time BETWEEN '2015-01-26 00:00:00' AND '2015-01-26
23:59:59'
GROUP BY wafer_id, data_cst.lot_id, target_name_id


... snip ...



Thanks for taking the time to read this, and for any help or pointers
you can give me.



The biggest difference is the added selectivity generated by the WHERE term
against the data_recipe table.

Compare the two EXPLAINS, in the faster query you see that data_recipe is
listed second. This allows the additional term a chance to reduce the number
of row combinations for the entire query.

To really get at the logic behind how the Optimizer chooses its execution
plan, get an optimizer trace. Look at the cost estimates for each phase
being considered.
http://dev.mysql.com/doc/refman/5.6/en/optimizer-trace-table.html
http://dev.mysql.com/doc/internals/en/optimizer-tracing.html


Thanks very much Shawn for the reply and the links. I will check those
out and I'm sure I will find them very useful.

Meanwhile I changed the query to select from data_cst using the where
clause into a temp table and then I join the temp table with the other
tables. That has improved the slow query from 4 hours to 10 seconds
(!)



Did you also add an index to the temporary table for the JOIN condition? 
It might make it even faster


Yours,
--
Shawn Green
MySQL Senior 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: Help improving query performance

[Larry Martell]

On Wed, Feb 4, 2015 at 3:25 PM, shawn l.green shawn.l.gr...@oracle.com wrote:
 Hi Larry,


 On 2/4/2015 3:18 PM, Larry Martell wrote:

 On Wed, Feb 4, 2015 at 2:56 PM, shawn l.green shawn.l.gr...@oracle.com
 wrote:

 Hi Larry,


 On 2/1/2015 4:49 PM, Larry Martell wrote:


 I have 2 queries. One takes 4 hours to run and returns 21 rows, and
 the other, which has 1 additional where clause, takes 3 minutes and
 returns 20 rows. The main table being selected from is largish
 (37,247,884 rows with 282 columns). Caching is off for my testing, so
 it's not related to that. To short circuit anyone asking, these
 queries are generated by python code, which is why there's an IN
 clause with 1 value, as oppose to an =.

 Here are the queries and their explains. The significant difference is
 that the faster query has Using
 intersect(data_cst_bbccbce0,data_cst_fba12377) in the query plan -
 those 2 indexes are on the 2 columns in the where clause, so that's
 why the second one is faster. But I am wondering what I can do to make
 the first one faster.


 4 hour query:

 SELECT MIN(data_tool.name) as tool,
  MIN(data_cst.date_time) start,
  MAX(data_cst.date_time) end,
  MIN(data_target.name) as target,
  MIN(data_lot.name) as lot,
  MIN(data_wafer.name) as wafer,
  MIN(measname) as measname,
  MIN(data_recipe.name) as recipe
 FROM data_cst
 INNER JOIN data_tool ON data_tool.id = data_cst.tool_id
 INNER JOIN data_target ON data_target.id = data_cst.target_name_id
 INNER JOIN data_lot ON data_lot.id = data_cst.lot_id
 INNER JOIN data_wafer ON data_wafer.id = data_cst.wafer_id
 INNER JOIN data_measparams ON data_measparams.id =
 data_cst.meas_params_name_id
 INNER JOIN data_recipe ON data_recipe.id = data_cst.recipe_id
 WHERE data_target.id IN (172) AND
 data_cst.date_time BETWEEN '2015-01-26 00:00:00' AND '2015-01-26
 23:59:59'
 GROUP BY wafer_id, data_cst.lot_id, target_name_id


 ... snipped ...



 Faster query:

 SELECT MIN(data_tool.name) as tool,
  MIN(data_cst.date_time) start,
  MAX(data_cst.date_time) end,
  MIN(data_target.name) as target,
  MIN(data_lot.name) as lot,
  MIN(data_wafer.name) as wafer,
  MIN(measname) as measname,
  MIN(data_recipe.name) as recipe
 FROM data_cst
 INNER JOIN data_tool ON data_tool.id = data_cst.tool_id
 INNER JOIN data_target ON data_target.id = data_cst.target_name_id
 INNER JOIN data_lot ON data_lot.id = data_cst.lot_id
 INNER JOIN data_wafer ON data_wafer.id = data_cst.wafer_id
 INNER JOIN data_measparams ON data_measparams.id =
 data_cst.meas_params_name_id
 INNER JOIN data_recipe ON data_recipe.id = data_cst.recipe_id
 WHERE data_target.id IN (172) AND
 data_recipe.id IN (148) AND
 data_cst.date_time BETWEEN '2015-01-26 00:00:00' AND '2015-01-26
 23:59:59'
 GROUP BY wafer_id, data_cst.lot_id, target_name_id

 ... snip ...



 Thanks for taking the time to read this, and for any help or pointers
 you can give me.


 The biggest difference is the added selectivity generated by the WHERE
 term
 against the data_recipe table.

 Compare the two EXPLAINS, in the faster query you see that data_recipe is
 listed second. This allows the additional term a chance to reduce the
 number
 of row combinations for the entire query.

 To really get at the logic behind how the Optimizer chooses its execution
 plan, get an optimizer trace. Look at the cost estimates for each phase
 being considered.
 http://dev.mysql.com/doc/refman/5.6/en/optimizer-trace-table.html
 http://dev.mysql.com/doc/internals/en/optimizer-tracing.html


 Thanks very much Shawn for the reply and the links. I will check those
 out and I'm sure I will find them very useful.

 Meanwhile I changed the query to select from data_cst using the where
 clause into a temp table and then I join the temp table with the other
 tables. That has improved the slow query from 4 hours to 10 seconds
 (!)


 Did you also add an index to the temporary table for the JOIN condition? It
 might make it even faster

No, I didn't. I (and the users) were so shocked and happy with the
massive improvement I moved on to make similar changes to other
queries.

This is a django app, and it's a one-shot deal - i.e. there's just the
one query run and the response is sent back to the browser and that's
the end of the session and the temp table. So I'm thinking it's
probably not worth it.

As an aside this change has messed up all my unit tests - they send
multiple requests, but they're all in the same session. So only the
first succeeds and the next one fails because the temp table already
exists. I haven't figured out how to get it run each request in its
own session. I guess I'm going to have to drop the temp table after I
join with it before I sent the response back.

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

Re: Help improving query performance

[Larry Martell]

On Wed, Feb 4, 2015 at 2:56 PM, shawn l.green shawn.l.gr...@oracle.com wrote:
 Hi Larry,


 On 2/1/2015 4:49 PM, Larry Martell wrote:

 I have 2 queries. One takes 4 hours to run and returns 21 rows, and
 the other, which has 1 additional where clause, takes 3 minutes and
 returns 20 rows. The main table being selected from is largish
 (37,247,884 rows with 282 columns). Caching is off for my testing, so
 it's not related to that. To short circuit anyone asking, these
 queries are generated by python code, which is why there's an IN
 clause with 1 value, as oppose to an =.

 Here are the queries and their explains. The significant difference is
 that the faster query has Using
 intersect(data_cst_bbccbce0,data_cst_fba12377) in the query plan -
 those 2 indexes are on the 2 columns in the where clause, so that's
 why the second one is faster. But I am wondering what I can do to make
 the first one faster.


 4 hour query:

 SELECT MIN(data_tool.name) as tool,
 MIN(data_cst.date_time) start,
 MAX(data_cst.date_time) end,
 MIN(data_target.name) as target,
 MIN(data_lot.name) as lot,
 MIN(data_wafer.name) as wafer,
 MIN(measname) as measname,
 MIN(data_recipe.name) as recipe
 FROM data_cst
 INNER JOIN data_tool ON data_tool.id = data_cst.tool_id
 INNER JOIN data_target ON data_target.id = data_cst.target_name_id
 INNER JOIN data_lot ON data_lot.id = data_cst.lot_id
 INNER JOIN data_wafer ON data_wafer.id = data_cst.wafer_id
 INNER JOIN data_measparams ON data_measparams.id =
 data_cst.meas_params_name_id
 INNER JOIN data_recipe ON data_recipe.id = data_cst.recipe_id
 WHERE data_target.id IN (172) AND
data_cst.date_time BETWEEN '2015-01-26 00:00:00' AND '2015-01-26
 23:59:59'
 GROUP BY wafer_id, data_cst.lot_id, target_name_id


 ... snipped ...



 Faster query:

 SELECT MIN(data_tool.name) as tool,
 MIN(data_cst.date_time) start,
 MAX(data_cst.date_time) end,
 MIN(data_target.name) as target,
 MIN(data_lot.name) as lot,
 MIN(data_wafer.name) as wafer,
 MIN(measname) as measname,
 MIN(data_recipe.name) as recipe
 FROM data_cst
 INNER JOIN data_tool ON data_tool.id = data_cst.tool_id
 INNER JOIN data_target ON data_target.id = data_cst.target_name_id
 INNER JOIN data_lot ON data_lot.id = data_cst.lot_id
 INNER JOIN data_wafer ON data_wafer.id = data_cst.wafer_id
 INNER JOIN data_measparams ON data_measparams.id =
 data_cst.meas_params_name_id
 INNER JOIN data_recipe ON data_recipe.id = data_cst.recipe_id
 WHERE data_target.id IN (172) AND
data_recipe.id IN (148) AND
data_cst.date_time BETWEEN '2015-01-26 00:00:00' AND '2015-01-26
 23:59:59'
 GROUP BY wafer_id, data_cst.lot_id, target_name_id

 ... snip ...


 Thanks for taking the time to read this, and for any help or pointers
 you can give me.


 The biggest difference is the added selectivity generated by the WHERE term
 against the data_recipe table.

 Compare the two EXPLAINS, in the faster query you see that data_recipe is
 listed second. This allows the additional term a chance to reduce the number
 of row combinations for the entire query.

 To really get at the logic behind how the Optimizer chooses its execution
 plan, get an optimizer trace. Look at the cost estimates for each phase
 being considered.
 http://dev.mysql.com/doc/refman/5.6/en/optimizer-trace-table.html
 http://dev.mysql.com/doc/internals/en/optimizer-tracing.html

Thanks very much Shawn for the reply and the links. I will check those
out and I'm sure I will find them very useful.

Meanwhile I changed the query to select from data_cst using the where
clause into a temp table and then I join the temp table with the other
tables. That has improved the slow query from 4 hours to 10 seconds
(!)

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

Re: Help improving query performance

[shawn l.green]

Hello Larry,

On 2/4/2015 3:37 PM, Larry Martell wrote:

On Wed, Feb 4, 2015 at 3:25 PM, shawn l.green shawn.l.gr...@oracle.com wrote:

Hi Larry,


On 2/4/2015 3:18 PM, Larry Martell wrote:


On Wed, Feb 4, 2015 at 2:56 PM, shawn l.green shawn.l.gr...@oracle.com
wrote:


Hi Larry,


On 2/1/2015 4:49 PM, Larry Martell wrote:



I have 2 queries. One takes 4 hours to run and returns 21 rows, and
the other, which has 1 additional where clause, takes 3 minutes and
returns 20 rows. The main table being selected from is largish
(37,247,884 rows with 282 columns). Caching is off for my testing, so
it's not related to that. To short circuit anyone asking, these
queries are generated by python code, which is why there's an IN
clause with 1 value, as oppose to an =.

Here are the queries and their explains. The significant difference is
that the faster query has Using
intersect(data_cst_bbccbce0,data_cst_fba12377) in the query plan -
those 2 indexes are on the 2 columns in the where clause, so that's
why the second one is faster. But I am wondering what I can do to make
the first one faster.


4 hour query:

SELECT MIN(data_tool.name) as tool,
  MIN(data_cst.date_time) start,
  MAX(data_cst.date_time) end,
  MIN(data_target.name) as target,
  MIN(data_lot.name) as lot,
  MIN(data_wafer.name) as wafer,
  MIN(measname) as measname,
  MIN(data_recipe.name) as recipe
FROM data_cst
INNER JOIN data_tool ON data_tool.id = data_cst.tool_id
INNER JOIN data_target ON data_target.id = data_cst.target_name_id
INNER JOIN data_lot ON data_lot.id = data_cst.lot_id
INNER JOIN data_wafer ON data_wafer.id = data_cst.wafer_id
INNER JOIN data_measparams ON data_measparams.id =
data_cst.meas_params_name_id
INNER JOIN data_recipe ON data_recipe.id = data_cst.recipe_id
WHERE data_target.id IN (172) AND
 data_cst.date_time BETWEEN '2015-01-26 00:00:00' AND '2015-01-26
23:59:59'
GROUP BY wafer_id, data_cst.lot_id, target_name_id



... snipped ...




Faster query:

SELECT MIN(data_tool.name) as tool,
  MIN(data_cst.date_time) start,
  MAX(data_cst.date_time) end,
  MIN(data_target.name) as target,
  MIN(data_lot.name) as lot,
  MIN(data_wafer.name) as wafer,
  MIN(measname) as measname,
  MIN(data_recipe.name) as recipe
FROM data_cst
INNER JOIN data_tool ON data_tool.id = data_cst.tool_id
INNER JOIN data_target ON data_target.id = data_cst.target_name_id
INNER JOIN data_lot ON data_lot.id = data_cst.lot_id
INNER JOIN data_wafer ON data_wafer.id = data_cst.wafer_id
INNER JOIN data_measparams ON data_measparams.id =
data_cst.meas_params_name_id
INNER JOIN data_recipe ON data_recipe.id = data_cst.recipe_id
WHERE data_target.id IN (172) AND
 data_recipe.id IN (148) AND
 data_cst.date_time BETWEEN '2015-01-26 00:00:00' AND '2015-01-26
23:59:59'
GROUP BY wafer_id, data_cst.lot_id, target_name_id


... snip ...




Thanks for taking the time to read this, and for any help or pointers
you can give me.



The biggest difference is the added selectivity generated by the WHERE
term
against the data_recipe table.

Compare the two EXPLAINS, in the faster query you see that data_recipe is
listed second. This allows the additional term a chance to reduce the
number
of row combinations for the entire query.

To really get at the logic behind how the Optimizer chooses its execution
plan, get an optimizer trace. Look at the cost estimates for each phase
being considered.
http://dev.mysql.com/doc/refman/5.6/en/optimizer-trace-table.html
http://dev.mysql.com/doc/internals/en/optimizer-tracing.html



Thanks very much Shawn for the reply and the links. I will check those
out and I'm sure I will find them very useful.

Meanwhile I changed the query to select from data_cst using the where
clause into a temp table and then I join the temp table with the other
tables. That has improved the slow query from 4 hours to 10 seconds
(!)



Did you also add an index to the temporary table for the JOIN condition? It
might make it even faster


No, I didn't. I (and the users) were so shocked and happy with the
massive improvement I moved on to make similar changes to other
queries.

This is a django app, and it's a one-shot deal - i.e. there's just the
one query run and the response is sent back to the browser and that's
the end of the session and the temp table. So I'm thinking it's
probably not worth it.

As an aside this change has messed up all my unit tests - they send
multiple requests, but they're all in the same session. So only the
first succeeds and the next one fails because the temp table already
exists. I haven't figured out how to get it run each request in its
own session. I guess I'm going to have to drop the temp table after I
join with it before I sent the response back.



If...
* it's a MEMORY temp table
* it's always the same table design

Then, you can use DELETE to clear the content (it's faster than DROP or 

Help improving query performance

[Larry Martell]

I have 2 queries. One takes 4 hours to run and returns 21 rows, and
the other, which has 1 additional where clause, takes 3 minutes and
returns 20 rows. The main table being selected from is largish
(37,247,884 rows with 282 columns). Caching is off for my testing, so
it's not related to that. To short circuit anyone asking, these
queries are generated by python code, which is why there's an IN
clause with 1 value, as oppose to an =.

Here are the queries and their explains. The significant difference is
that the faster query has Using
intersect(data_cst_bbccbce0,data_cst_fba12377) in the query plan -
those 2 indexes are on the 2 columns in the where clause, so that's
why the second one is faster. But I am wondering what I can do to make
the first one faster.


4 hour query:

SELECT MIN(data_tool.name) as tool,
   MIN(data_cst.date_time) start,
   MAX(data_cst.date_time) end,
   MIN(data_target.name) as target,
   MIN(data_lot.name) as lot,
   MIN(data_wafer.name) as wafer,
   MIN(measname) as measname,
   MIN(data_recipe.name) as recipe
FROM data_cst
INNER JOIN data_tool ON data_tool.id = data_cst.tool_id
INNER JOIN data_target ON data_target.id = data_cst.target_name_id
INNER JOIN data_lot ON data_lot.id = data_cst.lot_id
INNER JOIN data_wafer ON data_wafer.id = data_cst.wafer_id
INNER JOIN data_measparams ON data_measparams.id = data_cst.meas_params_name_id
INNER JOIN data_recipe ON data_recipe.id = data_cst.recipe_id
WHERE data_target.id IN (172) AND
  data_cst.date_time BETWEEN '2015-01-26 00:00:00' AND '2015-01-26 23:59:59'
GROUP BY wafer_id, data_cst.lot_id, target_name_id

Explain:

++-+-++---+---+-+-+--+-+
| id | select_type | table   | type   | possible_keys

| key   | key_len | ref
 | rows | Extra   |
++-+-++---+---+-+-+--+-+
|  1 | SIMPLE  | data_target | const  | PRIMARY

| PRIMARY   | 4   | const
 |1 | Using temporary; Using filesort |
|  1 | SIMPLE  | data_measparams | index  | PRIMARY

| PRIMARY   | 4   | NULL
 |  767 | Using index |
|  1 | SIMPLE  | data_cst| ref|
data_cst_4262c4f4,data_cst_01213b5c,data_cst_bbccbce0,data_cst_b0229011,data_cst_65c0,data_cst_fba12377,data_cst_634020d0
| data_cst_634020d0 | 5   | motor_gf.data_measparams.id |   48 |
Using where |
|  1 | SIMPLE  | data_tool   | eq_ref | PRIMARY

| PRIMARY   | 4   |
motor_gf.data_cst.tool_id   |1 | NULL|
|  1 | SIMPLE  | data_recipe | eq_ref | PRIMARY

| PRIMARY   | 4   |
motor_gf.data_cst.recipe_id |1 | NULL|
|  1 | SIMPLE  | data_lot| eq_ref | PRIMARY

| PRIMARY   | 4   |
motor_gf.data_cst.lot_id|1 | NULL|
|  1 | SIMPLE  | data_wafer  | eq_ref | PRIMARY

| PRIMARY   | 4   |
motor_gf.data_cst.wafer_id  |1 | NULL|
++-+-++---+---+-+-+--+-+

Faster query:

SELECT MIN(data_tool.name) as tool,
   MIN(data_cst.date_time) start,
   MAX(data_cst.date_time) end,
   MIN(data_target.name) as target,
   MIN(data_lot.name) as lot,
   MIN(data_wafer.name) as wafer,
   MIN(measname) as measname,
   MIN(data_recipe.name) as recipe
FROM data_cst
INNER JOIN data_tool ON data_tool.id = data_cst.tool_id
INNER JOIN data_target ON data_target.id = data_cst.target_name_id
INNER JOIN data_lot ON data_lot.id = data_cst.lot_id
INNER JOIN data_wafer ON data_wafer.id = data_cst.wafer_id
INNER JOIN data_measparams ON data_measparams.id = data_cst.meas_params_name_id
INNER JOIN data_recipe ON data_recipe.id = data_cst.recipe_id
WHERE data_target.id IN (172) AND
  data_recipe.id IN (148) AND
  data_cst.date_time BETWEEN '2015-01-26 00:00:00' AND '2015-01-26 23:59:59'
GROUP BY wafer_id, data_cst.lot_id, 

Re: Help optimize query.

[shawn l.green]

Hello Mimko,

Sorry for the late reply. I had a bunch of work to take care of before 
vacation, then there was the vacation itself. :)


On 11/13/2014 2:34 PM, Mimiko wrote:

Hello. I have this table:

  show create table cc_agents_tier_status_log:
CREATE TABLE cc_agents_tier_status_log (
   id int(10) unsigned NOT NULL AUTO_INCREMENT,
   date_log timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
   cc_agent varchar(45) NOT NULL,
   cc_agent_tier_status_id tinyint(3) unsigned NOT NULL,
   cc_queue_id tinyint(3) unsigned NOT NULL,
   cc_agent_id int(10) unsigned NOT NULL,
   cc_agent_phone smallint(5) unsigned NOT NULL,
   cc_agent_domain varchar(45) NOT NULL DEFAULT 'pbx01.apa-canal.md',
   PRIMARY KEY (id),
   KEY IDX_cc_agents_tier_status_log_2 (cc_agent) USING HASH,
   KEY IDX_cc_agents_tier_status_log_3 (date_log),
   KEY FK_cc_agents_tier_status_log_2 (cc_agent_id),
   KEY FK_cc_agents_tier_status_log_3 (cc_queue_id),
   KEY FK_cc_agents_tier_status_log_1 (cc_agent_tier_status_id)
USING BTREE,
   KEY IDX_cc_agents_tier_status_log_7 (id,date_log),
   CONSTRAINT FK_cc_agents_tier_status_log_1 FOREIGN KEY
(cc_agent_tier_status_id) REFERENCES cc_agent_tier_status_chart
(id) ON UPDATE CASCADE,
   CONSTRAINT FK_cc_agents_tier_status_log_2 FOREIGN KEY
(cc_agent_id) REFERENCES apacanal.employee (id) ON UPDATE CASCADE,
   CONSTRAINT FK_cc_agents_tier_status_log_3 FOREIGN KEY
(cc_queue_id) REFERENCES cc_queues (id) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=23799 DEFAULT CHARSET=ascii

  show index from cc_agents_tier_status_log:
TableNon_uniqueKey_nameSeq_in_indexColumn_name
Collation CardinalitySub_partPackedNullIndex_type
CommentIndex_comment
cc_agents_tier_status_log0PRIMARY1idA
23999(null)BTREE(null) (null)
cc_agents_tier_status_log1IDX_cc_agents_tier_status_log_21
cc_agentA260(null)BTREE(null)(null)
cc_agents_tier_status_log1IDX_cc_agents_tier_status_log_31
date_logA23999(null)BTREE(null)(null)
cc_agents_tier_status_log1FK_cc_agents_tier_status_log_21
cc_agent_idA2(null)BTREE(null)(null)
cc_agents_tier_status_log1FK_cc_agents_tier_status_log_31
cc_queue_idA14(null)BTREE(null)(null)
cc_agents_tier_status_log1FK_cc_agents_tier_status_log_11
cc_agent_tier_status_idA2(null)BTREE
(null)(null)
cc_agents_tier_status_log1 IDX_cc_agents_tier_status_log_71
idA23999(null)BTREE(null)(null)
cc_agents_tier_status_log1IDX_cc_agents_tier_status_log_72
date_logA23999(null)BTREE(null)(null)

And the query is:
 set @enddate:=now();
 set @startdate:='2014-11-01';
 set @que_id:=-1;
select s.theHour as theHour,avg(s.nrAgents) as nrAgents from
(select date(a.theDateHour) as theDate,extract(hour from a.theDateHour)
as theHour,count(c.cc_agent_tier_status_id) as nrAgents
from (

select dh.theDateHour as theDateHour, max(c.date_log) as maxdatelog,c.*
FROM
( select concat(d.thedate,' ',h.theHour,':0:0') as theDateHour
from
( select DATE(DATE_ADD(date(@startdate), INTERVAL @i:=@i+1 DAY) ) as
theDate from (select @i:=-1) as t1
inner join cc_member_queue_end_log b on 1=1 and
b.id=datediff(@enddate,@startdate)+1 ) as d
left outer join
(SELECT 0 AS theHour UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION
ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL
SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17
UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL
SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23) as h
on 1=1 ) AS dh
left outer join
cc_agents_tier_status_log as c
on c.date_log=dh.theDateHour where (if(@queue_id0,1,0) or
if(@queue_id=c.cc_queue_id,1,0))
group by dh.theDateHour,c.cc_queue_id,c.cc_agent_id,c.cc_agent_phone


) as a
left outer join cc_agents_tier_status_log as c
on c.date_log=a.maxdatelog and c.cc_queue_id=a.cc_queue_id and
c.cc_agent_id=a.cc_agent_id and c.cc_agent_phone=a.cc_agent_phone and
c.cc_agent_tier_status_id=2
group by a.theDateHour
order by date(a.theDateHour),extract(hour from a.theDateHour))
as s
group by s.theHour
order by s.theHour;


This query takes 20 seconds to populate.

Table cc_agents_tier_status_log contains log entries of agent_id
login/logout per queue per phone. status_id can have value 1 (logged
out) and 2 (login) at date_log datetime.

The resulting table must contain average number of agents logged in at
every hour per startdate to enddate.

Hope for some hints. Thank you.


The first problem is that you are generating a lot of extra rows before 
you actually need them. The only place where you should be faking the 

Re: Help optimize query.

[Mimiko]

On 15.11.2014 01:06, Peter Brawley wrote:

Let's see the results of Explain Extended this query,  result of Show
Create Table cc_member_queue_end_log.


cc_member_queue_end_log is not of interest, it is used just as a series 
of numbers. It may be any table with ids.


I've changed a bit the query which seemed to reduce the select time, but 
not for a lot.


set @enddate:=now();
set @startdate:='2014-11-01';
set @que_id:=-1;
explain extended select s.theHour as theHour,avg(s.nrAgents) as 
nrAgents from
- (select date(FROM_UNIXTIME(a.theDateHour)) as 
theDate,extract(hour from FROM_UNIXTIME(a.theDateHour)) as 
theHour,count(c.cc_agent_tier_status_id) as nrAgents

- from (
-
- select dh.theDateHour as theDateHour, max(c.date_log) as 
maxdatelog,c.*

- FROM
- ( select UNIX_TIMESTAMP(concat(d.thedate,' ',h.theHour,':0:0')) 
as theDateHour

- from
- ( select DATE(DATE_ADD(date('2014-11-01'), INTERVAL @i:=@i+1 
DAY) ) as theDate from (select @i:=-1) as t1
- inner join cc_agents_tier_status_log b on 1=1 and 
b.id=datediff(now(),'2014-11-01')+1 ) as d

- straight_join
- (SELECT 0 AS theHour UNION ALL SELECT 1 UNION ALL SELECT 2 UNION 
ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 
UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL 
SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 
UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL 
SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 
UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23) as h

- on 1=1 ) AS dh
- straight_join
- cc_agents_tier_status_log as c
- on UNIX_TIMESTAMP(c.date_log)=dh.theDateHour where 
(if(-10,1,0) or if(-1=c.cc_queue_id,1,0))

- group by dh.theDateHour,c.cc_queue_id,c.cc_agent_id,c.cc_agent_phone
-
-
- ) as a
- straight_join cc_agents_tier_status_log as c
- on c.date_log=a.maxdatelog and c.cc_queue_id=a.cc_queue_id and 
c.cc_agent_id=a.cc_agent_id and c.cc_agent_phone=a.cc_agent_phone and 
c.cc_agent_tier_status_id=2

- group by a.theDateHour
- order by date(FROM_UNIXTIME(a.theDateHour)),extract(hour from 
FROM_UNIXTIME(a.theDateHour)))

- as s
- group by s.theHour
- order by s.theHour\G
*** 1. row ***
   id: 1
  select_type: PRIMARY
table: derived2
 type: ALL
possible_keys: NULL
  key: NULL
  key_len: NULL
  ref: NULL
 rows: 360
 filtered: 100.00
Extra: Using temporary; Using filesort
*** 2. row ***
   id: 2
  select_type: DERIVED
table: derived3
 type: ALL
possible_keys: NULL
  key: NULL
  key_len: NULL
  ref: NULL
 rows: 43560
 filtered: 100.00
Extra: Using temporary; Using filesort
*** 3. row ***
   id: 2
  select_type: DERIVED
table: c
 type: ref
possible_keys: 
IDX_cc_agents_tier_status_log_3,FK_cc_agents_tier_status_log_2,FK_cc_agents_tier_status_log_3,FK_cc_agents_tier_status_log_1

  key: IDX_cc_agents_tier_status_log_3
  key_len: 4
  ref: a.maxdatelog
 rows: 1
 filtered: 100.00
Extra: Using where
*** 4. row ***
   id: 3
  select_type: DERIVED
table: derived4
 type: ALL
possible_keys: NULL
  key: NULL
  key_len: NULL
  ref: NULL
 rows: 360
 filtered: 100.00
Extra: Using temporary; Using filesort
*** 5. row ***
   id: 3
  select_type: DERIVED
table: c
 type: ALL
possible_keys: NULL
  key: NULL
  key_len: NULL
  ref: NULL
 rows: 24207
 filtered: 100.00
Extra: Using where; Using join buffer
*** 6. row ***
   id: 4
  select_type: DERIVED
table: derived5
 type: ALL
possible_keys: NULL
  key: NULL
  key_len: NULL
  ref: NULL
 rows: 15
 filtered: 100.00
Extra:
*** 7. row ***
   id: 4
  select_type: DERIVED
table: derived7
 type: ALL
possible_keys: NULL
  key: NULL
  key_len: NULL
  ref: NULL
 rows: 24
 filtered: 100.00
Extra: Using join buffer
*** 8. row ***
   id: 7
  select_type: DERIVED
table: NULL
 type: NULL
possible_keys: NULL
  key: NULL
  key_len: NULL
  ref: NULL
 rows: NULL
 filtered: NULL
Extra: No tables used
*** 9. row ***
   id: 8
  select_type: UNION
table: NULL
 

Re: Help optimize query.

[Peter Brawley]

Let's see the results of Explain Extended this query,  result of Show 
Create Table cc_member_queue_end_log.


PB

-

On 2014-11-13 1:34 PM, Mimiko wrote:

Hello. I have this table:

 show create table cc_agents_tier_status_log:
CREATE TABLE cc_agents_tier_status_log (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  date_log timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  cc_agent varchar(45) NOT NULL,
  cc_agent_tier_status_id tinyint(3) unsigned NOT NULL,
  cc_queue_id tinyint(3) unsigned NOT NULL,
  cc_agent_id int(10) unsigned NOT NULL,
  cc_agent_phone smallint(5) unsigned NOT NULL,
  cc_agent_domain varchar(45) NOT NULL DEFAULT 'pbx01.apa-canal.md',
  PRIMARY KEY (id),
  KEY IDX_cc_agents_tier_status_log_2 (cc_agent) USING HASH,
  KEY IDX_cc_agents_tier_status_log_3 (date_log),
  KEY FK_cc_agents_tier_status_log_2 (cc_agent_id),
  KEY FK_cc_agents_tier_status_log_3 (cc_queue_id),
  KEY FK_cc_agents_tier_status_log_1 (cc_agent_tier_status_id) 
USING BTREE,

  KEY IDX_cc_agents_tier_status_log_7 (id,date_log),
  CONSTRAINT FK_cc_agents_tier_status_log_1 FOREIGN KEY 
(cc_agent_tier_status_id) REFERENCES cc_agent_tier_status_chart 
(id) ON UPDATE CASCADE,
  CONSTRAINT FK_cc_agents_tier_status_log_2 FOREIGN KEY 
(cc_agent_id) REFERENCES apacanal.employee (id) ON UPDATE 
CASCADE,
  CONSTRAINT FK_cc_agents_tier_status_log_3 FOREIGN KEY 
(cc_queue_id) REFERENCES cc_queues (id) ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=23799 DEFAULT CHARSET=ascii

 show index from cc_agents_tier_status_log:
TableNon_uniqueKey_nameSeq_in_indexColumn_name 
Collation CardinalitySub_partPackedNull Index_type
CommentIndex_comment
cc_agents_tier_status_log0PRIMARY1idA 
23999(null)BTREE(null) (null)
cc_agents_tier_status_log1 IDX_cc_agents_tier_status_log_21 
cc_agentA 260(null)BTREE(null)(null)
cc_agents_tier_status_log1 IDX_cc_agents_tier_status_log_31 
date_logA 23999(null)BTREE(null)(null)
cc_agents_tier_status_log1 FK_cc_agents_tier_status_log_21 
cc_agent_idA 2(null)BTREE(null)(null)
cc_agents_tier_status_log1 FK_cc_agents_tier_status_log_31 
cc_queue_idA 14(null)BTREE(null)(null)
cc_agents_tier_status_log1 FK_cc_agents_tier_status_log_11 
cc_agent_tier_status_id A2(null)BTREE(null)
(null)
cc_agents_tier_status_log1 IDX_cc_agents_tier_status_log_7 1
idA23999(null)BTREE(null) (null)
cc_agents_tier_status_log1 IDX_cc_agents_tier_status_log_72 
date_logA 23999(null)BTREE(null)(null)


And the query is:
set @enddate:=now();
set @startdate:='2014-11-01';
set @que_id:=-1;
select s.theHour as theHour,avg(s.nrAgents) as nrAgents from
(select date(a.theDateHour) as theDate,extract(hour from 
a.theDateHour) as theHour,count(c.cc_agent_tier_status_id) as nrAgents

from (

select dh.theDateHour as theDateHour, max(c.date_log) as maxdatelog,c.*
FROM
( select concat(d.thedate,' ',h.theHour,':0:0') as theDateHour
from
( select DATE(DATE_ADD(date(@startdate), INTERVAL @i:=@i+1 DAY) ) as 
theDate from (select @i:=-1) as t1
inner join cc_member_queue_end_log b on 1=1 and 
b.id=datediff(@enddate,@startdate)+1 ) as d

left outer join
(SELECT 0 AS theHour UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL 
SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 
UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL 
SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 
UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL 
SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 
UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23) as h

on 1=1 ) AS dh
left outer join
cc_agents_tier_status_log as c
on c.date_log=dh.theDateHour where (if(@queue_id0,1,0) or 
if(@queue_id=c.cc_queue_id,1,0))

group by dh.theDateHour,c.cc_queue_id,c.cc_agent_id,c.cc_agent_phone


) as a
left outer join cc_agents_tier_status_log as c
on c.date_log=a.maxdatelog and c.cc_queue_id=a.cc_queue_id and 
c.cc_agent_id=a.cc_agent_id and c.cc_agent_phone=a.cc_agent_phone and 
c.cc_agent_tier_status_id=2

group by a.theDateHour
order by date(a.theDateHour),extract(hour from a.theDateHour))
as s
group by s.theHour
order by s.theHour;


This query takes 20 seconds to populate.

Table cc_agents_tier_status_log contains log entries of agent_id 
login/logout per queue per phone. status_id can have value 1 (logged 
out) and 2 (login) at date_log datetime.


The resulting table must contain average number of agents logged in at 
every hour per startdate to enddate.


Hope for some hints. Thank you.



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

Help optimize query.

[Mimiko]

Hello. I have this table:

 show create table cc_agents_tier_status_log:
CREATE TABLE cc_agents_tier_status_log (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  date_log timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  cc_agent varchar(45) NOT NULL,
  cc_agent_tier_status_id tinyint(3) unsigned NOT NULL,
  cc_queue_id tinyint(3) unsigned NOT NULL,
  cc_agent_id int(10) unsigned NOT NULL,
  cc_agent_phone smallint(5) unsigned NOT NULL,
  cc_agent_domain varchar(45) NOT NULL DEFAULT 'pbx01.apa-canal.md',
  PRIMARY KEY (id),
  KEY IDX_cc_agents_tier_status_log_2 (cc_agent) USING HASH,
  KEY IDX_cc_agents_tier_status_log_3 (date_log),
  KEY FK_cc_agents_tier_status_log_2 (cc_agent_id),
  KEY FK_cc_agents_tier_status_log_3 (cc_queue_id),
  KEY FK_cc_agents_tier_status_log_1 (cc_agent_tier_status_id) 
USING BTREE,

  KEY IDX_cc_agents_tier_status_log_7 (id,date_log),
  CONSTRAINT FK_cc_agents_tier_status_log_1 FOREIGN KEY 
(cc_agent_tier_status_id) REFERENCES cc_agent_tier_status_chart 
(id) ON UPDATE CASCADE,
  CONSTRAINT FK_cc_agents_tier_status_log_2 FOREIGN KEY 
(cc_agent_id) REFERENCES apacanal.employee (id) ON UPDATE CASCADE,
  CONSTRAINT FK_cc_agents_tier_status_log_3 FOREIGN KEY 
(cc_queue_id) REFERENCES cc_queues (id) ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=23799 DEFAULT CHARSET=ascii

 show index from cc_agents_tier_status_log:
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation 
Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
cc_agents_tier_status_log	0	PRIMARY	1	id	A	23999			(null)	BTREE	(null) 
(null)
cc_agents_tier_status_log	1	IDX_cc_agents_tier_status_log_2	1 
cc_agent	A	260			(null)	BTREE	(null)	(null)
cc_agents_tier_status_log	1	IDX_cc_agents_tier_status_log_3	1 
date_log	A	23999			(null)	BTREE	(null)	(null)
cc_agents_tier_status_log	1	FK_cc_agents_tier_status_log_2	1 
cc_agent_id	A	2			(null)	BTREE	(null)	(null)
cc_agents_tier_status_log	1	FK_cc_agents_tier_status_log_3	1 
cc_queue_id	A	14			(null)	BTREE	(null)	(null)
cc_agents_tier_status_log	1	FK_cc_agents_tier_status_log_1	1 
cc_agent_tier_status_id	A	2			(null)	BTREE	(null)	(null)
cc_agents_tier_status_log	1 
IDX_cc_agents_tier_status_log_7	1	id	A	23999			(null)	BTREE	(null)	(null)
cc_agents_tier_status_log	1	IDX_cc_agents_tier_status_log_7	2 
date_log	A	23999			(null)	BTREE	(null)	(null)


And the query is:
set @enddate:=now();
set @startdate:='2014-11-01';
set @que_id:=-1;
select s.theHour as theHour,avg(s.nrAgents) as nrAgents from
(select date(a.theDateHour) as theDate,extract(hour from a.theDateHour) 
as theHour,count(c.cc_agent_tier_status_id) as nrAgents

from (

select dh.theDateHour as theDateHour, max(c.date_log) as maxdatelog,c.*
FROM
( select concat(d.thedate,' ',h.theHour,':0:0') as theDateHour
from
( select DATE(DATE_ADD(date(@startdate), INTERVAL @i:=@i+1 DAY) ) as 
theDate from (select @i:=-1) as t1
inner join cc_member_queue_end_log b on 1=1 and 
b.id=datediff(@enddate,@startdate)+1 ) as d

left outer join
(SELECT 0 AS theHour UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL 
SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION 
ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 
UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL 
SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 
UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL 
SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23) as h

on 1=1 ) AS dh
left outer join
cc_agents_tier_status_log as c
on c.date_log=dh.theDateHour where (if(@queue_id0,1,0) or 
if(@queue_id=c.cc_queue_id,1,0))

group by dh.theDateHour,c.cc_queue_id,c.cc_agent_id,c.cc_agent_phone


) as a
left outer join cc_agents_tier_status_log as c
on c.date_log=a.maxdatelog and c.cc_queue_id=a.cc_queue_id and 
c.cc_agent_id=a.cc_agent_id and c.cc_agent_phone=a.cc_agent_phone and 
c.cc_agent_tier_status_id=2

group by a.theDateHour
order by date(a.theDateHour),extract(hour from a.theDateHour))
as s
group by s.theHour
order by s.theHour;


This query takes 20 seconds to populate.

Table cc_agents_tier_status_log contains log entries of agent_id 
login/logout per queue per phone. status_id can have value 1 (logged 
out) and 2 (login) at date_log datetime.


The resulting table must contain average number of agents logged in at 
every hour per startdate to enddate.


Hope for some hints. Thank you.
--
Mimiko desu.

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

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

Need help with query

[LAMP]

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

[Joerg Bruehe]

Hi Paul!


Paul Halliday wrote:
 I have a query (thanks to this list) that uses a join to add country
 information to an IP. It looks like this:
 
 SELECT COUNT(signature) AS count, INET_NTOA(src_ip), map1.cc as
 src_cc, INET_NTOA(dst_ip), map2.cc as dst_cc
 FROM event LEFT JOIN mappings AS map1 ON event.src_ip = map1.ip LEFT
 JOIN mappings AS map2 ON event.dst_ip = map2.ip
 WHERE timestamp BETWEEN '2011-01-29 00:00:00' AND '2011-01-30 00:00:00'
 GROUP BY src_ip, src_cc, dst_ip, dst_cc
 ORDER BY src_cc, dst_cc ASC;
 
 This would return something like this:
 
  +---+---++---++
 | count | INET_NTOA(src_ip) | src_cc | INET_NTOA(dst_ip) | dst_cc |
 +---+---++---++
 | 8 | 10.0.0.8   | NULL   | 61.55.142.129 | CN |
 | 1 | 210.52.216.92 | CN | 10.0.0.2 | NULL   |
 | 1 | 121.33.205.235| CN | 172.16.0.6| NULL   |
 |   239 | 210.52.216.92 | CN | 10.0.0.2| NULL   |
 | 2 | 121.33.205.235| CN | 172.16.0.15   | NULL   |
 | 4 | 121.33.205.235| CN | 10.0.0.1| NULL   |
 |39 | 210.52.216.92 | CN | 172.16.0.15   | NULL   |
 | 1 | 121.33.205.235| CN | 172.16.0.14   | NULL   |
 +---+---++---++
 
 All I am interested in is the event count for each country, in this case:
 
 295 CN
 ... Other countries..

As a first step, remove the columns src_ip and dst_ip from your
query, both from the select list and from the group by (but not from
the join condition):

  SELECT COUNT(signature) AS count, map1.cc as src_cc, map2.cc as dst_cc
  FROM event LEFT JOIN mappings AS map1 ON event.src_ip = map1.ip LEFT
  JOIN mappings AS map2 ON event.dst_ip = map2.ip
  WHERE timestamp BETWEEN '2011-01-29 00:00:00' AND '2011-01-30 00:00:00'
  GROUP BY src_cc, dst_cc
  ORDER BY src_cc, dst_cc ASC;

The result should be:

  +---+++
  | count | src_cc | dst_cc |
  +---+++
  | 8 | NULL   | CN |
  |   287 | CN | NULL   |
  +---+++

Now, you are left with two problems:

1) Your query still groups by the country codes of both source and
destination (which doesn't become obvious with your sample data, as one
of these is always shown as NULL).
For example: traffic just between three countries (each combination
occurring) would give nine rows, with each country occurring three times
as source and three times as destination.
If you want the total for the source country, you must stop grouping by
destination (and vice versa).

2) If you really want the total of source and destination (the 295 in
your example, not the 287 and 8 I expect from my version), it really
might be easiest to do this in the application; I have no idea how to do
it nicely in SQL.


HTH,
Jörg

-- 
Joerg Bruehe,  MySQL Build Team,  joerg.bru...@oracle.com
ORACLE Deutschland B.V.  Co. KG,   Komturstrasse 18a,   D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven
Amtsgericht Muenchen: HRA 95603


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

[Simcha Younger]

On Tue, 1 Feb 2011 14:46:39 -0400
Paul Halliday paul.halli...@gmail.com wrote:

 I have a query (thanks to this list) that uses a join to add country
 information to an IP. It looks like this:
 
 SELECT COUNT(signature) AS count, INET_NTOA(src_ip), map1.cc as
 src_cc, INET_NTOA(dst_ip), map2.cc as dst_cc
 FROM event LEFT JOIN mappings AS map1 ON event.src_ip = map1.ip LEFT
 JOIN mappings AS map2 ON event.dst_ip = map2.ip
 WHERE timestamp BETWEEN '2011-01-29 00:00:00' AND '2011-01-30 00:00:00'
 GROUP BY src_ip, src_cc, dst_ip, dst_cc
 ORDER BY src_cc, dst_cc ASC;
.
 All I am interested in is the event count for each country, in this case:
 
 295 CN
 ... Other countries..

try:

SELECT COUNT(*) AS count, IF(map1.cc, map1.cc, map2.cc) AS country 

FROM event LEFT JOIN mappings AS map1 ON event.src_ip = map1.ip 
LEFT JOIN mappings AS map2 ON event.dst_ip = map2.ip
WHERE timestamp BETWEEN '2011-01-29 00:00:00' AND '2011-01-30 00:00:00'

GROUP BY country
ORDER BY country


note, I am assuming in this query that you have either a source country or a 
destination country, but not both.
If both values might be set, and you need to count each, this will not work.


 Thanks!
 -- 
 Paul Halliday
 http://www.pintumbler.org
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=sim...@syounger.com
 


-- 
Simcha Younger sim...@syounger.com

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

Help with query.

[Paul Halliday]

I have a query (thanks to this list) that uses a join to add country
information to an IP. It looks like this:

SELECT COUNT(signature) AS count, INET_NTOA(src_ip), map1.cc as
src_cc, INET_NTOA(dst_ip), map2.cc as dst_cc
FROM event LEFT JOIN mappings AS map1 ON event.src_ip = map1.ip LEFT
JOIN mappings AS map2 ON event.dst_ip = map2.ip
WHERE timestamp BETWEEN '2011-01-29 00:00:00' AND '2011-01-30 00:00:00'
GROUP BY src_ip, src_cc, dst_ip, dst_cc
ORDER BY src_cc, dst_cc ASC;

This would return something like this:

 +---+---++---++
| count | INET_NTOA(src_ip) | src_cc | INET_NTOA(dst_ip) | dst_cc |
+---+---++---++
| 8 | 10.0.0.8   | NULL   | 61.55.142.129 | CN |
| 1 | 210.52.216.92 | CN | 10.0.0.2 | NULL   |
| 1 | 121.33.205.235| CN | 172.16.0.6| NULL   |
|   239 | 210.52.216.92 | CN | 10.0.0.2| NULL   |
| 2 | 121.33.205.235| CN | 172.16.0.15   | NULL   |
| 4 | 121.33.205.235| CN | 10.0.0.1| NULL   |
|39 | 210.52.216.92 | CN | 172.16.0.15   | NULL   |
| 1 | 121.33.205.235| CN | 172.16.0.14   | NULL   |
+---+---++---++

All I am interested in is the event count for each country, in this case:

295 CN
... Other countries..

I can do this in code, more work of course, but I am just curious if I
can pull it off with a single query.

Thanks!
-- 
Paul Halliday
http://www.pintumbler.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 with query

[Simon Wilkinson]

Thank you, that did the trick.

Simon

On 11 January 2011 12:09, Steve Meyers steve-mysql-l...@spamwiz.com wrote:

 On 1/11/11 9:31 AM, Simon Wilkinson wrote:

 select users.id from users where users.id in (select newletters.user_id
 from
 newletters left join articles on newletters.id = articles.newsletter_id
 where articles.newsletter_id is null);


 I think this would do what you require:

 SELECT
  u.id AS user_id,
  COUNT(DISTINCT n.id) AS num_newsletters,
  COUNT(DISTINCT a.id) AS num_articles

 FROM
  users u
  JOIN newsletters n ON n.user_id=u.id
  LEFT JOIN articles a ON a.newsletter_id=n.id

 GROUP BY
  u.id

 HAVING
  num_newsletters  0
  AND num_articles = 0

help with query

[Simon Wilkinson]

Hi,

I have 3 tables that I am trying to search across, and could use some help
on how to structure the query.  I have a users table, a newsletter table,
and an articles table.  The newsletter table has a user_id column, and the
articles table has a newsletter_id column.  A user can have multiple
newsletters, and a newsletter can have multiple articles.  What I would like
to do is find the list of users that have only newletters with no content.

My current query is as follows:
select users.id from users where users.id in (select newletters.user_id from
newletters left join articles on newletters.id = articles.newsletter_id
where articles.newsletter_id is null);

But I believe this is finding users that have any empty newletters, and not
users that have only empty newletters.  How could I change this to return
only the users that have only empty newsletters?

Thanks,

Simon

Re: help with query

[Steve Meyers]

On 1/11/11 9:31 AM, Simon Wilkinson wrote:

select users.id from users where users.id in (select newletters.user_id from
newletters left join articles on newletters.id = articles.newsletter_id
where articles.newsletter_id is null);


I think this would do what you require:

SELECT
  u.id AS user_id,
  COUNT(DISTINCT n.id) AS num_newsletters,
  COUNT(DISTINCT a.id) AS num_articles

FROM
  users u
  JOIN newsletters n ON n.user_id=u.id
  LEFT JOIN articles a ON a.newsletter_id=n.id

GROUP BY
  u.id

HAVING
  num_newsletters  0
  AND num_articles = 0

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

Need help with query optimization

[Jesse]

I have the following query:

SELECT 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=TAP.CurrentMemberID
  JOIN Chapters C On C.ID=M.ChapterID
  JOIN Schools S On 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.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=arch...@jab.org

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: help refactoring query

[Shawn Green]

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


I have 3 tables:

users
  id,
  (etc. the usual)

disciplines
  id,
  name (ie. film, photography, writing, etc.)

disciplines_users
  discipline_id,
  user_id

Each user may have one or more discipline.

The view I'm looking for shows the total number of users who have a 
particular discipline. NOTE: the sum of the totals is greater than the 
total number of users, which is by design.


SELECT name, COUNT(discipline.u_id) AS total
FROM (
  SELECT du.discipline_id, du.user_id as u_id, d.name
  FROM disciplines_users AS du
  LEFT JOIN disciplines AS d
  ON d.id = du.discipline_id
) AS discipline
GROUP BY discipline.name ORDER BY discipline.name;


+-+---+
| name| total |
+-+---+
| Dance   |   176 |
| Film and Television |   376 |
etc.


I've a feeling that this could be done without that sub-query and using 
another join. If not, I might make the sub-query its own view and see 
what the performance is like. I'd appreciate any suggestions, especially 
any pointers on refactoring sub-queries into joins, in general.






Why not use just your subquery as your VIEW?

SELECT d.id, d.name, du.user_id as u_id,
FROM disciplines AS d
LEFT JOIN disciplines_users AS du
  ON d.id = du.discipline_id
GROUP BY d.id, d.name

The reason I inverted the FROM and LEFT JOIN was so that if you had a 
discipline with 0 users, you can now see a zero. In your original 
orientation, a relationship had to exist or its discipline wouldn't have 
been counted.


--
Shawn Green, MySQL Senior Support Engineer
Sun Microsystems, 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

help refactoring query

[b]

I'm having some difficulty getting my head around a particular query. 
I'd like to make this a view once I get something working. However, all 
I've been able to come up with uses a sub-query. So, no view on the horizon.


I have 3 tables:

users
  id,
  (etc. the usual)

disciplines
  id,
  name (ie. film, photography, writing, etc.)

disciplines_users
  discipline_id,
  user_id

Each user may have one or more discipline.

The view I'm looking for shows the total number of users who have a 
particular discipline. NOTE: the sum of the totals is greater than the 
total number of users, which is by design.


SELECT name, COUNT(discipline.u_id) AS total
FROM (
  SELECT du.discipline_id, du.user_id as u_id, d.name
  FROM disciplines_users AS du
  LEFT JOIN disciplines AS d
  ON d.id = du.discipline_id
) AS discipline
GROUP BY discipline.name ORDER BY discipline.name;


+-+---+
| name| total |
+-+---+
| Dance   |   176 |
| Film and Television |   376 |
etc.


I've a feeling that this could be done without that sub-query and using 
another join. If not, I might make the sub-query its own view and see 
what the performance is like. I'd appreciate any suggestions, especially 
any pointers on refactoring sub-queries into joins, in general.




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

need help with query...

[Lamp Lists]

I hate when somebody put in Subject line something like I just did but after 15 
minutes to try to be specific just with one short sentence - I gave up. So, you 
can hate me - I understand (though, help with my problem too) :-)


I have let say 3 tables people, organization, addresses. and they are linked to 
each other with column person_id.
to select person by specified person_id it's not big deal, really simple. 
though, I wonder how can I select records from these three tables but somehow 
marked which table it belongs?

e.g.

select p.first_name, p.last_name, p.status, p.date_registered, o.org_id, 
o.org_name, o.org_department, a.addres1, a.address2, a.city, a.state, a.zip
from people p, organization o, addresses a
where p.person_id=123 and o.person_id=p.person_id and a.person_id=p.person_id

I need somehow, together with result data, info which table data belogs?

e.g.
not exactly this way but something like this:
$data = array(
'people' = array('lamp', 'lists', 1, '2008-12-12'), 
'organization' = array(56, 'Lamp List', 'web'), 
'addresses' = array('123 Main St.', 'Toon Town', 'NY', '12345')
}
  
thanks for any help.

-afan



  

Re: need help with query...

[Jason Pruim]

On Dec 17, 2008, at 2:56 PM, Lamp Lists wrote:

I hate when somebody put in Subject line something like I just did  
but after 15 minutes to try to be specific just with one short  
sentence - I gave up. So, you can hate me - I understand (though,  
help with my problem too) :-)



I have let say 3 tables people, organization, addresses. and they  
are linked to each other with column person_id.
to select person by specified person_id it's not big deal, really  
simple. though, I wonder how can I select records from these three  
tables but somehow marked which table it belongs?


e.g.

select p.first_name, p.last_name, p.status, p.date_registered,  
o.org_id, o.org_name, o.org_department, a.addres1, a.address2,  
a.city, a.state, a.zip

from people p, organization o, addresses a
where p.person_id=123 and o.person_id=p.person_id and  
a.person_id=p.person_id


I need somehow, together with result data, info which table data  
belogs?


e.g.
not exactly this way but something like this:
$data = array(
'people' = array('lamp', 'lists', 1, '2008-12-12'),
'organization' = array(56, 'Lamp List', 'web'),
'addresses' = array('123 Main St.', 'Toon Town', 'NY', '12345')
}

thanks for any help.

-afan



It may not be the best option, but when I've done that type of stuff  
in the past, I add another field to the database and call it like  
List and put People in the people database. and then you could  
just query the field List and display it how ever you needed.



--
Jason Pruim
japr...@raoset.com
616.399.2355

Re: need help with query...

[Andy Shellam]

Hi Afan

Why not prefix your field names with the table name?

select
 p.first_name AS person_first_name,
 p.last_name AS person_last_name,
 p.status AS person_status,
 p.date_registered AS person_date_registered,
 o.org_id AS organization_org_id,
 o.org_name AS organization_org_name,
 o.org_department AS organization_org_department,
 a.addres1 AS addresses_address1,
 a.address2 AS addresses_address2,
 a.city AS addresses_city,
 a.state AS addresses_state,
 a.zip AS addresses_zip
from people p, organization o, addresses a
where p.person_id=123 and o.person_id=p.person_id and 
a.person_id=p.person_id


Then in PHP (which I guess you're using from your example) do something 
like:


// Get every record from the database ($result is your MySQL result from 
mysql_query)

while ($row = mysql_fetch_assoc($result))
{
   $result = Array();

   // Run through each field in the row
   foreach ($row as $field = $value)
   {
  // Split the field into 2 segments split by _
  $fieldSplit = explode('_', $field, 1);

  // $fieldSplit will be, for example, Array(0 = 'person', 1 = 
'first_name')


  $result[$fieldSplit[0]][$fieldSplit[1]] = $value;

  // Now you should be able to access the person's first name using 
$result['person']['first_name']

   }
}

This code may not be perfect as I've just typed it out from memory so it 
may take a bit of tweaking.


Thanks,
Andy

Jason Pruim wrote:


On Dec 17, 2008, at 2:56 PM, Lamp Lists wrote:

I hate when somebody put in Subject line something like I just did 
but after 15 minutes to try to be specific just with one short 
sentence - I gave up. So, you can hate me - I understand (though, 
help with my problem too) :-)



I have let say 3 tables people, organization, addresses. and they are 
linked to each other with column person_id.
to select person by specified person_id it's not big deal, really 
simple. though, I wonder how can I select records from these three 
tables but somehow marked which table it belongs?


e.g.

select p.first_name, p.last_name, p.status, p.date_registered, 
o.org_id, o.org_name, o.org_department, a.addres1, a.address2, 
a.city, a.state, a.zip

from people p, organization o, addresses a
where p.person_id=123 and o.person_id=p.person_id and 
a.person_id=p.person_id


I need somehow, together with result data, info which table data belogs?

e.g.
not exactly this way but something like this:
$data = array(
'people' = array('lamp', 'lists', 1, '2008-12-12'),
'organization' = array(56, 'Lamp List', 'web'),
'addresses' = array('123 Main St.', 'Toon Town', 'NY', '12345')
}

thanks for any help.

-afan



It may not be the best option, but when I've done that type of stuff 
in the past, I add another field to the database and call it like 
List and put People in the people database. and then you could 
just query the field List and display it how ever you needed.



--
Jason Pruim
japr...@raoset.com
616.399.2355






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

RE: need help with query...

[Jerry Schwartz]

-Original Message-
From: Lamp Lists [mailto:lamp.li...@yahoo.com]
Sent: Wednesday, December 17, 2008 2:57 PM
To: mysql@lists.mysql.com
Subject: need help with query...

...snip...

I have let say 3 tables people, organization, addresses. and they are
linked to each other with column person_id.
to select person by specified person_id it's not big deal, really
simple. though, I wonder how can I select records from these three
tables but somehow marked which table it belongs?

[JS] Admittedly I'm not really good with this stuff, but I think this works:

SELECT 'table1' AS tablename, person_id FROM table1 WHERE person_id = 123
UNION
SELECT 'table2', person_id FROM table2 WHERE person_id = 123
UNION
SELECT 'table3', person_id FROM table2 WHERE person_id = 123;

That would give you all of the tables in which a particular person_id is
found.

I don't know if this suits your needs.


Regards,
 
Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
 
860.674.8796 / FAX: 860.674.8341
 
www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com





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

RE: need help with query...

[Jerry Schwartz]

-Original Message-
From: Andy Shellam [mailto:andy-li...@networkmail.eu]
Sent: Wednesday, December 17, 2008 3:29 PM
To: Lamp Lists
Cc: mysql@lists.mysql.com
Subject: Re: need help with query...

Hi Afan

Why not prefix your field names with the table name?

select
  p.first_name AS person_first_name,
  p.last_name AS person_last_name,
  p.status AS person_status,
  p.date_registered AS person_date_registered,
  o.org_id AS organization_org_id,
  o.org_name AS organization_org_name,
  o.org_department AS organization_org_department,
  a.addres1 AS addresses_address1,
  a.address2 AS addresses_address2,
  a.city AS addresses_city,
  a.state AS addresses_state,
  a.zip AS addresses_zip
from people p, organization o, addresses a
where p.person_id=123 and o.person_id=p.person_id and
a.person_id=p.person_id

[JS] That would only retrieve a person_id if it is all three tables. I'm not
sure that's what is wanted.




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

Re: need help with query...

[Lamp Lists]

From: Andy Shellam andy-li...@networkmail.eu
To: Lamp Lists lamp.li...@yahoo.com
Cc: mysql@lists.mysql.com
Sent: Wednesday, December 17, 2008 2:29:08 PM
Subject: Re: need help with query...

Hi Afan

Why not prefix your field names with the table name?

select
p.first_name AS person_first_name,
p.last_name AS person_last_name,
p.status AS person_status,
p.date_registered AS person_date_registered,
o.org_id AS organization_org_id,
o.org_name AS organization_org_name,
o.org_department AS organization_org_department,
a.addres1 AS addresses_address1,
a.address2 AS addresses_address2,
a.city AS addresses_city,
a.state AS addresses_state,
a.zip AS addresses_zip
from people p, organization o, addresses a
where p.person_id=123 and o.person_id=p.person_id and a.person_id=p.person_id

Then in PHP (which I guess you're using from your example) do something like:

// Get every record from the database ($result is your MySQL result from 
mysql_query)
while ($row = mysql_fetch_assoc($result))
{
   $result = Array();

   // Run through each field in the row
   foreach ($row as $field = $value)
   {
  // Split the field into 2 segments split by _
  $fieldSplit = explode('_', $field, 1);

  // $fieldSplit will be, for example, Array(0 = 'person', 1 = 
'first_name')

  $result[$fieldSplit[0]][$fieldSplit[1]] = $value;

  // Now you should be able to access the person's first name using 
$result['person']['first_name']
   }
}

This code may not be perfect as I've just typed it out from memory so it may 
take a bit of tweaking.

Thanks,
Andy




Hi Andy,
the reason I can't use this because fields (columns) in select statement 
(p.first_name, p.last_name,...) are actually dynamically created. In my project 
different client will select different fields to be shown. 99% will select 
first_name, and last_name, but some don't care about date_registered, some will 
need more org data... 

actually, it will be more this way:

SELECT {$selected_fields} FROM people p, organization o. addresses a
WHERE ...

where 
$selected_fields = p.first_name, p.last_name, o.org_name
or
$selected_fields = p.first_name, p.last_name, o.org_name, a.address, a.city, 
a.state, a.zip
or
$selected_fields = o.org_name, a.address, a.city, a.state, a.zip

I hope I'm more clear now?

Though, I can do something as you suggested while creating $selected_fields
:-)

Thanks




















Jason Pruim wrote:
 
 On Dec 17, 2008, at 2:56 PM, Lamp Lists wrote:
 
 I hate when somebody put in Subject line something like I just did but after 
 15 minutes to try to be specific just with one short sentence - I gave up. 
 So, you can hate me - I understand (though, help with my problem too) :-)
 
 
 I have let say 3 tables people, organization, addresses. and they are linked 
 to each other with column person_id.
 to select person by specified person_id it's not big deal, really simple. 
 though, I wonder how can I select records from these three tables but 
 somehow marked which table it belongs?
 
 e.g.
 
 select p.first_name, p.last_name, p.status, p.date_registered, o.org_id, 
 o.org_name, o.org_department, a.addres1, a.address2, a.city, a.state, a.zip
 from people p, organization o, addresses a
 where p.person_id=123 and o.person_id=p.person_id and a.person_id=p.person_id
 
 I need somehow, together with result data, info which table data belogs?
 
 e.g.
 not exactly this way but something like this:
 $data = array(
 'people' = array('lamp', 'lists', 1, '2008-12-12'),
 'organization' = array(56, 'Lamp List', 'web'),
 'addresses' = array('123 Main St.', 'Toon Town', 'NY', '12345')
 }
 
 thanks for any help.
 
 -afan
 
 
 It may not be the best option, but when I've done that type of stuff in the 
 past, I add another field to the database and call it like List and put 
 People in the people database. and then you could just query the field List 
 and display it how ever you needed.
 
 
 -- Jason Pruim
 japr...@raoset.com
 616.399.2355
 
 
 
 



  

Re: need help with query...

[Andy Shellam]

Jerry Schwartz wrote:

-Original Message-
From: Andy Shellam [mailto:andy-li...@networkmail.eu]
Sent: Wednesday, December 17, 2008 3:29 PM
To: Lamp Lists
Cc: mysql@lists.mysql.com
Subject: Re: need help with query...

Hi Afan

Why not prefix your field names with the table name?

select
 p.first_name AS person_first_name,
 p.last_name AS person_last_name,
 p.status AS person_status,
 p.date_registered AS person_date_registered,
 o.org_id AS organization_org_id,
 o.org_name AS organization_org_name,
 o.org_department AS organization_org_department,
 a.addres1 AS addresses_address1,
 a.address2 AS addresses_address2,
 a.city AS addresses_city,
 a.state AS addresses_state,
 a.zip AS addresses_zip


from people p, organization o, addresses a
  

where p.person_id=123 and o.person_id=p.person_id and
a.person_id=p.person_id



[JS] That would only retrieve a person_id if it is all three tables. I'm not
sure that's what is wanted.
  


That was the original query as specified by the original poster - I just 
added the AS xxx to each field he'd selected.



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

Re: need help with query...

[Andy Shellam]

Hi,



Hi Andy,
the reason I can't use this because fields (columns) in select 
statement (p.first_name, p.last_name,...) are actually dynamically 
created. In my project different client will select different fields 
to be shown. 99% will select first_name, and last_name, but some don't 
care about date_registered, some will need more org data...


actually, it will be more this way:

SELECT {$selected_fields} FROM people p, organization o. addresses a
WHERE ...

where
$selected_fields = p.first_name, p.last_name, o.org_name
or
$selected_fields = p.first_name, p.last_name, o.org_name, a.address, 
a.city, a.state, a.zip

or
$selected_fields = o.org_name, a.address, a.city, a.state, a.zip


So just tag AS table_field_name to each field when you're building 
your list of $selected_fields - e.g.


$selected_fields = p.first_name AS person_first_name, p.last_name AS 
person_last_name, o.org_name AS organization_org_name


You don't have to use the full table name either - for example in the 
following statement, you would then access the data using 
$result['p']['first_name'];


$selected_fields = p.first_name AS p_first_name, p.last_name AS 
p_last_name, o.org_name AS o_org_name


This approach is actually easier if you're creating the query 
dynamically, because you don't have to manually type a load of AS xxx 
statements after every field.


I've recently done something similar in one of my applications to wrap 
date/time fields in either FROM_UNIXTIME() or UNIX_TIMESTAMP() functions.


Andy

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

Re: need help with query...

[Lamp Lists]

From: Andy Shellam andy-li...@networkmail.eu
To: Lamp Lists lamp.li...@yahoo.com
Cc: mysql@lists.mysql.com
Sent: Wednesday, December 17, 2008 2:48:31 PM
Subject: Re: need help with query...

Hi,

 
 Hi Andy,
 the reason I can't use this because fields (columns) in select statement 
 (p.first_name, p.last_name,...) are actually dynamically created. In my 
 project different client will select different fields to be shown. 99% will 
 select first_name, and last_name, but some don't care about date_registered, 
 some will need more org data...
 
 actually, it will be more this way:
 
 SELECT {$selected_fields} FROM people p, organization o. addresses a
 WHERE ...
 
 where
 $selected_fields = p.first_name, p.last_name, o.org_name
 or
 $selected_fields = p.first_name, p.last_name, o.org_name, a.address, a.city, 
 a.state, a.zip
 or
 $selected_fields = o.org_name, a.address, a.city, a.state, a.zip

So just tag AS table_field_name to each field when you're building your list 
of $selected_fields - e.g.

$selected_fields = p.first_name AS person_first_name, p.last_name AS 
person_last_name, o.org_name AS organization_org_name

You don't have to use the full table name either - for example in the following 
statement, you would then access the data using $result['p']['first_name'];

$selected_fields = p.first_name AS p_first_name, p.last_name AS p_last_name, 
o.org_name AS o_org_name

This approach is actually easier if you're creating the query dynamically, 
because you don't have to manually type a load of AS xxx statements after 
every field.

I've recently done something similar in one of my applications to wrap 
date/time fields in either FROM_UNIXTIME() or UNIX_TIMESTAMP() functions.

Andy




Yup! That'll do it!

Thanks Andy
;-)



  

Help with query

[Néstor]

I have a char fiel where I am keeping dates formatted as year-month-day
(2006-10-09)
Now I am trying to find all the records between 2 strings (2 dates).   The
2 queries below
should return the same number of records by they do not.

My query is this:
 SELECT COUNT(*) FROM proj where proj_archive=0 AND (proj_adv_date =
'2008-01-01' AND proj_adv_date  '2008-12-16') order by proj_type,
proj_adv_date, proj_bid_date, proj_name ASC;
+-+---+
| proj_id | proj_adv_date |
+-+---+
| 181 | 2008-11-25|
| 217 | 2008-10-27|
| 136 | 2008-12-01|
| 219 | 2008-12-08|
| 225 | 2008-12-11|
+-+---+
5 rows in set (0.00 sec)

I get only 5 records returned but if I do this query:
SELECT COUNT(*) FROM proj where proj_archive=0 AND (proj_adv_date 
'2008-01-01') order by proj_type, proj_adv_date, proj_bid_date, proj_name
ASC;
+-+---+
| proj_id | proj_adv_date |
+-+---+
| 181 | 2008-11-25|
| 221 | 2008-12-23|
| 108 | 2009-01-00|
| 173 | 2009-03-00|
| 149 | 2009-10-00|
| 143 | 2009-7-00 |
| 179 | 2010-04-00|
| 217 | 2008-10-27|
| 136 | 2008-12-01|
| 219 | 2008-12-08|
| 225 | 2008-12-11|
| 187 | 2009-01-00|
| 199 | 2009-01-01|
| 177 | 2009-02-01|
|  69 | 2009-03-00|
|  70 | 2009-03-00|
|  71 | 2009-03-00|
| 142 | 2009-03-00|
| 122 | 2009-04-00|
| 124 | 2009-04-00|
| 207 | 2009-04-01|
|  72 | 2009-07-00|
|  73 | 2009-07-00|
|  82 | 2009-07-00|
| 209 | 2009-10-01|
| 211 | 2009-10-01|
| 213 | 2010-03-01|
+-+---+
27 rows in set (0.00 sec)

thanks,

Rotsen :-)

Re: Help with query

[John Daisley]

Are you sure those are the results to those queries?  ¢,m 

Your second query will return more because it includes values outside of
the date range specified in the first query.

In the second result set you have 2009 and 'invalid dates' that would
not be picked up by the first query.

Regards

John Daisley
Email: john.dais...@butterflysystems.co.uk
Mobile: 07816 815424

MySQL Certified Database Administrator (CMDBA)
MySQL Certified Developer (CMDEV)
MySQL Certified Associate (CMA)
Comptia A+ Certified Professional IT Technician















On Mon, 2008-12-15 at 09:12 -0800, Néstor wrote:

 I have a char fiel where I am keeping dates formatted as year-month-day
 (2006-10-09)
 Now I am trying to find all the records between 2 strings (2 dates).   The
 2 queries below
 should return the same number of records by they do not.
 
 My query is this:
  SELECT COUNT(*) FROM proj where proj_archive=0 AND (proj_adv_date =
 '2008-01-01' AND proj_adv_date  '2008-12-16') order by proj_type,
 proj_adv_date, proj_bid_date, proj_name ASC;
 +-+---+
 | proj_id | proj_adv_date |
 +-+---+
 | 181 | 2008-11-25|
 | 217 | 2008-10-27|
 | 136 | 2008-12-01|
 | 219 | 2008-12-08|
 | 225 | 2008-12-11|
 +-+---+
 5 rows in set (0.00 sec)
 
 I get only 5 records returned but if I do this query:
 SELECT COUNT(*) FROM proj where proj_archive=0 AND (proj_adv_date 
 '2008-01-01') order by proj_type, proj_adv_date, proj_bid_date, proj_name
 ASC;
 +-+---+
 | proj_id | proj_adv_date |
 +-+---+
 | 181 | 2008-11-25|
 | 221 | 2008-12-23|
 | 108 | 2009-01-00|
 | 173 | 2009-03-00|
 | 149 | 2009-10-00|
 | 143 | 2009-7-00 |
 | 179 | 2010-04-00|
 | 217 | 2008-10-27|
 | 136 | 2008-12-01|
 | 219 | 2008-12-08|
 | 225 | 2008-12-11|
 | 187 | 2009-01-00|
 | 199 | 2009-01-01|
 | 177 | 2009-02-01|
 |  69 | 2009-03-00|
 |  70 | 2009-03-00|
 |  71 | 2009-03-00|
 | 142 | 2009-03-00|
 | 122 | 2009-04-00|
 | 124 | 2009-04-00|
 | 207 | 2009-04-01|
 |  72 | 2009-07-00|
 |  73 | 2009-07-00|
 |  82 | 2009-07-00|
 | 209 | 2009-10-01|
 | 211 | 2009-10-01|
 | 213 | 2010-03-01|
 +-+---+
 27 rows in set (0.00 sec)
 
 thanks,
 
 Rotsen :-)
 
 
 __
 This email has been scanned by Netintelligence
 http://www.netintelligence.com/email

Re: Help with query

[Phil]

Am I totally missing something? Why do you believe the two queries should
return the same # of rows? First one has a qualification of proj_adv_date 
'2008-12-16' whilst the second one does not...

On Mon, Dec 15, 2008 at 12:12 PM, Néstor rot...@gmail.com wrote:

 I have a char fiel where I am keeping dates formatted as year-month-day
 (2006-10-09)
 Now I am trying to find all the records between 2 strings (2 dates).   The
 2 queries below
 should return the same number of records by they do not.

 My query is this:
  SELECT COUNT(*) FROM proj where proj_archive=0 AND (proj_adv_date =
 '2008-01-01' AND proj_adv_date  '2008-12-16') order by proj_type,
 proj_adv_date, proj_bid_date, proj_name ASC;
 +-+---+
 | proj_id | proj_adv_date |
 +-+---+
 | 181 | 2008-11-25|
 | 217 | 2008-10-27|
 | 136 | 2008-12-01|
 | 219 | 2008-12-08|
 | 225 | 2008-12-11|
 +-+---+
 5 rows in set (0.00 sec)

 I get only 5 records returned but if I do this query:
 SELECT COUNT(*) FROM proj where proj_archive=0 AND (proj_adv_date 
 '2008-01-01') order by proj_type, proj_adv_date, proj_bid_date, proj_name
 ASC;
 +-+---+
 | proj_id | proj_adv_date |
 +-+---+
 | 181 | 2008-11-25|
 | 221 | 2008-12-23|
 | 108 | 2009-01-00|
 | 173 | 2009-03-00|
 | 149 | 2009-10-00|
 | 143 | 2009-7-00 |
 | 179 | 2010-04-00|
 | 217 | 2008-10-27|
 | 136 | 2008-12-01|
 | 219 | 2008-12-08|
 | 225 | 2008-12-11|
 | 187 | 2009-01-00|
 | 199 | 2009-01-01|
 | 177 | 2009-02-01|
 |  69 | 2009-03-00|
 |  70 | 2009-03-00|
 |  71 | 2009-03-00|
 | 142 | 2009-03-00|
 | 122 | 2009-04-00|
 | 124 | 2009-04-00|
 | 207 | 2009-04-01|
 |  72 | 2009-07-00|
 |  73 | 2009-07-00|
 |  82 | 2009-07-00|
 | 209 | 2009-10-01|
 | 211 | 2009-10-01|
 | 213 | 2010-03-01|
 +-+---+
 27 rows in set (0.00 sec)

 thanks,

 Rotsen :-)




-- 
Distributed Computing stats
http://stats.free-dc.org

Re: Help with query

[Néstor]

Sorry!!!   I apoligized for being blind.   Yes, in my small mind I was
thinking that I do not have records biggeer than 2008 but I do and my mind
refused to see the records for 2009 and 2010.

Sorry again for being so blind to the obvious.

:-(

On Mon, Dec 15, 2008 at 9:50 AM, Phil freedc@gmail.com wrote:

 Am I totally missing something? Why do you believe the two queries should
 return the same # of rows? First one has a qualification of proj_adv_date 
 '2008-12-16' whilst the second one does not...

 On Mon, Dec 15, 2008 at 12:12 PM, Néstor rot...@gmail.com wrote:

  I have a char fiel where I am keeping dates formatted as year-month-day
  (2006-10-09)
  Now I am trying to find all the records between 2 strings (2 dates).
 The
  2 queries below
  should return the same number of records by they do not.
 
  My query is this:
   SELECT COUNT(*) FROM proj where proj_archive=0 AND (proj_adv_date =
  '2008-01-01' AND proj_adv_date  '2008-12-16') order by proj_type,
  proj_adv_date, proj_bid_date, proj_name ASC;
  +-+---+
  | proj_id | proj_adv_date |
  +-+---+
  | 181 | 2008-11-25|
  | 217 | 2008-10-27|
  | 136 | 2008-12-01|
  | 219 | 2008-12-08|
  | 225 | 2008-12-11|
  +-+---+
  5 rows in set (0.00 sec)
 
  I get only 5 records returned but if I do this query:
  SELECT COUNT(*) FROM proj where proj_archive=0 AND (proj_adv_date 
  '2008-01-01') order by proj_type, proj_adv_date, proj_bid_date, proj_name
  ASC;
  +-+---+
  | proj_id | proj_adv_date |
  +-+---+
  | 181 | 2008-11-25|
  | 221 | 2008-12-23|
  | 108 | 2009-01-00|
  | 173 | 2009-03-00|
  | 149 | 2009-10-00|
  | 143 | 2009-7-00 |
  | 179 | 2010-04-00|
  | 217 | 2008-10-27|
  | 136 | 2008-12-01|
  | 219 | 2008-12-08|
  | 225 | 2008-12-11|
  | 187 | 2009-01-00|
  | 199 | 2009-01-01|
  | 177 | 2009-02-01|
  |  69 | 2009-03-00|
  |  70 | 2009-03-00|
  |  71 | 2009-03-00|
  | 142 | 2009-03-00|
  | 122 | 2009-04-00|
  | 124 | 2009-04-00|
  | 207 | 2009-04-01|
  |  72 | 2009-07-00|
  |  73 | 2009-07-00|
  |  82 | 2009-07-00|
  | 209 | 2009-10-01|
  | 211 | 2009-10-01|
  | 213 | 2010-03-01|
  +-+---+
  27 rows in set (0.00 sec)
 
  thanks,
 
  Rotsen :-)
 



 --
 Distributed Computing stats
 http://stats.free-dc.org

Re: Need help to query with timestamp in C++

[Kandy Wong]

Hi Saul,

I need to use C++ and I'm not writing a web application.
Thanks anyway.

Kandy

 I have done queries to the database in PHP with variables like month but
 easily can select from a range of time and data to produce the same
 results, the output goes directly to the web so if that is what you are
 seeking for, I can help with PHP.

 Saul

 Kandy Wong wrote:
 Hi,

 I need to write a C++ program in a Linux environment to query with a
 timestamp.
 The user will only provide with an approximate time so I'd like to know
 how can I write a program or a query to return the closest data.

 The followings are the timestamp in the MySQL database:
 | 2008-08-05 03:56:09 | 1217933769 |
 | 2008-08-05 03:56:19 | 1217933779 |
 | 2008-08-05 03:56:29 | 1217933789 |
 | 2008-08-05 03:59:39 | 1217933979 |
 | 2008-08-05 03:59:49 | 1217933989 |
 | 2008-08-05 03:59:59 | 1217933999 |
 | 2008-08-05 04:02:39 | 1217934159 |
 | 2008-08-05 04:02:49 | 1217934169 |
 | 2008-08-05 04:02:59 | 1217934179 |

 For example, '2008-08-05 04:01:39' is the time provided by the user
 which
 does not exist in the database.  So, how can I return the closest data?
 I
 know I can make use of 'LIKE' but this will return more than one data.
 What is the best method to get the closest one?
 And what is the good connector (C++ to MySQL) to use?
 Any suggestion?
 Thank you.

 Kandy




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




-- 
Kandy Wong
Scientific Programmer Analyst
TRIUMF (Canada's National Laboratory for Particle and Nuclear Physics)
4004 Wesbrook Mall
Vancouver, BC, Canada, V6T 2A3
Phone: (604) 222- 1047 ext. 6193
Email: [EMAIL PROTECTED]

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

Re: Need help to query with timestamp in C++

[Dušan Pavlica]

Hi Kandy,

this could be the query you are looking for. It should return record 
with the closest timestamp to your required time:


(SELECT TIMEDIFF('20080815091907', timestamp_column) AS diff, t.* FROM 
table1 t

WHERE timestamp_column = '20080815091907'
ORDER BY timestamp_column DESC LIMIT 1
)
UNION
(SELECT TIMEDIFF(timestamp_column, '20080815091907') AS diff, t.* FROM 
table1 t

WHERE timestamp_column = '20080815091907'
ORDER BY timestamp_column LIMIT 1
)
ORDER BY diff LIMIT 1

HTH,
Dusan

Kandy Wong napsal(a):

Hi,

I need to write a C++ program in a Linux environment to query with a
timestamp.
The user will only provide with an approximate time so I'd like to know
how can I write a program or a query to return the closest data.

The followings are the timestamp in the MySQL database:
| 2008-08-05 03:56:09 | 1217933769 |
| 2008-08-05 03:56:19 | 1217933779 |
| 2008-08-05 03:56:29 | 1217933789 |
| 2008-08-05 03:59:39 | 1217933979 |
| 2008-08-05 03:59:49 | 1217933989 |
| 2008-08-05 03:59:59 | 1217933999 |
| 2008-08-05 04:02:39 | 1217934159 |
| 2008-08-05 04:02:49 | 1217934169 |
| 2008-08-05 04:02:59 | 1217934179 |

For example, '2008-08-05 04:01:39' is the time provided by the user which
does not exist in the database.  So, how can I return the closest data?  I
know I can make use of 'LIKE' but this will return more than one data.
What is the best method to get the closest one?
And what is the good connector (C++ to MySQL) to use?
Any suggestion?
Thank you.

Kandy


  


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

Re: Need help to query with timestamp in C++

[walter harms]

Kandy Wong wrote:
 Hi Saul,
 
 I need to use C++ and I'm not writing a web application.
 Thanks anyway.



you can do something like:

select min(abs(timediff(targettime,timestamp))) from table where 
condition ;

if you use the libmysql you can get the result as strings back (the method i 
prefer) and convert them
in what ever you need.

re,
 wh




 The followings are the timestamp in the MySQL database:
 | 2008-08-05 03:56:09 | 1217933769 |
 | 2008-08-05 03:56:19 | 1217933779 |
 | 2008-08-05 03:56:29 | 1217933789 |
 | 2008-08-05 03:59:39 | 1217933979 |
 | 2008-08-05 03:59:49 | 1217933989 |
 | 2008-08-05 03:59:59 | 1217933999 |
 | 2008-08-05 04:02:39 | 1217934159 |
 | 2008-08-05 04:02:49 | 1217934169 |
 | 2008-08-05 04:02:59 | 1217934179 |

 For example, '2008-08-05 04:01:39' is the time provided by the user
 which
 does not exist in the database.  So, how can I return the closest data?
 I
 know I can make use of 'LIKE' but this will return more than one data.
 What is the best method to get the closest one?
 And what is the good connector (C++ to MySQL) to use?
 Any suggestion?
 Thank you.

 Kandy



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


 
 

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

Re: Need help to query with timestamp in C++

[Warren Young]

Kandy Wong wrote:


And what is the good connector (C++ to MySQL) to use?


MySQL++ (http://tangentsoft.net/mysql++/) has native Date, Time, and 
DateTime data types.  You can convert to these types implicitly:


mysqlpp::DateTime dt = row[my_column];

Row::operator[] doesn't return DateTime, it returns a stringish type, 
which can convert itself to lots of different C++ data types.  This is 
useful because the MySQL C API normally returns results in string form, 
so you need a natural way to convert these values to the native C++ 
types for processing.  In this particular case, it saves you from having 
to do the timestamp string parsing yourself.

--
Warren Young, maintainer of MySQL++

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

RE: Need help to query with timestamp in C++

[Jerry Schwartz]

I'm puzzled by the layout of your table, if that's what you're showing us. Is 
the timestamp in the table truly associated with the time at which the user 
put in his approximate time?

If, for example, the user types in 04:05:07 at 04:03:02, and then types in 
04:02:59 at 04:03:03, what is it that you want to return?

Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com
-Original Message-
From: Kandy Wong [mailto:[EMAIL PROTECTED]
Sent: Friday, August 15, 2008 2:36 AM
To: Saul Bejarano
Cc: mysql@lists.mysql.com
Subject: Re: Need help to query with timestamp in C++

Hi Saul,

I need to use C++ and I'm not writing a web application.
Thanks anyway.

Kandy

 I have done queries to the database in PHP with variables like month
but
 easily can select from a range of time and data to produce the same
 results, the output goes directly to the web so if that is what you
are
 seeking for, I can help with PHP.

 Saul

 Kandy Wong wrote:
 Hi,

 I need to write a C++ program in a Linux environment to query with a
 timestamp.
 The user will only provide with an approximate time so I'd like to
know
 how can I write a program or a query to return the closest data.

 The followings are the timestamp in the MySQL database:
 | 2008-08-05 03:56:09 | 1217933769 |
 | 2008-08-05 03:56:19 | 1217933779 |
 | 2008-08-05 03:56:29 | 1217933789 |
 | 2008-08-05 03:59:39 | 1217933979 |
 | 2008-08-05 03:59:49 | 1217933989 |
 | 2008-08-05 03:59:59 | 1217933999 |
 | 2008-08-05 04:02:39 | 1217934159 |
 | 2008-08-05 04:02:49 | 1217934169 |
 | 2008-08-05 04:02:59 | 1217934179 |

 For example, '2008-08-05 04:01:39' is the time provided by the user
 which
 does not exist in the database.  So, how can I return the closest
data?
 I
 know I can make use of 'LIKE' but this will return more than one
data.
 What is the best method to get the closest one?
 And what is the good connector (C++ to MySQL) to use?
 Any suggestion?
 Thank you.

 Kandy




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




--
Kandy Wong
Scientific Programmer Analyst
TRIUMF (Canada's National Laboratory for Particle and Nuclear Physics)
4004 Wesbrook Mall
Vancouver, BC, Canada, V6T 2A3
Phone: (604) 222- 1047 ext. 6193
Email: [EMAIL PROTECTED]

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
infoshop.com





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

Re: Need help to query with timestamp in C++

[Kandy Wong]

Hi Dusan,

Thank you so much. It works!

Kandy

 Hi Kandy,

 this could be the query you are looking for. It should return record
 with the closest timestamp to your required time:

 (SELECT TIMEDIFF('20080815091907', timestamp_column) AS diff, t.* FROM
 table1 t
  WHERE timestamp_column = '20080815091907'
  ORDER BY timestamp_column DESC LIMIT 1
 )
 UNION
 (SELECT TIMEDIFF(timestamp_column, '20080815091907') AS diff, t.* FROM
 table1 t
  WHERE timestamp_column = '20080815091907'
  ORDER BY timestamp_column LIMIT 1
 )
 ORDER BY diff LIMIT 1

 HTH,
 Dusan

 Kandy Wong napsal(a):
 Hi,

 I need to write a C++ program in a Linux environment to query with a
 timestamp.
 The user will only provide with an approximate time so I'd like to know
 how can I write a program or a query to return the closest data.

 The followings are the timestamp in the MySQL database:
 | 2008-08-05 03:56:09 | 1217933769 |
 | 2008-08-05 03:56:19 | 1217933779 |
 | 2008-08-05 03:56:29 | 1217933789 |
 | 2008-08-05 03:59:39 | 1217933979 |
 | 2008-08-05 03:59:49 | 1217933989 |
 | 2008-08-05 03:59:59 | 1217933999 |
 | 2008-08-05 04:02:39 | 1217934159 |
 | 2008-08-05 04:02:49 | 1217934169 |
 | 2008-08-05 04:02:59 | 1217934179 |

 For example, '2008-08-05 04:01:39' is the time provided by the user
 which
 does not exist in the database.  So, how can I return the closest data?
 I
 know I can make use of 'LIKE' but this will return more than one data.
 What is the best method to get the closest one?
 And what is the good connector (C++ to MySQL) to use?
 Any suggestion?
 Thank you.

 Kandy


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

Need help to query with timestamp in C++

[Kandy Wong]

Hi,

I need to write a C++ program in a Linux environment to query with a
timestamp.
The user will only provide with an approximate time so I'd like to know
how can I write a program or a query to return the closest data.

The followings are the timestamp in the MySQL database:
| 2008-08-05 03:56:09 | 1217933769 |
| 2008-08-05 03:56:19 | 1217933779 |
| 2008-08-05 03:56:29 | 1217933789 |
| 2008-08-05 03:59:39 | 1217933979 |
| 2008-08-05 03:59:49 | 1217933989 |
| 2008-08-05 03:59:59 | 1217933999 |
| 2008-08-05 04:02:39 | 1217934159 |
| 2008-08-05 04:02:49 | 1217934169 |
| 2008-08-05 04:02:59 | 1217934179 |

For example, '2008-08-05 04:01:39' is the time provided by the user which
does not exist in the database.  So, how can I return the closest data?  I
know I can make use of 'LIKE' but this will return more than one data.
What is the best method to get the closest one?
And what is the good connector (C++ to MySQL) to use?
Any suggestion?
Thank you.

Kandy


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

Re: Need help to query with timestamp in C++

[Saul Bejarano]

I have done queries to the database in PHP with variables like month but 
easily can select from a range of time and data to produce the same 
results, the output goes directly to the web so if that is what you are 
seeking for, I can help with PHP.


Saul

Kandy Wong wrote:

Hi,

I need to write a C++ program in a Linux environment to query with a
timestamp.
The user will only provide with an approximate time so I'd like to know
how can I write a program or a query to return the closest data.

The followings are the timestamp in the MySQL database:
| 2008-08-05 03:56:09 | 1217933769 |
| 2008-08-05 03:56:19 | 1217933779 |
| 2008-08-05 03:56:29 | 1217933789 |
| 2008-08-05 03:59:39 | 1217933979 |
| 2008-08-05 03:59:49 | 1217933989 |
| 2008-08-05 03:59:59 | 1217933999 |
| 2008-08-05 04:02:39 | 1217934159 |
| 2008-08-05 04:02:49 | 1217934169 |
| 2008-08-05 04:02:59 | 1217934179 |

For example, '2008-08-05 04:01:39' is the time provided by the user which
does not exist in the database.  So, how can I return the closest data?  I
know I can make use of 'LIKE' but this will return more than one data.
What is the best method to get the closest one?
And what is the good connector (C++ to MySQL) to use?
Any suggestion?
Thank you.

Kandy





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

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

[Daevid Vincent]

I want to remove all records from 'feed_tag' where the feed_id foreign key
doesn't have any corresponding records in feed. 

For instance I may have a record in feed_tag that is like (23, 10, 4543,
'... (some date)'). 

Then lets say there is no record in feed that has a primary id key of 10. 

I want that record (or usually records because of the 1 feed to many
feed_tag relationship) to be removed.

CREATE TABLE IF NOT EXISTS `feed` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(100) default NULL,
  `url` varchar(255) default NULL,
  `host` varchar(100) default NULL,
  `type` varchar(100) default NULL,
  `status` char(1) default NULL,
  `total_stories` int(11) default '0',
  `created_at` datetime default NULL,
  `updated_at` datetime default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

CREATE TABLE IF NOT EXISTS `feed_tag` (
  `id` int(11) NOT NULL auto_increment,
  `feed_id` int(11) default NULL,
  `tag_id` int(11) default NULL,
  `created_at` datetime default NULL,
  PRIMARY KEY  (`id`),
  KEY `feed_tag_FI_1` (`feed_id`),
  KEY `feed_tag_FI_2` (`tag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

As you can see the foreign key 'feed_id' is the issue here (ignore the
tag_id key). 


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

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

[Isart Montane]

Hi Daevid

If you are using a foreign key you can set the reference as cascade
and when a row is deleted from feed it will be deleted from feed_tag.

http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html

If you don't like it you can delete it easy with a query like this

delete from feed_tag where id not in (select id from tag)


Best,
Isart



On Wed, Aug 13, 2008 at 5:51 PM, Daevid Vincent [EMAIL PROTECTED] wrote:
 I want to remove all records from 'feed_tag' where the feed_id foreign key
 doesn't have any corresponding records in feed.

 For instance I may have a record in feed_tag that is like (23, 10, 4543,
 '... (some date)').

 Then lets say there is no record in feed that has a primary id key of 10.

 I want that record (or usually records because of the 1 feed to many
 feed_tag relationship) to be removed.

 CREATE TABLE IF NOT EXISTS `feed` (
   `id` int(11) NOT NULL auto_increment,
   `title` varchar(100) default NULL,
   `url` varchar(255) default NULL,
   `host` varchar(100) default NULL,
   `type` varchar(100) default NULL,
   `status` char(1) default NULL,
   `total_stories` int(11) default '0',
   `created_at` datetime default NULL,
   `updated_at` datetime default NULL,
   PRIMARY KEY  (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

 CREATE TABLE IF NOT EXISTS `feed_tag` (
   `id` int(11) NOT NULL auto_increment,
   `feed_id` int(11) default NULL,
   `tag_id` int(11) default NULL,
   `created_at` datetime default NULL,
   PRIMARY KEY  (`id`),
   KEY `feed_tag_FI_1` (`feed_id`),
   KEY `feed_tag_FI_2` (`tag_id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

 As you can see the foreign key 'feed_id' is the issue here (ignore the
 tag_id key).


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



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

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

[Brent Baisley]

Just do a left join with the delete query.

DELETE feed_tag FROM feed_tag LEFT JOIN feed ON  
feed_tag.feed_id=feed.id WHERE feed.id IS NULL


That should do it. You can change DELETE feed_tag to SELECT and  
test it first.


--
Brent Baisley


On Aug 13, 2008, at 4:51 PM, Daevid Vincent wrote:

I want to remove all records from 'feed_tag' where the feed_id  
foreign key

doesn't have any corresponding records in feed.

For instance I may have a record in feed_tag that is like (23, 10,  
4543,

'... (some date)').

Then lets say there is no record in feed that has a primary id key  
of 10.


I want that record (or usually records because of the 1 feed to many
feed_tag relationship) to be removed.

CREATE TABLE IF NOT EXISTS `feed` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(100) default NULL,
  `url` varchar(255) default NULL,
  `host` varchar(100) default NULL,
  `type` varchar(100) default NULL,
  `status` char(1) default NULL,
  `total_stories` int(11) default '0',
  `created_at` datetime default NULL,
  `updated_at` datetime default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

CREATE TABLE IF NOT EXISTS `feed_tag` (
  `id` int(11) NOT NULL auto_increment,
  `feed_id` int(11) default NULL,
  `tag_id` int(11) default NULL,
  `created_at` datetime default NULL,
  PRIMARY KEY  (`id`),
  KEY `feed_tag_FI_1` (`feed_id`),
  KEY `feed_tag_FI_2` (`tag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

As you can see the foreign key 'feed_id' is the issue here (ignore the
tag_id key).


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




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

Re: Help with query MySQL and PHP

[axis]

This question is strictly related to the mysql query not the php code.
I need to either create a new table from the old one or add columns.
The thing is don't know how to do it.

let me simplify things up:

I need a query to retrieve values from the table 
PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS to a new one or add columns.

the new table should look like

ID Hair Eyes
  Blond  Green
  Brunette  Hazel

The php code is not what I want I need the query, to do exactly that.
Then I will use it in my php code, but what that is not what I am asking 
at this time. Just the query to do that.


Thanks,

Rick Fitzgerald


John Meyer wrote:
It would be better to post this sort of question at 
[EMAIL PROTECTED]

axis wrote:

Hi,

I want to create a new column or table from queries of the values of 
columns I( already have.


My table is :

PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS
products_id products_extra_fields_id products_extra_fields_value
   1 
1 Blond
   1 
2 Green
   1 
3 1.75
1 
4 24
2 
1 Brunette
2 
2 Hazel
2 
3 1.56
2 
4 28



with this Select query 

function get_extra($extra_array = '') {
   if (!is_array($extra_array)) $extra_array = array();

   $extra_query = db_query(select products_extra_fields_value from  
. TABLE_PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS .  where 
products_extra_fields_id = '1');

   while ($extra = db_fetch_array($extra_query)) {
 $extra_array[] = array('id' = 
$extra['products_extra_fields_id'], 'text' = 
$extra['products_extra_fields_value']);

   }

   return $extra_array;
 }

This function uses the query: select products_extra_fields_value from 
PRODUCTS_EXTRA_FIELDS where products_extra_fields_id = 1;


and returns Rubia and Morocha which is fine, now I need to

either create a new column or table in order to be able to have those 
values in their own column


my knowledge of mysql is limited so please give me suggestions as 
what to use in order to create a function to retrieve values and add 
them automatically to my table or columns


The table or columns have to be something like:

ID Hair Eyes
   Blond  Green
   Brunette  Hazel

so I can call them later by selecting from a column in a table like  
TABLE_PRODUCTS_EXTRA_FIELDS_TO_PRODUCTS_EXTRA_FIELDS_VALUE


pef2pev.hair, pef2pev.eyes ... and so on

here is my current switch

for ($i=0, $n=sizeof($column_list); $i$n; $i++) {
   switch ($column_list[$i]) {
 case 'PRODUCT_LIST_MODEL':
   $select_column_list .= 'p.products_model, ';
   break;
 case 'PRODUCT_LIST_MANUFACTURER':
   $select_column_list .= 'm.manufacturers_name, ';
   break;
   // aDD eXTRA FIELdS
 case 'PRODUCT_LIST_HAIR':
$select_column_list .= 'pef2pev.hair, ';
 break;

and so on ...

break;
   }
 }

Thanks


Rick Fitzgerald









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

Re: Help with query MySQL and PHP

[Ananda Kumar]

you can do some thing like this.

create table new_table(id int, hair varchar(50),eyes varchar(50)) select
column1,column2,...from old_table;




On 7/3/08, axis [EMAIL PROTECTED] wrote:

 This question is strictly related to the mysql query not the php code.
 I need to either create a new table from the old one or add columns.
 The thing is don't know how to do it.

 let me simplify things up:

 I need a query to retrieve values from the table
 PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS to a new one or add columns.
 the new table should look like

 ID Hair Eyes
  Blond  Green
  Brunette  Hazel

 The php code is not what I want I need the query, to do exactly that.
 Then I will use it in my php code, but what that is not what I am asking at
 this time. Just the query to do that.

 Thanks,

 Rick Fitzgerald


 John Meyer wrote:

 It would be better to post this sort of question at
 [EMAIL PROTECTED]
 axis wrote:

 Hi,

 I want to create a new column or table from queries of the values of
 columns I( already have.

 My table is :

 PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS
 products_id products_extra_fields_id products_extra_fields_value
   1 1
 Blond
   1 2
 Green
   1 3
 1.75
1 4
   24
2 1
   Brunette
2 2
   Hazel
2 3
   1.56
2 4
   28


 with this Select query 

 function get_extra($extra_array = '') {
   if (!is_array($extra_array)) $extra_array = array();

   $extra_query = db_query(select products_extra_fields_value from  .
 TABLE_PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS .  where products_extra_fields_id =
 '1');
   while ($extra = db_fetch_array($extra_query)) {
 $extra_array[] = array('id' = $extra['products_extra_fields_id'],
 'text' = $extra['products_extra_fields_value']);
   }

   return $extra_array;
  }

 This function uses the query: select products_extra_fields_value from
 PRODUCTS_EXTRA_FIELDS where products_extra_fields_id = 1;

 and returns Rubia and Morocha which is fine, now I need to

 either create a new column or table in order to be able to have those
 values in their own column

 my knowledge of mysql is limited so please give me suggestions as what to
 use in order to create a function to retrieve values and add them
 automatically to my table or columns

 The table or columns have to be something like:

 ID Hair Eyes
   Blond  Green
   Brunette  Hazel

 so I can call them later by selecting from a column in a table like
  TABLE_PRODUCTS_EXTRA_FIELDS_TO_PRODUCTS_EXTRA_FIELDS_VALUE

 pef2pev.hair, pef2pev.eyes ... and so on

 here is my current switch

 for ($i=0, $n=sizeof($column_list); $i$n; $i++) {
   switch ($column_list[$i]) {
 case 'PRODUCT_LIST_MODEL':
   $select_column_list .= 'p.products_model, ';
   break;
 case 'PRODUCT_LIST_MANUFACTURER':
   $select_column_list .= 'm.manufacturers_name, ';
   break;
   // aDD eXTRA FIELdS
 case 'PRODUCT_LIST_HAIR':
$select_column_list .= 'pef2pev.hair, ';
 break;

 and so on ...

break;
   }
  }

 Thanks


 Rick Fitzgerald







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

Help with query MySQL and PHP

[axis]

Hi,

I want to create a new column or table from queries of the values of 
columns I( already have.


My table is :

PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS
products_id products_extra_fields_id products_extra_fields_value
   1 
1 Blond
   1 
2 Green
   1 
3 1.75
1 
4 24
2 
1 Brunette
2 
2 Hazel
2 
3 1.56
2 
4 28



with this Select query 

function get_extra($extra_array = '') {
   if (!is_array($extra_array)) $extra_array = array();

   $extra_query = db_query(select products_extra_fields_value from  . 
TABLE_PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS .  where 
products_extra_fields_id = '1');

   while ($extra = db_fetch_array($extra_query)) {
 $extra_array[] = array('id' = $extra['products_extra_fields_id'], 
'text' = $extra['products_extra_fields_value']);

   }

   return $extra_array;
 }

This function uses the query: select products_extra_fields_value from 
PRODUCTS_EXTRA_FIELDS where products_extra_fields_id = 1;


and returns Rubia and Morocha which is fine, now I need to

either create a new column or table in order to be able to have those 
values in their own column


my knowledge of mysql is limited so please give me suggestions as what 
to use in order to create a function to retrieve values and add them 
automatically to my table or columns


The table or columns have to be something like:

ID Hair Eyes
   Blond  Green
   Brunette  Hazel

so I can call them later by selecting from a column in a table like  
TABLE_PRODUCTS_EXTRA_FIELDS_TO_PRODUCTS_EXTRA_FIELDS_VALUE


pef2pev.hair, pef2pev.eyes ... and so on

here is my current switch

for ($i=0, $n=sizeof($column_list); $i$n; $i++) {
   switch ($column_list[$i]) {
 case 'PRODUCT_LIST_MODEL':
   $select_column_list .= 'p.products_model, ';
   break;
 case 'PRODUCT_LIST_MANUFACTURER':
   $select_column_list .= 'm.manufacturers_name, ';
   break;
   // aDD eXTRA FIELdS
 case 'PRODUCT_LIST_HAIR':
   
 $select_column_list .= 'pef2pev.hair, ';

 break;

and so on ...

break;
   }
 }

Thanks


Rick Fitzgerald



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

help with query

[Nacho Garcia]

Hi, im having a lot of trouble with one query, i hope someone could give me
a hand with this, i would be really grateful

these are the tables:

TABLE friends
id_usr INT
id_friend INT
with INDEX on (id_usr,id_friend)

TABLE status
id_usr INT
lastConnection
 (other irrelevant info)
with INDEX on (id_usr,lastConnection)


im trying to get friends of a given id_usr ordered by lastConnection.

i tried a lot, the simplest query give me the best results

explain SELECT F.id_friend,S.lastConnection
FROM friends F, user_status S
WHERE F.id_friend = S.id_usr
AND F.id_usr = 1
ORDER BY lastConnection

id select_type table type possible_keys key key_len ref rows Extra   1
SIMPLE F ref id_usr id_usr 4 const 20 Using temporary; Using filesort  1
SIMPLE S ref id_usr id_usr 4 netlivin3.F.id_friend 3 Using index
it's really bad, but at least it's ref type so only read the rows of friends
table matching id_usr=1

**

Re: help wit query optimization (cont'd)

[Sebastian Mendel]

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

usually ... but i do not know of any index capable of having FULLTEXT and
'normal' fields in one index

 WHERE MATCH table_2.field AGAINST ('value')
 AND table_2.current = 1
 AND table_2.shared_id IN (SELECT shared_id FROM table_1_view) 

or am i wrong?

-- 
Sebastian

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

Re: help wit query optimization (cont'd)

[Perrin Harkins]

On Jan 9, 2008 4:33 AM, Sebastian Mendel [EMAIL PROTECTED] wrote:
 usually ... but i do not know of any index capable of having FULLTEXT and
 'normal' fields in one index

Does that matter?  It would have to be doing a full scan for
SQL_CALC_FOUND_ROWS to work out well.

- Perrin

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

Re: help wit query optimization (cont'd)

[Sebastian Mendel]

Perrin Harkins schrieb:
 On Jan 9, 2008 4:33 AM, Sebastian Mendel [EMAIL PROTECTED] wrote:
 usually ... but i do not know of any index capable of having FULLTEXT and
 'normal' fields in one index
 
 Does that matter?

yes, as written in the mentioned article the test is only relevant with
correct used indexes, but MySQL does not use more than one index, so this
query cannot all be done with indexes


please correct me if i am wrong

-- 
Sebastian



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

Re: help wit query optimization (cont'd)

[Perrin Harkins]

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

Well, first of all, MySQL 5 does use more than one index, although I'm
not sure it can do this with a full text index.
http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.html

Also, there's a lot of room between a full table scan and using every
index.  Using any index in a way that allows the query to be satisfied
without scanning every single row should be enough to make the count
query better.

- Perrin

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

Re: help with query optimization

[Brent Baisley]

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


SELECT SQL_CALC_FOUND_ROWS table_1.id,table_2.*
FROM table_2
JOIN
(SELECT shared_id FROM table_1_view) as table_3 ON  
table_2.shared_id=table_3.shared_id

LEFT JOIN table_1 ON table_2.shared_id = table_1.shared_id
WHERE MATCH table_2.field AGAINST ('value')
AND table_2.current = 1

I know the difference doesn't seem that much, but MySQL optimizes it  
very differently.


Brent


On Jan 4, 2008, at 5:47 PM, Eben wrote:


Hi,

I have a query that has to run on a full text indexed table with  
many millions of records.  I'm trying to figure out some  
optimizations for it.  Here's the general query:


SELECT SQL_CALC_FOUND_ROWS table_1.id,table_2.*
FROM table_2
LEFT JOIN table_1 ON table_2.shared_id = table_1.shared_id
WHERE MATCH table_2.field AGAINST ('value')
AND table_2.current = 1
AND table_2.shared_id IN (SELECT shared_id FROM table_1_view)

Some details:
1. table_1.shared_id and table_2.shared_id are indexed
2. table_2.current is an indexed tinyint
3. table_1_view is derived from a query like:

SELECT shared_id
FROM table_1
WHERE some_field LIKE 'some_value%'

table_1 is a relatively small table i.e.  100k records
table_2 is massive with  10 million records

Any ideas or suggestions are appreciated

thanks,
Eben



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql? 
[EMAIL PROTECTED]





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

help wit query optimization (cont'd)

[Eben]

I left something out, the query looks like:
SELECT SQL_CALC_FOUND_ROWS table_1.id,table_2.*
FROM table_2
LEFT JOIN table_1 ON table_2.shared_id = table_1.shared_id
WHERE MATCH table_2.field AGAINST ('value')
AND table_2.current = 1
AND table_2.shared_id IN (SELECT shared_id FROM table_1_view)
LIMIT 0,10

The resultset is paginated on the front end using the 
SQL_CALC_FOUND_ROWS functionality...


thanks,
Eben



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

Re: help wit query optimization (cont'd)

[Perrin Harkins]

On Jan 4, 2008 5:51 PM, Eben [EMAIL PROTECTED] wrote:
 The resultset is paginated on the front end using the
 SQL_CALC_FOUND_ROWS functionality...

Usually a bad idea:
http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/

- Perrin

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

help with query optimization

[Eben]

Hi,

I have a query that has to run on a full text indexed table with many 
millions of records.  I'm trying to figure out some optimizations for 
it.  Here's the general query:


SELECT SQL_CALC_FOUND_ROWS table_1.id,table_2.*
FROM table_2
LEFT JOIN table_1 ON table_2.shared_id = table_1.shared_id
WHERE MATCH table_2.field AGAINST ('value')
AND table_2.current = 1
AND table_2.shared_id IN (SELECT shared_id FROM table_1_view)

Some details:
1. table_1.shared_id and table_2.shared_id are indexed
2. table_2.current is an indexed tinyint
3. table_1_view is derived from a query like:

SELECT shared_id
FROM table_1
WHERE some_field LIKE 'some_value%'

table_1 is a relatively small table i.e.  100k records
table_2 is massive with  10 million records

Any ideas or suggestions are appreciated

thanks,
Eben



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

Re: help wit query optimization (cont'd)

[Moon's Father]

I've never used this feature before on my application.

On Jan 5, 2008 7:06 AM, Perrin Harkins [EMAIL PROTECTED] wrote:

 On Jan 4, 2008 5:51 PM, Eben [EMAIL PROTECTED] wrote:
  The resultset is paginated on the front end using the
  SQL_CALC_FOUND_ROWS functionality...

 Usually a bad idea:

 http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/

 - Perrin

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




-- 
I'm a mysql DBA in china.
More about me just visit here:
http://yueliangdao0608.cublog.cn

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

[Richard]

Richard a écrit :

Sorry about my last email which was long and not clear.
This is what I want to do

Join two tables on code table1 = code table3 where messageid = for 
example 28


table 1 contains :

 message   from   messageid
--
message1  |  code1  |28
message2  |  code1  |28
message3  |  code1  |28
message4  |  code1  |29


table 2 contains

name |  code  |  num
--
name1  |  code2  |  1
name2  |  code1  |  2
name3  |  code1  |  3

If I do :
SELECT a.message,,b.name  FROM table1 a JOIN table2 b ON a.code=b.code 
WHERE a.id='28'


I get :
message|  name
---
message1  |  name2
message2  |  name2
message3  |  name2
message1  |  name3
message2  |  name3
message3  |  name3


But all I want to get is :

message|  name
---
message1  |  name3
message2  |  name3
message3  |  name3

If I do :
SELECT * FROM table2 WHERE code = 'code1' ORDER BY num DESC LIMIT 1

I get :

name |  code  |  num
--
name3  |  code1  |  3

I now need to somehow combine the two to get :

message|  name
---
message1  |  name3
message2  |  name3
message3  |  name3

Of course I have simplified everything down to the minimum :)

Thanks in advance,

Richard


As I have had no answer I presume that what I want to do is not possible 
or my question is not well explained. Anyhow I've rethought the system 
so I do not need to keep members information and now instead of adding a 
new entry I will now just change the existing one. I won't keep old 
members information in the database but I'll still have the database 
daily backups if I need the old information.


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

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

[Martin Gainty]

the simple answer is and b.name='name3' ?Bon 
ChanceMartin__Disclaimer and 
confidentiality noteEverything in this e-mail and any attachments relates to 
the official business of Sender. This transmission is of a confidential nature 
and Sender does not endorse distribution to any party other than intended 
recipient. Sender does not necessarily endorse content contained within this 
transmission. Date: Sun, 30 Dec 2007 13:54:32 +0100 From: [EMAIL PROTECTED] 
To: mysql@lists.mysql.com Subject: Re: Help with query, (question simplified 
as last mail was very complicated to understand :))  Richard a écrit :  
Sorry about my last email which was long and not clear.  This is what I want 
to do   Join two tables on code table1 = code table3 where messageid = 
for   example 28   table 1 contains :   message from messageid  
--  message1 | code1 | 28  message2 
| code1 | 28  message3 | code1 | 28  message4 | code1 | 29table 2 
contains   name | code | num  --  
name1 | code2 | 1  name2 | code1 | 2  name3 | code1 | 3   If I do :  
SELECT a.message,,b.name FROM table1 a JOIN table2 b ON a.code=b.code   WHERE 
a.id='28'   I get :  message | name  ---  
message1 | name2  message2 | name2  message3 | name2  message1 | name3  
message2 | name3  message3 | name3But all I want to get is :   
message | name  ---  message1 | name3  message2 | 
name3  message3 | name3   If I do :  SELECT * FROM table2 WHERE code = 
'code1' ORDER BY num DESC LIMIT 1   I get :   name | code | num  
--  name3 | code1 | 3   I now need to 
somehow combine the two to get :   message | name  
---  message1 | name3  message2 | name3  message3 
| name3   Of course I have simplified everything down to the minimum :)  
 Thanks in advance,   Richard   As I have had no answer I presume 
that what I want to do is not possible  or my question is not well explained. 
Anyhow I've rethought the system  so I do not need to keep members information 
and now instead of adding a  new entry I will now just change the existing 
one. I won't keep old  members information in the database but I'll still have 
the database  daily backups if I need the old information.  --  MySQL 
General Mailing List For list archives: http://lists.mysql.com/mysql To 
unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] 
_
The best games are on Xbox 360.  Click here for a special offer on an Xbox 360 
Console.
http://www.xbox.com/en-US/hardware/wheretobuy/

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

[Richard]

Sorry about my last email which was long and not clear.
This is what I want to do

Join two tables on code table1 = code table3 where messageid = for 
example 28


table 1 contains :

 message   from   messageid
--
message1  |  code1  |28
message2  |  code1  |28
message3  |  code1  |28
message4  |  code1  |29


table 2 contains

name |  code  |  num
--
name1  |  code2  |  1
name2  |  code1  |  2
name3  |  code1  |  3

If I do :
SELECT a.message,,b.name  FROM table1 a JOIN table2 b ON a.code=b.code 
WHERE a.id='28'


I get :
message|  name
---
message1  |  name2
message2  |  name2
message3  |  name2
message1  |  name3
message2  |  name3
message3  |  name3


But all I want to get is :

message|  name
---
message1  |  name3
message2  |  name3
message3  |  name3

If I do :
SELECT * FROM table2 WHERE code = 'code1' ORDER BY num DESC LIMIT 1

I get :

name |  code  |  num
--
name3  |  code1  |  3

I now need to somehow combine the two to get :

message|  name
---
message1  |  name3
message2  |  name3
message3  |  name3

Of course I have simplified everything down to the minimum :)

Thanks in advance,

Richard


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

Re: Need help creating query statement

[Néstor]

Guys, just wanted to thank you again for helping me with
the sql statement that I needed.  I was able to sorted using
php and I was able to display the correct result.

Thanks again!!1

Nestor :-)

On Nov 6, 2007 7:37 AM, Néstor [EMAIL PROTECTED] wrote:

 You guys are correct, that is exactly what happened.

 I must thing of this in the future.

 At this moment I have a lot of other projects to take care, that it is
 eaiser for me to read the information into an associative array with
 the columns and the values and sort the array and then print the
 top 5 values within each array.

 Thanks,

 Nestor :-)



 On 11/6/07, Enrique Sanchez Vela [EMAIL PROTECTED] wrote:
 
 
  --- [EMAIL PROTECTED] wrote:
 
   Néstor wrote:
 
  
   I think you'd best begin by normalising your
   database. Something along
   these lines:
  
 
  very true indeed, that would save you major headaches
  when right after finishing the demo, someone would
  say, can we add a 18 gallon pledge? what about a 25?
 
 
  as of the sort of columns per row, I believe it is not
  possible nor in the goals of MySQL to make it
  possible/easy.
 
  best regards,
  enrique.
 
  --
  What you have been obliged to discover
  by yourself leaves a path in your mind
  which you can use again when the need
  arises.--G. C. Lichtenberg
 
  http://themathcircle.org/
 
  __
  Do You Yahoo!?
  Tired of spam?  Yahoo! Mail has the best spam protection around
  http://mail.yahoo.com
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 
 

Re: Need help creating query statement

[Enrique Sanchez Vela]

--- [EMAIL PROTECTED] wrote:

 Néstor wrote:
 
 
 I think you'd best begin by normalising your
 database. Something along 
 these lines:
 

very true indeed, that would save you major headaches
when right after finishing the demo, someone would
say, can we add a 18 gallon pledge? what about a 25?


as of the sort of columns per row, I believe it is not
possible nor in the goals of MySQL to make it
possible/easy.

best regards,
enrique.

--
What you have been obliged to discover
by yourself leaves a path in your mind
which you can use again when the need
arises.--G. C. Lichtenberg

http://themathcircle.org/

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

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

Re: Need help creating query statement

[Néstor]

You guys are correct, that is exactly what happened.

I must thing of this in the future.

At this moment I have a lot of other projects to take care, that it is
eaiser for me to read the information into an associative array with
the columns and the values and sort the array and then print the
top 5 values within each array.

Thanks,

Nestor :-)


On 11/6/07, Enrique Sanchez Vela [EMAIL PROTECTED] wrote:


 --- [EMAIL PROTECTED] wrote:

  Néstor wrote:

 
  I think you'd best begin by normalising your
  database. Something along
  these lines:
 

 very true indeed, that would save you major headaches
 when right after finishing the demo, someone would
 say, can we add a 18 gallon pledge? what about a 25?


 as of the sort of columns per row, I believe it is not
 possible nor in the goals of MySQL to make it
 possible/easy.

 best regards,
 enrique.

 --
 What you have been obliged to discover
 by yourself leaves a path in your mind
 which you can use again when the need
 arises.--G. C. Lichtenberg

 http://themathcircle.org/

 __
 Do You Yahoo!?
 Tired of spam?  Yahoo! Mail has the best spam protection around
 http://mail.yahoo.com

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

Need help creating query statement

[Néstor]

I can do simple select statements but I need your brains to create query
statment.
I am using mysql 4.0 in my 1and1 site.

I have a table that has 8 fields, one is the agency field and the other 7
are
*tip* values on saving water and the value of this field is either 0 or an
amount.
--
|agency| 5g | 8g | 10g | 12g | 15g | 20g | 40g |
--

so the record could be
|Helix   | 0   |  8  |  10  |   12 |   15 |   0  |   40 |
|Corp| 5   |  0  |  0   |   12 |   15 |   0  |0 |
|Helix   | 0   |  8  |  10  |   0  |   15 |   0  |   40 |

I need to get the to count *tips* per *agency
*so in this case of 3 records I would have:
|Helix   |   0 |   2 |   2  |1  |   2   |   0  |   2  |
|Corp   |1 |   0 |  0  |1  |1   |0 |0 |

and then I need to come out with top 5 *tips *per agency
|Helix   | 8g = 2 | 10g = 2 | 15g = 2 | 8g = 2 | 40g = 2|
|Corp| 8g = 1 | 12g = 1  |  15g= 1 |

Is there an easy way to do this?

I hope I made myself understood.

Thanks,

Néstor :-)

Fwd: Re: Need help creating query statement

[Enrique Sanchez Vela]

--- Enrique Sanchez Vela [EMAIL PROTECTED]
wrote:

 Date: Mon, 5 Nov 2007 15:01:59 -0800 (PST)
 From: Enrique Sanchez Vela [EMAIL PROTECTED]
 Subject: Re: Need help creating query statement
 To: Néstor [EMAIL PROTECTED]
 
 
 --- Néstor [EMAIL PROTECTED] wrote:
 
  I can do simple select statements but I need your
  brains to create query
  statment.
  I am using mysql 4.0 in my 1and1 site.
  
  I have a table that has 8 fields, one is the
  agency field and the other 7
  are
  *tip* values on saving water and the value of this
  field is either 0 or an
  amount.
 

--
  |agency| 5g | 8g | 10g | 12g | 15g | 20g | 40g |
 

--
  
  so the record could be
  |Helix   | 0   |  8  |  10  |   12 |   15 |   0  |
  
  40 |
  |Corp| 5   |  0  |  0   |   12 |   15 |   0  |
  
   0 |
  |Helix   | 0   |  8  |  10  |   0  |   15 |   0  |
  
  40 |
  
  I need to get the to count *tips* per *agency
  *so in this case of 3 records I would have:
  |Helix   |   0 |   2 |   2  |1  |   2   |   0 
 |
2  |
  |Corp   |1 |   0 |  0  |1  |1   |0
 |
 0 |
  
 
 let's see if I got the point
 
 CREATE TABLE `agency` (
   `name` varchar(11) collate latin1_bin NOT NULL
 default '',
   `5g` int(11) NOT NULL default '0',
   `8g` int(11) NOT NULL default '0',
   `10g` int(11) NOT NULL default '0',
   `12g` int(11) NOT NULL default '0',
   `15g` int(11) NOT NULL default '0',
   `20g` int(11) NOT NULL default '0',
   `40g` int(11) NOT NULL default '0'
 ) ENGINE=MyISAM ;
 
 
 select * from agency;
 
 +---+++-+-+-+-+-+
 | name  | 5g | 8g | 10g | 12g | 15g | 20g | 40g |
 +---+++-+-+-+-+-+
 | Helix |  0 |  0 |   0 |  12 |   0 |   0 |   0 |
 | Helix |  0 |  8 |  10 |   0 |  15 |   0 |  40 |
 | Corp  |  5 |  0 |   0 |  12 |  15 |   0 |  40 |
 | Helix |  0 |  8 |  10 |   0 |  15 |   0 |  40 |
 +---+++-+-+-+-+-+
 4 rows in set (0.00 sec)
 
 
  select `name`, sum(if(5g0, 1,0)) as 5g ,
 sum(if(8g0, 1,0)) as 8g, sum(if(10g0,1,0)) as 10g,
 sum(if(12g0,1,0))as 12g ,sum(if(15g0,1,0)) as 15g
 ,
 sum(if (20g0,1,0)) as 20g, sum(if(40g0,1,0)) as
 40g 
 from agency group by `name`;
 
 produces...
 

+---+--+--+--+--+--+--+--+
 | name  | 5g   | 8g   | 10g  | 12g  | 15g  | 20g  |
 40g  |

+---+--+--+--+--+--+--+--+
 | Corp  |1 |0 |0 |1 |1 |0 | 
  
 1 |
 | Helix |0 |2 |2 |1 |2 |0 | 
  
 2 |

+---+--+--+--+--+--+--+--+
 2 rows in set (0.00 sec)
 
 regards,
 esv.
 
 
 --
 What you have been obliged to discover
 by yourself leaves a path in your mind
 which you can use again when the need
 arises.--G. C. Lichtenberg
 
 http://themathcircle.org/
 
 __
 Do You Yahoo!?
 Tired of spam?  Yahoo! Mail has the best spam
 protection around 
 http://mail.yahoo.com 
 


--
What you have been obliged to discover
by yourself leaves a path in your mind
which you can use again when the need
arises.--G. C. Lichtenberg

http://themathcircle.org/

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

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

Re: Need help creating query statement

[Néstor]

Enrique that is pretty good and close to what I need.

On top of what you have generously provide, I guess I can just put
each gallon field into an array an then sort the array to display the
first 5 gallon fields per agency.  Is there an easier way?

Thanks,

Nestor :-)

On 11/5/07, Enrique Sanchez Vela [EMAIL PROTECTED] wrote:


 --- Néstor [EMAIL PROTECTED] wrote:

  I can do simple select statements but I need your
  brains to create query
  statment.
  I am using mysql 4.0 in my 1and1 site.
 
  I have a table that has 8 fields, one is the
  agency field and the other 7
  are
  *tip* values on saving water and the value of this
  field is either 0 or an
  amount.
 
 --
  |agency| 5g | 8g | 10g | 12g | 15g | 20g | 40g |
 
 --
 
  so the record could be
  |Helix   | 0   |  8  |  10  |   12 |   15 |   0  |
  40 |
  |Corp| 5   |  0  |  0   |   12 |   15 |   0  |
   0 |
  |Helix   | 0   |  8  |  10  |   0  |   15 |   0  |
  40 |
 
  I need to get the to count *tips* per *agency
  *so in this case of 3 records I would have:
  |Helix   |   0 |   2 |   2  |1  |   2   |   0  |
2  |
  |Corp   |1 |   0 |  0  |1  |1   |0 |
 0 |
 

 let's see if I got the point

 CREATE TABLE `agency` (
   `name` varchar(11) collate latin1_bin NOT NULL
 default '',
   `5g` int(11) NOT NULL default '0',
   `8g` int(11) NOT NULL default '0',
   `10g` int(11) NOT NULL default '0',
   `12g` int(11) NOT NULL default '0',
   `15g` int(11) NOT NULL default '0',
   `20g` int(11) NOT NULL default '0',
   `40g` int(11) NOT NULL default '0'
 ) ENGINE=MyISAM ;


 select * from agency;

 +---+++-+-+-+-+-+
 | name  | 5g | 8g | 10g | 12g | 15g | 20g | 40g |
 +---+++-+-+-+-+-+
 | Helix |  0 |  0 |   0 |  12 |   0 |   0 |   0 |
 | Helix |  0 |  8 |  10 |   0 |  15 |   0 |  40 |
 | Corp  |  5 |  0 |   0 |  12 |  15 |   0 |  40 |
 | Helix |  0 |  8 |  10 |   0 |  15 |   0 |  40 |
 +---+++-+-+-+-+-+
 4 rows in set (0.00 sec)


 select `name`, sum(if(5g0, 1,0)) as 5g ,
 sum(if(8g0, 1,0)) as 8g, sum(if(10g0,1,0)) as 10g,
 sum(if(12g0,1,0))as 12g ,sum(if(15g0,1,0)) as 15g ,
 sum(if (20g0,1,0)) as 20g, sum(if(40g0,1,0)) as 40g
 from agency group by `name`;

 produces...

 +---+--+--+--+--+--+--+--+
 | name  | 5g   | 8g   | 10g  | 12g  | 15g  | 20g  |
 40g  |
 +---+--+--+--+--+--+--+--+
 | Corp  |1 |0 |0 |1 |1 |0 |
 1 |
 | Helix |0 |2 |2 |1 |2 |0 |
 2 |
 +---+--+--+--+--+--+--+--+
 2 rows in set (0.00 sec)

 regards,
 esv.


 --
 What you have been obliged to discover
 by yourself leaves a path in your mind
 which you can use again when the need
 arises.--G. C. Lichtenberg

 http://themathcircle.org/

 __
 Do You Yahoo!?
 Tired of spam?  Yahoo! Mail has the best spam protection around
 http://mail.yahoo.com

Re: Need help creating query statement

[mysql]

Néstor wrote:

I can do simple select statements but I need your brains to create query
statment.
I am using mysql 4.0 in my 1and1 site.

I have a table that has 8 fields, one is the agency field and the other 7
are
*tip* values on saving water and the value of this field is either 0 or an
amount.
--
|agency| 5g | 8g | 10g | 12g | 15g | 20g | 40g |
--

so the record could be
|Helix   | 0   |  8  |  10  |   12 |   15 |   0  |   40 |
|Corp| 5   |  0  |  0   |   12 |   15 |   0  |0 |
|Helix   | 0   |  8  |  10  |   0  |   15 |   0  |   40 |

I need to get the to count *tips* per *agency
*so in this case of 3 records I would have:
|Helix   |   0 |   2 |   2  |1  |   2   |   0  |   2  |
|Corp   |1 |   0 |  0  |1  |1   |0 |0 |

and then I need to come out with top 5 *tips *per agency
|Helix   | 8g = 2 | 10g = 2 | 15g = 2 | 8g = 2 | 40g = 2|
|Corp| 8g = 1 | 12g = 1  |  15g= 1 |

Is there an easy way to do this?



I think you'd best begin by normalising your database. Something along 
these lines:


CREATE TABLE agency (
id tinyint(3) unsigned NOT NULL auto_increment,
name varchar(64)NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM;

CREATE TABLE pledge (
id tinyint(3) unsigned NOT NULL auto_increment,
agency_id tinyint(3) unsigned NOT NULL,
gallons enum('5','8','10','12','15','20','40') DEFAULT NULL,
PRIMARY KEY (id),
FOREIGN KEY (agency_id) REFERENCES agency (id)
) ENGINE=MyISAM;

You could create a separate 'gallons' table, with the '5','8','10', etc. 
as rows but, since this is MySQL you might as well go with the ENUM.


Once you have your data normalised you'll be able to things like joining 
across both tables and grouping by agency, etc.


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

Help with query...

[Anders Norrbring]

I'm trying to set up a query, but I don't really get the result I 
expected, so can someone please help me out here?


The query I've built is this:

SELECT a1.username FROM accountuser AS a1
LEFT JOIN (freeaccounts AS f1, payments AS p1)
ON (a1.username = p1.username
AND p1.username = f1.username)
WHERE a1.username LIKE 'cit%'
AND a1.imp + a1.pp + a1.se + a1.auth != 0
AND (f1.free IS NULL OR f1.free = false)
AND (p1.validdate  UNIX_TIMESTAMP(NOW()) OR p1.validdate IS NULL)

The accountuser table is always fully populated.
The freeaccounts and payments tables are only occupied with the 
'username' field if it's been used previously, so they may not contain 
any data.


What  I expect to get from the query is 'username' from the accountuser 
table when:


The a1.username starts with 'cit',
AND
Any of a1.imp, a1.pp, a1.se or a1.auth is not 0,
AND
f1.free is either not populated or false.
AND
p1.validdate is either not populated, or the timestamp is before NOW.

Everything seems to work except for the passed time check. If I set 
p1.validdate to a timestamp for something next week, the username is 
still returned in the result.


Grateful for any hints...
Anders.


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

Re: Help with query...

[mysql]

Anders Norrbring wrote:
I'm trying to set up a query, but I don't really get the result I 
expected, so can someone please help me out here?


The query I've built is this:

SELECT a1.username FROM accountuser AS a1
LEFT JOIN (freeaccounts AS f1, payments AS p1)
ON (a1.username = p1.username
AND p1.username = f1.username)
WHERE a1.username LIKE 'cit%'
AND a1.imp + a1.pp + a1.se + a1.auth != 0
AND (f1.free IS NULL OR f1.free = false)
AND (p1.validdate  UNIX_TIMESTAMP(NOW()) OR p1.validdate IS NULL)

The accountuser table is always fully populated.
The freeaccounts and payments tables are only occupied with the 
'username' field if it's been used previously, so they may not contain 
any data.


What  I expect to get from the query is 'username' from the accountuser 
table when:


The a1.username starts with 'cit',
AND
Any of a1.imp, a1.pp, a1.se or a1.auth is not 0,
AND
f1.free is either not populated or false.
AND
p1.validdate is either not populated, or the timestamp is before NOW.

Everything seems to work except for the passed time check. If I set 
p1.validdate to a timestamp for something next week, the username is 
still returned in the result.




Put the last line (the tests on p1.validdate) in the JOIN condition instead.

brian

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

SV: Help with query...

[Anders Norrbring]

 Hello
 where is the FROM for f1 table?
 Martin--

Why would there be any FROM for the f1 table? It's not needed in the result.

Anyway, I've come up with a query that actually seems to do what I'm looking 
for;

SELECT a1.username FROM accountuser AS a1
LEFT JOIN payments AS p1 ON (a1.username = p1.username)
LEFT JOIN freeaccounts AS f1 ON (a1.username = f1.username)
WHERE a1.username LIKE 'cit%'
AND a1.imp + a1.pp + a1.se + a1.auth != 0
AND (p1.validdate  UNIX_TIMESTAMP(NOW()) OR p1.validdate IS NULL)
AND (f1.free = false OR f1.free IS NULL)

Thanks for the hints, you who gave them.. ;)

Anders.

 - Original Message -
 From: Anders Norrbring [EMAIL PROTECTED]
 To: mysql@lists.mysql.com
 Sent: Sunday, October 14, 2007 7:13 AM
 Subject: Help with query...
 
 
  I'm trying to set up a query, but I don't really get the result I
  expected, so can someone please help me out here?
 
  The query I've built is this:
 
  SELECT a1.username FROM accountuser AS a1
  LEFT JOIN (freeaccounts AS f1, payments AS p1)
  ON (a1.username = p1.username
  AND p1.username = f1.username)
  WHERE a1.username LIKE 'cit%'
  AND a1.imp + a1.pp + a1.se + a1.auth != 0
  AND (f1.free IS NULL OR f1.free = false)
  AND (p1.validdate  UNIX_TIMESTAMP(NOW()) OR p1.validdate IS NULL)
 
  The accountuser table is always fully populated.
  The freeaccounts and payments tables are only occupied with the
  'username' field if it's been used previously, so they may not
 contain
  any data.
 
  What  I expect to get from the query is 'username' from the
 accountuser
  table when:
 
  The a1.username starts with 'cit',
  AND
  Any of a1.imp, a1.pp, a1.se or a1.auth is not 0,
  AND
  f1.free is either not populated or false.
  AND
  p1.validdate is either not populated, or the timestamp is before NOW.
 
  Everything seems to work except for the passed time check. If I set
  p1.validdate to a timestamp for something next week, the username is
  still returned in the result.
 
  Grateful for any hints...
  Anders.
 
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:
 http://lists.mysql.com/[EMAIL PROTECTED]
 
 


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

Help with Query

[Ed Curtis]

I need to get some duplicate record information from a table and I 
haven't found any way to do it yet. I figured there might be some type 
of query I could do using a for each type command.


What I have is a table with names and companies. Some people have 
multiple entries for different companies. What I need to get is the name 
that has multiple entries along with the company names.


Name|   Company

Joe BlowCompany 1
Joe BlowCompany 2
Joe G. Blow Company 1

Running the query should only return Joe Blow with Company 1 and Company 2.

I can find out how many records Joe Blow has or list out each Company 
record grouped by Name but I only want Names with multiple entries 
shown. Can anyone help? I'm sure this also makes a difference but I'm 
stuck using MySQL 3.23.


Thanks,

Ed


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

Re: Help with Query

[Baron Schwartz]

Hi Ed,

Ed Curtis wrote:
I need to get some duplicate record information from a table and I 
haven't found any way to do it yet. I figured there might be some type 
of query I could do using a for each type command.


What I have is a table with names and companies. Some people have 
multiple entries for different companies. What I need to get is the name 
that has multiple entries along with the company names.


Name | Company

Joe BlowCompany 1
Joe BlowCompany 2
Joe G. BlowCompany 1

Running the query should only return Joe Blow with Company 1 and Company 2.

I can find out how many records Joe Blow has or list out each Company 
record grouped by Name but I only want Names with multiple entries 
shown. Can anyone help? I'm sure this also makes a difference but I'm 
stuck using MySQL 3.23.


Since you are using 3.23, you can't use subqueries, which would otherwise make this 
easy (http://www.xaprb.com/blog/2006/10/09/how-to-find-duplicate-rows-with-sql/).  But 
you can do it with temporary tables instead:


CREATE TEMPORARY TABLE tmp AS SELECT Name, COUNT(*) AS cnt FROM tbl GROUP BY 
Name;
SELECT tbl.* FROM tbl JOIN tmp USING(Name) WHERE cnt  1;

Baron

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

Need help with query

[Jesse]

The following query works just fine:

SELECT B.BusNum, COALESCE(CampCount.Cnt,0) As Kids, B.CamperCapacity,
  COALESCE(CounselorCount.Cnt,0) As Adults, B.AdultCapacity
FROM Buses B
  LEFT JOIN (SELECT BusID,Count(*) As Cnt FROM Campers GROUP BY BusID) AS 
CampCount ON CampCount.BusID=B.ID
  LEFT JOIN (SELECT BusID,Count(*) As Cnt FROM Counselors GROUP BY BusID) 
AS CounselorCount On CounselorCount.BusID=B.ID

ORDER BY B.BusNum

However, I would like to add the to have a total of the adults and kids on 
the bus, so I change my query as follows:


SELECT B.BusNum, COALESCE(CampCount.Cnt,0) As Kids, B.CamperCapacity,
  COALESCE(CounselorCount.Cnt,0) As Adults, B.AdultCapacity,
  Kids + Adults As GT
FROM Buses B
  LEFT JOIN (SELECT BusID,Count(*) As Cnt FROM Campers GROUP BY BusID) AS 
CampCount ON CampCount.BusID=B.ID
  LEFT JOIN (SELECT BusID,Count(*) As Cnt FROM Counselors GROUP BY BusID) 
AS CounselorCount On CounselorCount.BusID=B.ID

ORDER BY B.BusNum

When I try to execute this, I get the error, Unknown column 'Kids' in 
'field list'  How do I properly add these together to get a total column?


Jesse 



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

Re: Need help with query

[Baron Schwartz]

Hi Jesse,

Jesse wrote:

The following query works just fine:

SELECT B.BusNum, COALESCE(CampCount.Cnt,0) As Kids, B.CamperCapacity,
  COALESCE(CounselorCount.Cnt,0) As Adults, B.AdultCapacity
FROM Buses B
  LEFT JOIN (SELECT BusID,Count(*) As Cnt FROM Campers GROUP BY BusID) 
AS CampCount ON CampCount.BusID=B.ID
  LEFT JOIN (SELECT BusID,Count(*) As Cnt FROM Counselors GROUP BY 
BusID) AS CounselorCount On CounselorCount.BusID=B.ID

ORDER BY B.BusNum

However, I would like to add the to have a total of the adults and kids 
on the bus, so I change my query as follows:


SELECT B.BusNum, COALESCE(CampCount.Cnt,0) As Kids, B.CamperCapacity,
  COALESCE(CounselorCount.Cnt,0) As Adults, B.AdultCapacity,
  Kids + Adults As GT
FROM Buses B
  LEFT JOIN (SELECT BusID,Count(*) As Cnt FROM Campers GROUP BY BusID) 
AS CampCount ON CampCount.BusID=B.ID
  LEFT JOIN (SELECT BusID,Count(*) As Cnt FROM Counselors GROUP BY 
BusID) AS CounselorCount On CounselorCount.BusID=B.ID

ORDER BY B.BusNum

When I try to execute this, I get the error, Unknown column 'Kids' in 
'field list'  How do I properly add these together to get a total column?


Column aliases can't be referred to later in the select list as far as I 
know.  The only way I know to do  this is simply duplicate the expressions:


COALESCE(CampCount.Cnt,0) + COALESCE(CounselorCount.Cnt,0) as GT

Baron

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

Re: Need help with query

[Jesse]

COALESCE(CampCount.Cnt,0) + COALESCE(CounselorCount.Cnt,0) as GT


Duuuh.  Why didn't I think of that.  What is MySQL's issue with referring to 
the variables (As assignments, whatever you want to call them)?  I've had 
issues like this in situations like this one, when trying to use them in the 
ORDER BY clause, and other places.


Jesse 



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

Re: Need help with query

[Mark Leith]

Jesse wrote:

COALESCE(CampCount.Cnt,0) + COALESCE(CounselorCount.Cnt,0) as GT


Duuuh.  Why didn't I think of that.  What is MySQL's issue with 
referring to the variables (As assignments, whatever you want to 
call them)?  I've had issues like this in situations like this one, 
when trying to use them in the ORDER BY clause, and other places.


They simply don't 'exist' at that point in time. All of the rows are 
read at one point in time (according to a WHERE clause if applicable), 
then the expressions within the column lists are done, then grouping, 
order by etc., and finally HAVING clauses are done (where one can use a 
column alias, when not available within a WHERE clause).


However, you should be able to use them in an ORDER BY - as they do 
exist at that point - so I'm not sure that issue would have been related 
to this.


Best regards

Mark

--
Mark Leith, Support Engineer
MySQL AB, Worcester, England, www.mysql.com
Are you MySQL certified?  www.mysql.com/certification


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

Re: need a help in Query

[balaraju mandala]

Thank you very much Jorn. Your suggestion helped me.

need a help in Query

[balaraju mandala]

Hi Fello MySQL users,

I am Bala Raju, i have a problem to extract data by writing query, i need u
r help. Please help me.

I have two tables, i am giving the tables as attachement, firs table is
playersloginfo and second one is playerhanddetails.

playersloginfo : This table stores all the loginsession details of the
players, who visited the game.

playerhanddetails: This table stores all the games the player played
during a session.

I need to extract the data from both these tables, i need to display the
player's logintime, logouttime and the totaltime per a session(these are
available in 1 table) and number of games the player played(from 2 table).
Please give some ideas to write this queries.

(I tried in this way to extract data, i simple selected all the columns and
used time difference function, but to find number of games by a player, i
need to use second table(playerhanddetails). I am searching second table by
conditions of user_name, and date between (logindate, logoutdate) and
time between (logintime, logouttime))


playersloginfo:
--
++-+--+-+-+---+
| Field  | Type| Null | Key | Default | Extra |
++-+--+-+-+---+
| user_name  | varchar(20) | YES  | | NULL|   |
| ip | varchar(16) | YES  | | NULL|   |
| action | varchar(15) | YES  | | NULL|   |
| logindate  | date| YES  | | NULL|   |
| logoutdate | date| YES  | | NULL|   |
| logintime  | time| YES  | | NULL|   |
| logouttime | time| YES  | | NULL|   |
| status | varchar(15) | YES  | | NULL|   |
++-+--+-+-+---+

playerhanddetails :
-
++-+--+-+-+---+
| Field  | Type| Null | Key | Default | Extra |
++-+--+-+-+---+
| playername | varchar(20) | YES  | | NULL|   |
| handnumber | bigint(20)  | YES  | | NULL|   |
| date   | date| YES  | | NULL|   |
| time   | time| YES  | | NULL|   |
++-+--+-+-+---+

Re: need a help in Query

[Jørn Dahl-Stamnes]

On Saturday 10 February 2007 12:45, balaraju mandala wrote:
 Hi Fello MySQL users,

 I am Bala Raju, i have a problem to extract data by writing query, i need u
 r help. Please help me.

 I have two tables, i am giving the tables as attachement, firs table is
 playersloginfo and second one is playerhanddetails.

 playersloginfo : This table stores all the loginsession details of the
 players, who visited the game.

 playerhanddetails: This table stores all the games the player played
 during a session.

 I need to extract the data from both these tables, i need to display the
 player's logintime, logouttime and the totaltime per a session(these are
 available in 1 table) and number of games the player played(from 2 table).
 Please give some ideas to write this queries.

 (I tried in this way to extract data, i simple selected all the columns and
 used time difference function, but to find number of games by a player, i
 need to use second table(playerhanddetails). I am searching second table by
 conditions of user_name, and date between (logindate, logoutdate) and
 time between (logintime, logouttime))

You should redesign your table playersloginfo. logindate and logintime should 
be merge into one field:  login DATETIME
The same goes for logoutdate and logouttime: logout DATETIME.

Then you query will be much easier to handle.

You should also change ip from varchar(15) to INT UNSIGNED and use INET_ATON 
to convert a ip of the form a.b.c.d to unsinged int, and INET_NTOA to go back 
to a.b.c.d

-- 
Jørn Dahl-Stamnes
homepage: http://www.dahl-stamnes.net/dahls/

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

Re: need a help in Query

[balaraju mandala]

Hi Jorn,

Thank you for reply, of course i can merge the columns and change the
datatype. But buddy that is not problem here, the problem is extract the
data.

Did u able to understand my language, if not i will explain you again.
Please read the mail again and tell some ways from u r experience. I am
running out of solutions, as i am working on this from last two days.

Re: need a help in Query

[Jørn Dahl-Stamnes]

On Saturday 10 February 2007 19:21, balaraju mandala wrote:
 Hi Jorn,

 Thank you for reply, of course i can merge the columns and change the
 datatype. But buddy that is not problem here, the problem is extract the
 data.

After reading your message once more, I realise that you should do even more 
changes. The username should not be a part of the playersloginfo table.

Consider this:

Table: userinfo
id  mediumint unsigned not null auto_increment,
user_name   varchar(20)

Table: playersloginfo
user_id mediumint unsigned not null comment '--userinfo.id',
ip  logint unsinged,
action  varchar(15),
login   datetime,
logout  datetime,
status  varchar(15)

Table: playershanddetails
playername  mediumint unsigned not null comment '--userinfo.id',
handnumber  bigint(20),
date_time   datetime

Your query might look something like.

select u.user_name,l.login,l.logout,timediff(l.login,l.logout) as totaltime,
count(h.*) as no_of_games from userinfo as u inner join playersloginfo as l on 
(l.user_id=u.id) inner join playershanddetails as h on (h.playername=u.id) 
group by u.id;

Please note: I have not tried this... just a quick suggestion right out of my 
brain... :-)


-- 
Jørn Dahl-Stamnes
homepage: http://www.dahl-stamnes.net/dahls/

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

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

[Jerry Schwartz]

Is a sub-select more efficient than an outer join?

SELECT cust_id FROM customers LEFT JOIN orders on customers.cust_id =
  orders.cust_id WHERE orders.cust_id IS NULL;

Or am I missing something (as usual)?

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341


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

 On Fri, 20 Oct 2006, [EMAIL PROTECTED] wrote:

  hi to all,
  I have table customers (PK cust_id)
  I have table orders (PK order_id, FK cust_id)
 
  I need query that will selecct all customers from
 'customers' they don't
  have any order, there is not their cust_id in 'orders'.
 
  couls somebody help me?
 
  thanks.
 
  -afan

 I think the most explicit way is with a sub select:

 select cust_id from customers where
 cust_id not in
   (select cust_id from orders);

 -w

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

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






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

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

[Waldemar Jankowski]

You're right. A join is often more efficient than a subselect especially 
if you have good indices set up.  I believe the IS NULL will also get 
optimized away in your query. In this example I personally like the 
subselect syntax for it's explicitness if the speed difference is negligible.
For large data sets I would definetly go with your solution of using an 
outer join.


Waldemar

On Mon, 23 Oct 2006, Jerry Schwartz wrote:


Is a sub-select more efficient than an outer join?

SELECT cust_id FROM customers LEFT JOIN orders on customers.cust_id =
 orders.cust_id WHERE orders.cust_id IS NULL;

Or am I missing something (as usual)?

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341



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

On Fri, 20 Oct 2006, [EMAIL PROTECTED] wrote:


hi to all,
I have table customers (PK cust_id)
I have table orders (PK order_id, FK cust_id)

I need query that will selecct all customers from

'customers' they don't

have any order, there is not their cust_id in 'orders'.

couls somebody help me?

thanks.

-afan


I think the most explicit way is with a sub select:

select cust_id from customers where
cust_id not in
(select cust_id from orders);

-w


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

http://lists.mysql.com/[EMAIL PROTECTED]






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










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

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

[afan]

hi to all,
I have table customers (PK cust_id)
I have table orders (PK order_id, FK cust_id)

I need query that will selecct all customers from 'customers' they don't
have any order, there is not their cust_id in 'orders'.

couls somebody help me?

thanks.

-afan

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

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

[Waldemar Jankowski]

On Fri, 20 Oct 2006, [EMAIL PROTECTED] wrote:


hi to all,
I have table customers (PK cust_id)
I have table orders (PK order_id, FK cust_id)

I need query that will selecct all customers from 'customers' they don't
have any order, there is not their cust_id in 'orders'.

couls somebody help me?

thanks.

-afan


I think the most explicit way is with a sub select:

select cust_id from customers where
cust_id not in
(select cust_id from orders);

-w


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





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

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

[afan]

Ok. Just found I gave wrong info. To make my life easier, the person who
created db named cust_id in 'orders' table as SoldTo

[EMAIL PROTECTED]

in this case,
select cust_id from customers
where cust_id not in
   (select Soldto from orders);

will not work

:(






 On Fri, 20 Oct 2006, [EMAIL PROTECTED] wrote:

 hi to all,
 I have table customers (PK cust_id)
 I have table orders (PK order_id, FK cust_id)

 I need query that will selecct all customers from 'customers' they don't
 have any order, there is not their cust_id in 'orders'.

 couls somebody help me?

 thanks.

 -afan

 I think the most explicit way is with a sub select:

 select cust_id from customers where
 cust_id not in
   (select cust_id from orders);

 -w

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




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




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