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

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 

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

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

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

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

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

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]

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]

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/

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]

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]

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]

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]

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

[Waldemar Jankowski]

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


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

[EMAIL PROTECTED]

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

will not work

:(



Maybe I'm not understanding you but as long as Soldto is cust_id just with 
a different column name it will still work.


The subquery will result in a list of values and then the NOT IN clause 
will check if your cust_id is not in that list.


For more info check out:
http://dev.mysql.com/doc/refman/5.0/en/subqueries.html





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

[Visolve DB Team]

Hi,

Try with FULLTEXT search.  Alter the table to make the search columns as 
FULLTEXT columns, with MyISAM engine and retrieve the records using MATCH 
keyword.

Ref:http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

Thanks,
ViSolve DB Team.

- Original Message - 
From: Ed Curtis [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Monday, September 25, 2006 11:47 PM
Subject: Help with query


 
 I'm trying to do a keyword search within a phrase saved in a table.
 
 Here's the query:
 
 SELECT * from closedtickets WHERE
 
 keyphrase LIKE '%$keyword1%'
 
 OR keyphrase LIKE '%$keyword2%'
 
 OR keyphrase LIKE '%$keyword3%'
 
 The problem I'm having is that the query is returning every record in the
 table. I only want it to return the records where the keywords (any
 combination) are contained within 'keyphrase' Any ideas?
 
 Thanks,
 
 Ed
 
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Help for query

[Xiaobo Chen]

Yes, you're right, Jerry.

Thing is that I can NOT use 'min_t' in 2) query, but have to plug in the
actual value from 1) query. This isn't what I want.

If I tried this:

1) create temporary table tbl_min as (select min(abs(Ta - timestamp)) as
min_t from B;

so tbl_min is like:

min_t   |

0.00012 |
-

2) then, I say:

  select min_t, * from tbl_min, B where (timestamp + min_t) = Ta or
(timestamp - min_t) = Ta;

then I will get the record as:

--
min_t | other fields
---

you see, the field 'min_t' isn't necessary there.

Wait, if I say:

select * from tbl_min, B where (timestamp + min_t) = Ta or (timestamp -
min_t) = Ta;

Yes, I got the result without 'min_t' in it.

But this solution still used more than 1 query and used a temporary table
to hold the imtermidiate value.

Any better solution?

Thanks.



 Your first query returns a scalar (single value), right? You can put its
 value into a variable, and use that in the second query. It's not exactly
 what you wanted, but it will work without external programming.


 Regards,

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

 860.674.8796 / FAX: 860.674.8341

 -Original Message-
 From: Xiaobo Chen [mailto:[EMAIL PROTECTED]
 Sent: Tuesday, September 26, 2006 10:09 AM
 To: [EMAIL PROTECTED]
 Cc: mysql@lists.mysql.com
 Subject: Re: Help for query

 I found if I divided into 2 steps, I will find the record in table B:

 Ta - the given timestamp from table A;

 1) select min(abs(Ta - timestamp)) as min_t from B;

 2) select * from B where (timestamp + min_t = Ta) or (timestamp - min_t =
 Ta);

 But, how can I make these 2 steps into 1 query?

 Thanks.

 Xiaobo

 Hi, all

 The version of Mysql is 4.0.15 on Solaris 9. I am in such a situation.
 There are 2 tables something like these:

 Table A:
 --
 location|timestamp | other fields
 ---


 Table B

 
 location|timestamp | other fields
 -

 (location. timestamp) make the 'primary key' for each table.

 The task is that: the locations are the same, given a timestamp from
 table
 A, I need to find the record in table B which has the closest timestamp
 as
 the given one in table A.

 I checked the book and research the internet, but didn't find a hint or
 solution yet. Could any one give me a hint please?

 Thanks in advance.

 Xiaobo


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





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

Re: Help for query

[Xiaobo Chen]

I found if I divided into 2 steps, I will find the record in table B:

Ta - the given timestamp from table A;

1) select min(abs(Ta - timestamp)) as min_t from B;

2) select * from B where (timestamp + min_t = Ta) or (timestamp - min_t =
Ta);

But, how can I make these 2 steps into 1 query?

Thanks.

Xiaobo

 Hi, all

 The version of Mysql is 4.0.15 on Solaris 9. I am in such a situation.
 There are 2 tables something like these:

 Table A:
 --
 location|timestamp | other fields
 ---


 Table B

 
 location|timestamp | other fields
 -

 (location. timestamp) make the 'primary key' for each table.

 The task is that: the locations are the same, given a timestamp from table
 A, I need to find the record in table B which has the closest timestamp as
 the given one in table A.

 I checked the book and research the internet, but didn't find a hint or
 solution yet. Could any one give me a hint please?

 Thanks in advance.

 Xiaobo


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

[Jerry Schwartz]

Your first query returns a scalar (single value), right? You can put its
value into a variable, and use that in the second query. It's not exactly
what you wanted, but it will work without external programming.


Regards,

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

860.674.8796 / FAX: 860.674.8341

-Original Message-
From: Xiaobo Chen [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 26, 2006 10:09 AM
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: Re: Help for query

I found if I divided into 2 steps, I will find the record in table B:

Ta - the given timestamp from table A;

1) select min(abs(Ta - timestamp)) as min_t from B;

2) select * from B where (timestamp + min_t = Ta) or (timestamp - min_t =
Ta);

But, how can I make these 2 steps into 1 query?

Thanks.

Xiaobo

 Hi, all

 The version of Mysql is 4.0.15 on Solaris 9. I am in such a situation.
 There are 2 tables something like these:

 Table A:
 --
 location|timestamp | other fields
 ---


 Table B

 
 location|timestamp | other fields
 -

 (location. timestamp) make the 'primary key' for each table.

 The task is that: the locations are the same, given a timestamp from table
 A, I need to find the record in table B which has the closest timestamp as
 the given one in table A.

 I checked the book and research the internet, but didn't find a hint or
 solution yet. Could any one give me a hint please?

 Thanks in advance.

 Xiaobo


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

[Jerry Schwartz]

Have you dumped out your variables to make sure none of them is a
zero-length string? That would surely cause your problem.


Regards,

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

860.674.8796 / FAX: 860.674.8341

-Original Message-
From: Ed Curtis [mailto:[EMAIL PROTECTED]
Sent: Monday, September 25, 2006 2:18 PM
To: mysql@lists.mysql.com
Subject: Help with query


 I'm trying to do a keyword search within a phrase saved in a table.

 Here's the query:

 SELECT * from closedtickets WHERE

 keyphrase LIKE '%$keyword1%'

 OR keyphrase LIKE '%$keyword2%'

 OR keyphrase LIKE '%$keyword3%'

 The problem I'm having is that the query is returning every record in the
table. I only want it to return the records where the keywords (any
combination) are contained within 'keyphrase' Any ideas?

Thanks,

Ed



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

[Douglas Sims]

I think you are trying to use a regular expression in the like  
phrase.  I wasn't aware that MS SQL can do regular expressions at  
all?  I've been using 2000 - perhaps the newer version can?


In MySQL, instead of saying:
LA.LastName LIKE '[A-E]%'
try this:
LA.LastName REGEXP '^[A-E]'

You can read more about MySQL regular expressions here: http:// 
mysql.com/doc/refman/5.0/en/regexp.html


Also, are you using spellcheck with Outlook Express?  Because if so,  
Microsoft keeps trying to rename MySQL to be Myself.  I think  
they want to take over everything. :-)


Good luck.

Douglas Sims
[EMAIL PROTECTED]



On Aug 21, 2006, at 10:57 AM, Jesse wrote:

I have the following query which works in MS SQL Server, but I'm  
sure the syntax is different for Myself.  I'm sure it's in the area  
of the LIKE clause.  I don't know how to do this with Myself.   
Can anyone point me in the right direction?:


SELECT LA.FirstName,LA.LastName,LA.EMailAddress, LA.UserName,  
U.Password

FROM LocalAdvisors LA
   JOIN Users U ON U.UserName=LA.UserName
WHERE EMailAddress  '' AND EMailAddress IS NOT NULL
   AND (EMailPermission is NULL or EMailPermission=1) AND  
LA.LastName LIKE '[A-E]%'

ORDER BY LastName,FirstName

Thanks,
Jesse

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

Re: Help with query

[Jesse]

Yes, evidentially, MS SQL Server does allow regular expressions.  I've used 
this for a long time, and it's working just fine.  I am using version 2000 
since I started working with MS SQL Server, so maybe it is a new feature.

Your suggestion appears to have resolved the problem. I really appreciate your 
help.

Jesse
  - Original Message - 
  From: Douglas Sims 
  To: Jesse 
  Cc: MySQL List 
  Sent: Monday, August 21, 2006 12:07 PM
  Subject: Re: Help with query




  I think you are trying to use a regular expression in the like phrase.  I 
wasn't aware that MS SQL can do regular expressions at all?  I've been using 
2000 - perhaps the newer version can?


  In MySQL, instead of saying:
  LA.LastName LIKE '[A-E]%'
  try this:
  LA.LastName REGEXP '^[A-E]'


  You can read more about MySQL regular expressions here: 
http://mysql.com/doc/refman/5.0/en/regexp.html


  Also, are you using spellcheck with Outlook Express?  Because if so, 
Microsoft keeps trying to rename MySQL to be Myself.  I think they want to 
take over everything. :-)


  Good luck.


  Douglas Sims
  [EMAIL PROTECTED]






  On Aug 21, 2006, at 10:57 AM, Jesse wrote:


I have the following query which works in MS SQL Server, but I'm sure the 
syntax is different for Myself.  I'm sure it's in the area of the LIKE 
clause.  I don't know how to do this with Myself.  Can anyone point me in the 
right direction?:


SELECT LA.FirstName,LA.LastName,LA.EMailAddress, LA.UserName, U.Password
FROM LocalAdvisors LA
   JOIN Users U ON U.UserName=LA.UserName
WHERE EMailAddress  '' AND EMailAddress IS NOT NULL
   AND (EMailPermission is NULL or EMailPermission=1) AND LA.LastName 
LIKE '[A-E]%'
ORDER BY LastName,FirstName


Thanks,
Jesse 


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

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

[Dan]

Thanks Peter,

That looks pretty good to me.  I never would have figured that out on  
my own.


Dan T

On Jun 1, 2006, at 4:06 PM, Peter Brawley wrote:


Dan,

I want to get a particular users 'rank' or row number from the query.

SELECT 1+COUNT(*) AS Rank
FROM results r1
INNER JOIN results r2 ON r1.pointsr2.points
WHERE r1.user='Steve';

PB

-

Dan wrote:

I have a regular query lets say:

SELECT user, points FROM results ORDER BY points DESC

so I get:

userpoints
---
john23
steve17
bill14

From this I want to get a particular users 'rank' or row number  
from the query.  I was hoping for a single line elegant query for  
this.


I thought of this:

SET @i = O;
SELECT @i:= @i + 1 as row, SUM(IF(user = 'steve',@i,0)) as rank,  
user FROM points ORDER BY points DESC GROUP BY id;


but I get:

rankuser
---
0john
2steve
0bill

which does not really help me

Any ideas?  I tried the manual, Google and the list with no sucsess.

Thanks,

Dan T

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




--No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.8.0/353 - Release Date:  
5/31/2006






--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.8.0/353 - Release Date:  
5/31/2006





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

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

[Michael Stassen]

Dan wrote:

I have a regular query lets say:


Better to show the real query, rather than a simplified version.  Simplified 
requests get you simplified answers.  What seems like a simplification to you, 
may in fact hide a crucial piece of information, thus preventing anyone from 
giving you a solution to your real problem.  We need the real query to give a 
real answer.



SELECT user, points FROM results ORDER BY points DESC

so I get:

userpoints
---
john23
steve   17
bill14

 From this I want to get a particular users 'rank' or row number from  
the query.  I was hoping for a single line elegant query for this.


I thought of this:

SET @i = O;
SELECT @i:= @i + 1 as row, SUM(IF(user = 'steve',@i,0)) as rank, user  
FROM points ORDER BY points DESC GROUP BY id;


That's not your real query, as the table name seems to have changed from 
results to points.



but I get:

rankuser
---
0john
2steve
0bill


I don't think so.  Your select has a column named row, but it's missing in your 
output.


From your description of the problem, I cannot tell why you made the second 
query so complicated.  What is wrong with the following, simpler query?


  SET @i = 0;

  SELECT @i:= @i + 1 AS rank, user
  FROM results
  ORDER BY points DESC;

It should give

  rankuser
  ---
  1   john
  2   steve
  3   bill

as the results.  Isn't that what you want?


which does not really help me

Any ideas?  I tried the manual, Google and the list with no sucsess.

Thanks,

Dan T


If you show us what you want, instead of something that isn't what you want, 
someone may be able to tell you how to get it.


Michael


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

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

[Peter Brawley]

Dan,

I want to get a particular users 'rank' or row number from the query.

SELECT 1+COUNT(*) AS Rank
FROM results r1
INNER JOIN results r2 ON r1.pointsr2.points
WHERE r1.user='Steve';

PB

-

Dan wrote:

I have a regular query lets say:

SELECT user, points FROM results ORDER BY points DESC

so I get:

userpoints
---
john23
steve17
bill14

From this I want to get a particular users 'rank' or row number from 
the query.  I was hoping for a single line elegant query for this.


I thought of this:

SET @i = O;
SELECT @i:= @i + 1 as row, SUM(IF(user = 'steve',@i,0)) as rank, user 
FROM points ORDER BY points DESC GROUP BY id;


but I get:

rankuser
---
0john
2steve
0bill

which does not really help me

Any ideas?  I tried the manual, Google and the list with no sucsess.

Thanks,

Dan T

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




--No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.8.0/353 - Release Date: 5/31/2006





--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.8.0/353 - Release Date: 5/31/2006


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

[Peter Brawley]

Kishore,

 We use Vbulletin for our message boards, and I have a
query which
takes approximately 1.2 - 1.5 seconds to execute, on a table
('thread')
having around 130,000 rows, it is as follows

SELECT threadid
FROM thread 
LEFT JOIN deletionlog
ON ( thread.threadid = deletionlog.primaryid AND TYPE = 'thread' )
WHERE forumid = 98
 AND sticky = 0
 AND visible = 1
 AND deletionlog.primaryid IS NULL
ORDER BY sticky DESC , lastpost DESC
LIMIT 0 , 15

Your formatting of EXPLAIN output is a bit hard to read, but did you
try indexes on 
(sticky,lastpost) and (forumid,sticky,visible)? And depending on how
big deletionlog is, 
you might move ...
 AND deletionlog.primaryid IS NULL
from the WHERE clause to the ON clause, to reduce the number of rows
the query engine 
has to write to its temp table.

PB

-

Kishore Jalleda wrote:

  Hi All,
 We use Vbulletin for our message boards, and I have a query which
takes approximately 1.2 - 1.5 seconds to execute, on a table ('thread')
having around 130,000 rows, it is as follows

SELECT threadid
FROM thread AS thread
LEFT JOIN deletionlog AS deletionlog ON ( thread.threadid = deletionlog.
primaryid
AND TYPE = 'thread' )
WHERE forumid =98
AND sticky =0
AND visible =1
AND deletionlog.primaryid IS NULL
ORDER BY sticky DESC , lastpost DESC
LIMIT 0 , 15

