Re: SQL query question for GROUP BY
I just thought of something else... could the same be accomplished
using stored routines? I could find no way in MySQL to create stored
routines which could be used with the 'group by' queries though.
If this were possible, it should then be also possible to define a
'LAST' stored routine, or something which would output a given field
value based on whether some other field (say, numeric ID, or timestamp)
was the highest in its group.
This looks to be possible with external functions ('CREATE AGGREGATE
FUNCTION'), but this would require writing an external library to handle
the call, too. It would be strange it if were impossible to create an
aggregate stored procedure.
Does anyone know if it's possible to define stored procedures this way?
Rob Wultsch wrote:
On Fri, Apr 11, 2008 at 1:01 PM, Victor Danilchenko
[EMAIL PROTECTED] wrote:
Oooh, this looks evil. It seems like such a simple thing. I guess
creating max(log_date) as a field, and then joining on it, is a solution --
but my actual query (not the abridged version) is already half a page long.
I think at this point, unless someone else suggests a better
solution, this would be easier to do programatically -- skip the group
altogether, and instead simply order the rows, and grab the last one for
each username in code.
I guess another alternative would be to use a View for the UNIONized
query, but doesn't MySQL 'fake' views in 5.0 somehow?
I have used views to good results, however I have read not good things
about them. I would not be surprised if they worked well for this use.
I would also not be surprised if the merge storage engine was a better
option for you.
Possibly interesting:
http://www.mysqlperformanceblog.com/2007/08/12/mysql-view-as-performance-troublemaker/
--
Victor Danilchenko
Senior Software Engineer, AskOnline.net
[EMAIL PROTECTED] - 617-273-0119
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQl 5.0 optimization problem
Hi, I am trying to optimize our DB server. We have one table which has 1.3M entries, and the keys are GUIDs (so the key space is large). However, I have it all indexed. The performance was iffy, though, so I increased memory allocation, and the searches on the indexed fields seem to be OK now. Still, a simple count(field) on this table still takes, like, 2 minutes! I am guessing i am missing something obvious, but I read through a few MySQL optimization guides, and it seems like i am covering my bases. Here is the relevant slice of my my.cnf: # # * Fine Tuning # key_buffer = 256M max_allowed_packet = 64M thread_stack= 512K thread_cache_size = 32 # # * Query Cache Configuration # query_cache_limit = 32M query_cache_size= 256M query_cache_type= 1 table_cache = 512 sort_buffer_size= 32M I am running Ubuntu 7.04, with 1GB of RAM. The relevant fields are all indexed, but I can't imagine why a simple count() would take so long, when the actual query by value on the same field is effectively instant (after my cache setting expansion). Does anyone have an idea of what I am missing? Also, if you think any of the above settings seem wrong for a server with 1GB of RAM, please let me know. -- Victor Danilchenko Senior Software Engineer, AskOnline.net [EMAIL PROTECTED] - 617-273-0119 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQl 5.0 optimization problem
every row of a table. (I have 250GB tables which have excellent performance but would take minutes to count every row :) It might be better to consider the type of queries you will be using, and the type of table, and optimise for that... Ben Victor Danilchenko wrote: Hi, I am trying to optimize our DB server. We have one table which has 1.3M entries, and the keys are GUIDs (so the key space is large). However, I have it all indexed. The performance was iffy, though, so I increased memory allocation, and the searches on the indexed fields seem to be OK now. Still, a simple count(field) on this table still takes, like, 2 minutes! I am guessing i am missing something obvious, but I read through a few MySQL optimization guides, and it seems like i am covering my bases. Here is the relevant slice of my my.cnf: # # * Fine Tuning # key_buffer = 256M max_allowed_packet = 64M thread_stack= 512K thread_cache_size = 32 # # * Query Cache Configuration # query_cache_limit = 32M query_cache_size= 256M query_cache_type= 1 table_cache = 512 sort_buffer_size= 32M I am running Ubuntu 7.04, with 1GB of RAM. The relevant fields are all indexed, but I can't imagine why a simple count() would take so long, when the actual query by value on the same field is effectively instant (after my cache setting expansion). Does anyone have an idea of what I am missing? Also, if you think any of the above settings seem wrong for a server with 1GB of RAM, please let me know. If the field you are counting is the first field in an index I would think it would go much faster (system will just use the index). If some of your counts are fast and some are slow (you said iffy) that might explain the difference. -- William R. Mussatto Systems Engineer http://www.csz.com 909-920-9154 -- Victor Danilchenko Senior Software Engineer, AskOnline.net [EMAIL PROTECTED] - 617-273-0119 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL query question for GROUP BY
Hi all,
I trying to run a query where, after doing a UNION on two different
SELECTs, I need to sort the result by username and log_date fields, and
then grab the last entry for each username ('last' as determined by the
ordering of the log_date field, which is a datetime).
GROUP BY seems like an obvious choice; 'GROUP BY username', to be
exact. However, this seems to produce not the last row's values, but
ones from a random row in the group.
I don't think the fact that I am doing this on a subquery is relevant,
but just in case, I am including this info.
Here is what the query looks like, abridged:
SELECT id,username,log_date,event_type
FROM (SELECT * FROM a
UNION
SELECT * from b) as h
GROUP BY username
Basically, what I need is the chronologically last event_type value for
each user. I can achieve something similar by doing SELECT
MAX(event_type) -- but I need the equivalent of SELECT LAST(event_type);
last row instead of max-field-value row.
I keep having a feeling that I am making this way more complicated than
it has to be, and that there's a very simple way to return only the last
row for each username; but i am at a loss as to how to do it.
--
Victor Danilchenko
Senior Software Engineer, AskOnline.net
[EMAIL PROTECTED] - 617-273-0119
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL query question for GROUP BY
Oooh, this looks evil. It seems like such a simple thing. I guess creating max(log_date) as a field, and then joining on it, is a solution -- but my actual query (not the abridged version) is already half a page long. I think at this point, unless someone else suggests a better solution, this would be easier to do programatically -- skip the group altogether, and instead simply order the rows, and grab the last one for each username in code. I guess another alternative would be to use a View for the UNIONized query, but doesn't MySQL 'fake' views in 5.0 somehow? Rob Wultsch wrote: On Fri, Apr 11, 2008 at 11:46 AM, Victor Danilchenko [EMAIL PROTECTED] wrote: GROUP BY seems like an obvious choice; 'GROUP BY username', to be exact. However, this seems to produce not the last row's values, but ones from a random row in the group. Under most databases your query is erroneous. Take a look at http://lists.mysql.com/mysql/212084 . I don't think the fact that I am doing this on a subquery is relevant, but just in case, I am including this info. Here is what the query looks like, abridged: SELECT id,username,log_date,event_type FROM (SELECT * FROM a UNION SELECT * from b) as h GROUP BY username Read http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ Your are probably going to end up with a fairly ugly query (mostly because of the union) with what you have a derived table which will join against a and b again. SELECT if(a2.id is NULL, b2.id, a2.id) ,a1.username ... FROM (SELECT username, MAX(log_date) as mlog_date FROM (SELECT * FROM a UNION SELECT * from b) as h GROUP BY username) AS a1 LEFT JOIN a AS a2 ON a1.mlog_date = a2.log_date AND username... LEFT JOIN b AS b2 ... Any one have a suggestion for how to do with in a way that is not ugly as heck? -- Victor Danilchenko Senior Software Engineer, AskOnline.net [EMAIL PROTECTED] - 617-273-0119 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
