Re: help with query to count rows while excluding certain rows
On Fri, Jan 1, 2016 at 9:31 PM, Peter Brawleywrote: > 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
On Fri, Jan 1, 2016 at 2:12 PM, Peter Brawleywrote: > 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
On 12/31/2015 0:51, Larry Martell wrote: I need to count the number of rows in a table that are grouped by a list of columns, but I also need to exclude rows that have more then some count when grouped by a different set of columns. Conceptually, this is not hard, but I am having trouble doing this efficiently. My first counting query would be this: SELECT count(*) FROM cst_rollup GROUP BY target_name_id, ep, roiname, recipe_process, recipe_product, recipe_layer, f_tag_bottom, measname, recipe_id But from this count I need to subtract the count of rows that have more then 50 rows with a different grouping: SELECT count(*) FROM cst_rollup GROUP BY target_name_id, ep, wafer_id HAVING count(*) >= 50 As you can see, the second query has wafer_id, but the first query does not. Currently I am doing this in python, and it's slow. In my current implementation I have one query, and it selects the columns (i.e. doesn't just count), and I have added wafer_id: SELECT target_name_id, ep, roiname, recipe_process, recipe_product, recipe_layer, f_tag_bottom, measname, recipe_id, wafer_id FROM cst_rollup Then I go through the result set (which can be over 200k rows) and I count the number of rows with matching (target_name_id, ep, wafer_id). Then I go through the rows again and regroup them without wafer_id, but skipping the rows that have more then 50 rows for that row's (target_name_id, ep, wafer_id). Is this clear to everyone what I am trying to do? If I've understand this correctly, the resultset you wish to aggregate on is ... select target_name_id, ep, wafer_id from cst_rollup a left join ( -- exclude rows for which wafer_id count >= 50 select name_id, ep, wafer, count(*) n from cst_rollup group by target_name_id, ep, wafer_id having n >= 50 ) b using ( target_name_id, ep, wafer_id ) where b.target_name is null ; If that's so, you could assemble that resultset in a temp table then run the desired aggregate query on it, or you could aggregate on it directly as a subquery. PB - I'd like to do this all in sql with count because then I do not have to actually return and parse the data in python. Can anyone think of a way to do this in sql in a way that will be more efficient then my current implementation? Thanks! -Larry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
help with query to count rows while excluding certain rows
I need to count the number of rows in a table that are grouped by a list of columns, but I also need to exclude rows that have more then some count when grouped by a different set of columns. Conceptually, this is not hard, but I am having trouble doing this efficiently. My first counting query would be this: SELECT count(*) FROM cst_rollup GROUP BY target_name_id, ep, roiname, recipe_process, recipe_product, recipe_layer, f_tag_bottom, measname, recipe_id But from this count I need to subtract the count of rows that have more then 50 rows with a different grouping: SELECT count(*) FROM cst_rollup GROUP BY target_name_id, ep, wafer_id HAVING count(*) >= 50 As you can see, the second query has wafer_id, but the first query does not. Currently I am doing this in python, and it's slow. In my current implementation I have one query, and it selects the columns (i.e. doesn't just count), and I have added wafer_id: SELECT target_name_id, ep, roiname, recipe_process, recipe_product, recipe_layer, f_tag_bottom, measname, recipe_id, wafer_id FROM cst_rollup Then I go through the result set (which can be over 200k rows) and I count the number of rows with matching (target_name_id, ep, wafer_id). Then I go through the rows again and regroup them without wafer_id, but skipping the rows that have more then 50 rows for that row's (target_name_id, ep, wafer_id). Is this clear to everyone what I am trying to do? I'd like to do this all in sql with count because then I do not have to actually return and parse the data in python. Can anyone think of a way to do this in sql in a way that will be more efficient then my current implementation? Thanks! -Larry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Help improving query performance
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
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
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
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
Hello Larry, On 2/4/2015 3:37 PM, Larry Martell wrote: On Wed, Feb 4, 2015 at 3:25 PM, shawn l.green shawn.l.gr...@oracle.com wrote: Hi Larry, On 2/4/2015 3:18 PM, Larry Martell wrote: On Wed, Feb 4, 2015 at 2:56 PM, shawn l.green shawn.l.gr...@oracle.com wrote: Hi Larry, On 2/1/2015 4:49 PM, Larry Martell wrote: I have 2 queries. One takes 4 hours to run and returns 21 rows, and the other, which has 1 additional where clause, takes 3 minutes and returns 20 rows. The main table being selected from is largish (37,247,884 rows with 282 columns). Caching is off for my testing, so it's not related to that. To short circuit anyone asking, these queries are generated by python code, which is why there's an IN clause with 1 value, as oppose to an =. Here are the queries and their explains. The significant difference is that the faster query has Using intersect(data_cst_bbccbce0,data_cst_fba12377) in the query plan - those 2 indexes are on the 2 columns in the where clause, so that's why the second one is faster. But I am wondering what I can do to make the first one faster. 4 hour query: SELECT MIN(data_tool.name) as tool, MIN(data_cst.date_time) start, MAX(data_cst.date_time) end, MIN(data_target.name) as target, MIN(data_lot.name) as lot, MIN(data_wafer.name) as wafer, MIN(measname) as measname, MIN(data_recipe.name) as recipe FROM data_cst INNER JOIN data_tool ON data_tool.id = data_cst.tool_id INNER JOIN data_target ON data_target.id = data_cst.target_name_id INNER JOIN data_lot ON data_lot.id = data_cst.lot_id INNER JOIN data_wafer ON data_wafer.id = data_cst.wafer_id INNER JOIN data_measparams ON data_measparams.id = data_cst.meas_params_name_id INNER JOIN data_recipe ON data_recipe.id = data_cst.recipe_id WHERE data_target.id IN (172) AND data_cst.date_time BETWEEN '2015-01-26 00:00:00' AND '2015-01-26 23:59:59' GROUP BY wafer_id, data_cst.lot_id, target_name_id ... snipped ... Faster query: SELECT MIN(data_tool.name) as tool, MIN(data_cst.date_time) start, MAX(data_cst.date_time) end, MIN(data_target.name) as target, MIN(data_lot.name) as lot, MIN(data_wafer.name) as wafer, MIN(measname) as measname, MIN(data_recipe.name) as recipe FROM data_cst INNER JOIN data_tool ON data_tool.id = data_cst.tool_id INNER JOIN data_target ON data_target.id = data_cst.target_name_id INNER JOIN data_lot ON data_lot.id = data_cst.lot_id INNER JOIN data_wafer ON data_wafer.id = data_cst.wafer_id INNER JOIN data_measparams ON data_measparams.id = data_cst.meas_params_name_id INNER JOIN data_recipe ON data_recipe.id = data_cst.recipe_id WHERE data_target.id IN (172) AND data_recipe.id IN (148) AND data_cst.date_time BETWEEN '2015-01-26 00:00:00' AND '2015-01-26 23:59:59' GROUP BY wafer_id, data_cst.lot_id, target_name_id ... snip ... Thanks for taking the time to read this, and for any help or pointers you can give me. The biggest difference is the added selectivity generated by the WHERE term against the data_recipe table. Compare the two EXPLAINS, in the faster query you see that data_recipe is listed second. This allows the additional term a chance to reduce the number of row combinations for the entire query. To really get at the logic behind how the Optimizer chooses its execution plan, get an optimizer trace. Look at the cost estimates for each phase being considered. http://dev.mysql.com/doc/refman/5.6/en/optimizer-trace-table.html http://dev.mysql.com/doc/internals/en/optimizer-tracing.html Thanks very much Shawn for the reply and the links. I will check those out and I'm sure I will find them very useful. Meanwhile I changed the query to select from data_cst using the where clause into a temp table and then I join the temp table with the other tables. That has improved the slow query from 4 hours to 10 seconds (!) Did you also add an index to the temporary table for the JOIN condition? It might make it even faster No, I didn't. I (and the users) were so shocked and happy with the massive improvement I moved on to make similar changes to other queries. This is a django app, and it's a one-shot deal - i.e. there's just the one query run and the response is sent back to the browser and that's the end of the session and the temp table. So I'm thinking it's probably not worth it. As an aside this change has messed up all my unit tests - they send multiple requests, but they're all in the same session. So only the first succeeds and the next one fails because the temp table already exists. I haven't figured out how to get it run each request in its own session. I guess I'm going to have to drop the temp table after I join with it before I sent the response back. If... * it's a MEMORY temp table * it's always the same table design Then, you can use DELETE to clear the content (it's faster than DROP or
Help improving query performance
I have 2 queries. One takes 4 hours to run and returns 21 rows, and the other, which has 1 additional where clause, takes 3 minutes and returns 20 rows. The main table being selected from is largish (37,247,884 rows with 282 columns). Caching is off for my testing, so it's not related to that. To short circuit anyone asking, these queries are generated by python code, which is why there's an IN clause with 1 value, as oppose to an =. Here are the queries and their explains. The significant difference is that the faster query has Using intersect(data_cst_bbccbce0,data_cst_fba12377) in the query plan - those 2 indexes are on the 2 columns in the where clause, so that's why the second one is faster. But I am wondering what I can do to make the first one faster. 4 hour query: SELECT MIN(data_tool.name) as tool, MIN(data_cst.date_time) start, MAX(data_cst.date_time) end, MIN(data_target.name) as target, MIN(data_lot.name) as lot, MIN(data_wafer.name) as wafer, MIN(measname) as measname, MIN(data_recipe.name) as recipe FROM data_cst INNER JOIN data_tool ON data_tool.id = data_cst.tool_id INNER JOIN data_target ON data_target.id = data_cst.target_name_id INNER JOIN data_lot ON data_lot.id = data_cst.lot_id INNER JOIN data_wafer ON data_wafer.id = data_cst.wafer_id INNER JOIN data_measparams ON data_measparams.id = data_cst.meas_params_name_id INNER JOIN data_recipe ON data_recipe.id = data_cst.recipe_id WHERE data_target.id IN (172) AND data_cst.date_time BETWEEN '2015-01-26 00:00:00' AND '2015-01-26 23:59:59' GROUP BY wafer_id, data_cst.lot_id, target_name_id Explain: ++-+-++---+---+-+-+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+-++---+---+-+-+--+-+ | 1 | SIMPLE | data_target | const | PRIMARY | PRIMARY | 4 | const |1 | Using temporary; Using filesort | | 1 | SIMPLE | data_measparams | index | PRIMARY | PRIMARY | 4 | NULL | 767 | Using index | | 1 | SIMPLE | data_cst| ref| data_cst_4262c4f4,data_cst_01213b5c,data_cst_bbccbce0,data_cst_b0229011,data_cst_65c0,data_cst_fba12377,data_cst_634020d0 | data_cst_634020d0 | 5 | motor_gf.data_measparams.id | 48 | Using where | | 1 | SIMPLE | data_tool | eq_ref | PRIMARY | PRIMARY | 4 | motor_gf.data_cst.tool_id |1 | NULL| | 1 | SIMPLE | data_recipe | eq_ref | PRIMARY | PRIMARY | 4 | motor_gf.data_cst.recipe_id |1 | NULL| | 1 | SIMPLE | data_lot| eq_ref | PRIMARY | PRIMARY | 4 | motor_gf.data_cst.lot_id|1 | NULL| | 1 | SIMPLE | data_wafer | eq_ref | PRIMARY | PRIMARY | 4 | motor_gf.data_cst.wafer_id |1 | NULL| ++-+-++---+---+-+-+--+-+ Faster query: SELECT MIN(data_tool.name) as tool, MIN(data_cst.date_time) start, MAX(data_cst.date_time) end, MIN(data_target.name) as target, MIN(data_lot.name) as lot, MIN(data_wafer.name) as wafer, MIN(measname) as measname, MIN(data_recipe.name) as recipe FROM data_cst INNER JOIN data_tool ON data_tool.id = data_cst.tool_id INNER JOIN data_target ON data_target.id = data_cst.target_name_id INNER JOIN data_lot ON data_lot.id = data_cst.lot_id INNER JOIN data_wafer ON data_wafer.id = data_cst.wafer_id INNER JOIN data_measparams ON data_measparams.id = data_cst.meas_params_name_id INNER JOIN data_recipe ON data_recipe.id = data_cst.recipe_id WHERE data_target.id IN (172) AND data_recipe.id IN (148) AND data_cst.date_time BETWEEN '2015-01-26 00:00:00' AND '2015-01-26 23:59:59' GROUP BY wafer_id, data_cst.lot_id,
Re: Help optimize query.
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.
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.
Let's see the results of Explain Extended this query, result of Show Create Table cc_member_queue_end_log. PB - On 2014-11-13 1:34 PM, Mimiko wrote: Hello. I have this table: show create table cc_agents_tier_status_log: CREATE TABLE cc_agents_tier_status_log ( id int(10) unsigned NOT NULL AUTO_INCREMENT, date_log timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, cc_agent varchar(45) NOT NULL, cc_agent_tier_status_id tinyint(3) unsigned NOT NULL, cc_queue_id tinyint(3) unsigned NOT NULL, cc_agent_id int(10) unsigned NOT NULL, cc_agent_phone smallint(5) unsigned NOT NULL, cc_agent_domain varchar(45) NOT NULL DEFAULT 'pbx01.apa-canal.md', PRIMARY KEY (id), KEY IDX_cc_agents_tier_status_log_2 (cc_agent) USING HASH, KEY IDX_cc_agents_tier_status_log_3 (date_log), KEY FK_cc_agents_tier_status_log_2 (cc_agent_id), KEY FK_cc_agents_tier_status_log_3 (cc_queue_id), KEY FK_cc_agents_tier_status_log_1 (cc_agent_tier_status_id) USING BTREE, KEY IDX_cc_agents_tier_status_log_7 (id,date_log), CONSTRAINT FK_cc_agents_tier_status_log_1 FOREIGN KEY (cc_agent_tier_status_id) REFERENCES cc_agent_tier_status_chart (id) ON UPDATE CASCADE, CONSTRAINT FK_cc_agents_tier_status_log_2 FOREIGN KEY (cc_agent_id) REFERENCES apacanal.employee (id) ON UPDATE CASCADE, CONSTRAINT FK_cc_agents_tier_status_log_3 FOREIGN KEY (cc_queue_id) REFERENCES cc_queues (id) ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=23799 DEFAULT CHARSET=ascii show index from cc_agents_tier_status_log: TableNon_uniqueKey_nameSeq_in_indexColumn_name Collation CardinalitySub_partPackedNull Index_type CommentIndex_comment cc_agents_tier_status_log0PRIMARY1idA 23999(null)BTREE(null) (null) cc_agents_tier_status_log1 IDX_cc_agents_tier_status_log_21 cc_agentA 260(null)BTREE(null)(null) cc_agents_tier_status_log1 IDX_cc_agents_tier_status_log_31 date_logA 23999(null)BTREE(null)(null) cc_agents_tier_status_log1 FK_cc_agents_tier_status_log_21 cc_agent_idA 2(null)BTREE(null)(null) cc_agents_tier_status_log1 FK_cc_agents_tier_status_log_31 cc_queue_idA 14(null)BTREE(null)(null) cc_agents_tier_status_log1 FK_cc_agents_tier_status_log_11 cc_agent_tier_status_id A2(null)BTREE(null) (null) cc_agents_tier_status_log1 IDX_cc_agents_tier_status_log_7 1 idA23999(null)BTREE(null) (null) cc_agents_tier_status_log1 IDX_cc_agents_tier_status_log_72 date_logA 23999(null)BTREE(null)(null) And the query is: set @enddate:=now(); set @startdate:='2014-11-01'; set @que_id:=-1; select s.theHour as theHour,avg(s.nrAgents) as nrAgents from (select date(a.theDateHour) as theDate,extract(hour from a.theDateHour) as theHour,count(c.cc_agent_tier_status_id) as nrAgents from ( select dh.theDateHour as theDateHour, max(c.date_log) as maxdatelog,c.* FROM ( select concat(d.thedate,' ',h.theHour,':0:0') as theDateHour from ( select DATE(DATE_ADD(date(@startdate), INTERVAL @i:=@i+1 DAY) ) as theDate from (select @i:=-1) as t1 inner join cc_member_queue_end_log b on 1=1 and b.id=datediff(@enddate,@startdate)+1 ) as d left outer join (SELECT 0 AS theHour UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23) as h on 1=1 ) AS dh left outer join cc_agents_tier_status_log as c on c.date_log=dh.theDateHour where (if(@queue_id0,1,0) or if(@queue_id=c.cc_queue_id,1,0)) group by dh.theDateHour,c.cc_queue_id,c.cc_agent_id,c.cc_agent_phone ) as a left outer join cc_agents_tier_status_log as c on c.date_log=a.maxdatelog and c.cc_queue_id=a.cc_queue_id and c.cc_agent_id=a.cc_agent_id and c.cc_agent_phone=a.cc_agent_phone and c.cc_agent_tier_status_id=2 group by a.theDateHour order by date(a.theDateHour),extract(hour from a.theDateHour)) as s group by s.theHour order by s.theHour; This query takes 20 seconds to populate. Table cc_agents_tier_status_log contains log entries of agent_id login/logout per queue per phone. status_id can have value 1 (logged out) and 2 (login) at date_log datetime. The resulting table must contain average number of agents logged in at every hour per startdate to enddate. Hope for some hints. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Help optimize query.
Hello. I have this table: show create table cc_agents_tier_status_log: CREATE TABLE cc_agents_tier_status_log ( id int(10) unsigned NOT NULL AUTO_INCREMENT, date_log timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, cc_agent varchar(45) NOT NULL, cc_agent_tier_status_id tinyint(3) unsigned NOT NULL, cc_queue_id tinyint(3) unsigned NOT NULL, cc_agent_id int(10) unsigned NOT NULL, cc_agent_phone smallint(5) unsigned NOT NULL, cc_agent_domain varchar(45) NOT NULL DEFAULT 'pbx01.apa-canal.md', PRIMARY KEY (id), KEY IDX_cc_agents_tier_status_log_2 (cc_agent) USING HASH, KEY IDX_cc_agents_tier_status_log_3 (date_log), KEY FK_cc_agents_tier_status_log_2 (cc_agent_id), KEY FK_cc_agents_tier_status_log_3 (cc_queue_id), KEY FK_cc_agents_tier_status_log_1 (cc_agent_tier_status_id) USING BTREE, KEY IDX_cc_agents_tier_status_log_7 (id,date_log), CONSTRAINT FK_cc_agents_tier_status_log_1 FOREIGN KEY (cc_agent_tier_status_id) REFERENCES cc_agent_tier_status_chart (id) ON UPDATE CASCADE, CONSTRAINT FK_cc_agents_tier_status_log_2 FOREIGN KEY (cc_agent_id) REFERENCES apacanal.employee (id) ON UPDATE CASCADE, CONSTRAINT FK_cc_agents_tier_status_log_3 FOREIGN KEY (cc_queue_id) REFERENCES cc_queues (id) ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=23799 DEFAULT CHARSET=ascii show index from cc_agents_tier_status_log: Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment cc_agents_tier_status_log 0 PRIMARY 1 id A 23999 (null) BTREE (null) (null) cc_agents_tier_status_log 1 IDX_cc_agents_tier_status_log_2 1 cc_agent A 260 (null) BTREE (null) (null) cc_agents_tier_status_log 1 IDX_cc_agents_tier_status_log_3 1 date_log A 23999 (null) BTREE (null) (null) cc_agents_tier_status_log 1 FK_cc_agents_tier_status_log_2 1 cc_agent_id A 2 (null) BTREE (null) (null) cc_agents_tier_status_log 1 FK_cc_agents_tier_status_log_3 1 cc_queue_id A 14 (null) BTREE (null) (null) cc_agents_tier_status_log 1 FK_cc_agents_tier_status_log_1 1 cc_agent_tier_status_id A 2 (null) BTREE (null) (null) cc_agents_tier_status_log 1 IDX_cc_agents_tier_status_log_7 1 id A 23999 (null) BTREE (null) (null) cc_agents_tier_status_log 1 IDX_cc_agents_tier_status_log_7 2 date_log A 23999 (null) BTREE (null) (null) And the query is: set @enddate:=now(); set @startdate:='2014-11-01'; set @que_id:=-1; select s.theHour as theHour,avg(s.nrAgents) as nrAgents from (select date(a.theDateHour) as theDate,extract(hour from a.theDateHour) as theHour,count(c.cc_agent_tier_status_id) as nrAgents from ( select dh.theDateHour as theDateHour, max(c.date_log) as maxdatelog,c.* FROM ( select concat(d.thedate,' ',h.theHour,':0:0') as theDateHour from ( select DATE(DATE_ADD(date(@startdate), INTERVAL @i:=@i+1 DAY) ) as theDate from (select @i:=-1) as t1 inner join cc_member_queue_end_log b on 1=1 and b.id=datediff(@enddate,@startdate)+1 ) as d left outer join (SELECT 0 AS theHour UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23) as h on 1=1 ) AS dh left outer join cc_agents_tier_status_log as c on c.date_log=dh.theDateHour where (if(@queue_id0,1,0) or if(@queue_id=c.cc_queue_id,1,0)) group by dh.theDateHour,c.cc_queue_id,c.cc_agent_id,c.cc_agent_phone ) as a left outer join cc_agents_tier_status_log as c on c.date_log=a.maxdatelog and c.cc_queue_id=a.cc_queue_id and c.cc_agent_id=a.cc_agent_id and c.cc_agent_phone=a.cc_agent_phone and c.cc_agent_tier_status_id=2 group by a.theDateHour order by date(a.theDateHour),extract(hour from a.theDateHour)) as s group by s.theHour order by s.theHour; This query takes 20 seconds to populate. Table cc_agents_tier_status_log contains log entries of agent_id login/logout per queue per phone. status_id can have value 1 (logged out) and 2 (login) at date_log datetime. The resulting table must contain average number of agents logged in at every hour per startdate to enddate. Hope for some hints. Thank you. -- Mimiko desu. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Need help with query
2011/03/15 17:51 -0500, LAMP Let's say there is a table orders (simplified, of course) CREATE TABLE orders ( item_id int, org_id int, ) ENGINE=MyISAM Need to select all (distinct) org_id they have item_id 34, 36, 58 and 63. All of them, not only some of them. Result is org_id=2607 and org_id=1520 I can have it by select org_id from orders where item_id in (34, 36, 58, 63) group by org_id having count(org_id)=4 I now noticed the aggregate function GROUP_CONCAT: select org_id,GROUP_CONCAT(DISTINCT item_id, ORDER BY item_id) AS itemset from orders where item_id in (34, 36, 58, 63) group by org_id having itemset = '34,36,58,63' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Need help with query
Hi! I think that the query that you have proposed is the best possible for the problem. However, if there are duplicates in the orders table, then HAVING COUNT(item_id) = 4 should be replaced with HAVING COUNT(DISTINCT item_id) = 4 (I assume that you meant item_id and not org_id in the COUNT function). Thanks, Roy On 17.03.11 18.00, LAMP wrote: Yes, that was my question. Though, since English is not my first language, let me try to post it again: There is a list of all orgs and items org bough, from table called orders item_id org_id 34 2607 34 2607 34 1520 36 2607 36 1520 36 8934 38 28 38 15 38 5 38 13 58 2607 58 2607 58 7295 58 1649 58 7295 58 1520 63 2607 63 2607 63 8871 63 7295 63 1520 65 15 65 20 95 1520 95 1520 95 7295 98 1520 98 7295 select org_id from orders where item_id in (34. 36. 58. 63) will give me a result 5 13 15 28 1520 1649 2607 7295 8871 8934 This is the list of ALL orgs they bought ANY of items (34. 36. 58. 63). Agree? What I need is a list of orgs they bought all of items 34, 36, 58, 63. every of them. Result should be only orgs 2607 and 1520. I hope it's more clear now. On Mar 15, 2011, at 10:47 PM, Rhino wrote: Your original question said: Need to select all (distinct) org_id they have item_id 34, 36, 58 and 63. All of them, not only some of them. That's the question I answered with my suggested query. It sounds like that is not what you meant after all but I'm not sure what you DO want with your query. Why are 2607 and 1520 the only right answers? Because they are. I look at the database and manually found the result I have to get. What's wrong with my statement? Based on your own query, it looks like you only want an org_id for item_ids 34, 36, 58, and 63 if there are exactly 4 occurrences of that org_id amongst the desired item_ids actually, there is mistake in my query, it should say having count(org_id) = 4 and, yes, that's what I want. I can get the correct list using the query I posted but I was hoping there is BETTER way. but that wasn't in your statement of the problem. So please clarify EXACTLY what you want. Giving an incomplete or contradictory description of you want only wastes both your time and mine. As I stated earlier, English is not my first language and I was trying to do my best. Sorry for confusing you. -- Rhino On 2011-03-15 20:35, LAMP wrote: On Mar 15, 2011, at 6:18 PM, Rhino wrote: All you should need is this: select distinct org_id from orders where item_id in (34, 36, 58, 63) I'm assuming that the DISTINCT operator is available in the version of MySQL that you are using. I don't currently have any version of MySQL installed so I can't try this myself to be sure it works in your version of MySQL. -- Rhino your query will give me every org_id that has ANY of item_id., I need org_id that has ALL of item_id. right? result would be 2607 1520 8934 7295 1649 8871 On 2011-03-15 18:51, LAMP wrote: Hi, I need a help to build a query. Let's say there is a table orders (simplified, of course) CREATE TABLE orders ( `item_id` int, `org_id` int, ) ENGINE=MyISAM item_id org_id 34 2607 34 2607 34 1520 36 2607 36 1520 36 8934 38 28 38 15 38 5 38 13 58 2607 58 2607 58 7295 58 1649 58 7295 58 1520 63 2607 63 2607 63 8871 63 7295 63 1520 65 15 65 20 95 1520 95 1520 95 7295 98 1520 98 7295 Need to select all (distinct) org_id they have item_id 34, 36, 58 and 63. All of them, not only some of them. Result is org_id=2607 and org_id=1520 I can have it by select org_id from orders where item_id in (34, 36, 58, 63) group by org_id having count(org_id)=4 but, I'm sure there is better solution? Thanks for any help. LAMP -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Need help with query
On Mar 17, 2011, at 3:01 PM, Geert-Jan Brits wrote: Indeed, I don't thing there is. Just be sure that each record has an unique combination of org_id and item_id, otherwise you might end up with an org_id that, for example, references 4 times item_id 34 in 4 different records, but no other item_ids. This is obvisouly not what you want. Geert-Jan Correct. That's why I use select distinct org_id, item_id in sub- query. Is here anybody from mysql development team, to suggest to build IN ALL function? :-) 2011/3/17 LAMP l...@afan.net First I was thinking there is function IN ALL or something like that, since there are functions IN and EXISTS. And I would be able to make a query something like this select distinct org_id, item_id from orders where item_id in all (34, 36, 58, 63) order by org_id asc But, there isn't any. :-( The correct query is select r.org_id from ( select distinct a.org_id, a.item_id from orders a where a.item_id in (34, 36, 58, 63) order by a.org_id asc ) r group by r.org_id having count(*) = 4 On Mar 17, 2011, at 12:24 PM, Peter Brawley wrote: What I need is a list of orgs they bought all of items 34, 36, 58, 63. every of them. Some solutions under What else did buyers of X buy at http://www.artfulsoftware.com/infotree/queries.php . PB --- On 3/17/2011 12:00 PM, LAMP wrote: Yes, that was my question. Though, since English is not my first language, let me try to post it again: There is a list of all orgs and items org bough, from table called orders item_idorg_id 342607 342607 341520 362607 361520 368934 3828 3815 385 3813 582607 582607 587295 581649 587295 581520 632607 632607 638871 637295 631520 6515 6520 951520 951520 957295 981520 987295 select org_id from orders where item_id in (34. 36. 58. 63) will give me a result 5 13 15 28 1520 1649 2607 7295 8871 8934 This is the list of ALL orgs they bought ANY of items (34. 36. 58. 63). Agree? What I need is a list of orgs they bought all of items 34, 36, 58, 63. every of them. Result should be only orgs 2607 and 1520. I hope it's more clear now. On Mar 15, 2011, at 10:47 PM, Rhino wrote: Your original question said: Need to select all (distinct) org_id they have item_id 34, 36, 58 and 63. All of them, not only some of them. That's the question I answered with my suggested query. It sounds like that is not what you meant after all but I'm not sure what you DO want with your query. Why are 2607 and 1520 the only right answers? Because they are. I look at the database and manually found the result I have to get. What's wrong with my statement? Based on your own query, it looks like you only want an org_id for item_ids 34, 36, 58, and 63 if there are exactly 4 occurrences of that org_id amongst the desired item_ids actually, there is mistake in my query, it should say having count(org_id) = 4 and, yes, that's what I want. I can get the correct list using the query I posted but I was hoping there is BETTER way. but that wasn't in your statement of the problem. So please clarify EXACTLY what you want. Giving an incomplete or contradictory description of you want only wastes both your time and mine. As I stated earlier, English is not my first language and I was trying to do my best. Sorry for confusing you. -- Rhino On 2011-03-15 20:35, LAMP wrote: On Mar 15, 2011, at 6:18 PM, Rhino wrote: All you should need is this: select distinct org_id from orders where item_id in (34, 36, 58, 63) I'm assuming that the DISTINCT operator is available in the version of MySQL that you are using. I don't currently have any version of MySQL installed so I can't try this myself to be sure it works in your version of MySQL. -- Rhino your query will give me every org_id that has ANY of item_id., I need org_id that has ALL of item_id. right? result would be 2607 1520 8934 7295 1649 8871 On 2011-03-15 18:51, LAMP wrote: Hi, I need a help to build a query. Let's say there is a table orders (simplified, of course) CREATE TABLE orders ( `item_id` int, `org_id` int, ) ENGINE=MyISAM item_idorg_id 342607 342607 341520 362607 361520 368934 3828 3815 385 3813 582607 582607 587295 581649 587295 581520 632607 632607 638871 637295 631520 6515 6520 951520 951520 957295 981520 987295 Need to select all (distinct) org_id they have item_id 34, 36, 58 and 63. All of them, not only some of them. Result is org_id=2607 and org_id=1520 I can have it by select
Re: Need help with query
2011/03/18 08:49 -0500, LAMP Is here anybody from mysql development team, to suggest to build IN ALL function? There is a problem here: the basic operation is on the record, each record by each record, all by itself. The solution to your problem entails acting on more distinct records until enough have been encountered. If you imagine the table input to a program that checks for hits, you will see the problem. The program reads its input, for every number of the four that you want matched it holds on to its mate until that mate is matched with all four of the chosen. It is a global condition, and SQL works one record at a time. Global conditions are detected only through the summary functions. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Need help with query
Yes, that was my question. Though, since English is not my first language, let me try to post it again: There is a list of all orgs and items org bough, from table called orders item_idorg_id 342607 342607 341520 362607 361520 368934 3828 3815 385 3813 582607 582607 587295 581649 587295 581520 632607 632607 638871 637295 631520 6515 6520 951520 951520 957295 981520 987295 select org_id from orders where item_id in (34. 36. 58. 63) will give me a result 5 13 15 28 1520 1649 2607 7295 8871 8934 This is the list of ALL orgs they bought ANY of items (34. 36. 58. 63). Agree? What I need is a list of orgs they bought all of items 34, 36, 58, 63. every of them. Result should be only orgs 2607 and 1520. I hope it's more clear now. On Mar 15, 2011, at 10:47 PM, Rhino wrote: Your original question said: Need to select all (distinct) org_id they have item_id 34, 36, 58 and 63. All of them, not only some of them. That's the question I answered with my suggested query. It sounds like that is not what you meant after all but I'm not sure what you DO want with your query. Why are 2607 and 1520 the only right answers? Because they are. I look at the database and manually found the result I have to get. What's wrong with my statement? Based on your own query, it looks like you only want an org_id for item_ids 34, 36, 58, and 63 if there are exactly 4 occurrences of that org_id amongst the desired item_ids actually, there is mistake in my query, it should say having count(org_id) = 4 and, yes, that's what I want. I can get the correct list using the query I posted but I was hoping there is BETTER way. but that wasn't in your statement of the problem. So please clarify EXACTLY what you want. Giving an incomplete or contradictory description of you want only wastes both your time and mine. As I stated earlier, English is not my first language and I was trying to do my best. Sorry for confusing you. -- Rhino On 2011-03-15 20:35, LAMP wrote: On Mar 15, 2011, at 6:18 PM, Rhino wrote: All you should need is this: select distinct org_id from orders where item_id in (34, 36, 58, 63) I'm assuming that the DISTINCT operator is available in the version of MySQL that you are using. I don't currently have any version of MySQL installed so I can't try this myself to be sure it works in your version of MySQL. -- Rhino your query will give me every org_id that has ANY of item_id., I need org_id that has ALL of item_id. right? result would be 2607 1520 8934 7295 1649 8871 On 2011-03-15 18:51, LAMP wrote: Hi, I need a help to build a query. Let's say there is a table orders (simplified, of course) CREATE TABLE orders ( `item_id` int, `org_id` int, ) ENGINE=MyISAM item_idorg_id 342607 342607 341520 362607 361520 368934 3828 3815 385 3813 582607 582607 587295 581649 587295 581520 632607 632607 638871 637295 631520 6515 6520 951520 951520 957295 981520 987295 Need to select all (distinct) org_id they have item_id 34, 36, 58 and 63. All of them, not only some of them. Result is org_id=2607 and org_id=1520 I can have it by select org_id from orders where item_id in (34, 36, 58, 63) group by org_id having count(org_id)=4 but, I'm sure there is better solution? Thanks for any help. LAMP
Re: Need help with query
What I need is a list of orgs they bought all of items 34, 36, 58, 63. every of them. Some solutions under What else did buyers of X buy at http://www.artfulsoftware.com/infotree/queries.php. PB --- On 3/17/2011 12:00 PM, LAMP wrote: Yes, that was my question. Though, since English is not my first language, let me try to post it again: There is a list of all orgs and items org bough, from table called orders item_idorg_id 342607 342607 341520 362607 361520 368934 3828 3815 385 3813 582607 582607 587295 581649 587295 581520 632607 632607 638871 637295 631520 6515 6520 951520 951520 957295 981520 987295 select org_id from orders where item_id in (34. 36. 58. 63) will give me a result 5 13 15 28 1520 1649 2607 7295 8871 8934 This is the list of ALL orgs they bought ANY of items (34. 36. 58. 63). Agree? What I need is a list of orgs they bought all of items 34, 36, 58, 63. every of them. Result should be only orgs 2607 and 1520. I hope it's more clear now. On Mar 15, 2011, at 10:47 PM, Rhino wrote: Your original question said: Need to select all (distinct) org_id they have item_id 34, 36, 58 and 63. All of them, not only some of them. That's the question I answered with my suggested query. It sounds like that is not what you meant after all but I'm not sure what you DO want with your query. Why are 2607 and 1520 the only right answers? Because they are. I look at the database and manually found the result I have to get. What's wrong with my statement? Based on your own query, it looks like you only want an org_id for item_ids 34, 36, 58, and 63 if there are exactly 4 occurrences of that org_id amongst the desired item_ids actually, there is mistake in my query, it should say having count(org_id) = 4 and, yes, that's what I want. I can get the correct list using the query I posted but I was hoping there is BETTER way. but that wasn't in your statement of the problem. So please clarify EXACTLY what you want. Giving an incomplete or contradictory description of you want only wastes both your time and mine. As I stated earlier, English is not my first language and I was trying to do my best. Sorry for confusing you. -- Rhino On 2011-03-15 20:35, LAMP wrote: On Mar 15, 2011, at 6:18 PM, Rhino wrote: All you should need is this: select distinct org_id from orders where item_id in (34, 36, 58, 63) I'm assuming that the DISTINCT operator is available in the version of MySQL that you are using. I don't currently have any version of MySQL installed so I can't try this myself to be sure it works in your version of MySQL. -- Rhino your query will give me every org_id that has ANY of item_id., I need org_id that has ALL of item_id. right? result would be 2607 1520 8934 7295 1649 8871 On 2011-03-15 18:51, LAMP wrote: Hi, I need a help to build a query. Let's say there is a table orders (simplified, of course) CREATE TABLE orders ( `item_id` int, `org_id` int, ) ENGINE=MyISAM item_idorg_id 342607 342607 341520 362607 361520 368934 3828 3815 385 3813 582607 582607 587295 581649 587295 581520 632607 632607 638871 637295 631520 6515 6520 951520 951520 957295 981520 987295 Need to select all (distinct) org_id they have item_id 34, 36, 58 and 63. All of them, not only some of them. Result is org_id=2607 and org_id=1520 I can have it by select org_id from orders where item_id in (34, 36, 58, 63) group by org_id having count(org_id)=4 but, I'm sure there is better solution? Thanks for any help. LAMP -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Need help with query
First I was thinking there is function IN ALL or something like that, since there are functions IN and EXISTS. And I would be able to make a query something like this select distinct org_id, item_id from orders where item_id in all (34, 36, 58, 63) order by org_id asc But, there isn't any. :-( The correct query is select r.org_id from ( select distinct a.org_id, a.item_id from orders a where a.item_id in (34, 36, 58, 63) order by a.org_id asc ) r group by r.org_id having count(*) = 4 On Mar 17, 2011, at 12:24 PM, Peter Brawley wrote: What I need is a list of orgs they bought all of items 34, 36, 58, 63. every of them. Some solutions under What else did buyers of X buy at http://www.artfulsoftware.com/infotree/queries.php . PB --- On 3/17/2011 12:00 PM, LAMP wrote: Yes, that was my question. Though, since English is not my first language, let me try to post it again: There is a list of all orgs and items org bough, from table called orders item_idorg_id 342607 342607 341520 362607 361520 368934 3828 3815 385 3813 582607 582607 587295 581649 587295 581520 632607 632607 638871 637295 631520 6515 6520 951520 951520 957295 981520 987295 select org_id from orders where item_id in (34. 36. 58. 63) will give me a result 5 13 15 28 1520 1649 2607 7295 8871 8934 This is the list of ALL orgs they bought ANY of items (34. 36. 58. 63). Agree? What I need is a list of orgs they bought all of items 34, 36, 58, 63. every of them. Result should be only orgs 2607 and 1520. I hope it's more clear now. On Mar 15, 2011, at 10:47 PM, Rhino wrote: Your original question said: Need to select all (distinct) org_id they have item_id 34, 36, 58 and 63. All of them, not only some of them. That's the question I answered with my suggested query. It sounds like that is not what you meant after all but I'm not sure what you DO want with your query. Why are 2607 and 1520 the only right answers? Because they are. I look at the database and manually found the result I have to get. What's wrong with my statement? Based on your own query, it looks like you only want an org_id for item_ids 34, 36, 58, and 63 if there are exactly 4 occurrences of that org_id amongst the desired item_ids actually, there is mistake in my query, it should say having count(org_id) = 4 and, yes, that's what I want. I can get the correct list using the query I posted but I was hoping there is BETTER way. but that wasn't in your statement of the problem. So please clarify EXACTLY what you want. Giving an incomplete or contradictory description of you want only wastes both your time and mine. As I stated earlier, English is not my first language and I was trying to do my best. Sorry for confusing you. -- Rhino On 2011-03-15 20:35, LAMP wrote: On Mar 15, 2011, at 6:18 PM, Rhino wrote: All you should need is this: select distinct org_id from orders where item_id in (34, 36, 58, 63) I'm assuming that the DISTINCT operator is available in the version of MySQL that you are using. I don't currently have any version of MySQL installed so I can't try this myself to be sure it works in your version of MySQL. -- Rhino your query will give me every org_id that has ANY of item_id., I need org_id that has ALL of item_id. right? result would be 2607 1520 8934 7295 1649 8871 On 2011-03-15 18:51, LAMP wrote: Hi, I need a help to build a query. Let's say there is a table orders (simplified, of course) CREATE TABLE orders ( `item_id` int, `org_id` int, ) ENGINE=MyISAM item_idorg_id 342607 342607 341520 362607 361520 368934 3828 3815 385 3813 582607 582607 587295 581649 587295 581520 632607 632607 638871 637295 631520 6515 6520 951520 951520 957295 981520 987295 Need to select all (distinct) org_id they have item_id 34, 36, 58 and 63. All of them, not only some of them. Result is org_id=2607 and org_id=1520 I can have it by select org_id from orders where item_id in (34, 36, 58, 63) group by org_id having count(org_id)=4 but, I'm sure there is better solution? Thanks for any help. LAMP -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Need help with query
Hi, I need a help to build a query. Let's say there is a table orders (simplified, of course) CREATE TABLE orders ( `item_id` int, `org_id` int, ) ENGINE=MyISAM item_idorg_id 342607 342607 341520 362607 361520 368934 3828 3815 385 3813 582607 582607 587295 581649 587295 581520 632607 632607 638871 637295 631520 6515 6520 951520 951520 957295 981520 987295 Need to select all (distinct) org_id they have item_id 34, 36, 58 and 63. All of them, not only some of them. Result is org_id=2607 and org_id=1520 I can have it by select org_id from orders where item_id in (34, 36, 58, 63) group by org_id having count(org_id)=4 but, I'm sure there is better solution? Thanks for any help. LAMP -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Need help with query
On Mar 15, 2011, at 6:18 PM, Rhino wrote: All you should need is this: select distinct org_id from orders where item_id in (34, 36, 58, 63) I'm assuming that the DISTINCT operator is available in the version of MySQL that you are using. I don't currently have any version of MySQL installed so I can't try this myself to be sure it works in your version of MySQL. -- Rhino your query will give me every org_id that has ANY of item_id., I need org_id that has ALL of item_id. right? result would be 2607 1520 8934 7295 1649 8871 On 2011-03-15 18:51, LAMP wrote: Hi, I need a help to build a query. Let's say there is a table orders (simplified, of course) CREATE TABLE orders ( `item_id` int, `org_id` int, ) ENGINE=MyISAM item_idorg_id 342607 342607 341520 362607 361520 368934 3828 3815 385 3813 582607 582607 587295 581649 587295 581520 632607 632607 638871 637295 631520 6515 6520 951520 951520 957295 981520 987295 Need to select all (distinct) org_id they have item_id 34, 36, 58 and 63. All of them, not only some of them. Result is org_id=2607 and org_id=1520 I can have it by select org_id from orders where item_id in (34, 36, 58, 63) group by org_id having count(org_id)=4 but, I'm sure there is better solution? Thanks for any help. LAMP -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help with query.
Hi Paul! Paul Halliday wrote: I have a query (thanks to this list) that uses a join to add country information to an IP. It looks like this: SELECT COUNT(signature) AS count, INET_NTOA(src_ip), map1.cc as src_cc, INET_NTOA(dst_ip), map2.cc as dst_cc FROM event LEFT JOIN mappings AS map1 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.
On Tue, 1 Feb 2011 14:46:39 -0400 Paul Halliday paul.halli...@gmail.com wrote: I have a query (thanks to this list) that uses a join to add country information to an IP. It looks like this: SELECT COUNT(signature) AS count, INET_NTOA(src_ip), map1.cc as src_cc, INET_NTOA(dst_ip), map2.cc as dst_cc FROM event LEFT JOIN mappings AS map1 ON event.src_ip = map1.ip LEFT JOIN mappings AS map2 ON event.dst_ip = map2.ip WHERE timestamp BETWEEN '2011-01-29 00:00:00' AND '2011-01-30 00:00:00' GROUP BY src_ip, src_cc, dst_ip, dst_cc ORDER BY src_cc, dst_cc ASC; . All I am interested in is the event count for each country, in this case: 295 CN ... Other countries.. try: SELECT COUNT(*) AS count, IF(map1.cc, map1.cc, map2.cc) AS country FROM event LEFT JOIN mappings AS map1 ON event.src_ip = map1.ip LEFT JOIN mappings AS map2 ON event.dst_ip = map2.ip WHERE timestamp BETWEEN '2011-01-29 00:00:00' AND '2011-01-30 00:00:00' GROUP BY country ORDER BY country note, I am assuming in this query that you have either a source country or a destination country, but not both. If both values might be set, and you need to count each, this will not work. Thanks! -- Paul Halliday http://www.pintumbler.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sim...@syounger.com -- Simcha Younger sim...@syounger.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Help with query.
I have a query (thanks to this list) that uses a join to add country information to an IP. It looks like this: SELECT COUNT(signature) AS count, INET_NTOA(src_ip), map1.cc as src_cc, INET_NTOA(dst_ip), map2.cc as dst_cc FROM event LEFT JOIN mappings AS map1 ON event.src_ip = map1.ip LEFT JOIN mappings AS map2 ON event.dst_ip = map2.ip WHERE timestamp BETWEEN '2011-01-29 00:00:00' AND '2011-01-30 00:00:00' GROUP BY src_ip, src_cc, dst_ip, dst_cc ORDER BY src_cc, dst_cc ASC; This would return something like this: +---+---++---++ | count | INET_NTOA(src_ip) | src_cc | INET_NTOA(dst_ip) | dst_cc | +---+---++---++ | 8 | 10.0.0.8 | NULL | 61.55.142.129 | CN | | 1 | 210.52.216.92 | CN | 10.0.0.2 | NULL | | 1 | 121.33.205.235| CN | 172.16.0.6| NULL | | 239 | 210.52.216.92 | CN | 10.0.0.2| NULL | | 2 | 121.33.205.235| CN | 172.16.0.15 | NULL | | 4 | 121.33.205.235| CN | 10.0.0.1| NULL | |39 | 210.52.216.92 | CN | 172.16.0.15 | NULL | | 1 | 121.33.205.235| CN | 172.16.0.14 | NULL | +---+---++---++ All I am interested in is the event count for each country, in this case: 295 CN ... Other countries.. I can do this in code, more work of course, but I am just curious if I can pull it off with a single query. Thanks! -- Paul Halliday http://www.pintumbler.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: help with query
Thank you, that did the trick. Simon On 11 January 2011 12:09, Steve Meyers steve-mysql-l...@spamwiz.com wrote: On 1/11/11 9:31 AM, Simon Wilkinson wrote: select users.id from users where users.id in (select newletters.user_id from newletters left join articles on newletters.id = articles.newsletter_id where articles.newsletter_id is null); I think this would do what you require: SELECT u.id AS user_id, COUNT(DISTINCT n.id) AS num_newsletters, COUNT(DISTINCT a.id) AS num_articles FROM users u JOIN newsletters n ON n.user_id=u.id LEFT JOIN articles a ON a.newsletter_id=n.id GROUP BY u.id HAVING num_newsletters 0 AND num_articles = 0
help with query
Hi, I have 3 tables that I am trying to search across, and could use some help on how to structure the query. I have a users table, a newsletter table, and an articles table. The newsletter table has a user_id column, and the articles table has a newsletter_id column. A user can have multiple newsletters, and a newsletter can have multiple articles. What I would like to do is find the list of users that have only newletters with no content. My current query is as follows: select users.id from users where users.id in (select newletters.user_id from newletters left join articles on newletters.id = articles.newsletter_id where articles.newsletter_id is null); But I believe this is finding users that have any empty newletters, and not users that have only empty newletters. How could I change this to return only the users that have only empty newsletters? Thanks, Simon
Re: help with query
On 1/11/11 9:31 AM, Simon Wilkinson wrote: select users.id from users where users.id in (select newletters.user_id from newletters left join articles on newletters.id = articles.newsletter_id where articles.newsletter_id is null); I think this would do what you require: SELECT u.id AS user_id, COUNT(DISTINCT n.id) AS num_newsletters, COUNT(DISTINCT a.id) AS num_articles FROM users u JOIN newsletters n ON n.user_id=u.id LEFT JOIN articles a ON a.newsletter_id=n.id GROUP BY u.id HAVING num_newsletters 0 AND num_articles = 0 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Need help with query optimization
It may only be returning 51 rows but its having to read significantly more. Get rid of the derived table join if possible. Something like SELECT TAP.ID http://tap.id/, M.UID, TAP.FirstName, TAP.MI, TAP.LastName, TAP.State, TAP.SchoolName, TAP.StateApproved, TAP.DiplomatApproved, C.ChapterType, S.Region, S.District,Sum(Pts.Points) Total_Points FROM TorchAwardParticipants TAP JOIN Members M On M.ID http://m.id/=TAP.CurrentMemberID JOIN Chapters C On C.ID http://c.id/=M.ChapterID JOIN Schools S On S.ID http://s.id/=C.SchoolID JOIN TorchAwardSelAct Pts ON Pts.AchievementID=TAP.ID http://tap.id/ WHERE TAP.State='OH' AND TAP.Sub='1' AND TAP.SubmittedToDiplomat IS NOT NULL AND Pts.LocalApproveStatus='A' GROUP BY TAP.ID http://tap.id/, M.UID, TAP.FirstName, TAP.MI, TAP.LastName, TAP.State, TAP.SchoolName, TAP.StateApproved, TAP.DiplomatApproved, C.ChapterType, S.Region, S.District ORDER BY TAP.LastName, TAP.FirstName, Sum(Pts.Points) Regards John On Tue, Mar 16, 2010 at 6:17 PM, Jesse j...@msdlg.com wrote: I have the following query: SELECT TAP.ID http://tap.id/, M.UID, TAP.FirstName, TAP.MI, TAP.LastName, TAP.State, TAP.SchoolName, TAP.StateApproved, TAP.DiplomatApproved, C.ChapterType, S.Region, S.District,Pts.TotPoints FROM TorchAwardParticipants TAP JOIN Members M On M.ID http://m.id/=TAP.CurrentMemberID JOIN Chapters C On C.ID http://c.id/=M.ChapterID JOIN Schools S On S.ID http://s.id/=C.SchoolID JOIN (SELECT AchievementID,Sum(Points) As TotPoints FROM TorchAwardSelAct TASA WHERE LocalApproveStatus='A' GROUP BY AchievementID) Pts ON Pts.AchievementID=TAP.IDhttp://tap.id/ WHERE TAP.State='OH' AND TAP.Sub='1' AND TAP.SubmittedToDiplomat IS NOT NULL ORDER BY TAP.LastName, TAP.FirstName, Pts.TotPoints The TorchAwardParticipants table has about 84,000 records in it. The query takes almost 40 seconds to return the data, which is only 51 rows. An EXPLAIN returns the following: ++-+++-+---+-+-++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+++-+---+-+-++-+ | 1 | PRIMARY | derived2 | ALL| NULL| NULL | NULL| NULL| 4382 | Using temporary; Using filesort | | 1 | PRIMARY | TAP| eq_ref | PRIMARY,CurrentMemberID | PRIMARY | 4 | Pts.AchievementID | 1 | Using where | | 1 | PRIMARY | M | eq_ref | PRIMARY,IX_Members_3| PRIMARY | 4 | bpa.TAP.CurrentMemberID | 1 | | | 1 | PRIMARY | C | eq_ref | PRIMARY,IX_Chapters_1 | PRIMARY | 4 | bpa.M.ChapterID | 1 | | | 1 | PRIMARY | S | eq_ref | PRIMARY | PRIMARY | 4 | bpa.C.SchoolID | 1 | | | 2 | DERIVED | TASA | index | NULL| AchievementID | 5 | NULL| 161685 | Using where | ++-+++-+---+-+-++-+ What is the best way to optimize this query so that it doesn't take 40 seconds to return the dataset? Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=john.dais...@butterflysystems.co.uk
Need help with query optimization
I have the following query: SELECT TAP.ID, M.UID, TAP.FirstName, TAP.MI, TAP.LastName, TAP.State, TAP.SchoolName, TAP.StateApproved, TAP.DiplomatApproved, C.ChapterType, S.Region, S.District,Pts.TotPoints FROM TorchAwardParticipants TAP JOIN Members M On M.ID=TAP.CurrentMemberID JOIN Chapters C On C.ID=M.ChapterID JOIN Schools S On S.ID=C.SchoolID JOIN (SELECT AchievementID,Sum(Points) As TotPoints FROM TorchAwardSelAct TASA WHERE LocalApproveStatus='A' GROUP BY AchievementID) Pts ON Pts.AchievementID=TAP.ID WHERE TAP.State='OH' AND TAP.Sub='1' AND TAP.SubmittedToDiplomat IS NOT NULL ORDER BY TAP.LastName, TAP.FirstName, Pts.TotPoints The TorchAwardParticipants table has about 84,000 records in it. The query takes almost 40 seconds to return the data, which is only 51 rows. An EXPLAIN returns the following: ++-+++-+---+-+-++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+++-+---+-+-++-+ | 1 | PRIMARY | derived2 | ALL| NULL| NULL | NULL| NULL| 4382 | Using temporary; Using filesort | | 1 | PRIMARY | TAP| eq_ref | PRIMARY,CurrentMemberID | PRIMARY | 4 | Pts.AchievementID | 1 | Using where | | 1 | PRIMARY | M | eq_ref | PRIMARY,IX_Members_3| PRIMARY | 4 | bpa.TAP.CurrentMemberID | 1 | | | 1 | PRIMARY | C | eq_ref | PRIMARY,IX_Chapters_1 | PRIMARY | 4 | bpa.M.ChapterID | 1 | | | 1 | PRIMARY | S | eq_ref | PRIMARY | PRIMARY | 4 | bpa.C.SchoolID | 1 | | | 2 | DERIVED | TASA | index | NULL| AchievementID | 5 | NULL| 161685 | Using where | ++-+++-+---+-+-++-+ What is the best way to optimize this query so that it doesn't take 40 seconds to return the dataset? Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Need help with query optimization
Can you please show us the indexes on both the tables. regards anandkl On Tue, Mar 16, 2010 at 11:47 PM, Jesse j...@msdlg.com wrote: I have the following query: SELECT TAP.ID http://tap.id/, M.UID, TAP.FirstName, TAP.MI, TAP.LastName, TAP.State, TAP.SchoolName, TAP.StateApproved, TAP.DiplomatApproved, C.ChapterType, S.Region, S.District,Pts.TotPoints FROM TorchAwardParticipants TAP JOIN Members M On M.ID http://m.id/=TAP.CurrentMemberID JOIN Chapters C On C.ID http://c.id/=M.ChapterID JOIN Schools S On S.ID http://s.id/=C.SchoolID JOIN (SELECT AchievementID,Sum(Points) As TotPoints FROM TorchAwardSelAct TASA WHERE LocalApproveStatus='A' GROUP BY AchievementID) Pts ON Pts.AchievementID=TAP.IDhttp://tap.id/ WHERE TAP.State='OH' AND TAP.Sub='1' AND TAP.SubmittedToDiplomat IS NOT NULL ORDER BY TAP.LastName, TAP.FirstName, Pts.TotPoints The TorchAwardParticipants table has about 84,000 records in it. The query takes almost 40 seconds to return the data, which is only 51 rows. An EXPLAIN returns the following: ++-+++-+---+-+-++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+++-+---+-+-++-+ | 1 | PRIMARY | derived2 | ALL| NULL| NULL | NULL| NULL| 4382 | Using temporary; Using filesort | | 1 | PRIMARY | TAP| eq_ref | PRIMARY,CurrentMemberID | PRIMARY | 4 | Pts.AchievementID | 1 | Using where | | 1 | PRIMARY | M | eq_ref | PRIMARY,IX_Members_3| PRIMARY | 4 | bpa.TAP.CurrentMemberID | 1 | | | 1 | PRIMARY | C | eq_ref | PRIMARY,IX_Chapters_1 | PRIMARY | 4 | bpa.M.ChapterID | 1 | | | 1 | PRIMARY | S | eq_ref | PRIMARY | PRIMARY | 4 | bpa.C.SchoolID | 1 | | | 2 | DERIVED | TASA | index | NULL| AchievementID | 5 | NULL| 161685 | Using where | ++-+++-+---+-+-++-+ What is the best way to optimize this query so that it doesn't take 40 seconds to return the dataset? Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com
Re: help refactoring query
b wrote: I'm having some difficulty getting my head around a particular query. I'd like to make this a view once I get something working. However, all I've been able to come up with uses a sub-query. So, no view on the horizon. I have 3 tables: users id, (etc. the usual) disciplines id, name (ie. film, photography, writing, etc.) disciplines_users discipline_id, user_id Each user may have one or more discipline. The view I'm looking for shows the total number of users who have a particular discipline. NOTE: the sum of the totals is greater than the total number of users, which is by design. SELECT name, COUNT(discipline.u_id) AS total FROM ( SELECT du.discipline_id, du.user_id as u_id, d.name FROM disciplines_users AS du LEFT JOIN disciplines AS d ON d.id = du.discipline_id ) AS discipline GROUP BY discipline.name ORDER BY discipline.name; +-+---+ | name| total | +-+---+ | Dance | 176 | | Film and Television | 376 | etc. I've a feeling that this could be done without that sub-query and using another join. If not, I might make the sub-query its own view and see what the performance is like. I'd appreciate any suggestions, especially any pointers on refactoring sub-queries into joins, in general. Why not use just your subquery as your VIEW? SELECT d.id, d.name, du.user_id as u_id, FROM disciplines AS d LEFT JOIN disciplines_users AS du ON d.id = du.discipline_id GROUP BY d.id, d.name The reason I inverted the FROM and LEFT JOIN was so that if you had a discipline with 0 users, you can now see a zero. In your original orientation, a relationship had to exist or its discipline wouldn't have been counted. -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
help refactoring query
I'm having some difficulty getting my head around a particular query. I'd like to make this a view once I get something working. However, all I've been able to come up with uses a sub-query. So, no view on the horizon. I have 3 tables: users id, (etc. the usual) disciplines id, name (ie. film, photography, writing, etc.) disciplines_users discipline_id, user_id Each user may have one or more discipline. The view I'm looking for shows the total number of users who have a particular discipline. NOTE: the sum of the totals is greater than the total number of users, which is by design. SELECT name, COUNT(discipline.u_id) AS total FROM ( SELECT du.discipline_id, du.user_id as u_id, d.name FROM disciplines_users AS du LEFT JOIN disciplines AS d ON d.id = du.discipline_id ) AS discipline GROUP BY discipline.name ORDER BY discipline.name; +-+---+ | name| total | +-+---+ | Dance | 176 | | Film and Television | 376 | etc. I've a feeling that this could be done without that sub-query and using another join. If not, I might make the sub-query its own view and see what the performance is like. I'd appreciate any suggestions, especially any pointers on refactoring sub-queries into joins, in general. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
need help with query...
I hate when somebody put in Subject line something like I just did but after 15 minutes to try to be specific just with one short sentence - I gave up. So, you can hate me - I understand (though, help with my problem too) :-) I have let say 3 tables people, organization, addresses. and they are linked to each other with column person_id. to select person by specified person_id it's not big deal, really simple. though, I wonder how can I select records from these three tables but somehow marked which table it belongs? e.g. select p.first_name, p.last_name, p.status, p.date_registered, o.org_id, o.org_name, o.org_department, a.addres1, a.address2, a.city, a.state, a.zip from people p, organization o, addresses a where p.person_id=123 and o.person_id=p.person_id and a.person_id=p.person_id I need somehow, together with result data, info which table data belogs? e.g. not exactly this way but something like this: $data = array( 'people' = array('lamp', 'lists', 1, '2008-12-12'), 'organization' = array(56, 'Lamp List', 'web'), 'addresses' = array('123 Main St.', 'Toon Town', 'NY', '12345') } thanks for any help. -afan
Re: need help with query...
On Dec 17, 2008, at 2:56 PM, Lamp Lists wrote: I hate when somebody put in Subject line something like I just did but after 15 minutes to try to be specific just with one short sentence - I gave up. So, you can hate me - I understand (though, help with my problem too) :-) I have let say 3 tables people, organization, addresses. and they are linked to each other with column person_id. to select person by specified person_id it's not big deal, really simple. though, I wonder how can I select records from these three tables but somehow marked which table it belongs? e.g. select p.first_name, p.last_name, p.status, p.date_registered, o.org_id, o.org_name, o.org_department, a.addres1, a.address2, a.city, a.state, a.zip from people p, organization o, addresses a where p.person_id=123 and o.person_id=p.person_id and a.person_id=p.person_id I need somehow, together with result data, info which table data belogs? e.g. not exactly this way but something like this: $data = array( 'people' = array('lamp', 'lists', 1, '2008-12-12'), 'organization' = array(56, 'Lamp List', 'web'), 'addresses' = array('123 Main St.', 'Toon Town', 'NY', '12345') } thanks for any help. -afan It may not be the best option, but when I've done that type of stuff in the past, I add another field to the database and call it like List and put People in the people database. and then you could just query the field List and display it how ever you needed. -- Jason Pruim japr...@raoset.com 616.399.2355
Re: need help with query...
Hi Afan Why not prefix your field names with the table name? select p.first_name AS person_first_name, p.last_name AS person_last_name, p.status AS person_status, p.date_registered AS person_date_registered, o.org_id AS organization_org_id, o.org_name AS organization_org_name, o.org_department AS organization_org_department, a.addres1 AS addresses_address1, a.address2 AS addresses_address2, a.city AS addresses_city, a.state AS addresses_state, a.zip AS addresses_zip from people p, organization o, addresses a where p.person_id=123 and o.person_id=p.person_id and a.person_id=p.person_id Then in PHP (which I guess you're using from your example) do something like: // Get every record from the database ($result is your MySQL result from mysql_query) while ($row = mysql_fetch_assoc($result)) { $result = Array(); // Run through each field in the row foreach ($row as $field = $value) { // Split the field into 2 segments split by _ $fieldSplit = explode('_', $field, 1); // $fieldSplit will be, for example, Array(0 = 'person', 1 = 'first_name') $result[$fieldSplit[0]][$fieldSplit[1]] = $value; // Now you should be able to access the person's first name using $result['person']['first_name'] } } This code may not be perfect as I've just typed it out from memory so it may take a bit of tweaking. Thanks, Andy Jason Pruim wrote: On Dec 17, 2008, at 2:56 PM, Lamp Lists wrote: I hate when somebody put in Subject line something like I just did but after 15 minutes to try to be specific just with one short sentence - I gave up. So, you can hate me - I understand (though, help with my problem too) :-) I have let say 3 tables people, organization, addresses. and they are linked to each other with column person_id. to select person by specified person_id it's not big deal, really simple. though, I wonder how can I select records from these three tables but somehow marked which table it belongs? e.g. select p.first_name, p.last_name, p.status, p.date_registered, o.org_id, o.org_name, o.org_department, a.addres1, a.address2, a.city, a.state, a.zip from people p, organization o, addresses a where p.person_id=123 and o.person_id=p.person_id and a.person_id=p.person_id I need somehow, together with result data, info which table data belogs? e.g. not exactly this way but something like this: $data = array( 'people' = array('lamp', 'lists', 1, '2008-12-12'), 'organization' = array(56, 'Lamp List', 'web'), 'addresses' = array('123 Main St.', 'Toon Town', 'NY', '12345') } thanks for any help. -afan It may not be the best option, but when I've done that type of stuff in the past, I add another field to the database and call it like List and put People in the people database. and then you could just query the field List and display it how ever you needed. -- Jason Pruim japr...@raoset.com 616.399.2355 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: need help with query...
-Original Message- From: Lamp Lists [mailto:lamp.li...@yahoo.com] Sent: Wednesday, December 17, 2008 2:57 PM To: mysql@lists.mysql.com Subject: need help with query... ...snip... I have let say 3 tables people, organization, addresses. and they are linked to each other with column person_id. to select person by specified person_id it's not big deal, really simple. though, I wonder how can I select records from these three tables but somehow marked which table it belongs? [JS] Admittedly I'm not really good with this stuff, but I think this works: SELECT 'table1' AS tablename, person_id FROM table1 WHERE person_id = 123 UNION SELECT 'table2', person_id FROM table2 WHERE person_id = 123 UNION SELECT 'table3', person_id FROM table2 WHERE person_id = 123; That would give you all of the tables in which a particular person_id is found. I don't know if this suits your needs. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: need help with query...
-Original Message- From: Andy Shellam [mailto:andy-li...@networkmail.eu] Sent: Wednesday, December 17, 2008 3:29 PM To: Lamp Lists Cc: mysql@lists.mysql.com Subject: Re: need help with query... Hi Afan Why not prefix your field names with the table name? select p.first_name AS person_first_name, p.last_name AS person_last_name, p.status AS person_status, p.date_registered AS person_date_registered, o.org_id AS organization_org_id, o.org_name AS organization_org_name, o.org_department AS organization_org_department, a.addres1 AS addresses_address1, a.address2 AS addresses_address2, a.city AS addresses_city, a.state AS addresses_state, a.zip AS addresses_zip from people p, organization o, addresses a where p.person_id=123 and o.person_id=p.person_id and a.person_id=p.person_id [JS] That would only retrieve a person_id if it is all three tables. I'm not sure that's what is wanted. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: need help with query...
From: Andy Shellam andy-li...@networkmail.eu To: Lamp Lists lamp.li...@yahoo.com Cc: mysql@lists.mysql.com Sent: Wednesday, December 17, 2008 2:29:08 PM Subject: Re: need help with query... Hi Afan Why not prefix your field names with the table name? select p.first_name AS person_first_name, p.last_name AS person_last_name, p.status AS person_status, p.date_registered AS person_date_registered, o.org_id AS organization_org_id, o.org_name AS organization_org_name, o.org_department AS organization_org_department, a.addres1 AS addresses_address1, a.address2 AS addresses_address2, a.city AS addresses_city, a.state AS addresses_state, a.zip AS addresses_zip from people p, organization o, addresses a where p.person_id=123 and o.person_id=p.person_id and a.person_id=p.person_id Then in PHP (which I guess you're using from your example) do something like: // Get every record from the database ($result is your MySQL result from mysql_query) while ($row = mysql_fetch_assoc($result)) { $result = Array(); // Run through each field in the row foreach ($row as $field = $value) { // Split the field into 2 segments split by _ $fieldSplit = explode('_', $field, 1); // $fieldSplit will be, for example, Array(0 = 'person', 1 = 'first_name') $result[$fieldSplit[0]][$fieldSplit[1]] = $value; // Now you should be able to access the person's first name using $result['person']['first_name'] } } This code may not be perfect as I've just typed it out from memory so it may take a bit of tweaking. Thanks, Andy Hi Andy, the reason I can't use this because fields (columns) in select statement (p.first_name, p.last_name,...) are actually dynamically created. In my project different client will select different fields to be shown. 99% will select first_name, and last_name, but some don't care about date_registered, some will need more org data... actually, it will be more this way: SELECT {$selected_fields} FROM people p, organization o. addresses a WHERE ... where $selected_fields = p.first_name, p.last_name, o.org_name or $selected_fields = p.first_name, p.last_name, o.org_name, a.address, a.city, a.state, a.zip or $selected_fields = o.org_name, a.address, a.city, a.state, a.zip I hope I'm more clear now? Though, I can do something as you suggested while creating $selected_fields :-) Thanks Jason Pruim wrote: On Dec 17, 2008, at 2:56 PM, Lamp Lists wrote: I hate when somebody put in Subject line something like I just did but after 15 minutes to try to be specific just with one short sentence - I gave up. So, you can hate me - I understand (though, help with my problem too) :-) I have let say 3 tables people, organization, addresses. and they are linked to each other with column person_id. to select person by specified person_id it's not big deal, really simple. though, I wonder how can I select records from these three tables but somehow marked which table it belongs? e.g. select p.first_name, p.last_name, p.status, p.date_registered, o.org_id, o.org_name, o.org_department, a.addres1, a.address2, a.city, a.state, a.zip from people p, organization o, addresses a where p.person_id=123 and o.person_id=p.person_id and a.person_id=p.person_id I need somehow, together with result data, info which table data belogs? e.g. not exactly this way but something like this: $data = array( 'people' = array('lamp', 'lists', 1, '2008-12-12'), 'organization' = array(56, 'Lamp List', 'web'), 'addresses' = array('123 Main St.', 'Toon Town', 'NY', '12345') } thanks for any help. -afan It may not be the best option, but when I've done that type of stuff in the past, I add another field to the database and call it like List and put People in the people database. and then you could just query the field List and display it how ever you needed. -- Jason Pruim japr...@raoset.com 616.399.2355
Re: need help with query...
Jerry Schwartz wrote: -Original Message- From: Andy Shellam [mailto:andy-li...@networkmail.eu] Sent: Wednesday, December 17, 2008 3:29 PM To: Lamp Lists Cc: mysql@lists.mysql.com Subject: Re: need help with query... Hi Afan Why not prefix your field names with the table name? select p.first_name AS person_first_name, p.last_name AS person_last_name, p.status AS person_status, p.date_registered AS person_date_registered, o.org_id AS organization_org_id, o.org_name AS organization_org_name, o.org_department AS organization_org_department, a.addres1 AS addresses_address1, a.address2 AS addresses_address2, a.city AS addresses_city, a.state AS addresses_state, a.zip AS addresses_zip from people p, organization o, addresses a where p.person_id=123 and o.person_id=p.person_id and a.person_id=p.person_id [JS] That would only retrieve a person_id if it is all three tables. I'm not sure that's what is wanted. That was the original query as specified by the original poster - I just added the AS xxx to each field he'd selected. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: need help with query...
Hi, Hi Andy, the reason I can't use this because fields (columns) in select statement (p.first_name, p.last_name,...) are actually dynamically created. In my project different client will select different fields to be shown. 99% will select first_name, and last_name, but some don't care about date_registered, some will need more org data... actually, it will be more this way: SELECT {$selected_fields} FROM people p, organization o. addresses a WHERE ... where $selected_fields = p.first_name, p.last_name, o.org_name or $selected_fields = p.first_name, p.last_name, o.org_name, a.address, a.city, a.state, a.zip or $selected_fields = o.org_name, a.address, a.city, a.state, a.zip So just tag AS table_field_name to each field when you're building your list of $selected_fields - e.g. $selected_fields = p.first_name AS person_first_name, p.last_name AS person_last_name, o.org_name AS organization_org_name You don't have to use the full table name either - for example in the following statement, you would then access the data using $result['p']['first_name']; $selected_fields = p.first_name AS p_first_name, p.last_name AS p_last_name, o.org_name AS o_org_name This approach is actually easier if you're creating the query dynamically, because you don't have to manually type a load of AS xxx statements after every field. I've recently done something similar in one of my applications to wrap date/time fields in either FROM_UNIXTIME() or UNIX_TIMESTAMP() functions. Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: need help with query...
From: Andy Shellam andy-li...@networkmail.eu To: Lamp Lists lamp.li...@yahoo.com Cc: mysql@lists.mysql.com Sent: Wednesday, December 17, 2008 2:48:31 PM Subject: Re: need help with query... Hi, Hi Andy, the reason I can't use this because fields (columns) in select statement (p.first_name, p.last_name,...) are actually dynamically created. In my project different client will select different fields to be shown. 99% will select first_name, and last_name, but some don't care about date_registered, some will need more org data... actually, it will be more this way: SELECT {$selected_fields} FROM people p, organization o. addresses a WHERE ... where $selected_fields = p.first_name, p.last_name, o.org_name or $selected_fields = p.first_name, p.last_name, o.org_name, a.address, a.city, a.state, a.zip or $selected_fields = o.org_name, a.address, a.city, a.state, a.zip So just tag AS table_field_name to each field when you're building your list of $selected_fields - e.g. $selected_fields = p.first_name AS person_first_name, p.last_name AS person_last_name, o.org_name AS organization_org_name You don't have to use the full table name either - for example in the following statement, you would then access the data using $result['p']['first_name']; $selected_fields = p.first_name AS p_first_name, p.last_name AS p_last_name, o.org_name AS o_org_name This approach is actually easier if you're creating the query dynamically, because you don't have to manually type a load of AS xxx statements after every field. I've recently done something similar in one of my applications to wrap date/time fields in either FROM_UNIXTIME() or UNIX_TIMESTAMP() functions. Andy Yup! That'll do it! Thanks Andy ;-)
Help with query
I have a char fiel where I am keeping dates formatted as year-month-day (2006-10-09) Now I am trying to find all the records between 2 strings (2 dates). The 2 queries below should return the same number of records by they do not. My query is this: SELECT COUNT(*) FROM proj where proj_archive=0 AND (proj_adv_date = '2008-01-01' AND proj_adv_date '2008-12-16') order by proj_type, proj_adv_date, proj_bid_date, proj_name ASC; +-+---+ | proj_id | proj_adv_date | +-+---+ | 181 | 2008-11-25| | 217 | 2008-10-27| | 136 | 2008-12-01| | 219 | 2008-12-08| | 225 | 2008-12-11| +-+---+ 5 rows in set (0.00 sec) I get only 5 records returned but if I do this query: SELECT COUNT(*) FROM proj where proj_archive=0 AND (proj_adv_date '2008-01-01') order by proj_type, proj_adv_date, proj_bid_date, proj_name ASC; +-+---+ | proj_id | proj_adv_date | +-+---+ | 181 | 2008-11-25| | 221 | 2008-12-23| | 108 | 2009-01-00| | 173 | 2009-03-00| | 149 | 2009-10-00| | 143 | 2009-7-00 | | 179 | 2010-04-00| | 217 | 2008-10-27| | 136 | 2008-12-01| | 219 | 2008-12-08| | 225 | 2008-12-11| | 187 | 2009-01-00| | 199 | 2009-01-01| | 177 | 2009-02-01| | 69 | 2009-03-00| | 70 | 2009-03-00| | 71 | 2009-03-00| | 142 | 2009-03-00| | 122 | 2009-04-00| | 124 | 2009-04-00| | 207 | 2009-04-01| | 72 | 2009-07-00| | 73 | 2009-07-00| | 82 | 2009-07-00| | 209 | 2009-10-01| | 211 | 2009-10-01| | 213 | 2010-03-01| +-+---+ 27 rows in set (0.00 sec) thanks, Rotsen :-)
Re: Help with query
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
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
Sorry!!! I apoligized for being blind. Yes, in my small mind I was thinking that I do not have records biggeer than 2008 but I do and my mind refused to see the records for 2009 and 2010. Sorry again for being so blind to the obvious. :-( On Mon, Dec 15, 2008 at 9:50 AM, Phil freedc@gmail.com wrote: Am I totally missing something? Why do you believe the two queries should return the same # of rows? First one has a qualification of proj_adv_date '2008-12-16' whilst the second one does not... On Mon, Dec 15, 2008 at 12:12 PM, Néstor rot...@gmail.com wrote: I have a char fiel where I am keeping dates formatted as year-month-day (2006-10-09) Now I am trying to find all the records between 2 strings (2 dates). The 2 queries below should return the same number of records by they do not. My query is this: SELECT COUNT(*) FROM proj where proj_archive=0 AND (proj_adv_date = '2008-01-01' AND proj_adv_date '2008-12-16') order by proj_type, proj_adv_date, proj_bid_date, proj_name ASC; +-+---+ | proj_id | proj_adv_date | +-+---+ | 181 | 2008-11-25| | 217 | 2008-10-27| | 136 | 2008-12-01| | 219 | 2008-12-08| | 225 | 2008-12-11| +-+---+ 5 rows in set (0.00 sec) I get only 5 records returned but if I do this query: SELECT COUNT(*) FROM proj where proj_archive=0 AND (proj_adv_date '2008-01-01') order by proj_type, proj_adv_date, proj_bid_date, proj_name ASC; +-+---+ | proj_id | proj_adv_date | +-+---+ | 181 | 2008-11-25| | 221 | 2008-12-23| | 108 | 2009-01-00| | 173 | 2009-03-00| | 149 | 2009-10-00| | 143 | 2009-7-00 | | 179 | 2010-04-00| | 217 | 2008-10-27| | 136 | 2008-12-01| | 219 | 2008-12-08| | 225 | 2008-12-11| | 187 | 2009-01-00| | 199 | 2009-01-01| | 177 | 2009-02-01| | 69 | 2009-03-00| | 70 | 2009-03-00| | 71 | 2009-03-00| | 142 | 2009-03-00| | 122 | 2009-04-00| | 124 | 2009-04-00| | 207 | 2009-04-01| | 72 | 2009-07-00| | 73 | 2009-07-00| | 82 | 2009-07-00| | 209 | 2009-10-01| | 211 | 2009-10-01| | 213 | 2010-03-01| +-+---+ 27 rows in set (0.00 sec) thanks, Rotsen :-) -- Distributed Computing stats http://stats.free-dc.org
Re: Need help to query with timestamp in C++
Hi Saul, I need to use C++ and I'm not writing a web application. Thanks anyway. Kandy I have done queries to the database in PHP with variables like month but easily can select from a range of time and data to produce the same results, the output goes directly to the web so if that is what you are seeking for, I can help with PHP. Saul Kandy Wong wrote: Hi, I need to write a C++ program in a Linux environment to query with a timestamp. The user will only provide with an approximate time so I'd like to know how can I write a program or a query to return the closest data. The followings are the timestamp in the MySQL database: | 2008-08-05 03:56:09 | 1217933769 | | 2008-08-05 03:56:19 | 1217933779 | | 2008-08-05 03:56:29 | 1217933789 | | 2008-08-05 03:59:39 | 1217933979 | | 2008-08-05 03:59:49 | 1217933989 | | 2008-08-05 03:59:59 | 1217933999 | | 2008-08-05 04:02:39 | 1217934159 | | 2008-08-05 04:02:49 | 1217934169 | | 2008-08-05 04:02:59 | 1217934179 | For example, '2008-08-05 04:01:39' is the time provided by the user which does not exist in the database. So, how can I return the closest data? I know I can make use of 'LIKE' but this will return more than one data. What is the best method to get the closest one? And what is the good connector (C++ to MySQL) to use? Any suggestion? Thank you. Kandy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Kandy Wong Scientific Programmer Analyst TRIUMF (Canada's National Laboratory for Particle and Nuclear Physics) 4004 Wesbrook Mall Vancouver, BC, Canada, V6T 2A3 Phone: (604) 222- 1047 ext. 6193 Email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help to query with timestamp in C++
Hi Kandy, this could be the query you are looking for. It should return record with the closest timestamp to your required time: (SELECT TIMEDIFF('20080815091907', timestamp_column) AS diff, t.* FROM table1 t WHERE timestamp_column = '20080815091907' ORDER BY timestamp_column DESC LIMIT 1 ) UNION (SELECT TIMEDIFF(timestamp_column, '20080815091907') AS diff, t.* FROM table1 t WHERE timestamp_column = '20080815091907' ORDER BY timestamp_column LIMIT 1 ) ORDER BY diff LIMIT 1 HTH, Dusan Kandy Wong napsal(a): Hi, I need to write a C++ program in a Linux environment to query with a timestamp. The user will only provide with an approximate time so I'd like to know how can I write a program or a query to return the closest data. The followings are the timestamp in the MySQL database: | 2008-08-05 03:56:09 | 1217933769 | | 2008-08-05 03:56:19 | 1217933779 | | 2008-08-05 03:56:29 | 1217933789 | | 2008-08-05 03:59:39 | 1217933979 | | 2008-08-05 03:59:49 | 1217933989 | | 2008-08-05 03:59:59 | 1217933999 | | 2008-08-05 04:02:39 | 1217934159 | | 2008-08-05 04:02:49 | 1217934169 | | 2008-08-05 04:02:59 | 1217934179 | For example, '2008-08-05 04:01:39' is the time provided by the user which does not exist in the database. So, how can I return the closest data? I know I can make use of 'LIKE' but this will return more than one data. What is the best method to get the closest one? And what is the good connector (C++ to MySQL) to use? Any suggestion? Thank you. Kandy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help to query with timestamp in C++
Kandy Wong wrote: Hi Saul, I need to use C++ and I'm not writing a web application. Thanks anyway. you can do something like: select min(abs(timediff(targettime,timestamp))) from table where condition ; if you use the libmysql you can get the result as strings back (the method i prefer) and convert them in what ever you need. re, wh The followings are the timestamp in the MySQL database: | 2008-08-05 03:56:09 | 1217933769 | | 2008-08-05 03:56:19 | 1217933779 | | 2008-08-05 03:56:29 | 1217933789 | | 2008-08-05 03:59:39 | 1217933979 | | 2008-08-05 03:59:49 | 1217933989 | | 2008-08-05 03:59:59 | 1217933999 | | 2008-08-05 04:02:39 | 1217934159 | | 2008-08-05 04:02:49 | 1217934169 | | 2008-08-05 04:02:59 | 1217934179 | For example, '2008-08-05 04:01:39' is the time provided by the user which does not exist in the database. So, how can I return the closest data? I know I can make use of 'LIKE' but this will return more than one data. What is the best method to get the closest one? And what is the good connector (C++ to MySQL) to use? Any suggestion? Thank you. Kandy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help to query with timestamp in C++
Kandy Wong wrote: And what is the good connector (C++ to MySQL) to use? MySQL++ (http://tangentsoft.net/mysql++/) has native Date, Time, and DateTime data types. You can convert to these types implicitly: mysqlpp::DateTime dt = row[my_column]; Row::operator[] doesn't return DateTime, it returns a stringish type, which can convert itself to lots of different C++ data types. This is useful because the MySQL C API normally returns results in string form, so you need a natural way to convert these values to the native C++ types for processing. In this particular case, it saves you from having to do the timestamp string parsing yourself. -- Warren Young, maintainer of MySQL++ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help to query with timestamp in C++
I'm puzzled by the layout of your table, if that's what you're showing us. Is the timestamp in the table truly associated with the time at which the user put in his approximate time? If, for example, the user types in 04:05:07 at 04:03:02, and then types in 04:02:59 at 04:03:03, what is it that you want to return? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Kandy Wong [mailto:[EMAIL PROTECTED] Sent: Friday, August 15, 2008 2:36 AM To: Saul Bejarano Cc: mysql@lists.mysql.com Subject: Re: Need help to query with timestamp in C++ Hi Saul, I need to use C++ and I'm not writing a web application. Thanks anyway. Kandy I have done queries to the database in PHP with variables like month but easily can select from a range of time and data to produce the same results, the output goes directly to the web so if that is what you are seeking for, I can help with PHP. Saul Kandy Wong wrote: Hi, I need to write a C++ program in a Linux environment to query with a timestamp. The user will only provide with an approximate time so I'd like to know how can I write a program or a query to return the closest data. The followings are the timestamp in the MySQL database: | 2008-08-05 03:56:09 | 1217933769 | | 2008-08-05 03:56:19 | 1217933779 | | 2008-08-05 03:56:29 | 1217933789 | | 2008-08-05 03:59:39 | 1217933979 | | 2008-08-05 03:59:49 | 1217933989 | | 2008-08-05 03:59:59 | 1217933999 | | 2008-08-05 04:02:39 | 1217934159 | | 2008-08-05 04:02:49 | 1217934169 | | 2008-08-05 04:02:59 | 1217934179 | For example, '2008-08-05 04:01:39' is the time provided by the user which does not exist in the database. So, how can I return the closest data? I know I can make use of 'LIKE' but this will return more than one data. What is the best method to get the closest one? And what is the good connector (C++ to MySQL) to use? Any suggestion? Thank you. Kandy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Kandy Wong Scientific Programmer Analyst TRIUMF (Canada's National Laboratory for Particle and Nuclear Physics) 4004 Wesbrook Mall Vancouver, BC, Canada, V6T 2A3 Phone: (604) 222- 1047 ext. 6193 Email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help to query with timestamp in C++
Hi Dusan, Thank you so much. It works! Kandy Hi Kandy, this could be the query you are looking for. It should return record with the closest timestamp to your required time: (SELECT TIMEDIFF('20080815091907', timestamp_column) AS diff, t.* FROM table1 t WHERE timestamp_column = '20080815091907' ORDER BY timestamp_column DESC LIMIT 1 ) UNION (SELECT TIMEDIFF(timestamp_column, '20080815091907') AS diff, t.* FROM table1 t WHERE timestamp_column = '20080815091907' ORDER BY timestamp_column LIMIT 1 ) ORDER BY diff LIMIT 1 HTH, Dusan Kandy Wong napsal(a): Hi, I need to write a C++ program in a Linux environment to query with a timestamp. The user will only provide with an approximate time so I'd like to know how can I write a program or a query to return the closest data. The followings are the timestamp in the MySQL database: | 2008-08-05 03:56:09 | 1217933769 | | 2008-08-05 03:56:19 | 1217933779 | | 2008-08-05 03:56:29 | 1217933789 | | 2008-08-05 03:59:39 | 1217933979 | | 2008-08-05 03:59:49 | 1217933989 | | 2008-08-05 03:59:59 | 1217933999 | | 2008-08-05 04:02:39 | 1217934159 | | 2008-08-05 04:02:49 | 1217934169 | | 2008-08-05 04:02:59 | 1217934179 | For example, '2008-08-05 04:01:39' is the time provided by the user which does not exist in the database. So, how can I return the closest data? I know I can make use of 'LIKE' but this will return more than one data. What is the best method to get the closest one? And what is the good connector (C++ to MySQL) to use? Any suggestion? Thank you. Kandy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need help to query with timestamp in C++
Hi, I need to write a C++ program in a Linux environment to query with a timestamp. The user will only provide with an approximate time so I'd like to know how can I write a program or a query to return the closest data. The followings are the timestamp in the MySQL database: | 2008-08-05 03:56:09 | 1217933769 | | 2008-08-05 03:56:19 | 1217933779 | | 2008-08-05 03:56:29 | 1217933789 | | 2008-08-05 03:59:39 | 1217933979 | | 2008-08-05 03:59:49 | 1217933989 | | 2008-08-05 03:59:59 | 1217933999 | | 2008-08-05 04:02:39 | 1217934159 | | 2008-08-05 04:02:49 | 1217934169 | | 2008-08-05 04:02:59 | 1217934179 | For example, '2008-08-05 04:01:39' is the time provided by the user which does not exist in the database. So, how can I return the closest data? I know I can make use of 'LIKE' but this will return more than one data. What is the best method to get the closest one? And what is the good connector (C++ to MySQL) to use? Any suggestion? Thank you. Kandy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help to query with timestamp in C++
I have done queries to the database in PHP with variables like month but easily can select from a range of time and data to produce the same results, the output goes directly to the web so if that is what you are seeking for, I can help with PHP. Saul Kandy Wong wrote: Hi, I need to write a C++ program in a Linux environment to query with a timestamp. The user will only provide with an approximate time so I'd like to know how can I write a program or a query to return the closest data. The followings are the timestamp in the MySQL database: | 2008-08-05 03:56:09 | 1217933769 | | 2008-08-05 03:56:19 | 1217933779 | | 2008-08-05 03:56:29 | 1217933789 | | 2008-08-05 03:59:39 | 1217933979 | | 2008-08-05 03:59:49 | 1217933989 | | 2008-08-05 03:59:59 | 1217933999 | | 2008-08-05 04:02:39 | 1217934159 | | 2008-08-05 04:02:49 | 1217934169 | | 2008-08-05 04:02:59 | 1217934179 | For example, '2008-08-05 04:01:39' is the time provided by the user which does not exist in the database. So, how can I return the closest data? I know I can make use of 'LIKE' but this will return more than one data. What is the best method to get the closest one? And what is the good connector (C++ to MySQL) to use? Any suggestion? Thank you. Kandy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with query to remove all records where foreign key doesn't have corresponding records
I want to remove all records from 'feed_tag' where the feed_id foreign key doesn't have any corresponding records in feed. For instance I may have a record in feed_tag that is like (23, 10, 4543, '... (some date)'). Then lets say there is no record in feed that has a primary id key of 10. I want that record (or usually records because of the 1 feed to many feed_tag relationship) to be removed. CREATE TABLE IF NOT EXISTS `feed` ( `id` int(11) NOT NULL auto_increment, `title` varchar(100) default NULL, `url` varchar(255) default NULL, `host` varchar(100) default NULL, `type` varchar(100) default NULL, `status` char(1) default NULL, `total_stories` int(11) default '0', `created_at` datetime default NULL, `updated_at` datetime default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; CREATE TABLE IF NOT EXISTS `feed_tag` ( `id` int(11) NOT NULL auto_increment, `feed_id` int(11) default NULL, `tag_id` int(11) default NULL, `created_at` datetime default NULL, PRIMARY KEY (`id`), KEY `feed_tag_FI_1` (`feed_id`), KEY `feed_tag_FI_2` (`tag_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; As you can see the foreign key 'feed_id' is the issue here (ignore the tag_id key). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with query to remove all records where foreign key doesn't have corresponding records
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
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
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
you can do some thing like this. create table new_table(id int, hair varchar(50),eyes varchar(50)) select column1,column2,...from old_table; On 7/3/08, axis [EMAIL PROTECTED] wrote: This question is strictly related to the mysql query not the php code. I need to either create a new table from the old one or add columns. The thing is don't know how to do it. let me simplify things up: I need a query to retrieve values from the table PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS to a new one or add columns. the new table should look like ID Hair Eyes Blond Green Brunette Hazel The php code is not what I want I need the query, to do exactly that. Then I will use it in my php code, but what that is not what I am asking at this time. Just the query to do that. Thanks, Rick Fitzgerald John Meyer wrote: It would be better to post this sort of question at [EMAIL PROTECTED] axis wrote: Hi, I want to create a new column or table from queries of the values of columns I( already have. My table is : PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS products_id products_extra_fields_id products_extra_fields_value 1 1 Blond 1 2 Green 1 3 1.75 1 4 24 2 1 Brunette 2 2 Hazel 2 3 1.56 2 4 28 with this Select query function get_extra($extra_array = '') { if (!is_array($extra_array)) $extra_array = array(); $extra_query = db_query(select products_extra_fields_value from . TABLE_PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS . where products_extra_fields_id = '1'); while ($extra = db_fetch_array($extra_query)) { $extra_array[] = array('id' = $extra['products_extra_fields_id'], 'text' = $extra['products_extra_fields_value']); } return $extra_array; } This function uses the query: select products_extra_fields_value from PRODUCTS_EXTRA_FIELDS where products_extra_fields_id = 1; and returns Rubia and Morocha which is fine, now I need to either create a new column or table in order to be able to have those values in their own column my knowledge of mysql is limited so please give me suggestions as what to use in order to create a function to retrieve values and add them automatically to my table or columns The table or columns have to be something like: ID Hair Eyes Blond Green Brunette Hazel so I can call them later by selecting from a column in a table like TABLE_PRODUCTS_EXTRA_FIELDS_TO_PRODUCTS_EXTRA_FIELDS_VALUE pef2pev.hair, pef2pev.eyes ... and so on here is my current switch for ($i=0, $n=sizeof($column_list); $i$n; $i++) { switch ($column_list[$i]) { case 'PRODUCT_LIST_MODEL': $select_column_list .= 'p.products_model, '; break; case 'PRODUCT_LIST_MANUFACTURER': $select_column_list .= 'm.manufacturers_name, '; break; // aDD eXTRA FIELdS case 'PRODUCT_LIST_HAIR': $select_column_list .= 'pef2pev.hair, '; break; and so on ... break; } } Thanks Rick Fitzgerald -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with query MySQL and PHP
Hi, I want to create a new column or table from queries of the values of columns I( already have. My table is : PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS products_id products_extra_fields_id products_extra_fields_value 1 1 Blond 1 2 Green 1 3 1.75 1 4 24 2 1 Brunette 2 2 Hazel 2 3 1.56 2 4 28 with this Select query function get_extra($extra_array = '') { if (!is_array($extra_array)) $extra_array = array(); $extra_query = db_query(select products_extra_fields_value from . TABLE_PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS . where products_extra_fields_id = '1'); while ($extra = db_fetch_array($extra_query)) { $extra_array[] = array('id' = $extra['products_extra_fields_id'], 'text' = $extra['products_extra_fields_value']); } return $extra_array; } This function uses the query: select products_extra_fields_value from PRODUCTS_EXTRA_FIELDS where products_extra_fields_id = 1; and returns Rubia and Morocha which is fine, now I need to either create a new column or table in order to be able to have those values in their own column my knowledge of mysql is limited so please give me suggestions as what to use in order to create a function to retrieve values and add them automatically to my table or columns The table or columns have to be something like: ID Hair Eyes Blond Green Brunette Hazel so I can call them later by selecting from a column in a table like TABLE_PRODUCTS_EXTRA_FIELDS_TO_PRODUCTS_EXTRA_FIELDS_VALUE pef2pev.hair, pef2pev.eyes ... and so on here is my current switch for ($i=0, $n=sizeof($column_list); $i$n; $i++) { switch ($column_list[$i]) { case 'PRODUCT_LIST_MODEL': $select_column_list .= 'p.products_model, '; break; case 'PRODUCT_LIST_MANUFACTURER': $select_column_list .= 'm.manufacturers_name, '; break; // aDD eXTRA FIELdS case 'PRODUCT_LIST_HAIR': $select_column_list .= 'pef2pev.hair, '; break; and so on ... break; } } Thanks Rick Fitzgerald -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help with query
Hi, im having a lot of trouble with one query, i hope someone could give me a hand with this, i would be really grateful these are the tables: TABLE friends id_usr INT id_friend INT with INDEX on (id_usr,id_friend) TABLE status id_usr INT lastConnection (other irrelevant info) with INDEX on (id_usr,lastConnection) im trying to get friends of a given id_usr ordered by lastConnection. i tried a lot, the simplest query give me the best results explain SELECT F.id_friend,S.lastConnection FROM friends F, user_status S WHERE F.id_friend = S.id_usr AND F.id_usr = 1 ORDER BY lastConnection id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE F ref id_usr id_usr 4 const 20 Using temporary; Using filesort 1 SIMPLE S ref id_usr id_usr 4 netlivin3.F.id_friend 3 Using index it's really bad, but at least it's ref type so only read the rows of friends table matching id_usr=1 **
Re: help wit query optimization (cont'd)
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)
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)
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)
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
Your biggest problem is probably the subquery/IN your are performing. You should change that to a join. And I don't know about using SQL_CALC_FOUND_ROWS in a full text query that's not boolean, and you shouldn't use it unless you have a LIMIT clause. SELECT SQL_CALC_FOUND_ROWS table_1.id,table_2.* FROM table_2 JOIN (SELECT shared_id FROM table_1_view) as table_3 ON table_2.shared_id=table_3.shared_id LEFT JOIN table_1 ON table_2.shared_id = table_1.shared_id WHERE MATCH table_2.field AGAINST ('value') AND table_2.current = 1 I know the difference doesn't seem that much, but MySQL optimizes it very differently. Brent On Jan 4, 2008, at 5:47 PM, Eben wrote: Hi, I have a query that has to run on a full text indexed table with many millions of records. I'm trying to figure out some optimizations for it. Here's the general query: SELECT SQL_CALC_FOUND_ROWS table_1.id,table_2.* FROM table_2 LEFT JOIN table_1 ON table_2.shared_id = table_1.shared_id WHERE MATCH table_2.field AGAINST ('value') AND table_2.current = 1 AND table_2.shared_id IN (SELECT shared_id FROM table_1_view) Some details: 1. table_1.shared_id and table_2.shared_id are indexed 2. table_2.current is an indexed tinyint 3. table_1_view is derived from a query like: SELECT shared_id FROM table_1 WHERE some_field LIKE 'some_value%' table_1 is a relatively small table i.e. 100k records table_2 is massive with 10 million records Any ideas or suggestions are appreciated thanks, Eben -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help wit query optimization (cont'd)
I left something out, the query looks like: SELECT SQL_CALC_FOUND_ROWS table_1.id,table_2.* FROM table_2 LEFT JOIN table_1 ON table_2.shared_id = table_1.shared_id WHERE MATCH table_2.field AGAINST ('value') AND table_2.current = 1 AND table_2.shared_id IN (SELECT shared_id FROM table_1_view) LIMIT 0,10 The resultset is paginated on the front end using the SQL_CALC_FOUND_ROWS functionality... thanks, Eben -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help wit query optimization (cont'd)
On Jan 4, 2008 5:51 PM, Eben [EMAIL PROTECTED] wrote: The resultset is paginated on the front end using the SQL_CALC_FOUND_ROWS functionality... Usually a bad idea: http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/ - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help with query optimization
Hi, I have a query that has to run on a full text indexed table with many millions of records. I'm trying to figure out some optimizations for it. Here's the general query: SELECT SQL_CALC_FOUND_ROWS table_1.id,table_2.* FROM table_2 LEFT JOIN table_1 ON table_2.shared_id = table_1.shared_id WHERE MATCH table_2.field AGAINST ('value') AND table_2.current = 1 AND table_2.shared_id IN (SELECT shared_id FROM table_1_view) Some details: 1. table_1.shared_id and table_2.shared_id are indexed 2. table_2.current is an indexed tinyint 3. table_1_view is derived from a query like: SELECT shared_id FROM table_1 WHERE some_field LIKE 'some_value%' table_1 is a relatively small table i.e. 100k records table_2 is massive with 10 million records Any ideas or suggestions are appreciated thanks, Eben -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help wit query optimization (cont'd)
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 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 :))
the simple answer is and b.name='name3' ?Bon ChanceMartin__Disclaimer and confidentiality noteEverything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. Date: Sun, 30 Dec 2007 13:54:32 +0100 From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Re: Help with query, (question simplified as last mail was very complicated to understand :)) Richard a écrit : Sorry about my last email which was long and not clear. This is what I want to do Join two tables on code table1 = code table3 where messageid = for example 28 table 1 contains : message from messageid -- message1 | code1 | 28 message2 | code1 | 28 message3 | code1 | 28 message4 | code1 | 29table 2 contains name | code | num -- name1 | code2 | 1 name2 | code1 | 2 name3 | code1 | 3 If I do : SELECT a.message,,b.name FROM table1 a JOIN table2 b ON a.code=b.code WHERE a.id='28' I get : message | name --- message1 | name2 message2 | name2 message3 | name2 message1 | name3 message2 | name3 message3 | name3But all I want to get is : message | name --- message1 | name3 message2 | name3 message3 | name3 If I do : SELECT * FROM table2 WHERE code = 'code1' ORDER BY num DESC LIMIT 1 I get : name | code | num -- name3 | code1 | 3 I now need to somehow combine the two to get : message | name --- message1 | name3 message2 | name3 message3 | name3 Of course I have simplified everything down to the minimum :) Thanks in advance, Richard As I have had no answer I presume that what I want to do is not possible or my question is not well explained. Anyhow I've rethought the system so I do not need to keep members information and now instead of adding a new entry I will now just change the existing one. I won't keep old members information in the database but I'll still have the database daily backups if I need the old information. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ The best games are on Xbox 360. Click here for a special offer on an Xbox 360 Console. http://www.xbox.com/en-US/hardware/wheretobuy/
Help with query, (question simplified as last mail was very complicated to understand :))
Sorry about my last email which was long and not clear. This is what I want to do Join two tables on code table1 = code table3 where messageid = for example 28 table 1 contains : message from messageid -- message1 | code1 |28 message2 | code1 |28 message3 | code1 |28 message4 | code1 |29 table 2 contains name | code | num -- name1 | code2 | 1 name2 | code1 | 2 name3 | code1 | 3 If I do : SELECT a.message,,b.name FROM table1 a JOIN table2 b ON a.code=b.code WHERE a.id='28' I get : message| name --- message1 | name2 message2 | name2 message3 | name2 message1 | name3 message2 | name3 message3 | name3 But all I want to get is : message| name --- message1 | name3 message2 | name3 message3 | name3 If I do : SELECT * FROM table2 WHERE code = 'code1' ORDER BY num DESC LIMIT 1 I get : name | code | num -- name3 | code1 | 3 I now need to somehow combine the two to get : message| name --- message1 | name3 message2 | name3 message3 | name3 Of course I have simplified everything down to the minimum :) Thanks in advance, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help creating query statement
Guys, just wanted to thank you again for helping me with the sql statement that I needed. I was able to sorted using php and I was able to display the correct result. Thanks again!!1 Nestor :-) On Nov 6, 2007 7:37 AM, Néstor [EMAIL PROTECTED] wrote: You guys are correct, that is exactly what happened. I must thing of this in the future. At this moment I have a lot of other projects to take care, that it is eaiser for me to read the information into an associative array with the columns and the values and sort the array and then print the top 5 values within each array. Thanks, Nestor :-) On 11/6/07, Enrique Sanchez Vela [EMAIL PROTECTED] wrote: --- [EMAIL PROTECTED] wrote: Néstor wrote: I think you'd best begin by normalising your database. Something along these lines: very true indeed, that would save you major headaches when right after finishing the demo, someone would say, can we add a 18 gallon pledge? what about a 25? as of the sort of columns per row, I believe it is not possible nor in the goals of MySQL to make it possible/easy. best regards, enrique. -- What you have been obliged to discover by yourself leaves a path in your mind which you can use again when the need arises.--G. C. Lichtenberg http://themathcircle.org/ __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help creating query statement
--- [EMAIL PROTECTED] wrote: Néstor wrote: I think you'd best begin by normalising your database. Something along these lines: very true indeed, that would save you major headaches when right after finishing the demo, someone would say, can we add a 18 gallon pledge? what about a 25? as of the sort of columns per row, I believe it is not possible nor in the goals of MySQL to make it possible/easy. best regards, enrique. -- What you have been obliged to discover by yourself leaves a path in your mind which you can use again when the need arises.--G. C. Lichtenberg http://themathcircle.org/ __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help creating query statement
You guys are correct, that is exactly what happened. I must thing of this in the future. At this moment I have a lot of other projects to take care, that it is eaiser for me to read the information into an associative array with the columns and the values and sort the array and then print the top 5 values within each array. Thanks, Nestor :-) On 11/6/07, Enrique Sanchez Vela [EMAIL PROTECTED] wrote: --- [EMAIL PROTECTED] wrote: Néstor wrote: I think you'd best begin by normalising your database. Something along these lines: very true indeed, that would save you major headaches when right after finishing the demo, someone would say, can we add a 18 gallon pledge? what about a 25? as of the sort of columns per row, I believe it is not possible nor in the goals of MySQL to make it possible/easy. best regards, enrique. -- What you have been obliged to discover by yourself leaves a path in your mind which you can use again when the need arises.--G. C. Lichtenberg http://themathcircle.org/ __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need help creating query statement
I can do simple select statements but I need your brains to create query statment. I am using mysql 4.0 in my 1and1 site. I have a table that has 8 fields, one is the agency field and the other 7 are *tip* values on saving water and the value of this field is either 0 or an amount. -- |agency| 5g | 8g | 10g | 12g | 15g | 20g | 40g | -- so the record could be |Helix | 0 | 8 | 10 | 12 | 15 | 0 | 40 | |Corp| 5 | 0 | 0 | 12 | 15 | 0 |0 | |Helix | 0 | 8 | 10 | 0 | 15 | 0 | 40 | I need to get the to count *tips* per *agency *so in this case of 3 records I would have: |Helix | 0 | 2 | 2 |1 | 2 | 0 | 2 | |Corp |1 | 0 | 0 |1 |1 |0 |0 | and then I need to come out with top 5 *tips *per agency |Helix | 8g = 2 | 10g = 2 | 15g = 2 | 8g = 2 | 40g = 2| |Corp| 8g = 1 | 12g = 1 | 15g= 1 | Is there an easy way to do this? I hope I made myself understood. Thanks, Néstor :-)
Fwd: Re: Need help creating query statement
--- Enrique Sanchez Vela [EMAIL PROTECTED] wrote: Date: Mon, 5 Nov 2007 15:01:59 -0800 (PST) From: Enrique Sanchez Vela [EMAIL PROTECTED] Subject: Re: Need help creating query statement To: Néstor [EMAIL PROTECTED] --- Néstor [EMAIL PROTECTED] wrote: I can do simple select statements but I need your brains to create query statment. I am using mysql 4.0 in my 1and1 site. I have a table that has 8 fields, one is the agency field and the other 7 are *tip* values on saving water and the value of this field is either 0 or an amount. -- |agency| 5g | 8g | 10g | 12g | 15g | 20g | 40g | -- so the record could be |Helix | 0 | 8 | 10 | 12 | 15 | 0 | 40 | |Corp| 5 | 0 | 0 | 12 | 15 | 0 | 0 | |Helix | 0 | 8 | 10 | 0 | 15 | 0 | 40 | I need to get the to count *tips* per *agency *so in this case of 3 records I would have: |Helix | 0 | 2 | 2 |1 | 2 | 0 | 2 | |Corp |1 | 0 | 0 |1 |1 |0 | 0 | let's see if I got the point CREATE TABLE `agency` ( `name` varchar(11) collate latin1_bin NOT NULL default '', `5g` int(11) NOT NULL default '0', `8g` int(11) NOT NULL default '0', `10g` int(11) NOT NULL default '0', `12g` int(11) NOT NULL default '0', `15g` int(11) NOT NULL default '0', `20g` int(11) NOT NULL default '0', `40g` int(11) NOT NULL default '0' ) ENGINE=MyISAM ; select * from agency; +---+++-+-+-+-+-+ | name | 5g | 8g | 10g | 12g | 15g | 20g | 40g | +---+++-+-+-+-+-+ | Helix | 0 | 0 | 0 | 12 | 0 | 0 | 0 | | Helix | 0 | 8 | 10 | 0 | 15 | 0 | 40 | | Corp | 5 | 0 | 0 | 12 | 15 | 0 | 40 | | Helix | 0 | 8 | 10 | 0 | 15 | 0 | 40 | +---+++-+-+-+-+-+ 4 rows in set (0.00 sec) select `name`, sum(if(5g0, 1,0)) as 5g , sum(if(8g0, 1,0)) as 8g, sum(if(10g0,1,0)) as 10g, sum(if(12g0,1,0))as 12g ,sum(if(15g0,1,0)) as 15g , sum(if (20g0,1,0)) as 20g, sum(if(40g0,1,0)) as 40g from agency group by `name`; produces... +---+--+--+--+--+--+--+--+ | name | 5g | 8g | 10g | 12g | 15g | 20g | 40g | +---+--+--+--+--+--+--+--+ | Corp |1 |0 |0 |1 |1 |0 | 1 | | Helix |0 |2 |2 |1 |2 |0 | 2 | +---+--+--+--+--+--+--+--+ 2 rows in set (0.00 sec) regards, esv. -- What you have been obliged to discover by yourself leaves a path in your mind which you can use again when the need arises.--G. C. Lichtenberg http://themathcircle.org/ __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- What you have been obliged to discover by yourself leaves a path in your mind which you can use again when the need arises.--G. C. Lichtenberg http://themathcircle.org/ __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help creating query statement
Enrique that is pretty good and close to what I need. On top of what you have generously provide, I guess I can just put each gallon field into an array an then sort the array to display the first 5 gallon fields per agency. Is there an easier way? Thanks, Nestor :-) On 11/5/07, Enrique Sanchez Vela [EMAIL PROTECTED] wrote: --- Néstor [EMAIL PROTECTED] wrote: I can do simple select statements but I need your brains to create query statment. I am using mysql 4.0 in my 1and1 site. I have a table that has 8 fields, one is the agency field and the other 7 are *tip* values on saving water and the value of this field is either 0 or an amount. -- |agency| 5g | 8g | 10g | 12g | 15g | 20g | 40g | -- so the record could be |Helix | 0 | 8 | 10 | 12 | 15 | 0 | 40 | |Corp| 5 | 0 | 0 | 12 | 15 | 0 | 0 | |Helix | 0 | 8 | 10 | 0 | 15 | 0 | 40 | I need to get the to count *tips* per *agency *so in this case of 3 records I would have: |Helix | 0 | 2 | 2 |1 | 2 | 0 | 2 | |Corp |1 | 0 | 0 |1 |1 |0 | 0 | let's see if I got the point CREATE TABLE `agency` ( `name` varchar(11) collate latin1_bin NOT NULL default '', `5g` int(11) NOT NULL default '0', `8g` int(11) NOT NULL default '0', `10g` int(11) NOT NULL default '0', `12g` int(11) NOT NULL default '0', `15g` int(11) NOT NULL default '0', `20g` int(11) NOT NULL default '0', `40g` int(11) NOT NULL default '0' ) ENGINE=MyISAM ; select * from agency; +---+++-+-+-+-+-+ | name | 5g | 8g | 10g | 12g | 15g | 20g | 40g | +---+++-+-+-+-+-+ | Helix | 0 | 0 | 0 | 12 | 0 | 0 | 0 | | Helix | 0 | 8 | 10 | 0 | 15 | 0 | 40 | | Corp | 5 | 0 | 0 | 12 | 15 | 0 | 40 | | Helix | 0 | 8 | 10 | 0 | 15 | 0 | 40 | +---+++-+-+-+-+-+ 4 rows in set (0.00 sec) select `name`, sum(if(5g0, 1,0)) as 5g , sum(if(8g0, 1,0)) as 8g, sum(if(10g0,1,0)) as 10g, sum(if(12g0,1,0))as 12g ,sum(if(15g0,1,0)) as 15g , sum(if (20g0,1,0)) as 20g, sum(if(40g0,1,0)) as 40g from agency group by `name`; produces... +---+--+--+--+--+--+--+--+ | name | 5g | 8g | 10g | 12g | 15g | 20g | 40g | +---+--+--+--+--+--+--+--+ | Corp |1 |0 |0 |1 |1 |0 | 1 | | Helix |0 |2 |2 |1 |2 |0 | 2 | +---+--+--+--+--+--+--+--+ 2 rows in set (0.00 sec) regards, esv. -- What you have been obliged to discover by yourself leaves a path in your mind which you can use again when the need arises.--G. C. Lichtenberg http://themathcircle.org/ __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: Need help creating query statement
Néstor wrote: I can do simple select statements but I need your brains to create query statment. I am using mysql 4.0 in my 1and1 site. I have a table that has 8 fields, one is the agency field and the other 7 are *tip* values on saving water and the value of this field is either 0 or an amount. -- |agency| 5g | 8g | 10g | 12g | 15g | 20g | 40g | -- so the record could be |Helix | 0 | 8 | 10 | 12 | 15 | 0 | 40 | |Corp| 5 | 0 | 0 | 12 | 15 | 0 |0 | |Helix | 0 | 8 | 10 | 0 | 15 | 0 | 40 | I need to get the to count *tips* per *agency *so in this case of 3 records I would have: |Helix | 0 | 2 | 2 |1 | 2 | 0 | 2 | |Corp |1 | 0 | 0 |1 |1 |0 |0 | and then I need to come out with top 5 *tips *per agency |Helix | 8g = 2 | 10g = 2 | 15g = 2 | 8g = 2 | 40g = 2| |Corp| 8g = 1 | 12g = 1 | 15g= 1 | Is there an easy way to do this? I think you'd best begin by normalising your database. Something along these lines: CREATE TABLE agency ( id tinyint(3) unsigned NOT NULL auto_increment, name varchar(64)NOT NULL, PRIMARY KEY (id) ) ENGINE=MyISAM; CREATE TABLE pledge ( id tinyint(3) unsigned NOT NULL auto_increment, agency_id tinyint(3) unsigned NOT NULL, gallons enum('5','8','10','12','15','20','40') DEFAULT NULL, PRIMARY KEY (id), FOREIGN KEY (agency_id) REFERENCES agency (id) ) ENGINE=MyISAM; You could create a separate 'gallons' table, with the '5','8','10', etc. as rows but, since this is MySQL you might as well go with the ENUM. Once you have your data normalised you'll be able to things like joining across both tables and grouping by agency, etc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with query...
I'm trying to set up a query, but I don't really get the result I expected, so can someone please help me out here? The query I've built is this: SELECT a1.username FROM accountuser AS a1 LEFT JOIN (freeaccounts AS f1, payments AS p1) ON (a1.username = p1.username AND p1.username = f1.username) WHERE a1.username LIKE 'cit%' AND a1.imp + a1.pp + a1.se + a1.auth != 0 AND (f1.free IS NULL OR f1.free = false) AND (p1.validdate UNIX_TIMESTAMP(NOW()) OR p1.validdate IS NULL) The accountuser table is always fully populated. The freeaccounts and payments tables are only occupied with the 'username' field if it's been used previously, so they may not contain any data. What I expect to get from the query is 'username' from the accountuser table when: The a1.username starts with 'cit', AND Any of a1.imp, a1.pp, a1.se or a1.auth is not 0, AND f1.free is either not populated or false. AND p1.validdate is either not populated, or the timestamp is before NOW. Everything seems to work except for the passed time check. If I set p1.validdate to a timestamp for something next week, the username is still returned in the result. Grateful for any hints... Anders. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with query...
Anders Norrbring wrote: I'm trying to set up a query, but I don't really get the result I expected, so can someone please help me out here? The query I've built is this: SELECT a1.username FROM accountuser AS a1 LEFT JOIN (freeaccounts AS f1, payments AS p1) ON (a1.username = p1.username AND p1.username = f1.username) WHERE a1.username LIKE 'cit%' AND a1.imp + a1.pp + a1.se + a1.auth != 0 AND (f1.free IS NULL OR f1.free = false) AND (p1.validdate UNIX_TIMESTAMP(NOW()) OR p1.validdate IS NULL) The accountuser table is always fully populated. The freeaccounts and payments tables are only occupied with the 'username' field if it's been used previously, so they may not contain any data. What I expect to get from the query is 'username' from the accountuser table when: The a1.username starts with 'cit', AND Any of a1.imp, a1.pp, a1.se or a1.auth is not 0, AND f1.free is either not populated or false. AND p1.validdate is either not populated, or the timestamp is before NOW. Everything seems to work except for the passed time check. If I set p1.validdate to a timestamp for something next week, the username is still returned in the result. Put the last line (the tests on p1.validdate) in the JOIN condition instead. brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SV: Help with query...
Hello where is the FROM for f1 table? Martin-- Why would there be any FROM for the f1 table? It's not needed in the result. Anyway, I've come up with a query that actually seems to do what I'm looking for; SELECT a1.username FROM accountuser AS a1 LEFT JOIN payments AS p1 ON (a1.username = p1.username) LEFT JOIN freeaccounts AS f1 ON (a1.username = f1.username) WHERE a1.username LIKE 'cit%' AND a1.imp + a1.pp + a1.se + a1.auth != 0 AND (p1.validdate UNIX_TIMESTAMP(NOW()) OR p1.validdate IS NULL) AND (f1.free = false OR f1.free IS NULL) Thanks for the hints, you who gave them.. ;) Anders. - Original Message - From: Anders Norrbring [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, October 14, 2007 7:13 AM Subject: Help with query... I'm trying to set up a query, but I don't really get the result I expected, so can someone please help me out here? The query I've built is this: SELECT a1.username FROM accountuser AS a1 LEFT JOIN (freeaccounts AS f1, payments AS p1) ON (a1.username = p1.username AND p1.username = f1.username) WHERE a1.username LIKE 'cit%' AND a1.imp + a1.pp + a1.se + a1.auth != 0 AND (f1.free IS NULL OR f1.free = false) AND (p1.validdate UNIX_TIMESTAMP(NOW()) OR p1.validdate IS NULL) The accountuser table is always fully populated. The freeaccounts and payments tables are only occupied with the 'username' field if it's been used previously, so they may not contain any data. What I expect to get from the query is 'username' from the accountuser table when: The a1.username starts with 'cit', AND Any of a1.imp, a1.pp, a1.se or a1.auth is not 0, AND f1.free is either not populated or false. AND p1.validdate is either not populated, or the timestamp is before NOW. Everything seems to work except for the passed time check. If I set p1.validdate to a timestamp for something next week, the username is still returned in the result. Grateful for any hints... Anders. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with Query
I need to get some duplicate record information from a table and I haven't found any way to do it yet. I figured there might be some type of query I could do using a for each type command. What I have is a table with names and companies. Some people have multiple entries for different companies. What I need to get is the name that has multiple entries along with the company names. Name| Company Joe BlowCompany 1 Joe BlowCompany 2 Joe G. Blow Company 1 Running the query should only return Joe Blow with Company 1 and Company 2. I can find out how many records Joe Blow has or list out each Company record grouped by Name but I only want Names with multiple entries shown. Can anyone help? I'm sure this also makes a difference but I'm stuck using MySQL 3.23. Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with Query
Hi Ed, Ed Curtis wrote: I need to get some duplicate record information from a table and I haven't found any way to do it yet. I figured there might be some type of query I could do using a for each type command. What I have is a table with names and companies. Some people have multiple entries for different companies. What I need to get is the name that has multiple entries along with the company names. Name | Company Joe BlowCompany 1 Joe BlowCompany 2 Joe G. BlowCompany 1 Running the query should only return Joe Blow with Company 1 and Company 2. I can find out how many records Joe Blow has or list out each Company record grouped by Name but I only want Names with multiple entries shown. Can anyone help? I'm sure this also makes a difference but I'm stuck using MySQL 3.23. Since you are using 3.23, you can't use subqueries, which would otherwise make this easy (http://www.xaprb.com/blog/2006/10/09/how-to-find-duplicate-rows-with-sql/). But you can do it with temporary tables instead: CREATE TEMPORARY TABLE tmp AS SELECT Name, COUNT(*) AS cnt FROM tbl GROUP BY Name; SELECT tbl.* FROM tbl JOIN tmp USING(Name) WHERE cnt 1; Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need help with query
The following query works just fine: SELECT B.BusNum, COALESCE(CampCount.Cnt,0) As Kids, B.CamperCapacity, COALESCE(CounselorCount.Cnt,0) As Adults, B.AdultCapacity FROM Buses B LEFT JOIN (SELECT BusID,Count(*) As Cnt FROM Campers GROUP BY BusID) AS CampCount ON CampCount.BusID=B.ID LEFT JOIN (SELECT BusID,Count(*) As Cnt FROM Counselors GROUP BY BusID) AS CounselorCount On CounselorCount.BusID=B.ID ORDER BY B.BusNum However, I would like to add the to have a total of the adults and kids on the bus, so I change my query as follows: SELECT B.BusNum, COALESCE(CampCount.Cnt,0) As Kids, B.CamperCapacity, COALESCE(CounselorCount.Cnt,0) As Adults, B.AdultCapacity, Kids + Adults As GT FROM Buses B LEFT JOIN (SELECT BusID,Count(*) As Cnt FROM Campers GROUP BY BusID) AS CampCount ON CampCount.BusID=B.ID LEFT JOIN (SELECT BusID,Count(*) As Cnt FROM Counselors GROUP BY BusID) AS CounselorCount On CounselorCount.BusID=B.ID ORDER BY B.BusNum When I try to execute this, I get the error, Unknown column 'Kids' in 'field list' How do I properly add these together to get a total column? Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with query
Hi Jesse, Jesse wrote: The following query works just fine: SELECT B.BusNum, COALESCE(CampCount.Cnt,0) As Kids, B.CamperCapacity, COALESCE(CounselorCount.Cnt,0) As Adults, B.AdultCapacity FROM Buses B LEFT JOIN (SELECT BusID,Count(*) As Cnt FROM Campers GROUP BY BusID) AS CampCount ON CampCount.BusID=B.ID LEFT JOIN (SELECT BusID,Count(*) As Cnt FROM Counselors GROUP BY BusID) AS CounselorCount On CounselorCount.BusID=B.ID ORDER BY B.BusNum However, I would like to add the to have a total of the adults and kids on the bus, so I change my query as follows: SELECT B.BusNum, COALESCE(CampCount.Cnt,0) As Kids, B.CamperCapacity, COALESCE(CounselorCount.Cnt,0) As Adults, B.AdultCapacity, Kids + Adults As GT FROM Buses B LEFT JOIN (SELECT BusID,Count(*) As Cnt FROM Campers GROUP BY BusID) AS CampCount ON CampCount.BusID=B.ID LEFT JOIN (SELECT BusID,Count(*) As Cnt FROM Counselors GROUP BY BusID) AS CounselorCount On CounselorCount.BusID=B.ID ORDER BY B.BusNum When I try to execute this, I get the error, Unknown column 'Kids' in 'field list' How do I properly add these together to get a total column? Column aliases can't be referred to later in the select list as far as I know. The only way I know to do this is simply duplicate the expressions: COALESCE(CampCount.Cnt,0) + COALESCE(CounselorCount.Cnt,0) as GT Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with query
COALESCE(CampCount.Cnt,0) + COALESCE(CounselorCount.Cnt,0) as GT Duuuh. Why didn't I think of that. What is MySQL's issue with referring to the variables (As assignments, whatever you want to call them)? I've had issues like this in situations like this one, when trying to use them in the ORDER BY clause, and other places. Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with query
Jesse wrote: COALESCE(CampCount.Cnt,0) + COALESCE(CounselorCount.Cnt,0) as GT Duuuh. Why didn't I think of that. What is MySQL's issue with referring to the variables (As assignments, whatever you want to call them)? I've had issues like this in situations like this one, when trying to use them in the ORDER BY clause, and other places. They simply don't 'exist' at that point in time. All of the rows are read at one point in time (according to a WHERE clause if applicable), then the expressions within the column lists are done, then grouping, order by etc., and finally HAVING clauses are done (where one can use a column alias, when not available within a WHERE clause). However, you should be able to use them in an ORDER BY - as they do exist at that point - so I'm not sure that issue would have been related to this. Best regards Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need a help in Query
Thank you very much Jorn. Your suggestion helped me.
need a help in Query
Hi Fello MySQL users, I am Bala Raju, i have a problem to extract data by writing query, i need u r help. Please help me. I have two tables, i am giving the tables as attachement, firs table is playersloginfo and second one is playerhanddetails. playersloginfo : This table stores all the loginsession details of the players, who visited the game. playerhanddetails: This table stores all the games the player played during a session. I need to extract the data from both these tables, i need to display the player's logintime, logouttime and the totaltime per a session(these are available in 1 table) and number of games the player played(from 2 table). Please give some ideas to write this queries. (I tried in this way to extract data, i simple selected all the columns and used time difference function, but to find number of games by a player, i need to use second table(playerhanddetails). I am searching second table by conditions of user_name, and date between (logindate, logoutdate) and time between (logintime, logouttime)) playersloginfo: -- ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | user_name | varchar(20) | YES | | NULL| | | ip | varchar(16) | YES | | NULL| | | action | varchar(15) | YES | | NULL| | | logindate | date| YES | | NULL| | | logoutdate | date| YES | | NULL| | | logintime | time| YES | | NULL| | | logouttime | time| YES | | NULL| | | status | varchar(15) | YES | | NULL| | ++-+--+-+-+---+ playerhanddetails : - ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | playername | varchar(20) | YES | | NULL| | | handnumber | bigint(20) | YES | | NULL| | | date | date| YES | | NULL| | | time | time| YES | | NULL| | ++-+--+-+-+---+
Re: need a help in Query
On Saturday 10 February 2007 12:45, balaraju mandala wrote: Hi Fello MySQL users, I am Bala Raju, i have a problem to extract data by writing query, i need u r help. Please help me. I have two tables, i am giving the tables as attachement, firs table is playersloginfo and second one is playerhanddetails. playersloginfo : This table stores all the loginsession details of the players, who visited the game. playerhanddetails: This table stores all the games the player played during a session. I need to extract the data from both these tables, i need to display the player's logintime, logouttime and the totaltime per a session(these are available in 1 table) and number of games the player played(from 2 table). Please give some ideas to write this queries. (I tried in this way to extract data, i simple selected all the columns and used time difference function, but to find number of games by a player, i need to use second table(playerhanddetails). I am searching second table by conditions of user_name, and date between (logindate, logoutdate) and time between (logintime, logouttime)) You should redesign your table playersloginfo. logindate and logintime should be merge into one field: login DATETIME The same goes for logoutdate and logouttime: logout DATETIME. Then you query will be much easier to handle. You should also change ip from varchar(15) to INT UNSIGNED and use INET_ATON to convert a ip of the form a.b.c.d to unsinged int, and INET_NTOA to go back to a.b.c.d -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need a help in Query
Hi Jorn, Thank you for reply, of course i can merge the columns and change the datatype. But buddy that is not problem here, the problem is extract the data. Did u able to understand my language, if not i will explain you again. Please read the mail again and tell some ways from u r experience. I am running out of solutions, as i am working on this from last two days.
Re: need a help in Query
On Saturday 10 February 2007 19:21, balaraju mandala wrote: Hi Jorn, Thank you for reply, of course i can merge the columns and change the datatype. But buddy that is not problem here, the problem is extract the data. After reading your message once more, I realise that you should do even more changes. The username should not be a part of the playersloginfo table. Consider this: Table: userinfo id mediumint unsigned not null auto_increment, user_name varchar(20) Table: playersloginfo user_id mediumint unsigned not null comment '--userinfo.id', ip logint unsinged, action varchar(15), login datetime, logout datetime, status varchar(15) Table: playershanddetails playername mediumint unsigned not null comment '--userinfo.id', handnumber bigint(20), date_time datetime Your query might look something like. select u.user_name,l.login,l.logout,timediff(l.login,l.logout) as totaltime, count(h.*) as no_of_games from userinfo as u inner join playersloginfo as l on (l.user_id=u.id) inner join playershanddetails as h on (h.playername=u.id) group by u.id; Please note: I have not tried this... just a quick suggestion right out of my brain... :-) -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: help with query: select customers that ARO NOT in orders table
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
You're right. A join is often more efficient than a subselect especially if you have good indices set up. I believe the IS NULL will also get optimized away in your query. In this example I personally like the subselect syntax for it's explicitness if the speed difference is negligible. For large data sets I would definetly go with your solution of using an outer join. Waldemar On Mon, 23 Oct 2006, Jerry Schwartz wrote: Is a sub-select more efficient than an outer join? SELECT cust_id FROM customers LEFT JOIN orders on customers.cust_id = orders.cust_id WHERE orders.cust_id IS NULL; Or am I missing something (as usual)? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Waldemar Jankowski [mailto:[EMAIL PROTECTED] Sent: Friday, October 20, 2006 1:53 PM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: help with query: select customers that ARO NOT in orders table On Fri, 20 Oct 2006, [EMAIL PROTECTED] wrote: hi to all, I have table customers (PK cust_id) I have table orders (PK order_id, FK cust_id) I need query that will selecct all customers from 'customers' they don't have any order, there is not their cust_id in 'orders'. couls somebody help me? thanks. -afan I think the most explicit way is with a sub select: select cust_id from customers where cust_id not in (select cust_id from orders); -w -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help with query: select customers that ARO NOT in orders table
hi to all, I have table customers (PK cust_id) I have table orders (PK order_id, FK cust_id) I need query that will selecct all customers from 'customers' they don't have any order, there is not their cust_id in 'orders'. couls somebody help me? thanks. -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with query: select customers that ARO NOT in orders table
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
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]