There is an index on the field 'lastpost' ( Type: INT(10) ) , also Explain
Select gives me this..

   id
 select_type
 table
 type
 possible_keys
 key
 key_len
 ref
 rows
 Extra
 1
 SIMPLE
 thread
 index
 *NULL*
 lastpost
 4
 *NULL*
 112783
 Using where
 1
 SIMPLE
 deletionlog
 eq_ref
 PRIMARY
 PRIMARY
 5
 foxbox16.thread.threadid,const
 1
 Using where; Using index; Not exists


I am no SQL guru so can somebody kindly help me to optimize this query so
that it could run faster , as during peak times this slows the DB down a
lot..

Kishore Jalleda

  
  

No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 2/24/2006
  



No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 2/24/2006


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

Re: help with query

[Peter Brawley]

how to achieve this?
select table1.*, table2.*, table3.*, sum(table2.field3), sum(table2.field4)
from table1, table2, table3 where table1.field1 = table2.field1 and
table2.field1 = table3.field1

Your question as formulated has no answer. If you query aggregate
values like Sum on a table, you will get back meaningful individual row
values _only_ for fields which you Group By, so "table2.* makes no
sense here.

So if the joins are to be on field1, and if you want to Group your Sums
also By field1, your query would look like this:

SELECT table1.*, SUM(table2.field3), SUM(table2.field4)
FROM table1 
INNER JOIN table2 USING (field1)
INNER JOIN table3 USING (field1)
GROUP BY table.field1;

PB

-

xtcsuk wrote:

  3 tables:

table1 - table2 (one to many)
table2 - table3 (one to one) [designed like this]

how to achieve this?
select table1.*, table2.*, table3.*, sum(table2.field3), sum(table2.field4)
from table1, table2, table3 where table1.field1 = table2.field1 and
table2.field1 = table3.field1

regards

  
  

No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.11/264 - Release Date: 2/17/2006
  



No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.11/264 - Release Date: 2/17/2006


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

[سيد هادی راستگوی حقی]

Dear Reynier,

You can use JOIN on your both,
The JOIN have to run on the same feilds i.e IDA.

SELECT * FROM carro_de_compras LEFT JOIN os_articulo ON carro_de_compras.IDA
= os_articulo.IDA

This query returns all your users with their articles if any and you can
iterate on it.

but one note:
Use INDEX on both tables. You may encounter problems when your rows grow up.

about the UPDATE query:

UPDATE table SET value=value+1 WHERE id='1'

is enough, use that.


On 2/7/06, Reynier Perez Mira [EMAIL PROTECTED] wrote:

 Hi:
 I'm develop a simple shopping cart. I have this two tables:
 carro_de_compras
 --
 IDU int(11) NOT NULL
 IDA int(11) NOT NULL
 CantidadDeArticulos int(11) NOT NULL

 os_articulo
 --
 IDA int(11) NOT NULL auto_increment,
 IDC int(11) NOT NULL default '0',
 ANombre varchar(200) NOT NULL default '',

ADescripcion text,

ACantidad int(11) NOT NULL default '0',

AImagen varchar(50) default NULL,

IDU int(11) NOT NULL default '0',

APrecio float(6,2) default NULL,

KEY AI_IDA (`IDA`)

 Before ask let me explain some things. As you can see in the tables I have
 the same field IDU in both tables. So in first(table carro_de_compras) it
 means is user ID loged on ecommerce system, the second is the user ID who
 upload articles for sale. Something like eBay in wich you can sale and buy
 at every time. The arrive the point in wich I need to optimize queries:

 PHP Code:
 -
 $sql = mysql_query(SELECT * FROM carro_de_compras);
 $sresultado = mysql_fetch_assoc($sql);

 $query = mysql_query(SELECT * FROM os_articulo WHERE
 (IDA='.$sresultado['IDA'].'));
 while ($record = mysql_fetch_assoc($query)) {
 $productos[] = $record;
 }

 The question for this problem is: exists any way to optimize this query
 and leave only in one line? I read in MySQL doc about it and found some
 about JOIN but I can't understand how it works. Maybe because I'm cuban and
 not understand english as well as I want.

 The other questions is how to add some values to a field. For example:
 $sql = mysql_query(UPDATE table SET value=value+1 WHERE id='1');

 For do this query I do this:
 $sql = mysql_query(SELECT value FROM table WHERE id='1');
 $result = mysql_query($sql);
 $update = mysql_query(UPDATE table SET (value='.$result['value'].' + 1)
 WHERE id='1');

 So is possible to optimize this query?


 Regards
 ReynierPM
 4to. año Ing. Informática
 Usuario registrado de Linux: #310201
 *
 El programador superhéroe aprende de compartir sus conocimientos.
 Es el referente de sus compañeros. Todo el mundo va a preguntarle y él,
 secretamente, lo fomenta porque es así como adquiere su legendaria
 sabiduría: escuchando ayudando a los demás...

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




--
Sincerely,
Hadi Rastgou
a href=http://www.spreadfirefox.com/?q=affiliatesamp;id=0amp;t=1;Get
Firefox!/a

Re: Help with query

[SGreen]

Jay Paulson [EMAIL PROTECTED] wrote on 01/25/2006 10:09:36 AM:

 From the result set below I have 22 rows and the only difference is the
 date.  I was wondering if there was a way to get all of these results 
using
 GROUP BY instead of having to use LIMIT??
 
 As this table grows I'm going to want to get a LIMIT 0,77 but would like 
it
 to be grouped by date.  So basically I want 7 groups of results and in 
each
 group there should be 11 rows.
 
 Any idea how to do this?  Should it be separate queries or should I just
 break down and use LIMIT?
 
 thanks
 
 My attempt was, but doesn't really work.
 
 SELECT region_id, date, page_hit, score
 FROM statistics
 WHERE date = '2006-01-29'
 GROUP BY region_id, date
 ORDER BY date DESC;
 
 +---++--+---+
 | region_id | date   | page_hit | score |
 +---++--+---+
 | CE| 2006-01-23 |  978 |  6.72 |
 | FL| 2006-01-23 |  558 |  2.75 |
 | MA| 2006-01-23 |  312 |  0.09 |
 | MW| 2006-01-23 |  478 |  0.25 |
 | NA| 2006-01-23 | 4846 |  4.85 |
 | NC| 2006-01-23 | 3281 |  3.03 |
 | PN| 2006-01-23 | 3281 |  1.22 |
 | SW| 2006-01-23 | 1964 |  1.05 |
 | RM| 2006-01-23 | 1964 |  2.80 |
 | SO| 2006-01-23 |  173 |  0.11 |
 | SP| 2006-01-23 |  163 |  0.07 |
 | CE| 2006-01-29 |  978 |  6.72 |
 | FL| 2006-01-29 |  558 |  2.75 |
 | MA| 2006-01-29 |  312 |  0.09 |
 | MW| 2006-01-29 |  478 |  0.25 |
 | NA| 2006-01-29 | 4846 |  4.85 |
 | NC| 2006-01-29 | 3281 |  3.03 |
 | PN| 2006-01-29 | 3281 |  1.22 |
 | SW| 2006-01-29 | 1964 |  1.05 |
 | RM| 2006-01-29 | 1964 |  2.80 |
 | SO| 2006-01-29 |  173 |  0.11 |
 | SP| 2006-01-29 |  163 |  0.07 |
 +---++--+---+
 

Can you please explain what you mean by 7 groups of results and in each 
group there should be 11 rows. Eleven times seven is seventy-seven, the 
number of rows you already plan to be getting. I see two groups of 
results, each group consisting of 7 rows.

What would you rather the output look like other than what you showed us? 
If you want seven separate sets of results, you have to run seven separate 
queries. All databases work that way. Maybe if you described your problem 
and your data and your data structures in greater detail, one of us may 
have a solution different than the one you are currently working on. 

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: help with query

[SGreen]

[EMAIL PROTECTED] wrote on 10/22/2005 06:24:07 PM:

 
 
 I have a table of votes with four fields, a primary key, and userID,
 that are just there for tracking purposes.  But then I have 
 questionID and vote fields.  For each question, a user could pick a 
 number of it's importance from 1 to 5.  That number is what gets 
 stored in the vote field. 
 
 So, now I want to tabulate the votes.  I thought I could just do a 
 count with a group by but that isn't working.  What I want to do is 
 for each question, get a count of how many ones, how many twos, how 
 many threes, etc.
 
 Advice?
 
 --ja
 
 

ja,

