0
votes
1answer
27 views

column value is not increment after 10

I tried below 2 codes.... showing same error 1.SELECT columnname FROM tablename ORDER BY columnname DESC LIMIT 1 SELECT MAX(columnname ) AS max FROM tablename I am trying to increment value of ...
0
votes
0answers
38 views

Index on fields with a function

I have a log table. It logs visitor data (time of visit, browser, window size ...). I want to create reports. I use a query like this (MySQL): SELECT COUNT(*) as raw_views ... FROM logs WHERE ...
-1
votes
4answers
86 views

Index on 'GROUP BY' with functions

I have a log table. It logs visitor data (time of visit, browser, window size ...). I want to create reports. I use a query like this (MySQL): SELECT COUNT(*) as raw_views ... FROM logs WHERE ...
0
votes
0answers
28 views

Function performance issue

This one is puzzling me since yesterday and maybe someone could shed some light on this. We've got this table that records GPS data. We're currently doing some overloading tests to see how the ...
0
votes
1answer
24 views

Mysql functions for web apps

I have few questions about using functions in a MySQL database for plugins and extensions of web apps like Magento or Wordpress. Mostly this is for a few extensions I want to do for Magento. The ...
0
votes
0answers
22 views

Convert this Ruby function into MySQL Function?

From my answer at SO, I have the following function: def rgb_to_hue(r,g,b) # normalize r, g and b r_ = r / 255.0 g_ = g / 255.0 b_ = b / 255.0 c_min = [r_,g_,b_].min c_max = ...
1
vote
2answers
111 views

Latitude and Longtitude Distance Calculation [closed]

I'm using MySQL and I am trying to calculate the distance in meters between two different longitude, latitude coordinates. I wrote a stored function in MySQL but it calculates just 1 for each row in ...
1
vote
2answers
370 views

Datediff() in MySQL

While calculating difference between 2 dates as number days, by using MySQL Datediff() function, I am facing some issue, the SQL code is given below. select datediff((select curdate()), (select ...
0
votes
1answer
263 views

Use string functions and arithmetic calculations in SQL query or by programming language?

I need heavy arithmetic and string functions for processing the data captured from mysql database. Which is preferred: mysql functions or programming language (e.g., PHP)? $result = ...
5
votes
2answers
310 views

Optimize queries from MySQL SP/Triggers/Functions

I have to investigate a MySQL Production server,While investigating I found a DELETE query in "SHOW PROCESSLIST" which was taking more than 400 second in preparing state, I tried to find the query in ...
1
vote
0answers
114 views

Cumulative Function in MySQL

i have a problem creating cumulative function in mysql database. let say i have a stored procedure which getting data and counting quantity/sum_quantity example A table consist of consumer goods ...
1
vote
1answer
3k views

Dynamic SQL is not allowed error for creating function in MySQL

According to this question and answer of Angelo I create a MySQL function as below: DELIMITER $$ CREATE FUNCTION hierarchy_connect_by_parent_eq_prior_id(value INT, reqdTable VARCHAR(50)) RETURNS INT ...
3
votes
2answers
584 views

How to concatenate a column after x rows?

I have a table with columns object varchar(255), frequency tinyint(3) unsigned, I want to concatenate the objects if the sum of frequencies reach a value. Currently I am doing this in PHP as .. ...
3
votes
1answer
515 views

A function to check if a column allows NULL

Is there a way to write an insert/update query that checks if a column allows NULLs: If it does set the column to NULL, and '' (empty string) otherwise? I would be something like: UPDATE mytable ...
2
votes
1answer
781 views

Is a query with UNIX_TIMESTAMP using indexes in mysql?

If I use .. WHERE UNIX_TIMESTAMP(`some_timestamp`)>NOW() in a SELECT, does this query still make use on an index, set on some_timestamp ?
1
vote
1answer
1k views

MySQL - Create a function from another function

I want to create a function same like my old functions. When I create a table, I can use `AS to other table as follows: CREATE TABLE dummy2 AS SELECT * FROM dummy1; Can I do the same thing with ...
1
vote
2answers
2k views

How do I create a user-defined aggregate function?

I need an aggregate function that MySQL doesn't provide. I would like it to be in MySQL's flavor of SQL (that is, not in C). How do I do this? What I'm stuck on is creating an aggregate function -- ...
0
votes
1answer
3k views

MySQL: Why is DATEDIFF and TIMEDIFF/60.0/60.0/24.0 results different?

I have a database with two DATETIME values, and then I generate some new values from them: tdiff = timediff(d1,d2)+0 day1 = truncate(tdiff/60.0/60.0/24.0,0) day2 = datediff(d1,d2)+0 All three of ...
2
votes
3answers
1k views

Finding five-minute interval prior to current time

I want to find the five minute interval just before the current time. So if the current time is 8:43 I want to return 8:35 and 8:40. Here is the query that I have written and it is working as ...
1
vote
0answers
821 views

stored procedure returns ERROR 1305 (42000): FUNCTION does not exist

I have a stored procedure and function as below CREATE PROCEDURE ProcName() begin .... DECLARE curs CURSOR FOR select A.PList, B.nid from persons A inner join articles B on A.aid=B.id; .... ...