Best way to tune substr,min,max query

I have a query that I need to tune. Basically, substr a text, and select first and last entry. The table is currently a few million rows big. Index is on FromHost (text field) and ReceivedAt (index field) Is the best way to optimize my query. 1) create an index on substr() and the two date columns

group by different time period than functions allow

Can someone point a link, or show an example. basically, i have something like select week(mydate), count(mystuff) from table group by week(mydate); however, I need week to start on Wed 9am and end next Wed. What's the easiest way to accomplish that? thanks, andrey -- MySQL General Mailing L

Recommend a tool for editing data

Dear List, Can someone recommend a web based tool for mass-editing tables in excel like fashion? Preferably view-based (can you even edit a view in mysql, as long as it's 'simple' (sorry, coming from Ora world) We have an application which only allows to edit 'rows' one by one... yes, i know the

nested function does not work

Is there are a reason why this wouldn't work? select upper( monarch.group_decode(lower(hg.alias)) ) from nagios.nagios_hostgroups hg; ++ | upper( monarch.group_decode(lower(hg.alias)) ) | ++ | database

Replication for reporting

Is it true that a single mysql server can be a slave to only one master? So if you need to replicate a dozen databases into a single reporting server, you need to have a chain of a dozen servers in between? Someone shared that to me, but it didn't make much sense. In oracle we have the followin

JDBC error: sql: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'gname' at row 0

Have identical versions installed, in one query works fine, in another, pukes with the error in the subject. When I query by hand (via mysql client) works fine. Not even that much data to complain about! mysql> select -> group_concat(concat('',hosts.name,'') order by hosts.name asc separa

group_concat display limit

All, We are using group_concat but there seems to be some sort of display limit. Is there are a way to unset or increase it? Thanks, Andrey

one-liner perfectionist function question

DROP FUNCTION IF EXISTS secs_to_hrs; DELIMITER | create function secs_to_hrs (secs varchar(10)) returns varchar(13) DETERMINISTIC BEGIN return CONCAT_WS(' h ',lpad(secs/60 div 60,3,' ') , CONCAT(lpad(round(secs/60 mod 60),2,' '),' mins') ); END; | DELIMITER ; What if I wanted to not display mi

RE: query question

I knew I’ve seen this error before ☺ Thanks a lot. -andrey From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 30, 2007 1:55 AM To: Andrey Dmitriev Cc: mysql@lists.mysql.com Subject: Re: query question >Thanks.. It doesn't seem

RE: query question

o:[EMAIL PROTECTED] Sent: Monday, October 29, 2007 4:00 PM To: Andrey Dmitriev Cc: mysql@lists.mysql.com Subject: Re: query question Hi, Andrey Dmitriev wrote: > This is kind of achievable in Oracle in either sqlplus mode, or with the > use of analytical functions. Or in the worst case by w

query question

This is kind of achievable in Oracle in either sqlplus mode, or with the use of analytical functions. Or in the worst case by writing a function. But basically I have a few tables Services, Hosts, service_names And I can have a query something like select service_names.name as 'Service', host

View pocedures/backup procedures

How do I view and backup my procedural code? In oracle it would be something like Select text from user_source where name='MY_PROCEDURE' order by line; I did mysqldump, and didn't see any of the functions or procedures created. In Oracle they'd be backed up either via RMAN or EXP. Thanks, Andr

date function question

Can someone advise the best way to determine a) the # of days since the first of the month from last month (e.g. from 5/1/07) b) the # of days since the end of last month (e.g. from 5/31/07) Is there are a good way to determine the # of workdays in a month. Thanks, Andrey -- MySQL General Mai

RE: how to tell if something hasn't happened yet

It depends on what's in the datetime column. http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html Something like this: select store.storeid, store.stName from store left outer join transaction on(store.storeid = transaction.storeid) where transaction.created IS NULL and store.ac

RE: creating a function in mysql

: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Friday, April 13, 2007 5:37 PM To: Andrey Dmitriev Cc: [EMAIL PROTECTED] Subject: Re: creating a function in mysql >I've determined that I don't seem to >be able to create functions at all. Declare a delimiter, and lose the quote around

RE: Mysql Hogging all system resources

Perhaps a better solution is to determine why mysql is 'hogging' resources in the first place. There is a tuning section in MySQL manual. -a -Original Message- From: Chuck Swiger [mailto:[EMAIL PROTECTED] Sent: Friday, April 13, 2007 5:46 PM To: Don O'Neil Cc: [EMAIL PROTECTED]; [EMAIL

creating a function in mysql

All, I've been trying to create a function that will generate URLs so that I wouldn't have to wrote ugly SQL all the time. After no success, I've determined that I don't seem to be able to create functions at all. I am relatively new to mysql development (or management), so any advice is apprec