What you are looking for is called a crosstab query or a pivot table 
(lot's of literature out there on both of these subjects).  Here is a 
general pattern you should be able to easily adapt for your needs:

SELECT questionID
, SUM(if(importance=1,1,0)) as votes_for_1
, SUM(if(importance=2,1,0)) as votes_for_2
, SUM(if(importance=3,1,0)) as votes_for_3
, SUM(if(importance=4,1,0)) as votes_for_4
, SUM(if(importance=5,1,0)) as votes_for_5
FROM vote_table
GROUP BY questionID;

Alternative paterns can use the COUNT() aggregate function (depending on 
your data and the choices you want to make about dividing your data into 
columns).

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: help with query

[Peter Brawley]

ja,

Your question is a little cryptic. If a questionid column value 
identifies a particular question, and a vote column value contains a 
user's vote (1,2,3, c), can't you just write...


SELECT questionid, vote,count(vote)
FROM ...
GROUP BY questionid, vote

PB

-

[EMAIL PROTECTED] wrote:

I have a table of votes with four fields, a primary key, and userID, that are just there for tracking purposes.  But then I have questionID and vote fields.  For each question, a user could pick a number of it's importance from 1 to 5.  That number is what gets stored in the vote field. 


So, now I want to tabulate the votes.  I thought I could just do a count with a 
group by but that isn't working.  What I want to do is for each question, get a 
count of how many ones, how many twos, how many threes, etc.

Advice?

--ja


 




--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.12.4/146 - Release Date: 10/21/2005


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

Re: Help with query

[Dušan Pavlica]

Shawn,

  - Original Message - 
  From: [EMAIL PROTECTED] 
  To: Dušan Pavlica 
  Cc: Michael Stassen ; list mysql 
  Sent: Wednesday, October 12, 2005 4:45 PM
  Subject: Re: Help with query



  Dušan Pavlica [EMAIL PROTECTED] wrote on 10/12/2005 10:00:53 AM:

   Thanks, Michal, for your help.
   Your query works as I need.
   I tried to use same syntax as in MS Access, but results are for some reason 
   different for this query. I'm working on application which should be able 
to 
   connect to MySQL or to MSAccess (users' choice) and I didn't want to write 
   querries for each DB system separately. Now I see that I will have to.
   
   Dusan
   
   - Original Message - 
   From: Michael Stassen [EMAIL PROTECTED]
   To: Dušan Pavlica [EMAIL PROTECTED]
   Cc: list mysql mysql@lists.mysql.com
   Sent: Wednesday, October 12, 2005 2:54 PM
   Subject: Re: Help with query
   
   
Dušan Pavlica wrote:
Hello,
   
could someone help me please to construct correct query or tell me what 
I'm doing wrong?
  snip
Kind regards,
Dusan Pavlica
   
  snip
In any case, does this do what you want?
   
  SELECT p.Description, p.ID, pp.Product_ID, pc.Code, pp.StartDate
  FROM products_prices pp
  JOIN products p ON p.ID = pp.Product_ID
  LEFT JOIN products_codes pc ON p.ID = pc.Product_ID AND pc.CodeType = 1
  WHERE StartDate  Now()
  ORDER BY p.ID, pp.StartDate DESC;
   
Michael
   
   

  I don't think you will need to change anything. MS Access should be able to 
work with Michael's query just fine. Just because the Query Builder in Access 
(I despise the SQL that comes out of that tool) always nests its JOINs doesn't 
mean that MS Access can't use un-nested joins. Give it a shot, you may be 
surprised. 

  Shawn Green
  Database Administrator
  Unimin Corporation - Spruce Pine 

Unfortunately, I'm afraid that MS Access is not able to work with un-nested 
joins. I tried Michael's solution also with different changes but Access keeps 
complaining about syntax error and on MSDN help and in one book about Access I 
have I saw that they use only syntax with nested joins. I don't know if it's 
matter but I use ADO for accessing MS Access database.

Dusan

Re: Help with query

[Michael Stassen]

Dušan Pavlica wrote:

Hello,

could someone help me please to construct correct query or tell me what I'm 
doing wrong?

I have three tables:
table products
table products_codes where some products could have assigned another additional codes 
table products_prices

I want to get all rows from product_prices listed  with product Description and 
Code for particular CodeType

And here is the create script, sample data and the query:

CREATE TABLE  `products` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `Description` varchar(50) NOT NULL default '',
  `Units` varchar(10) NOT NULL default '',
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB;

INSERT INTO products VALUES(NULL, Product 1, lt);
INSERT INTO products VALUES(NULL, Product 2, lt);
INSERT INTO products VALUES(NULL, Product 3, lt);

CREATE TABLE  `products_codes` (
  `Product_ID` int(10) unsigned NOT NULL default '0',
  `Code` varchar(50) NOT NULL default '',
  `CodeType` tinyint NOT NULL default '',
  PRIMARY KEY  (`Product_ID`,`CodeType`)
) ENGINE=InnoDB;

INSERT INTO products_codes VALUES(1, ABC, 1);
INSERT INTO products_codes VALUES(2, XYZ, 1);

CREATE TABLE  `products_prices` (
  `Product_ID` int(10) unsigned NOT NULL default '0',
  `StartDate` datetime NOT NULL default '-00-00 00:00:00',
  `Price` double NOT NULL default '0',
  PRIMARY KEY  (`Product_ID`,`StartDate`)
) ENGINE=InnoDB;

INSERT INTO products_prices VALUES(1, '2005090100', 20);
INSERT INTO products_prices VALUES(1, '2005100100', 25);
INSERT INTO products_prices VALUES(1, '2005110100', 30);
INSERT INTO products_prices VALUES(2, '2005100100', 15);
INSERT INTO products_prices VALUES(3, '2005100100', 10);

SELECT p.Description, p.ID, pp.Product_ID, pc.Code, pp.StartDate FROM 
products_prices pp
INNER JOIN (products p
LEFT JOIN products_codes pc ON p.ID = pc.Product_ID AND pc.CodeType = 1)
ON p.ID = pp.Product_ID
where StartDate  Now()
ORDER BY p.ID, pp.StartDate desc

Here is the result of the query:
Description,ID,Product_ID,Code,StartDate

Product 1,1,3,NULL,2005-10-01 00:00:00
Product 1,1,1,ABC,2005-10-01 00:00:00
Product 1,1,2,NULL,2005-10-01 00:00:00
Product 1,1,1,ABC,2005-09-01 00:00:00
Product 2,2,2,XYZ,2005-10-01 00:00:00
Product 2,2,3,NULL,2005-10-01 00:00:00
Product 2,2,1,NULL,2005-10-01 00:00:00
Product 2,2,1,NULL,2005-09-01 00:00:00
Product 3,3,2,NULL,2005-10-01 00:00:00
Product 3,3,3,NULL,2005-10-01 00:00:00
Product 3,3,1,NULL,2005-10-01 00:00:00
Product 3,3,1,NULL,2005-09-01 00:00:00

I don't know why this query returns also rows where p.ID != pp.ProductID


And another thing. If I remove from selected columns pp.Product_ID query 
returns error:
Column 'Product_ID' in field list is ambiguous (ErrorNr. 1052). Why??

MySQL 4.1.14, WinXP

Thanks a lot in advance for any help

Kind regards,
Dusan Pavlica


I haven't really tried to figure out what mysql is doing with your query, 
but perhaps these lines from the manual 
http://dev.mysql.com/doc/mysql/en/join.html are relevant:


  In versions of MySQL prior to 5.0.1, parentheses in table_references were
  just omitted and all join operations were grouped to the left. In general,
  parentheses can be ignored in join expressions containing only inner join
  operations. As of 5.0.1, nested joins are allowed (see Section 7.2.10,
  “How MySQL Optimizes Nested Joins”).

In any case, does this do what you want?

  SELECT p.Description, p.ID, pp.Product_ID, pc.Code, pp.StartDate
  FROM products_prices pp
  JOIN products p ON p.ID = pp.Product_ID
  LEFT JOIN products_codes pc ON p.ID = pc.Product_ID AND pc.CodeType = 1
  WHERE StartDate  Now()
  ORDER BY p.ID, pp.StartDate DESC;

Michael


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

Re: Help with query

[Dušan Pavlica]

Thanks, Michal, for your help.
Your query works as I need.
I tried to use same syntax as in MS Access, but results are for some reason 
different for this query. I'm working on application which should be able to 
connect to MySQL or to MSAccess (users' choice) and I didn't want to write 
querries for each DB system separately. Now I see that I will have to.


Dusan



- Original Message - 
From: Michael Stassen [EMAIL PROTECTED]

To: Dušan Pavlica [EMAIL PROTECTED]
Cc: list mysql mysql@lists.mysql.com
Sent: Wednesday, October 12, 2005 2:54 PM
Subject: Re: Help with query



Dušan Pavlica wrote:

Hello,

could someone help me please to construct correct query or tell me what 
I'm doing wrong?


I have three tables:
table products
table products_codes where some products could have assigned another 
additional codes table products_prices
I want to get all rows from product_prices listed  with product 
Description and Code for particular CodeType


And here is the create script, sample data and the query:

CREATE TABLE  `products` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `Description` varchar(50) NOT NULL default '',
  `Units` varchar(10) NOT NULL default '',
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB;

INSERT INTO products VALUES(NULL, Product 1, lt);
INSERT INTO products VALUES(NULL, Product 2, lt);
INSERT INTO products VALUES(NULL, Product 3, lt);

CREATE TABLE  `products_codes` (
  `Product_ID` int(10) unsigned NOT NULL default '0',
  `Code` varchar(50) NOT NULL default '',
  `CodeType` tinyint NOT NULL default '',
  PRIMARY KEY  (`Product_ID`,`CodeType`)
) ENGINE=InnoDB;

INSERT INTO products_codes VALUES(1, ABC, 1);
INSERT INTO products_codes VALUES(2, XYZ, 1);

CREATE TABLE  `products_prices` (
  `Product_ID` int(10) unsigned NOT NULL default '0',
  `StartDate` datetime NOT NULL default '-00-00 00:00:00',
  `Price` double NOT NULL default '0',
  PRIMARY KEY  (`Product_ID`,`StartDate`)
) ENGINE=InnoDB;

INSERT INTO products_prices VALUES(1, '2005090100', 20);
INSERT INTO products_prices VALUES(1, '2005100100', 25);
INSERT INTO products_prices VALUES(1, '2005110100', 30);
INSERT INTO products_prices VALUES(2, '2005100100', 15);
INSERT INTO products_prices VALUES(3, '2005100100', 10);

SELECT p.Description, p.ID, pp.Product_ID, pc.Code, pp.StartDate FROM 
products_prices pp

INNER JOIN (products p
LEFT JOIN products_codes pc ON p.ID = pc.Product_ID AND pc.CodeType = 
1)

ON p.ID = pp.Product_ID
where StartDate  Now()
ORDER BY p.ID, pp.StartDate desc

Here is the result of the query:
Description,ID,Product_ID,Code,StartDate

Product 1,1,3,NULL,2005-10-01 00:00:00
Product 1,1,1,ABC,2005-10-01 00:00:00
Product 1,1,2,NULL,2005-10-01 00:00:00
Product 1,1,1,ABC,2005-09-01 00:00:00
Product 2,2,2,XYZ,2005-10-01 00:00:00
Product 2,2,3,NULL,2005-10-01 00:00:00
Product 2,2,1,NULL,2005-10-01 00:00:00
Product 2,2,1,NULL,2005-09-01 00:00:00
Product 3,3,2,NULL,2005-10-01 00:00:00
Product 3,3,3,NULL,2005-10-01 00:00:00
Product 3,3,1,NULL,2005-10-01 00:00:00
Product 3,3,1,NULL,2005-09-01 00:00:00

I don't know why this query returns also rows where p.ID != pp.ProductID


And another thing. If I remove from selected columns pp.Product_ID query 
returns error:

Column 'Product_ID' in field list is ambiguous (ErrorNr. 1052). Why??

MySQL 4.1.14, WinXP

Thanks a lot in advance for any help

Kind regards,
Dusan Pavlica


I haven't really tried to figure out what mysql is doing with your query, 
but perhaps these lines from the manual 
http://dev.mysql.com/doc/mysql/en/join.html are relevant:


  In versions of MySQL prior to 5.0.1, parentheses in table_references 
were
  just omitted and all join operations were grouped to the left. In 
general,
  parentheses can be ignored in join expressions containing only inner 
join

  operations. As of 5.0.1, nested joins are allowed (see Section 7.2.10,
  “How MySQL Optimizes Nested Joins”).

In any case, does this do what you want?

  SELECT p.Description, p.ID, pp.Product_ID, pc.Code, pp.StartDate
  FROM products_prices pp
  JOIN products p ON p.ID = pp.Product_ID
  LEFT JOIN products_codes pc ON p.ID = pc.Product_ID AND pc.CodeType = 1
  WHERE StartDate  Now()
  ORDER BY p.ID, pp.StartDate DESC;

Michael


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

[SGreen]

Dušan Pavlica [EMAIL PROTECTED] wrote on 10/12/2005 10:00:53 AM:

 Thanks, Michal, for your help.
 Your query works as I need.
 I tried to use same syntax as in MS Access, but results are for some 
reason 
 different for this query. I'm working on application which should be 
able to 
 connect to MySQL or to MSAccess (users' choice) and I didn't want to 
write 
 querries for each DB system separately. Now I see that I will have to.
 
 Dusan
 
 - Original Message - 
 From: Michael Stassen [EMAIL PROTECTED]
 To: Dušan Pavlica [EMAIL PROTECTED]
 Cc: list mysql mysql@lists.mysql.com
 Sent: Wednesday, October 12, 2005 2:54 PM
 Subject: Re: Help with query
 
 
  Dušan Pavlica wrote:
  Hello,
 
  could someone help me please to construct correct query or tell me 
what 
  I'm doing wrong?
snip
  Kind regards,
  Dusan Pavlica
 
snip
  In any case, does this do what you want?
 
SELECT p.Description, p.ID, pp.Product_ID, pc.Code, pp.StartDate
FROM products_prices pp
JOIN products p ON p.ID = pp.Product_ID
LEFT JOIN products_codes pc ON p.ID = pc.Product_ID AND pc.CodeType 
= 1
WHERE StartDate  Now()
ORDER BY p.ID, pp.StartDate DESC;
 
  Michael
 
 

I don't think you will need to change anything. MS Access should be able 
to work with Michael's query just fine. Just because the Query Builder in 
Access (I despise the SQL that comes out of that tool) always nests its 
JOINs doesn't mean that MS Access can't use un-nested joins. Give it a 
shot, you may be surprised.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: Help with query using IN()

[Felix Geerinckx]

On 04/10/2005, Jasper Bryant-Greene wrote:

 Kishore Jalleda wrote:
  Could you kindly advice if this query can be made to run faster
  SELECT title, template
  FROM template
  WHERE templateid IN
  (608,2072,2073,1888,1898,1895,1897,1894,1896,1901,1903,1889,1900,189
  0,1891,1902,1904,2104,624,625,2152,2212,1985,1996,614,1964,2103,2106
  ,2113,1982,1983,1984,2074,623,2117,2124,2123,2122,2119,2121); There
  is an index on templateid also this query takes ~ 0.04xx seconds to
  run I would like it to be in 0.00xx range explain select gives this
  id select_type table type possible_keys key key_len ref rows Extra
  1 SIMPLE template range PRIMARY PRIMARY 4 NULL 40 Using where
 
 It's using a primary key and only examining the 40 rows which you
 asked for, so that's about as optimised as you'll get for that query.
 You could always make the actual server faster...

If your template table contains many columns in addition to templateid,
title and template, and title and template are not TEXT columns, you
can consider a covering index on templateid, title and template:

ALTER TABLE template ADD UNIQUE (templateid, title, template);


-- 
felix

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

Re: Help with query using IN()

[Jasper Bryant-Greene]

Kishore Jalleda wrote:

 Could you kindly advice if this query can be made to run faster
 SELECT title, template
FROM template
WHERE templateid IN
(608,2072,2073,1888,1898,1895,1897,1894,1896,1901,1903,1889,1900,1890,1891,1902,1904,2104,624,625,2152,2212,1985,1996,614,1964,2103,2106,2113,1982,1983,1984,2074,623,2117,2124,2123,2122,2119,2121);
 There is an index on templateid also this query takes ~ 0.04xx seconds to
run I would like it to be in 0.00xx range
 explain select gives this
  id select_type table type possible_keys key key_len ref rows Extra  1
SIMPLE template range PRIMARY PRIMARY 4 *NULL* 40 Using where


It's using a primary key and only examining the 40 rows which you asked 
for, so that's about as optimised as you'll get for that query. You 
could always make the actual server faster...


--
Jasper Bryant-Greene
Freelance web developer
http://jasper.bryant-greene.name/

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

Re: Help with query

[mfatene]

Hi,
look at group by ... with rollup at :
http://dev.mysql.com/doc/mysql/en/group-by-modifiers.html

Mathias

Selon Ronan Lucio [EMAIL PROTECTED]:

 Hello,

 I have a table where is saved all site´s access:

 access
 
 id
 year
 month
 day
 weekday
 hour
 minute
 ip

 Any column has multiple lines, and I have the follow query
 that returns the amount of access per day of month:

  SELECT year, month, day, COUNT(*) AS access
  FROM access
  WHERE year = 2005
AND month = 5
  GROUP BY year, month, day
  ORDER BY year, month, day

 Now, I need to do the same query, but for unique access,
 in other words, with DISTINCT year, month, day, ip.

 I tryed to use the query:

  SELECT year, month, day, ip, COUNT(*) AS access
  FROM access
  WHERE year = 2005
AND month = 5
  GROUP BY year, month, day, ip
  ORDER BY year, month, day

 but it returns me several lines of the same day and the amount
 of access per IP, and I need the amount of access from different
 IPs.

 Could anybody help me?

 Ronan



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

[Ronan Lucio]

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


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

Re: Help with query

[SGreen]

Ronan Lucio [EMAIL PROTECTED] wrote on 05/16/2005 04:21:17 PM:

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

This should give you how many unique IP addresses were used and the total 
number of accesses for each day for the 5th month of 2005:

 SELECT year, month, day,COUNT(DISTINCT IP), COUNT(*) AS access
 FROM access
 WHERE year = 2005
   AND month = 5
 GROUP BY year, month, day
 ORDER BY year, month, day;

The DISTINCT keyword eliminates all duplicates so that you only count how 
many different values appear in that column. Is this what you are looking 
for?

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: Help with query

[mfatene]

Hi Ronan,
I don't know if i understand your need, but your query gives something like that
:
mysql  SELECT year, month, day, ip, COUNT(*) AS access
-   FROM access
-   WHERE year = 2005
- AND month = 5
-   GROUP BY year, month, day, ip
-  ORDER BY year, month, day;
+--+---+--+-++
| year | month | day  | ip  | access |
+--+---+--+-++
| 2005 | 5 |   13 | 192.168.0.1 |  2 |
| 2005 | 5 |   13 | 192.168.0.2 |  1 |
| 2005 | 5 |   14 | 192.168.0.2 |  1 |
| 2005 | 5 |   15 | 192.168.0.3 |  1 |
+--+---+--+-++
4 rows in set (0.00 sec)

Your last email lets me understand that your want this data + IP. I tought to
group_concat :

mysql  SELECT year, month, day, group_concat(ip),count(*) AS access
-   FROM access
-   WHERE year = 2005
- AND month = 5
-   GROUP BY year, month,day
-  ORDER BY year, month, day;
+--+---+--+-++
| year | month | day  | group_concat(ip)| access |
+--+---+--+-++
| 2005 | 5 |   13 | 192.168.0.1,192.168.0.1,192.168.0.2 |  3 |
| 2005 | 5 |   14 | 192.168.0.2 |  1 |
| 2005 | 5 |   15 | 192.168.0.3 |  1 |
+--+---+--+-++
3 rows in set (0.00 sec)

To drop multiple IP, you can use distinct :

mysql  SELECT year, month, day, group_concat(distinct ip),count(*) AS access
-   FROM access
-   WHERE year = 2005
- AND month = 5
-   GROUP BY year, month,day
-  ORDER BY year, month, day;
+--+---+--+---++
| year | month | day  | group_concat(distinct ip) | access |
+--+---+--+---++
| 2005 | 5 |   13 | 192.168.0.1,192.168.0.2   |  3 |
| 2005 | 5 |   14 | 192.168.0.2   |  1 |
| 2005 | 5 |   15 | 192.168.0.3   |  1 |
+--+---+--+---++
3 rows in set (0.00 sec)


But when you group by year-month-day-ip you have distinct year-month-day-ip  as
you said. The problem is that the count(*) is for those distinct values.


I hope that this is near what you need.

Mathias


Selon Ronan Lucio [EMAIL PROTECTED]:

 Mathias,

  Hi,
  look at group by ... with rollup at :
  http://dev.mysql.com/doc/mysql/en/group-by-modifiers.html

 Thank you very much for your help.

 My needs aren´t this, exactly.

 GROUP BY WITH ROLLUP, returns me several lines of the
 same day (one per IP), plus the total.

 I need that every year-month-day-ip be counted as 1. And I
 need this total per day.

 Thank you,
 Ronan



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

[Ronan Lucio]

Mathias,
To drop multiple IP, you can use distinct :
mysql  SELECT year, month, day, group_concat(distinct ip),count(*) AS 
access
   -   FROM access
   -   WHERE year = 2005
   - AND month = 5
   -   GROUP BY year, month,day
   -  ORDER BY year, month, day;
+--+---+--+---++
| year | month | day  | group_concat(distinct ip) | access |
+--+---+--+---++
| 2005 | 5 |   13 | 192.168.0.1,192.168.0.2   |  3 |
| 2005 | 5 |   14 | 192.168.0.2   |  1 |
| 2005 | 5 |   15 | 192.168.0.3   |  1 |
+--+---+--+---++
3 rows in set (0.00 sec)
Thank you very much for your attention.
It also answer my question, but I think the Shawn´s tip is more
optimized.
Any way, I appreciate your help.
Thank you,
Ronan 


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

Re: help on query/group by

[mel list_php]

Hi,
Sorry for the late reply, out for the week-end!
Here is the information you asked for (I modified the columns' type as you 
suggested)
mysql show create table matches\G
*** 1. row ***
  Table: matches
Create Table: CREATE TABLE `matches` (
 `protID` varchar(50) default NULL,
 `drugID` int(11) default NULL,
 `sentID` int(11) default NULL,
 KEY `sentenceId` (`sentID`),
 KEY `drugID` (`drugID`),
 KEY `protID` (`protID`)
) TYPE=MyISAM
1 row in set (0.00 sec)

mysql show create table sentence\G
*** 1. row ***
  Table: sentence
Create Table: CREATE TABLE `sentence` (
 `id` int(10) unsigned NOT NULL auto_increment,
 `text` text,
 `pmid` int(11) default NULL,
 PRIMARY KEY  (`id`)
) TYPE=MyISAM
1 row in set (0.00 sec)
mysql show create table synonyms\G
*** 1. row ***
  Table: synonyms
Create Table: CREATE TABLE `synonyms` (
 `nameID` varchar(50) default NULL,
 `syn` text,
 UNIQUE KEY `c` (`nameID`(20),`syn`(20))
) TYPE=MyISAM
1 row in set (0.01 sec)
I think I used int as much as possible,here some data samples:
INSERT INTO `matches` VALUES ('Q9UP51', 202531, 4);
INSERT INTO `matches` VALUES ('SVC2_HUMAN', 202037, 5);
INSERT INTO `matches` VALUES ('PF2R_SHEEP', 202096, 6);
INSERT INTO `matches` VALUES ('CAQS_RAT', 202037, 7);
INSERT INTO `sentence` VALUES (4, 'Of NP10 contraceptives /NPtested , 
NPspan class=\'drug\'Ortho-Gynol/span /NPwas found to be the most 
rapidly acting , followed by NPCooper Creme /NP, a 
href=\'http://srs.ebi.ac.uk/srsbin/cgi-bin/wgetz?[UNIPROT-acc:(Q9UP51)]+-id+008+-view+UniprotDateView\'Lactikol- 
B/a\n, NPVagi-Serol /NP, NPMarvosan /NP, NPClinicol /NP, 
NPJelly-X /NP, NPBor-Oxyquin /NP, NPCellikol /NP, and 
NPLanteen Blue Jelly /NPin NPthat order /NP. ', 12305459);

(the text is usually longer than 255 characters, so I think text is the only 
choice, except longtext which can only be worst for indexation in my 
opinion)

INSERT INTO `synonyms` VALUES ('202037', 'testosterone');
INSERT INTO `synonyms` VALUES ('Q91XV1', 'gonadotropin');
INSERT INTO `synonyms` VALUES ('202037', 'estradiol');
INSERT INTO `synonyms` VALUES ('Q91XV1', 'gonadotropin-suppressing');
I tried to run the query you gave me (with temporary tables) it is still too 
long so I guess that my only solution now is to use indexes.
When modifying the columns' type from text to varchar, even if the text was 
indexed as unique mysql  complained about duplicates, and actually there 
were few duplicates, I thought it was as you said because only the beginning 
of the text field is indexed but I had 2 cases were the names were short 
(less than 15 characters) and the same (even the spaces).Removing one and 
the index on varchar was created. Any idea why?

I will try to play with the indexes, if I understand well I'd better index 
the three columns in once, because that will automatically index each of 
them?or am I wrong?

Anyway, thank you for all your advices, I'm really learning a lot of things 
with that case!
Melanie



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

 Thanks for the explanation about the join and the group by.

 I wanted to test your query (almost a simple copy/paste :-)) ).

 The first 2 queries are ok, but the third one still is too long :
 mysql CREATE  TEMPORARY  TABLE tmpSynDrug(  KEY ( nameID (20))  )
SELECT
 nameID, max( syn )  AS drugSyn
 - FROM synonyms
 - WHERE syn
 - LIKE  'a%'
 - GROUP  BY nameID;
 Query OK, 9693 rows affected (1.07 sec)
 Records: 9693  Duplicates: 0  Warnings: 0

 mysql
 mysql CREATE TEMPORARY TABLE tmpSynProt (key (nameID(20)))
 - SELECT nameID, max(syn) as protSyn
 - FROM synonyms
 - WHERE syn LIKE 'a%'
 - GROUP BY nameID;
 Query OK, 9693 rows affected (1.03 sec)
 Records: 9693  Duplicates: 0  Warnings: 0

 mysql
 mysql CREATE  TEMPORARY  TABLE tmpMatch(  KEY ( sentID)  ) SELECT
m.sentID,
 m.drugID, m.protID, tsd.drugSyn, tsp.protSyn
 - FROM matches m
 - INNER  JOIN tmpSynDrug tsd ON tsd.nameID = m.drugID
 - INNER  JOIN tmpSynProt tsp ON tsp.nameID = m.protID;


 I've tried once this morning (GMT time), it ran for one hour nothing I
 killed mysql (btw, how can I kill only one query when mysql freezes?I
 aborted but then mysqladmin shutdown didn't work anymore..)
 I thought it was maybe because I have other heavy stuff running, but I
tried
 a second time now and it's been running for 2 hours now, with almost
nothing
 else on the desktop.
 So apparently the join between matches and the other tables is still too
 heavy
 Any idea?
snip
OK, I reviewed what you have posted so far and I found a performance
killer.  On the table matches, the columns protID and drugID are
declared as text

Re: help on query/group by

[SGreen]

My reply below:

mel list_php [EMAIL PROTECTED] wrote on 03/21/2005 11:49:26 AM:

 Hi,
 Sorry for the late reply, out for the week-end!
 
 Here is the information you asked for (I modified the columns' type as 
you 
 suggested)
 mysql show create table matches\G
 *** 1. row ***
Table: matches
 Create Table: CREATE TABLE `matches` (
   `protID` varchar(50) default NULL,
   `drugID` int(11) default NULL,
   `sentID` int(11) default NULL,
   KEY `sentenceId` (`sentID`),
   KEY `drugID` (`drugID`),
   KEY `protID` (`protID`)
 ) TYPE=MyISAM
 1 row in set (0.00 sec)
 
 mysql show create table sentence\G
 *** 1. row ***
Table: sentence
 Create Table: CREATE TABLE `sentence` (
   `id` int(10) unsigned NOT NULL auto_increment,
   `text` text,
   `pmid` int(11) default NULL,
   PRIMARY KEY  (`id`)
 ) TYPE=MyISAM
 1 row in set (0.00 sec)
 
 mysql show create table synonyms\G
 *** 1. row ***
Table: synonyms
 Create Table: CREATE TABLE `synonyms` (
   `nameID` varchar(50) default NULL,
   `syn` text,
   UNIQUE KEY `c` (`nameID`(20),`syn`(20))
 ) TYPE=MyISAM
 1 row in set (0.01 sec)
 
 I think I used int as much as possible,here some data samples:
 
 INSERT INTO `matches` VALUES ('Q9UP51', 202531, 4);
 INSERT INTO `matches` VALUES ('SVC2_HUMAN', 202037, 5);
 INSERT INTO `matches` VALUES ('PF2R_SHEEP', 202096, 6);
 INSERT INTO `matches` VALUES ('CAQS_RAT', 202037, 7);
 
 
 INSERT INTO `sentence` VALUES (4, 'Of NP10 contraceptives /NPtested 
, 
 NPspan class=\'drug\'Ortho-Gynol/span /NPwas found to be the 
most 
 rapidly acting , followed by NPCooper Creme /NP, a 
 href=\'http://srs.ebi.ac.uk/srsbin/cgi-bin/wgetz?[UNIPROT-acc:
 (Q9UP51)]+-id+008+-view+UniprotDateView\'Lactikol- 
 B/a\n, NPVagi-Serol /NP, NPMarvosan /NP, NPClinicol /NP, 
 NPJelly-X /NP, NPBor-Oxyquin /NP, NPCellikol /NP, and 
 NPLanteen Blue Jelly /NPin NPthat order /NP. ', 12305459);
 
 (the text is usually longer than 255 characters, so I think text is the 
only 
 choice, except longtext which can only be worst for indexation in my 
 opinion)
 
 INSERT INTO `synonyms` VALUES ('202037', 'testosterone');
 INSERT INTO `synonyms` VALUES ('Q91XV1', 'gonadotropin');
 INSERT INTO `synonyms` VALUES ('202037', 'estradiol');
 INSERT INTO `synonyms` VALUES ('Q91XV1', 'gonadotropin-suppressing');
 
 I tried to run the query you gave me (with temporary tables) it is still 
too 
 long so I guess that my only solution now is to use indexes.
 When modifying the columns' type from text to varchar, even if the text 
was 
 indexed as unique mysql  complained about duplicates, and actually there 

 were few duplicates, I thought it was as you said because only the 
beginning 
 of the text field is indexed but I had 2 cases were the names were short 

 (less than 15 characters) and the same (even the spaces).Removing one 
and 
 the index on varchar was created. Any idea why?
 
 I will try to play with the indexes, if I understand well I'd better 
index 
 the three columns in once, because that will automatically index each of 

 them?or am I wrong?
 
 Anyway, thank you for all your advices, I'm really learning a lot of 
things 
 with that case!
 Melanie
 
 
 
 
 
 From: [EMAIL PROTECTED]
 To: mel list_php [EMAIL PROTECTED]
 CC: mysql@lists.mysql.com
 Subject: Re: help on query/group by
 Date: Fri, 18 Mar 2005 12:43:06 -0500
 
 mel list_php [EMAIL PROTECTED] wrote on 03/18/2005 10:35:30 
AM:
 
   Hi again,
  
   Thanks for the explanation about the join and the group by.
  
   I wanted to test your query (almost a simple copy/paste :-)) ).
  
   The first 2 queries are ok, but the third one still is too long :
   mysql CREATE  TEMPORARY  TABLE tmpSynDrug(  KEY ( nameID (20))  )
 SELECT
   nameID, max( syn )  AS drugSyn
   - FROM synonyms
   - WHERE syn
   - LIKE  'a%'
   - GROUP  BY nameID;
   Query OK, 9693 rows affected (1.07 sec)
   Records: 9693  Duplicates: 0  Warnings: 0
  
   mysql
   mysql CREATE TEMPORARY TABLE tmpSynProt (key (nameID(20)))
   - SELECT nameID, max(syn) as protSyn
   - FROM synonyms
   - WHERE syn LIKE 'a%'
   - GROUP BY nameID;
   Query OK, 9693 rows affected (1.03 sec)
   Records: 9693  Duplicates: 0  Warnings: 0
  
   mysql
   mysql CREATE  TEMPORARY  TABLE tmpMatch(  KEY ( sentID)  ) SELECT
 m.sentID,
   m.drugID, m.protID, tsd.drugSyn, tsp.protSyn
   - FROM matches m
   - INNER  JOIN tmpSynDrug tsd ON tsd.nameID = m.drugID
   - INNER  JOIN tmpSynProt tsp ON tsp.nameID = m.protID;
  
  
   I've tried once this morning (GMT time), it ran for one hour nothing 
I
   killed mysql (btw, how can I kill only one query when mysql 
freezes?I
   aborted but then mysqladmin shutdown didn't work anymore..)
   I thought it was maybe because I have other heavy stuff running, but 
I
 tried
   a second time now and it's been running for 2 hours now

Re: help on query/group by

[mel list_php]

Hi Shawn,
Thank you very much, I'm impressed by the time you took to answer me, and 
the quality of the reply!!!
I forwarded the answer to my friend.
I'm wondering, I knew the mechanism of temporary tables, but as I've never 
used it I was trying the left join way.
Here is a summary of my questions:
- why using inner join here?is there any difference with using a left join?I 
thought using a left join would decrease the number of results.
- do you know why without group by my query was running very fast and become 
so slow with the group by?when it does a group by it's scanning the whole 
table or an other reason?
- I don't know if his version of mysql supports subqueries, but I was 
wondering if it is possible to replace the temporary tables by subqueries 
and keeping the same efficiency (my friend told me he would like to have 
only one sql query).

Once again thank you very much for your help, I will give temporary tables 
an other chance!!!
Melanie


From: [EMAIL PROTECTED]
To: mel list_php [EMAIL PROTECTED]
CC: mysql@lists.mysql.com
Subject: Re: help on query/group by
Date: Wed, 16 Mar 2005 13:52:44 -0500
I have a favorite technique for improving the results of queries , like
this, which involve fairly large JOINed tables. It's a form of
divide-and-conquer in that you pre-compute what you can then make the
JOINS you need to finish up the results.
I agree that the AND s2.syn LIKE '%' in the ON clause of the second JOIN
is worthless and can be eliminated however I recognize this as a common
pattern for a two-term search and it may be harder to eliminate that
clause than at first glance.
This is how I would speed things up, Your friend really has 3 types of
searches possible:
a) search by drug name only
b) search by protein name only
c) search by both drug name and protein name
Since the c) is the more complex situation, I will model it. It's almost
trivial to clip out the unnecessary parts to make the other two queries.
If I typed everything correctly, you should be able to cut and paste the
whole thing into the MySQL client and have it execute.
### begin##
CREATE TEMPORARY TABLE tmpSynDrug (key nameID)
SELECT nameID, max(syn) as drugSyn
FROM synonyms
WHERE syn LIKE 'a%'
GROUP BY nameID;
CREATE TEMPORARY TABLE tmpSynProt (key nameID)
SELECT nameID, max(syn) as protSyn
FROM synonyms
WHERE syn LIKE 'a%'
GROUP BY nameID;
CREATE TEMPORARY TABLE tmpMatch (key sentID)
SELECT m.sentID, m.drugID, m.protID, tsd.drugSyn, tsp.protSyn
FROM matches m
INNER JOIN tmpSynDrugs tsd
ON tsd.nameID = m.drugID
INNER JOIN tmpSynProt tsp
ON tsp.nameID = m.protID;
# what we should have now is a nice small table that meets most of the
# original query criteria. Now to summarize by publication by
# joining through the sentence table
SELECT tm.drugID, tm.protID, tm.drugSyn, tm.protSyn, COUNT(DISTINCT
s.pmid) as publications
FROM tmpMatch tm
INNER JOIN sentence s
ON s.id = tm.sentID
GROUP BY 1,2,3,4 ;
# I used a shortcut in the GROUP BY, I referenced the columns
# by their positions and not by their names
#Now that we have the data we wanted we can cleanup after ourselves:
DROP TABLE tmpMatch, tmpSynProt, tmpSynDrug;
 end #
