I have a situation in my application. For each x-axis point in my chart, I am plotting 5 y-axis values. To calculate each of these 5 values, I need to make 4 different queries. Ie, for each x-axis point I need to fire 20 sql queries. Now, I need to plot 40 such points in the my chart. Its resulting in a pathetic performance where it ta...
The table contains about 40,000,000 records having: CREATE TABLE `event` ( `id` bigint(20) unsigned NOT NULL auto_increment, `some_other_id_not_fk` int(10) unsigned default NOT NULL, `event_time` datetime NOT NULL, `radius` float default NULL, `how_heavy` smallint(6) default NULL, PRIMARY KEY (`id`), KEY `event_some_other...
I'm trying to figure out why a SQL Server stored procedure is executing slowly, so I've put in some crude timers, like this: Create Procedure DoStuff As Begin Declare @Stopwatch datetime Set @Stopwatch=GetDate() Print char(13) + 'Task A' /* Perform Task A */ Print DateDiff(ms, @Stopwatch, GetDate()); Set @Stopwatc...
I just set up FirePHP in Zend and I'm noticing a huge number of DESCRIBE queries. Some pages have 50 or more identical queries all on the same table. e.g. 0.00198 connect NULL 0.00449 DESCRIBE `nodes` NULL 0.00041 SELECT `nodes`.* FROM `nodes` WHERE (((`nodes`.`id` = 111))) NULL 0.0037 DESCRIBE `nodes` NULL 0.00155 SE...
Good day, I have a question I'm struggling with a lot, hope somebody already found a clever solution to this (I use MySQL). I have table like this: Table `log` ---------- id inserted message user_id My goal is to select last inserted record for user and make this fast. Log table is huge (around 900k records), so my first approach was...
There are some constraints to this question; I don't have the ability to fundamentally alter any database structure. The challenge here is that I have rows in a database that contain information that really should be on its own row. A somewhat simplified example of the column structure: [PersonID] [FirstName] [LastName] [FirstNameGuest...
hi, I have table where I need to select MAX(SUM(total)) of a periods total. SELECT SUM(P.amount) as total FROM bank P WHERE P.ReceivedDate >= '2008-07-28' AND P.ReceivedDate <= '2008-08-31'; SELECT SUM(P.amount) as total FROM bank P WHERE P.ReceivedDate >= '2008-09-01' AND P.ReceivedDate <= '2008-09-28'; SELECT SUM(P.amount) as total...
I could probably google this, but it seemed quirky enough that it might be worth having logged as an answer on SA. So in development land, if you want to swap interchance the values of two variables, you need a third temp variable. e.g. string x = "ABC"; string y = "DEF"; string temp; temp = x; x = y; y = temp; However in a SQL Up...
This may be a silly question, but it may shed some light on how joins work internally. Let's say I have a large table L and a small table S (100K rows vs. 100 rows). Would there be any difference in terms of speed between the following two options?: OPTION 1: OPTION 2: --------- --------- SELECT * ...
I realise different solutions will have different variations of what "Working Days" means but in my case I mean Monday to Friday inclusive. Basically I have Created a function to do the calculation for me and my current solution works. My concern (and reason for asking this question) is that I am worried that this is a bad way of achiev...
Can someone tell me what is the difference between these two: ALTER TABLE x1 ADD INDEX(a); ALTER TABLE x1 ADD INDEX(b); AND ALTER TABLE x1 ADD INDEX(a,b); I know this goes down to the very basics but sometimes I find the former seems to be a little faster than the latter. Is it just my feeling or is there some actual reason for it?...
I'm trying to optimize a complex SQL query and getting wildly different results when I make seemingly inconsequential changes. For example, this takes 336 ms to run: Declare @InstanceID int set @InstanceID=1; With myResults as ( Select Row = Row_Number() Over (Order by sv.LastFirst), ContactID From DirectoryC...
Each Poem as two Votes, one as poem_id, other_poem_id, wins & the second record which is the inverse of the first. Maybe there is a better way, but I'm trying to find the poems with the highest win percent over a period of time. It's confusing because of the double records for each comparison. Should I add another table, Results, whic...
On my WordPress site, when a user pages far back in the list of posts, the queries end up taking a couple seconds. I'd like to bring this down. Here's the query that's being executed: SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_s...
I have a query of the form SELECT uid1,uid2 FROM friend WHERE uid1 IN (SELECT uid2 FROM friend WHERE uid1='.$user_id.') and uid2 IN (SELECT uid2 FROM friend WHERE uid1='.$user_id.') The problem now is that the nested query SELECT uid2 FROM friend WHERE uid1='.$user_id.' returns a very large number of ids(approx. 5000). The ...
Possible Duplicate: Alerternative to MySQL Order By Rand() What is an efficient way to query for random result sets in the following scenarios: Select a single random row from many. Select (at least) n random rows from many. Select all rows in a random order. In particular interested in MySQL, but might be a reason to try ...
Hi all, I want to get 1000 records from a table randomly, so I use: SELECT top 1000 mycol1 , mycol2 , ROW_NUMBER() OVER (ORDER BY NEWID()) rn FROM mytable However, I don't want to see rn in my resultset, so I do: SELECT mycol1 , mycol2 FROM ( SELECT top 1000 mycol1 , mycol2 , ...
Why does explain say that it uses type ALL for contests table though contest table has chid as primary key? mysql> explain SELECT contests.chid, contests.diff_level from contests, contest_users where contests.chid=contest_users.chid; +----+-------------+-----------------+------+---------------------+---------------------+-...
I would like to retrieve an ordered query result, but I need to have some specific row(s) to be in front of the list. Something like here on Stack Overflow, in the list of answers the right answer is always the first one. Assumed I need to have the rows with IDs 1,2,3 to be the head, the rest sorted by a date field, is it possible to do...
Greetings friends, In my MySQL database, I have 'MAB' table which contains information about Gene Ids (GI_ID),and some other gene related information. 'MAB_CAN' table can contains the Gene Ids (GI_ID) only relevant to Cancer. I use following SQL query to get cancer related information from MAB table : SELECT * FROM MAB WHERE `GI_ID` ...