By minimizing the number of records that needs to be JOINed at each stage
of the query, we keep things moving along. This technique is very useful
for queries whose JOIN products are somewhere in the hundreds of billions
or records or more (which yours easily is).  If you didn't want the names
to be representative, but listed,  you would change the first two queries
to be like:
CREATE TEMPORARY TABLE tmpSynDrug (key nameID)
SELECT nameID, syn as drugSyn
FROM synonyms
WHERE syn LIKE 'a%';
If you didn't need names at all I would just say:
CREATE TEMPORARY TABLE tmpSynDrug (key nameID)
SELECT DISTINCT nameID
FROM synonyms
WHERE syn LIKE 'a%'
and modify the other queries to not look for the name columns.
HTH,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

== Original   message
==from mel list_php [EMAIL PROTECTED] 03/16/2005 12:45 PM
==
Hi,
A friend of mine asked me to have a look at one of his query, and I'm
stuck
Here was his query:
SELECT drugID, protID, COUNT(DISTINCT pmid),
   MAX(s1.syn) AS o1, MAX(s2.syn) AS o2
FROM matches
INNER JOIN synonyms AS s1 ON drugID=s1.nameID AND s1.syn LIKE 'a%'
INNER JOIN synonyms AS s2 ON protID=s2.nameID AND s2.syn LIKE '%'
INNER JOIN sentence ON sentID=id
GROUP BY drugID, protID ORDER BY o1, o2 LIMIT 601
and this is his goal:
The idea is quite simple: The table called 'matches' contains triples
  drugID, protID, sentID
indicating a co-occurence of a drug and a protein in a sentence. The
user of course searches for either drug name or protein name or
both. In the above query, the user wants everything for all drugs
starting with 'a'.
The MAX() calls more or less arbitrarily choose one of the many names

Re: help on query/group by

[SGreen]

Responses embedded below

mel list_php [EMAIL PROTECTED] wrote on 03/18/2005 05:57:29 AM:

 Hi Shawn,
 
 Thank you very much, I'm impressed by the time you took to answer me, 
and 
 the quality of the reply!!!
 I forwarded the answer to my friend.
 I'm wondering, I knew the mechanism of temporary tables, but as I've 
never 
 used it I was trying the left join way.
 Here is a summary of my questions:
 - why using inner join here?is there any difference with using a left 
join?I 
 thought using a left join would decrease the number of results.

The primary difference between a LEFT JOIN and an INNER JOIN is that with 
an INNER JOIN matching records MUST exist in both tables before they are 
considered for evaluation by the WHERE clause. You usually retrieve MORE 
records with a LEFT JOIN than an INNER JOIN but that depends on your data, 
too. In no case can an LEFT JOIN return fewer records than an INNER JOIN, 
all other conditions being equal.

 - do you know why without group by my query was running very fast and 
become 
 so slow with the group by?when it does a group by it's scanning the 
whole 
 table or an other reason?

The GROUP BY clause requests that the engine make another processing pass 
through the records that satisfy your WHERE clause conditions in order to 
aggregate records according to the columns you specified. It's that second 
pass and the processing that occurs within it that makes a grouped query 
slower to finish than an ungrouped one. (NOTE: Some ungrouped query 
results are so large that a grouped result may actually be _useful_ sooner 
due to less data transfer between the server and your application)

 - I don't know if his version of mysql supports subqueries, but I was 
 wondering if it is possible to replace the temporary tables by 
subqueries 
 and keeping the same efficiency (my friend told me he would like to have 

 only one sql query).

I have found very few cases where subqueries outperformed temp (or 
special-purpose, permanent) tables especially when working with larger 
amounts of data. Of course, subquery performance varies according to the 
nature of the subquery (can it be evaluated just once or does it have to 
have to be evaluated for each and every row of the result), the complexity 
of the subquery, and the hardware your server is on. The only way to know 
for sure is to develop a subquery version of this query and test it with 
your/their hardware. 


 Once again thank you very much for your help, I will give temporary 
tables 
 an other chance!!!
 Melanie
 

You are most welcome!

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


 BIG snip

Re: help on query/group by

[mel list_php]

Hi again,
Thanks for the explanation about the join and the group by.
I wanted to test your query (almost a simple copy/paste :-)) ).
The first 2 queries are ok, but the third one still is too long :
mysql CREATE  TEMPORARY  TABLE tmpSynDrug(  KEY ( nameID (20))  ) SELECT 
nameID, max( syn )  AS drugSyn
   - FROM synonyms
   - WHERE syn
   - LIKE  'a%'
   - GROUP  BY nameID;
Query OK, 9693 rows affected (1.07 sec)
Records: 9693  Duplicates: 0  Warnings: 0

mysql
mysql CREATE TEMPORARY TABLE tmpSynProt (key (nameID(20)))
   - SELECT nameID, max(syn) as protSyn
   - FROM synonyms
   - WHERE syn LIKE 'a%'
   - GROUP BY nameID;
Query OK, 9693 rows affected (1.03 sec)
Records: 9693  Duplicates: 0  Warnings: 0
mysql
mysql CREATE  TEMPORARY  TABLE tmpMatch(  KEY ( sentID)  ) SELECT m.sentID, 
m.drugID, m.protID, tsd.drugSyn, tsp.protSyn
   - FROM matches m
   - INNER  JOIN tmpSynDrug tsd ON tsd.nameID = m.drugID
   - INNER  JOIN tmpSynProt tsp ON tsp.nameID = m.protID;

I've tried once this morning (GMT time), it ran for one hour nothing I 
killed mysql (btw, how can I kill only one query when mysql freezes?I 
aborted but then mysqladmin shutdown didn't work anymore..)
I thought it was maybe because I have other heavy stuff running, but I tried 
a second time now and it's been running for 2 hours now, with almost nothing 
else on the desktop.
So apparently the join between matches and the other tables is still too 
heavy
Any idea?


From: [EMAIL PROTECTED]
To: mel list_php [EMAIL PROTECTED]
CC: mysql@lists.mysql.com
Subject: Re: help on query/group by
Date: Fri, 18 Mar 2005 09:14:02 -0500
Responses embedded below
mel list_php [EMAIL PROTECTED] wrote on 03/18/2005 05:57:29 AM:
 Hi Shawn,

 Thank you very much, I'm impressed by the time you took to answer me,
and
 the quality of the reply!!!
 I forwarded the answer to my friend.
 I'm wondering, I knew the mechanism of temporary tables, but as I've
never
 used it I was trying the left join way.
 Here is a summary of my questions:
 - why using inner join here?is there any difference with using a left
join?I
 thought using a left join would decrease the number of results.
The primary difference between a LEFT JOIN and an INNER JOIN is that with
an INNER JOIN matching records MUST exist in both tables before they are
considered for evaluation by the WHERE clause. You usually retrieve MORE
records with a LEFT JOIN than an INNER JOIN but that depends on your data,
too. In no case can an LEFT JOIN return fewer records than an INNER JOIN,
all other conditions being equal.
 - do you know why without group by my query was running very fast and
become
 so slow with the group by?when it does a group by it's scanning the
whole
 table or an other reason?
The GROUP BY clause requests that the engine make another processing pass
through the records that satisfy your WHERE clause conditions in order to
aggregate records according to the columns you specified. It's that second
pass and the processing that occurs within it that makes a grouped query
slower to finish than an ungrouped one. (NOTE: Some ungrouped query
results are so large that a grouped result may actually be _useful_ sooner
due to less data transfer between the server and your application)
 - I don't know if his version of mysql supports subqueries, but I was
 wondering if it is possible to replace the temporary tables by
subqueries
 and keeping the same efficiency (my friend told me he would like to have
 only one sql query).
I have found very few cases where subqueries outperformed temp (or
special-purpose, permanent) tables especially when working with larger
amounts of data. Of course, subquery performance varies according to the
nature of the subquery (can it be evaluated just once or does it have to
have to be evaluated for each and every row of the result), the complexity
of the subquery, and the hardware your server is on. The only way to know
for sure is to develop a subquery version of this query and test it with
your/their hardware.
 Once again thank you very much for your help, I will give temporary
tables
 an other chance!!!
 Melanie

You are most welcome!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
 BIG snip
_
Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: help on query/group by

[SGreen]

mel list_php [EMAIL PROTECTED] wrote on 03/18/2005 10:35:30 AM:

 Hi again,
 
 Thanks for the explanation about the join and the group by.
 
 I wanted to test your query (almost a simple copy/paste :-)) ).
 
 The first 2 queries are ok, but the third one still is too long :
 mysql CREATE  TEMPORARY  TABLE tmpSynDrug(  KEY ( nameID (20))  ) 
SELECT 
 nameID, max( syn )  AS drugSyn
 - FROM synonyms
 - WHERE syn
 - LIKE  'a%'
 - GROUP  BY nameID;
 Query OK, 9693 rows affected (1.07 sec)
 Records: 9693  Duplicates: 0  Warnings: 0
 
 mysql
 mysql CREATE TEMPORARY TABLE tmpSynProt (key (nameID(20)))
 - SELECT nameID, max(syn) as protSyn
 - FROM synonyms
 - WHERE syn LIKE 'a%'
 - GROUP BY nameID;
 Query OK, 9693 rows affected (1.03 sec)
 Records: 9693  Duplicates: 0  Warnings: 0
 
 mysql
 mysql CREATE  TEMPORARY  TABLE tmpMatch(  KEY ( sentID)  ) SELECT 
m.sentID, 
 m.drugID, m.protID, tsd.drugSyn, tsp.protSyn
 - FROM matches m
 - INNER  JOIN tmpSynDrug tsd ON tsd.nameID = m.drugID
 - INNER  JOIN tmpSynProt tsp ON tsp.nameID = m.protID;
 
 
 I've tried once this morning (GMT time), it ran for one hour nothing I 
 killed mysql (btw, how can I kill only one query when mysql freezes?I 
 aborted but then mysqladmin shutdown didn't work anymore..)
 I thought it was maybe because I have other heavy stuff running, but I 
tried 
 a second time now and it's been running for 2 hours now, with almost 
nothing 
 else on the desktop.
 So apparently the join between matches and the other tables is still too 

 heavy
 Any idea?
snip

OK, I reviewed what you have posted so far and I found a performance 
killer.  On the table matches, the columns protID and drugID are 
declared as text. This is bad for searching as you can only index the 
first portion of any text column. Those columns should be declared as CHAR 
 or VARCHAR or better yet, some integer value. If all 3 columns in the 
matches table are integers (INT or  BIGINT, preferably UNSIGNED) then 
this becomes a fixed-width table and lookups become exceedingly fast. All 
indexes on those columns also become number-based and numeric comparisons 
occur *much* faster than string comparisons. I very rarely use non-numeric 
primary keys for just this reason.
 
You mentioned there were indexes on the table and provided the output of 
DESC for the table  but DESC does a very poor job of actually describing 
indexes. I prefer the output of SHOW CREATE TABLE \G  as it gives me a 
complete table creation statement(Use /G and not ; to eliminate a lot of 
excess formatting in the output). Can you generate that for me, please? I 
practically guarantee that if we re-tool that table (including the 
indexes), our query times will drop like rocks.

You can kill a single query through the commands SHOW [FULL] PROCESSLIST 
(to identify the # of the process you want to kill) and KILL # (using the 
# you just looked up). This usually drops the connection to the client 
running the query you killed, too (so be prepared to reconnect).

http://dev.mysql.com/doc/mysql/en/show-processlist.html
http://dev.mysql.com/doc/mysql/en/kill.html

I just realized that we are only querying for the search conditions DRUG 
like 'a%' AND PROTIEN like 'a%'. If we wanted to do the OR of that (DRUG 
like ... OR Protien like ...) we could use a UNION query to generate 
tmpMatch

CREATE TEMPORARY TABLE tmpMatch (key sentID)
(SELECT m.sentID, m.drugID, m.protID, tsd.drugSyn, tsp.protSyn
FROM matches m
INNER JOIN tmpSynDrugs tsd
ON tsd.nameID = m.drugID
INNER JOIN tmpSynProt tsp
ON tsp.nameID = m.protID)
UNION
(SELECT m.sentID, m.drugID, m.protID, tsd.drugSyn, tsp.protSyn
FROM matches m
INNER JOIN tmpSynDrugs tsd
ON tsd.nameID = m.drugID
INNER JOIN tmpSynProt tsp
ON tsp.nameID = m.protID);

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: help on query/group by

[SGreen]

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

I agree that the AND s2.syn LIKE '%' in the ON clause of the second JOIN 
is worthless and can be eliminated however I recognize this as a common 
pattern for a two-term search and it may be harder to eliminate that 
clause than at first glance.

This is how I would speed things up, Your friend really has 3 types of 
searches possible:
a) search by drug name only
b) search by protein name only
c) search by both drug name and protein name

Since the c) is the more complex situation, I will model it. It's almost 
trivial to clip out the unnecessary parts to make the other two queries. 
If I typed everything correctly, you should be able to cut and paste the 
whole thing into the MySQL client and have it execute.

### begin##
CREATE TEMPORARY TABLE tmpSynDrug (key nameID)
SELECT nameID, max(syn) as drugSyn
FROM synonyms
WHERE syn LIKE 'a%'
GROUP BY nameID;

CREATE TEMPORARY TABLE tmpSynProt (key nameID)
SELECT nameID, max(syn) as protSyn
FROM synonyms
WHERE syn LIKE 'a%'
GROUP BY nameID;

CREATE TEMPORARY TABLE tmpMatch (key sentID)
SELECT m.sentID, m.drugID, m.protID, tsd.drugSyn, tsp.protSyn
FROM matches m
INNER JOIN tmpSynDrugs tsd
ON tsd.nameID = m.drugID
INNER JOIN tmpSynProt tsp
ON tsp.nameID = m.protID;

# what we should have now is a nice small table that meets most of the 
# original query criteria. Now to summarize by publication by 
# joining through the sentence table

SELECT tm.drugID, tm.protID, tm.drugSyn, tm.protSyn, COUNT(DISTINCT 
s.pmid) as publications
FROM tmpMatch tm
INNER JOIN sentence s
ON s.id = tm.sentID
GROUP BY 1,2,3,4 ;

# I used a shortcut in the GROUP BY, I referenced the columns 
# by their positions and not by their names

#Now that we have the data we wanted we can cleanup after ourselves:

DROP TABLE tmpMatch, tmpSynProt, tmpSynDrug;

 end #

By minimizing the number of records that needs to be JOINed at each stage 
of the query, we keep things moving along. This technique is very useful 
for queries whose JOIN products are somewhere in the hundreds of billions 
or records or more (which yours easily is).  If you didn't want the names 
to be representative, but listed,  you would change the first two queries 
to be like:

CREATE TEMPORARY TABLE tmpSynDrug (key nameID)
SELECT nameID, syn as drugSyn
FROM synonyms
WHERE syn LIKE 'a%';

If you didn't need names at all I would just say:

CREATE TEMPORARY TABLE tmpSynDrug (key nameID)
SELECT DISTINCT nameID
FROM synonyms
WHERE syn LIKE 'a%'

and modify the other queries to not look for the name columns.

HTH,

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



 
== Original   message 
==from mel list_php [EMAIL PROTECTED] 03/16/2005 12:45 PM
==
Hi,

A friend of mine asked me to have a look at one of his query, and I'm 
stuck
Here was his query:
SELECT drugID, protID, COUNT(DISTINCT pmid),
   MAX(s1.syn) AS o1, MAX(s2.syn) AS o2
FROM matches
INNER JOIN synonyms AS s1 ON drugID=s1.nameID AND s1.syn LIKE 'a%'
INNER JOIN synonyms AS s2 ON protID=s2.nameID AND s2.syn LIKE '%'
INNER JOIN sentence ON sentID=id
GROUP BY drugID, protID ORDER BY o1, o2 LIMIT 601

and this is his goal:

The idea is quite simple: The table called 'matches' contains triples

  drugID, protID, sentID

indicating a co-occurence of a drug and a protein in a sentence. The
user of course searches for either drug name or protein name or
both. In the above query, the user wants everything for all drugs
starting with 'a'.

The MAX() calls more or less arbitrarily choose one of the many names
associated with drugID as a representative. With the COUNT() I want to
find out how many different medline abstracts (not sentences) have a
hit.

The matches table is 1,247,508 rows, sentence is  817,255 rows and 
synonyms 
is 225,497 rows.

First I think using inner join in that case is not helpful, because it is 
making a whole cartesian product on the tables, whereas a left join would 
limit the number of rows.
The second line INNER JOIN synonyms AS s2 ON protID=s2.nameID AND s2.syn 
LIKE '%'  is useless I think, because it just retrieves the not null 
values 
for protID.

I also added indexes on the table (i'm not very familiar with indexes, so 
that is probably my problem)
- on matches: index on protID,drugID and sentID
- on sentence: index on id (primary key)
- on synonyms: index on nameID,syn

Here are the tables:
mysql desc matches;
++-+--+-+-+---+
| Field  | Type| Null | Key | Default | Extra |
++-+--+-+-+---+
| protID | text | YES  | MUL  | NULL   |   |
| drugID | text  

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

[Philippe Poelvoorde]

Graham Cossey wrote:
Could someone please offer a little help.
I have a table like:
Year, Month, Start_date
20041020041102
20041120041203
20041220050104
20050120050204
20050220050303
I need to get the latest Year,Month for a given date, so for example today
(20050204) I should retrieve 2005,01.
As I'm using 4.0.20 I can't use subqueries so how can I create a query that
does this?
SELECT year, month
FROM `dc_months`
WHERE start_date = (SELECT MAX(start_date)
  from dc_months
  where start_date = '20050204')
Any help much appreciated
Graham

have a look here :
http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html
it should be useful for you.
--
Philippe Poelvoorde
COS Trading Ltd.
+44.(0)20.7376.2401
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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

[Graham Cossey]

snip

  As I'm using 4.0.20 I can't use subqueries so how can I create 
 a query that
  does this?
  
  SELECT year, month
  FROM `dc_months`
  WHERE start_date = (SELECT MAX(start_date)
from dc_months
where start_date = '20050204')
  
  Any help much appreciated
  
  Graham
  
  
 
 have a look here :
 http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html
 it should be useful for you.
 
Thanks Philippe that could do it.

Graham.

 -- 
 Philippe Poelvoorde
 COS Trading Ltd.
 +44.(0)20.7376.2401
 

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

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

[Michael Dykman]

On Fri, 2005-02-04 at 09:19, Graham Cossey wrote:
 Could someone please offer a little help.
 
 I have a table like:
 
 Year, Month, Start_date
 20041020041102
 20041120041203
 20041220050104
 20050120050204
 20050220050303
 
 I need to get the latest Year,Month for a given date, so for example today
 (20050204) I should retrieve 2005,01.
 
 As I'm using 4.0.20 I can't use subqueries so how can I create a query that
 does this?
 
 SELECT year, month
 FROM `dc_months`
 WHERE start_date = (SELECT MAX(start_date)
   from dc_months
   where start_date = '20050204')
 
 Any help much appreciated
 
 Graham

I think this conveys the idea:

SELECT year, month
FROM `dc_months` 
WHERE start_date = '20050204' ORDER BY start_date DESC 
LIMIT 1


-- 
 - michael dykman
 - [EMAIL PROTECTED]


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

Re: Help optimizing query

[roger]

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

Try adding an index with subject_id as the first column.

ALTER TABLE `citation_subject` ADD INDEX (`subject_id`,`citation_id`);

-- 
Roger


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

Re: Help optimizing query

[Jesse Sheidlower]

On Tue, Nov 23, 2004 at 09:55:15PM +0100, [EMAIL PROTECTED] wrote:
 * Jesse Sheidlower
 [...]
  CREATE TABLE `citation_subject` (
`id` int(11) NOT NULL auto_increment,
`citation_id` int(11) NOT NULL default '0',
`subject_id` int(11) NOT NULL default '0',
PRIMARY KEY  (`id`),
KEY `citation_id` (`citation_id`,`subject_id`)
  )
 
 Try adding an index with subject_id as the first column.
 
 ALTER TABLE `citation_subject` ADD INDEX (`subject_id`,`citation_id`);

Thanks. This did help slightly--I didn't realize that the
order of this would make such a difference, if both were
always being used.

I'm now coming to the determination that there are other
parts of the application functioning as the biggest drags.
If this is so, I apologize for the wasted bandwidth; I'm
still poking at this query though.

Jesse Sheidlower

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

RE: Help with query performance anomaly

[Graham Cossey]

Thanks for the advice Steven, I'll bear it in mind and do some reading.

Graham

 -Original Message-
 From: Steven Roussey [mailto:[EMAIL PROTECTED]
 Sent: 13 November 2004 02:52
 To: 'Graham Cossey'
 Cc: [EMAIL PROTECTED]
 Subject: RE: Help with query performance anomaly


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

 http://dev.mysql.com/doc/mysql/en/JOIN.html

 STRAIGHT_JOIN is identical to JOIN, except that the left table is always
 read before the right table. This can be used for those (few) cases for
 which the join optimizer puts the tables in the wrong order.

 http://dev.mysql.com/doc/mysql/en/SELECT.html

 The use of USE INDEX, IGNORE INDEX, FORCE INDEX to give the
 optimizer hints
 about how to choose indexes is described in section 14.1.7.1 JOIN Syntax.

 -steve--






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

RE: Help with query performance anomaly (SOLVED)

[Graham Cossey]

It turns out that it appears to be a data discrepancy that caused the query
optimiser to, well, not optimise.

I thought the main table (r) with 3million records would be the problem, but
it was table p with 3100 records on the live server and 3082 records on my
dev pc that caused the problem. Although the results of show create table
etc were identical on both machines, uploading the data from dev to live has
solved the problem.

Thanks to all that offered advice.

Graham

snip size=big/



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

RE: Help with query performance anomaly

[Steven Roussey]

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

http://dev.mysql.com/doc/mysql/en/JOIN.html

STRAIGHT_JOIN is identical to JOIN, except that the left table is always
read before the right table. This can be used for those (few) cases for
which the join optimizer puts the tables in the wrong order.

http://dev.mysql.com/doc/mysql/en/SELECT.html

The use of USE INDEX, IGNORE INDEX, FORCE INDEX to give the optimizer hints
about how to choose indexes is described in section 14.1.7.1 JOIN Syntax. 

-steve--



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

Re: Help with query performance anomaly

[SGreen]

What does EXPLAIN show for the query on both systems?  (I am wondering if 
you may have an index on your development system that you do not have on 
your production server.)

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


Graham Cossey [EMAIL PROTECTED] wrote on 11/11/2004 11:19:08 AM:

 Hi
 
 Can someone offer any advice on a strange problem I have at present...
 
 If I run a certain query (see below) on my local development PC using
 mysqlcc it returns in 3.7s.
 
 If I run the exact same query on my live webserver (again using mysqlcc) 
I
 have yet to get a result !!
 
 Both databases have the same table definitions (live db originally 
created
 from mysqldump of dev PC) and have exactly the same [number of] records 
in
 each table. Both machines are running MySQL 3.23.58. Dev PC if Fedora 
Core2,
 live is RedHat 9.
 
 Other than this one query all else appears normal, any suggestions?
 Let me know if you need more info and I'll attempt to supply it...
 
 Many thanks
 
 Graham
 
 Query: SELECT code, sum(qty) as total
 FROM table1 as d, db2.table2 as r, table3 as p
 WHERE year=2004  AND month=10 AND r.col1=p.col1 and d.code=p.code and
 from_period = 200410 and to_period  200410 and d.col3!='6'
 GROUP BY code
 
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 

RE: Help with query performance anomaly

[Graham Cossey]

Thanks Shaun

EXPLAIN shows the same 'possible keys' for each table but 'key' and
'key-len' columns are different, as are the 'rows' as well of course.

I guess this points to a probable difference in key definitions?

Can 2 installations with the same table definitions produce different
results like this? Maybe something in the configs?

Thanks

Graham

 -Original Message-
 From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
 Sent: 11 November 2004 16:28
 To: Graham Cossey
 Cc: [EMAIL PROTECTED]
 Subject: Re: Help with query performance anomaly


 What does EXPLAIN show for the query on both systems?  (I am wondering if
 you may have an index on your development system that you do not have on
 your production server.)

 Shawn Green
 Database Administrator
 Unimin Corporation - Spruce Pine


 Graham Cossey [EMAIL PROTECTED] wrote on 11/11/2004 11:19:08 AM:

  Hi
 
  Can someone offer any advice on a strange problem I have at present...
 
  If I run a certain query (see below) on my local development PC using
  mysqlcc it returns in 3.7s.
 
  If I run the exact same query on my live webserver (again using
 mysqlcc)
 I
  have yet to get a result !!
 
  Both databases have the same table definitions (live db originally
 created
  from mysqldump of dev PC) and have exactly the same [number of] records
 in
  each table. Both machines are running MySQL 3.23.58. Dev PC if Fedora
 Core2,
  live is RedHat 9.
 
  Other than this one query all else appears normal, any suggestions?
  Let me know if you need more info and I'll attempt to supply it...
 
  Many thanks
 
  Graham
 
  Query: SELECT code, sum(qty) as total
  FROM table1 as d, db2.table2 as r, table3 as p
  WHERE year=2004  AND month=10 AND r.col1=p.col1 and d.code=p.code and
  from_period = 200410 and to_period  200410 and d.col3!='6'
  GROUP BY code
 
 
 
  --
  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 performance anomaly

[SGreen]

Check the results of SHOW CREATE TABLE for the three tables you use and 
compare between production and development. You should be able to spot any 
 differences in your key definitions.

If they are the same on both machines then you should probably run ANALYZE 
TABLE against the three tables on your production machine. That will 
update the query optimizer's statistics for those tables. If the optimizer 
has bad stats it can make poor choices about which index to use.

If that doesn't help, try using the OPTIMIZE TABLE command on your three 
tables. Heavy fragmentation can slow down data retrieval, too.

Let me know how things turn out.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


Graham Cossey [EMAIL PROTECTED] wrote on 11/11/2004 11:48:13 AM:

 Thanks Shaun
 
 EXPLAIN shows the same 'possible keys' for each table but 'key' and
 'key-len' columns are different, as are the 'rows' as well of course.
 
 I guess this points to a probable difference in key definitions?
 
 Can 2 installations with the same table definitions produce different
 results like this? Maybe something in the configs?
 
 Thanks
 
 Graham
 
  -Original Message-
  From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
  Sent: 11 November 2004 16:28
  To: Graham Cossey
  Cc: [EMAIL PROTECTED]
  Subject: Re: Help with query performance anomaly
 
 
  What does EXPLAIN show for the query on both systems?  (I am wondering 
if
  you may have an index on your development system that you do not have 
on
  your production server.)
 
  Shawn Green
  Database Administrator
  Unimin Corporation - Spruce Pine
 
 
  Graham Cossey [EMAIL PROTECTED] wrote on 11/11/2004 11:19:08 
AM:
 
   Hi
  
   Can someone offer any advice on a strange problem I have at 
present...
  
   If I run a certain query (see below) on my local development PC 
using
   mysqlcc it returns in 3.7s.
  
   If I run the exact same query on my live webserver (again using
  mysqlcc)
  I
   have yet to get a result !!
  
   Both databases have the same table definitions (live db originally
  created
   from mysqldump of dev PC) and have exactly the same [number of] 
records
  in
   each table. Both machines are running MySQL 3.23.58. Dev PC if 
Fedora
  Core2,
   live is RedHat 9.
  
   Other than this one query all else appears normal, any suggestions?
   Let me know if you need more info and I'll attempt to supply it...
  
   Many thanks
  
   Graham
  
   Query: SELECT code, sum(qty) as total
   FROM table1 as d, db2.table2 as r, table3 as p
   WHERE year=2004  AND month=10 AND r.col1=p.col1 and d.code=p.code 
and
   from_period = 200410 and to_period  200410 and d.col3!='6'
   GROUP BY code
  
  
  
   --
   MySQL General Mailing List
   For list archives: http://lists.mysql.com/mysql
   To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
  
 
 
 

RE: Help with query performance anomaly

[Graham Cossey]

 Thanks Shaun

 EXPLAIN shows the same 'possible keys' for each table but 'key' and
 'key-len' columns are different, as are the 'rows' as well of course.

 I guess this points to a probable difference in key definitions?

 Can 2 installations with the same table definitions produce different
 results like this? Maybe something in the configs?

[snip]
 
 
  What does EXPLAIN show for the query on both systems?  (I am
 wondering if
  you may have an index on your development system that you do not have on
  your production server.)
 
[snip]
  
   Can someone offer any advice on a strange problem I have at present...
  
   If I run a certain query (see below) on my local development PC using
   mysqlcc it returns in 3.7s.
  
   If I run the exact same query on my live webserver (again using
  mysqlcc)
  I
   have yet to get a result !!
  
   Both databases have the same table definitions (live db originally
  created
   from mysqldump of dev PC) and have exactly the same [number
 of] records
  in
   each table. Both machines are running MySQL 3.23.58. Dev PC if Fedora
  Core2,
   live is RedHat 9.
  
   Other than this one query all else appears normal, any suggestions?
   Let me know if you need more info and I'll attempt to supply it...
  
   Many thanks
  
   Graham
  
[snip]

I've done mysqldumps of the tables involved on both machines and the create
table definitions and key definitions are identical.

The results of my EXPLAINs are pasted below.

Thanks
Graham

DEV BOX:

EXPLAIN SELECT d.dcode, sum(qty) as total
FROM table1 as d, db2.table2 as r, table3 as p
WHERE year=2004  AND month=10 AND r.pcode=p.pcode and d.dcode=p.dcode and
from_period = 200410 and to_period  200410 and d.region!='6'
GROUP BY dcode

+---+---+---
-+--+-+-+--+
--+
| table | type  | possible_keys
| key  | key_len | ref | rows | Extra
|
+---+---+---
-+--+-+-+--+
--+
| d | ALL   | [NULL]
| [NULL]   |  [NULL] | [NULL]  |  322 | Using where; Using
temporary; Using filesort |
| p | index | PRIMARY
| PRIMARY  |  19 | [NULL]  | 6082 | Using where; Using index
|
| r | ref   |
PRIMARY,yr_mn_mk_rng_dr,yr_mn_st,yr_mn_pc,yr_mn_f,yr_mn_mk_st  | yr_mn_pc |
13 | const,const,p.pcode |   41 | Using where
|
+---+---+---
-+--+-+-+--+
--+

LIVE SERVER:

EXPLAIN SELECT d.dcode, sum(qty) as total
FROM table1 as d, db2.table2 as r, table3 as p
WHERE year=2004  AND month=10 AND r.pcode=p.pcode and d.dcode=p.dcode and
from_period = 200410 and to_period  200410 and d.region!='6'
GROUP BY dcode

+---+--+
+-+-+-+---+-
-+
| table | type | possible_keys
| key | key_len | ref | rows  | Extra
|
+---+--+
+-+-+-+---+-
-+
| d | ALL  | [NULL]
| [NULL]  |  [NULL] | [NULL]  |   322 | Using where; Using temporary;
Using filesort |
| r | ref  |
PRIMARY,yr_mn_mk_rng_dr,yr_mn_st,yr_mn_pc,yr_mn_f,yr_mn_mk_st  | PRIMARY |
8 | const,const | 89618 | Using where  |
| p | ref  | PRIMARY
| PRIMARY |   4 | r.pcode | 2 | Using where; Using index
|
+---+--+
+-+-+-+---+-
-+



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

RE: Help with query performance anomaly

[SGreen]

Response at end

Graham Cossey [EMAIL PROTECTED] wrote on 11/11/2004 12:19:17 PM:

  Thanks Shaun
 
  EXPLAIN shows the same 'possible keys' for each table but 'key' and
  'key-len' columns are different, as are the 'rows' as well of course.
 
  I guess this points to a probable difference in key definitions?
 
  Can 2 installations with the same table definitions produce different
  results like this? Maybe something in the configs?
 
 [snip]
  
  
   What does EXPLAIN show for the query on both systems?  (I am
  wondering if
   you may have an index on your development system that you do not 
have on
   your production server.)
  
 [snip]
   
Can someone offer any advice on a strange problem I have at 
present...
   
If I run a certain query (see below) on my local development PC 
using
mysqlcc it returns in 3.7s.
   
If I run the exact same query on my live webserver (again using
   mysqlcc)
   I
have yet to get a result !!
   
Both databases have the same table definitions (live db originally
   created
from mysqldump of dev PC) and have exactly the same [number
  of] records
   in
each table. Both machines are running MySQL 3.23.58. Dev PC if 
Fedora
   Core2,
live is RedHat 9.
   
Other than this one query all else appears normal, any 
suggestions?
Let me know if you need more info and I'll attempt to supply it...
   
Many thanks
   
Graham
   
 [snip]
 
 I've done mysqldumps of the tables involved on both machines and the 
create
 table definitions and key definitions are identical.
 
 The results of my EXPLAINs are pasted below.
 
 Thanks
 Graham
 
 DEV BOX:
 
 EXPLAIN SELECT d.dcode, sum(qty) as total
 FROM table1 as d, db2.table2 as r, table3 as p
 WHERE year=2004  AND month=10 AND r.pcode=p.pcode and d.dcode=p.dcode 
and
 from_period = 200410 and to_period  200410 and d.region!='6'
 GROUP BY dcode
 
 
+---+---+---
 
-+--+-+-+--+
 --+
 | table | type  | possible_keys
 | key  | key_len | ref | rows | Extra
 |
 
+---+---+---
 
-+--+-+-+--+
 --+
 | d | ALL   | [NULL]
 | [NULL]   |  [NULL] | [NULL]  |  322 | Using where; Using
 temporary; Using filesort |
 | p | index | PRIMARY
 | PRIMARY  |  19 | [NULL]  | 6082 | Using where; Using 
index
 |
 | r | ref   |
 PRIMARY,yr_mn_mk_rng_dr,yr_mn_st,yr_mn_pc,yr_mn_f,yr_mn_mk_st  | 
yr_mn_pc |
 13 | const,const,p.pcode |   41 | Using where
 |
 
+---+---+---
 
-+--+-+-+--+
 --+
 
 LIVE SERVER:
 
 EXPLAIN SELECT d.dcode, sum(qty) as total
 FROM table1 as d, db2.table2 as r, table3 as p
 WHERE year=2004  AND month=10 AND r.pcode=p.pcode and d.dcode=p.dcode 
and
 from_period = 200410 and to_period  200410 and d.region!='6'
 GROUP BY dcode
 
 
+---+--+
 
+-+-+-+---+-
 -+
 | table | type | possible_keys
 | key | key_len | ref | rows  | Extra
 |
 
+---+--+
 
+-+-+-+---+-
 -+
 | d | ALL  | [NULL]
 | [NULL]  |  [NULL] | [NULL]  |   322 | Using where; Using 
temporary;
 Using filesort |
 | r | ref  |
 PRIMARY,yr_mn_mk_rng_dr,yr_mn_st,yr_mn_pc,yr_mn_f,yr_mn_mk_st  | PRIMARY 
|
 8 | const,const | 89618 | Using where  |
 | p | ref  | PRIMARY
 | PRIMARY |   4 | r.pcode | 2 | Using where; Using index
 |
 
+---+--+
 
+-+-+-+---+-
 -+
 
 

These are two different plans. Your development machine is using the index 
yr_mn_pc on the r table and is joining that table last. On your production 
server, the r table is joined second and is joined by the index PRIMARY. 
Let me know how the ANALYZE TABLE I suggested in a previous message works 
out to help the statistics.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

RE: Help with query performance anomaly

[Graham Cossey]

[big snip]


 These are two different plans. Your development machine is using
 the index
 yr_mn_pc on the r table and is joining that table last. On your
 production
 server, the r table is joined second and is joined by the index PRIMARY.
 Let me know how the ANALYZE TABLE I suggested in a previous message works
 out to help the statistics.


I have run ANALYZE on all tables on the live server and the result of
EXPLAIN is the same as before.

Any further suggestions? I'm off to double check the create table stuff once
more...

Thanks for your help (and patience!!)

Graham



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

Re: Help with query performance anomaly

[Jamie Kinney]

How do the OS statistics look on both boxes.  Do top, sar, vmstat or
iostat show any CPU, memory or I/O performance issues?  Does anything
odd appear in the /var/log/messages file?

-Jamie

On Thu, 11 Nov 2004 18:42:48 -, Graham Cossey
[EMAIL PROTECTED] wrote:
 
 
 [big snip]
 
 
  These are two different plans. Your development machine is using
  the index
  yr_mn_pc on the r table and is joining that table last. On your
  production
  server, the r table is joined second and is joined by the index PRIMARY.
  Let me know how the ANALYZE TABLE I suggested in a previous message works
  out to help the statistics.
 
 
 I have run ANALYZE on all tables on the live server and the result of
 EXPLAIN is the same as before.
 
 Any further suggestions? I'm off to double check the create table stuff once
 more...
 
 Thanks for your help (and patience!!)
 
 Graham
 
 
 
 
 --
 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 performance anomaly

[Graham Cossey]

Everything seems fine except for this one query.
I'm not sure quite what the results of top will say or what they mean if
it's a Virtual Private Server environment?

Nothing untoward in /var/log/messages or var/log/httpd/error_log or virtual
host httpd logs.

Have just run 'top' on the live server...

Before running the query I get:

13:56:09  up 45 days, 11:47,  1 user,  load average: 0.00, 0.28, 0.44
24 processes: 23 sleeping, 1 running, 0 zombie, 0 stopped
CPU0 states:   0.0% user   0.0% system0.0% nice   0.0% iowait 100.0%
idle
CPU1 states:   0.0% user   0.0% system0.0% nice   0.0% iowait 100.0%
idle
CPU2 states:   0.0% user   0.1% system0.0% nice   0.0% iowait  99.4%
idle
CPU3 states:   0.0% user   0.0% system0.0% nice   0.0% iowait 100.0%
idle
Mem:  6203744k av, 6194148k used,9596k free,   0k shrd,  304848k
buff
  1948476k active,3601304k inactive
Swap: 4192956k av, 1876604k used, 2316352k free 4081216k
cached

  PID USER PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME CPU COMMAND
 7622   15   0   904  904   748 R 0.1  0.0   0:00   2 top
1 root   9   0   468  440   420 S 0.0  0.0   2:58   1 init
 1733 root   9   0   548  516   480 S 0.0  0.0   0:35   1 syslogd
 2242 root   8   0   808  736   684 S 0.0  0.0   0:11   0 xinetd
 3393 root   8   0   576  552   512 S 0.0  0.0   0:21   1 crond
15329 root   9   0  1284 1156  1088 S 0.0  0.0   0:27   2 sshd
 3264 root   8   0  3676 1548  1488 S 0.0  0.0   0:37   2 httpd
15296 apache 9   0  9904 8872  4752 S 0.0  0.1   0:00   2 httpd
 4576 apache 9   0  9876 8804  4344 S 0.0  0.1   0:01   1 httpd
 8992 root   9   0  1000 1000   848 S 0.0  0.0   0:00   1
mysqld_safe
10433 mysql  9   0 17036  16M  2012 S 0.0  0.2   0:00   2 mysqld
11360 mysql  8   0 17036  16M  2012 S 0.0  0.2   0:00   2 mysqld
11395 mysql  9   0 17036  16M  2012 S 0.0  0.2   0:00   0 mysqld
11425 mysql  9   0 17036  16M  2012 S 0.0  0.2   0:00   2 mysqld
11456 mysql  9   0 17036  16M  2012 S 0.0  0.2   0:00   3 mysqld
11491 mysql  9   0 17036  16M  2012 S 0.0  0.2   0:00   2 mysqld
12128 mysql  9   0 17036  16M  2012 S 0.0  0.2   0:00   2 mysqld
12162 mysql  9   0 17036  16M  2012 S 0.0  0.2   0:00   0 mysqld
12193 mysql  9   0 17036  16M  2012 S 0.0  0.2   0:00   2 mysqld
12224 mysql  9   0 17036  16M  2012 S 0.0  0.2   0:00   1 mysqld
32418 mysql  9   0 17036  16M  2012 S 0.0  0.2   0:00   2 mysqld
 5284    9   0  2288 2244  2068 S 0.0  0.0   0:00   3 sshd
 5538    9   0  1292 1292   984 S 0.0  0.0   0:00   1 bash

Now, I don't know if it's coincidental or not but after setting the query
running and re-issuing the top command I get:

 13:59:49  up 45 days, 11:51,  1 user,  load average: 0.98, 0.61, 0.53
26 processes: 24 sleeping, 2 running, 0 zombie, 0 stopped
CPU0 states:  75.0% user  25.0% system0.0% nice   0.0% iowait   0.0%
idle
CPU1 states:  76.0% user  24.0% system0.0% nice   0.0% iowait   0.0%
idle
CPU2 states:  63.0% user  36.0% system0.0% nice   0.0% iowait   0.0%
idle
Floating point exception

 Does not look good to me !!

Comments?
Advice?

Thanks
Graham

 -Original Message-
 From: Jamie Kinney [mailto:[EMAIL PROTECTED]
 Sent: 11 November 2004 19:25
 To: Graham Cossey
 Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
 Subject: Re: Help with query performance anomaly


 How do the OS statistics look on both boxes.  Do top, sar, vmstat or
 iostat show any CPU, memory or I/O performance issues?  Does anything
 odd appear in the /var/log/messages file?

 -Jamie

 On Thu, 11 Nov 2004 18:42:48 -, Graham Cossey
 [EMAIL PROTECTED] wrote:
 
 
  [big snip]
 
  
   These are two different plans. Your development machine is using
   the index
   yr_mn_pc on the r table and is joining that table last. On your
   production
   server, the r table is joined second and is joined by the
 index PRIMARY.
   Let me know how the ANALYZE TABLE I suggested in a previous
 message works
   out to help the statistics.
  
 
  I have run ANALYZE on all tables on the live server and the result of
  EXPLAIN is the same as before.
 
  Any further suggestions? I'm off to double check the create
 table stuff once
  more...
 
  Thanks for your help (and patience!!)
 
  Graham
 
 
 
 
  --
  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 performance anomaly

[Graham Cossey]

[snip]

 Have just run 'top' on the live server...

 Before running the query I get:

 13:56:09  up 45 days, 11:47,  1 user,  load average: 0.00, 0.28, 0.44
 24 processes: 23 sleeping, 1 running, 0 zombie, 0 stopped
 CPU0 states:   0.0% user   0.0% system0.0% nice   0.0% iowait 100.0%
 idle
 CPU1 states:   0.0% user   0.0% system0.0% nice   0.0% iowait 100.0%
 idle
 CPU2 states:   0.0% user   0.1% system0.0% nice   0.0% iowait  99.4%
 idle
 CPU3 states:   0.0% user   0.0% system0.0% nice   0.0% iowait 100.0%
 idle
 Mem:  6203744k av, 6194148k used,9596k free,   0k shrd,  304848k
 buff
   1948476k active,3601304k inactive
 Swap: 4192956k av, 1876604k used, 2316352k free 4081216k
 cached

   PID USER PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME CPU COMMAND
  7622   15   0   904  904   748 R 0.1  0.0   0:00   2 top
 1 root   9   0   468  440   420 S 0.0  0.0   2:58   1 init
  1733 root   9   0   548  516   480 S 0.0  0.0   0:35   1 syslogd
  2242 root   8   0   808  736   684 S 0.0  0.0   0:11   0 xinetd
  3393 root   8   0   576  552   512 S 0.0  0.0   0:21   1 crond
 15329 root   9   0  1284 1156  1088 S 0.0  0.0   0:27   2 sshd
  3264 root   8   0  3676 1548  1488 S 0.0  0.0   0:37   2 httpd
 15296 apache 9   0  9904 8872  4752 S 0.0  0.1   0:00   2 httpd
  4576 apache 9   0  9876 8804  4344 S 0.0  0.1   0:01   1 httpd
  8992 root   9   0  1000 1000   848 S 0.0  0.0   0:00   1
 mysqld_safe
 10433 mysql  9   0 17036  16M  2012 S 0.0  0.2   0:00   2 mysqld
 11360 mysql  8   0 17036  16M  2012 S 0.0  0.2   0:00   2 mysqld
 11395 mysql  9   0 17036  16M  2012 S 0.0  0.2   0:00   0 mysqld
 11425 mysql  9   0 17036  16M  2012 S 0.0  0.2   0:00   2 mysqld
 11456 mysql  9   0 17036  16M  2012 S 0.0  0.2   0:00   3 mysqld
 11491 mysql  9   0 17036  16M  2012 S 0.0  0.2   0:00   2 mysqld
 12128 mysql  9   0 17036  16M  2012 S 0.0  0.2   0:00   2 mysqld
 12162 mysql  9   0 17036  16M  2012 S 0.0  0.2   0:00   0 mysqld
 12193 mysql  9   0 17036  16M  2012 S 0.0  0.2   0:00   2 mysqld
 12224 mysql  9   0 17036  16M  2012 S 0.0  0.2   0:00   1 mysqld
 32418 mysql  9   0 17036  16M  2012 S 0.0  0.2   0:00   2 mysqld
  5284    9   0  2288 2244  2068 S 0.0  0.0   0:00   3 sshd
  5538    9   0  1292 1292   984 S 0.0  0.0   0:00   1 bash

 Now, I don't know if it's coincidental or not but after setting the query
 running and re-issuing the top command I get:

  13:59:49  up 45 days, 11:51,  1 user,  load average: 0.98, 0.61, 0.53
 26 processes: 24 sleeping, 2 running, 0 zombie, 0 stopped
 CPU0 states:  75.0% user  25.0% system0.0% nice   0.0% iowait   0.0%
 idle
 CPU1 states:  76.0% user  24.0% system0.0% nice   0.0% iowait   0.0%
 idle
 CPU2 states:  63.0% user  36.0% system0.0% nice   0.0% iowait   0.0%
 idle
 Floating point exception

  Does not look good to me !!

[snip]

I have now managed to get a top while the query is running:

14:29:52  up 45 days, 12:21,  1 user,  load average: 0.69, 0.28, 0.39
25 processes: 23 sleeping, 2 running, 0 zombie, 0 stopped
CPU0 states:  71.1% user  28.0% system0.0% nice   0.0% iowait   0.0%
idle
CPU1 states:  68.0% user  31.0% system0.0% nice   0.0% iowait   0.0%
idle
CPU2 states:  71.0% user  28.0% system0.0% nice   0.0% iowait   0.0%
idle
CPU3 states:  80.0% user  19.0% system0.0% nice   0.0% iowait   0.0%
idle
Mem:  6203744k av, 5764148k used,  439596k free,   0k shrd,  257900k
buff
  1839520k active,3282316k inactive
Swap: 4192956k av, 1881496k used, 2311460k free 3687672k
cached

  PID USER PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME CPU COMMAND
19462 mysql 14   0 19968  19M  2016 R95.4  0.3   0:38   0 mysqld
25248   10   0  1004 1004   748 R 0.3  0.0   0:00   3 top
1 root   9   0   468  440   420 S 0.0  0.0   2:58   1 init
 1733 root   9   0   548  516   480 S 0.0  0.0   0:35   3 syslogd
 2242 root   8   0   808  736   684 S 0.0  0.0   0:11   3 xinetd
 3393 root   9   0   576  552   512 S 0.0  0.0   0:21   2 crond
15329 root   9   0  1284 1156  1088 S 0.0  0.0   0:27   0 sshd
 3264 root   9   0  3676 1548  1500 S 0.0  0.0   0:37   1 httpd
15296 apache 9   0 10632 9608  4768 S 0.0  0.1   0:01   0 httpd
 4576 apache 9   0 10036 8964  4344 S 0.0  0.1   0:01   3 httpd
 8992 root   9   0  1000 1000   848 S 0.0  0.0   0:00   1
mysqld_safe
10433 mysql  9   0 19968  19M  2016 S 0.0  0.3   0:00   1 mysqld
11360 mysql  9   0 19968  19M  2016 S 0.0  0.3   0:00   2 mysqld
11395 mysql  9   0 19968  19M  2016 S 0.0  0.3   0:00   0 mysqld
11425 mysql  9   0 19968  19M  2016 S 0.0  0.3   0:00   2 mysqld
11456 mysql  

Re: Help with query

[Jigal van Hemert]

SELECT `TABLE_1`.* FROM `TABLE_1` JOIN `TABLE_2` USING (`id`) WHERE
`TABLE_2`.`id` IS NULL
Asuming that the reference is the id field...

Regards, Jigal.
- Original Message - 
From: Ronan Lucio [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Wednesday, October 27, 2004 3:12 PM
Subject: Help with query


 Hi,

 I have two tables:

 TABLE_1
 ===
 - id
 - name

 TABLE_2
 ===
 - id
 - table1_id
 - name

 How could I make a select on table_1 that returns me only the
 rows that don´t have any reference in table_2?

 Any help would be appreciated.

 Thank´s,
 Ronan



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


 !DSPAM:417f9f13272296489013257!



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

Re: Help with query

[SGreen]

This is a very FAQ:

SELECT t1.*
FROM TABLE_1 t1
LEFT JOIN TABLE_2 t2
ON t1.id = t2.table1_id
WHERE t2.id is null

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine 

Ronan Lucio [EMAIL PROTECTED] wrote on 10/27/2004 10:12:42 AM:

 Hi,
 
 I have two tables:
 
 TABLE_1
 ===
 - id
 - name
 
 TABLE_2
 ===
 - id
 - table1_id
 - name
 
 How could I make a select on table_1 that returns me only the
 rows that don´t have any reference in table_2?
 
 Any help would be appreciated.
 
 Thank´s,
 Ronan
 
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 

Re: Help with query

[Ronan Lucio]

Shawn,

Thank you very for your answer.

Actually, I thought that a main SELECT couldn´t be filtered
by the WHERE clause refered to a field in a LEFT JOIN.

Now, looking better in the JOIN documentation I see this
issue.

Thank´s,
Ronan
  This is a very FAQ: 

  SELECT t1.* 
  FROM TABLE_1 t1 
  LEFT JOIN TABLE_2 t2 
  ON t1.id = t2.table1_id 
  WHERE t2.id is null 

  Shawn Green
  Database Administrator
  Unimin Corporation - Spruce Pine 

  Ronan Lucio [EMAIL PROTECTED] wrote on 10/27/2004 10:12:42 AM:

   Hi,
   
   I have two tables:
   
   TABLE_1
   ===
   - id
   - name
   
   TABLE_2
   ===
   - id
   - table1_id
   - name
   
   How could I make a select on table_1 that returns me only the
   rows that don´t have any reference in table_2?
   
   Any help would be appreciated.
   
   Thank´s,
   Ronan
   
   
   
   -- 
   MySQL General Mailing List
   For list archives: http://lists.mysql.com/mysql
   To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
   

Re: Help with query

[SGreen]

SELECT CityName, Count(ClientID) as ClientCount
FROM City
INNER JOIN Client
on City.CityID = Client.CityID
GROUP BY CityName
HAVING ClientCount  30;


Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Ronan Lucio [EMAIL PROTECTED] wrote on 09/10/2004 11:14:37 AM:

 Hello,
 
 A have two tables:
 
 City:
 CityID
 CityName
 
 Client:
 ClientID
 ClienteName
 CityID
 
 How can I make a SELECT that returns me only the cities
 that have more than 30 (example) clients?
 
 Thanks,
 Ronan
 
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 

Re: Help with query

[Ronan Lucio]

Shawn

 SELECT CityName, Count(ClientID) as ClientCount
 FROM City
 INNER JOIN Client
 on City.CityID = Client.CityID
 GROUP BY CityName
 HAVING ClientCount  30;

Thank you very much,
It should solve by problem... :-)

Ronan



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

RE: Help with query

[Schwartz, Evelyn]

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

-Original Message- 
From: Erich Beyrent [mailto:[EMAIL PROTECTED] 
Sent: Fri 2/6/2004 8:46 AM 
To: [EMAIL PROTECTED] 
Cc: 
Subject: Help with query



Hi everyone,

I am having a rough time with a query, which seems to be taking so long
it hangs the systems.


SELECT
l.CatalogNumber,
l.PDFLink,
l.PDFName,
l.Title,
p.PublisherName,
c.ComposerLname,
a.ArrangerLname,
l.Price,
l.Description,
o.Alias
FROM
listings l,
publishers p,
composers c,
arrangers a,
categories o
WHERE
a.ArrangerLname like '%$Criteria%' or
p.PublisherName like '%$Criteria%' or
c.ComposerLname like '%$Criteria%' or
l.Title like '%$Criteria%' or
l.CatalogNumber like '%$Criteria%'
AND
l.PublisherID=p.PublisherID and
l.ComposerID=c.ComposerID and
l.ArrangerID=a.ArrangerID
ORDER BY
o.Alias;


How can I rewrite this query to be efficient (and functioning!) - I am
fairly new to MySQL and could use lots of advice!

Thanks!

-Erich-



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

RE: Help with query

[Erich Beyrent]

Hi Evelyn,
 
How would I do that - would something like this be what you had in mind?
 
left join categories o on o.CategoryID = l.CategoryID
 
 
This goes in the WHERE clause, right?
 
Thanks!
 
-Erich-
 
 
-Original Message-
From: Schwartz, Evelyn [mailto:[EMAIL PROTECTED] 
Sent: Friday, February 06, 2004 8:53 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: Help with query
 
You will need parentheses around the 'or' clauses of your where clause.  
 
You also don't seem to join the categories table with any other tables.  If
you don't join tables you will create what is called a 'cross product'
query.  If table A has 10 rows and table B has 20 rows then querying A and B
will return 200 rows (every row of A will be joined with every row of B!).
 
 
-Original Message- 
From: Erich Beyrent [mailto:[EMAIL PROTECTED] 
Sent: Fri 2/6/2004 8:46 AM 
To: [EMAIL PROTECTED] 
Cc: 
Subject: Help with query
Hi everyone,

I am having a rough time with a query, which seems to be taking so long
it hangs the systems.


SELECT
l.CatalogNumber,
l.PDFLink,
l.PDFName,
l.Title,
p.PublisherName,
c.ComposerLname,
a.ArrangerLname,
l.Price,
l.Description,
o.Alias
FROM
listings l,
publishers p,
composers c,
arrangers a,
categories o
WHERE
a.ArrangerLname like '%$Criteria%' or
p.PublisherName like '%$Criteria%' or
c.ComposerLname like '%$Criteria%' or
l.Title like '%$Criteria%' or
l.CatalogNumber like '%$Criteria%'
AND
l.PublisherID=p.PublisherID and
l.ComposerID=c.ComposerID and
l.ArrangerID=a.ArrangerID
ORDER BY
o.Alias;


How can I rewrite this query to be efficient (and functioning!) - I am
fairly new to MySQL and could use lots of advice!

Thanks!

-Erich-



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

[Schwartz, Evelyn]

If every record in the listing table will have a corresponding record in the category 
table you may just include the category clause in with the rest.  
 
WHERE
(a.ArrangerLname like '%$Criteria%' or
p.PublisherName like '%$Criteria%' or
c.ComposerLname like '%$Criteria%' or
l.Title like '%$Criteria%' or
l.CatalogNumber like '%$Criteria%')
AND
l.PublisherID=p.PublisherID and
l.ComposerID=c.ComposerID and
l.ArrangerID=a.ArrangerID and
l.CategoryID=o.CategoryID

-Original Message- 
From: Erich Beyrent [mailto:[EMAIL PROTECTED] 
Sent: Fri 2/6/2004 9:00 AM 
To: [EMAIL PROTECTED] 
Cc: 
Subject: RE: Help with query



Hi Evelyn,

How would I do that - would something like this be what you had in mind?

left join categories o on o.CategoryID = l.CategoryID


This goes in the WHERE clause, right?

Thanks!

-Erich-


-Original Message-
From: Schwartz, Evelyn [mailto:[EMAIL PROTECTED]
Sent: Friday, February 06, 2004 8:53 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: Help with query

You will need parentheses around the 'or' clauses of your where clause. 

You also don't seem to join the categories table with any other tables.  If
you don't join tables you will create what is called a 'cross product'
query.  If table A has 10 rows and table B has 20 rows then querying A and B
will return 200 rows (every row of A will be joined with every row of B!).


-Original Message-
From: Erich Beyrent [mailto:[EMAIL PROTECTED]
Sent: Fri 2/6/2004 8:46 AM
To: [EMAIL PROTECTED]
Cc:
Subject: Help with query
Hi everyone,

I am having a rough time with a query, which seems to be taking so long
it hangs the systems.


SELECT
l.CatalogNumber,
l.PDFLink,
l.PDFName,
l.Title,
p.PublisherName,
c.ComposerLname,
a.ArrangerLname,
l.Price,
l.Description,
o.Alias
FROM
listings l,
publishers p,
composers c,
arrangers a,
categories o
WHERE
a.ArrangerLname like '%$Criteria%' or
p.PublisherName like '%$Criteria%' or
c.ComposerLname like '%$Criteria%' or
l.Title like '%$Criteria%' or
l.CatalogNumber like '%$Criteria%'
AND
l.PublisherID=p.PublisherID and
l.ComposerID=c.ComposerID and
l.ArrangerID=a.ArrangerID
ORDER BY
o.Alias;


How can I rewrite this query to be efficient (and functioning!) - I am
fairly new to MySQL and could use lots of advice!

Thanks!

-Erich-



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

RE: Help with query

[Erich Beyrent]

OH MY GOD IT WORKS!!!  I got 32 rows in set (0.08 sec).

That is fantastic!  Thank you so much

Now, when I do an explain on this query, I get the following:
+---++---+-+
| table | type   | possible_keys | key |
+---++---+-+
| l | ALL| NULL  | NULL|
| p | eq_ref | PRIMARY   | PRIMARY |
| c | eq_ref | PRIMARY   | PRIMARY |
| a | eq_ref | PRIMARY   | PRIMARY |
| o | eq_ref | PRIMARY   | PRIMARY |
+---++---+-+
+-+---+--+-+
| key_len | ref   | rows | Extra   |
+-+---+--+-+
|NULL | NULL  | 2647 | Using temporary; Using filesort |
|   8 | l.PublisherID |1 | Using where |
|   8 | l.ComposerID  |1 | Using where |
|   8 | l.ArrangerID  |1 | Using where |
|   4 | l.CategoryID  |1 | |
+-+---+--+-+


This seems really efficient, since the only large number of rows to
search against is the main listings table, if I read this right.  Is
there any further optimization that I can do, or this as good as it
gets?  Believe me, I am NOT complaining!!!

Thanks again!

-Erich-


 If every record in the listing table will have a corresponding record
in  
 the category table you may just include the category clause in with
the 
 rest.  
  
 WHERE
 (a.ArrangerLname like '%$Criteria%' or
 p.PublisherName like '%$Criteria%' or
 c.ComposerLname like '%$Criteria%' or
 l.Title like '%$Criteria%' or
 l.CatalogNumber like '%$Criteria%')
 AND
 l.PublisherID=p.PublisherID and
 l.ComposerID=c.ComposerID and
 l.ArrangerID=a.ArrangerID and
 l.CategoryID=o.CategoryID

   
   

   Hi Evelyn,
   
   How would I do that - would something like this be what you had
in  
 mind?
   
   left join categories o on o.CategoryID = l.CategoryID
   
   
   This goes in the WHERE clause, right?
   
   Thanks!
   
   -Erich-
   
   
   -Original Message-
   From: Schwartz, Evelyn [mailto:[EMAIL PROTECTED]
   Sent: Friday, February 06, 2004 8:53 AM
   To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
   Subject: RE: Help with query
   
   You will need parentheses around the 'or' clauses of your where
 
 clause. 
   
   You also don't seem to join the categories table with any other 

 tables.  If
   you don't join tables you will create what is called a 'cross
product'
   query.  If table A has 10 rows and table B has 20 rows then
querying A  and B
   will return 200 rows (every row of A will be joined with every
row of  B!).


-Original Message-
From: Erich Beyrent [mailto:[EMAIL PROTECTED]
Sent: Fri 2/6/2004 8:46 AM
To: [EMAIL PROTECTED]
Cc:
Subject: Help with query
Hi everyone,

I am having a rough time with a query, which seems to be taking
so long
it hangs the systems.


SELECT
l.CatalogNumber,
l.PDFLink,
l.PDFName,
l.Title,
p.PublisherName,
c.ComposerLname,
a.ArrangerLname,
l.Price,
l.Description,
o.Alias
FROM
listings l,
publishers p,
composers c,
arrangers a,
categories o
WHERE
a.ArrangerLname like '%$Criteria%' or
p.PublisherName like '%$Criteria%' or
c.ComposerLname like '%$Criteria%' or
l.Title like '%$Criteria%' or
l.CatalogNumber like '%$Criteria%'
AND
l.PublisherID=p.PublisherID and
l.ComposerID=c.ComposerID and
l.ArrangerID=a.ArrangerID
ORDER BY
o.Alias;


How can I rewrite this query to be efficient (and functioning!)
- I am
fairly new to MySQL and could use lots of advice!

Thanks!

-Erich-



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

[David Hodgkinson]

On 6 Feb 2004, at 14:38, Erich Beyrent wrote:
This seems really efficient, since the only large number of rows to
search against is the main listings table, if I read this right.  Is
there any further optimization that I can do, or this as good as it
gets?  Believe me, I am NOT complaining!!!
Yes, it has to to a table scan on the criteria because of the
leading %: it can't use an index for that. And 2500-odd rows
is nothing.
--
Dave Hodgkinson
CTO, Rockit Factory Ltd.
http://www.rockitfactory.com/
Web sites for rock bands
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]