How to monitor MySQL queries on Windows
Is there any easy way to track/monitor all the SQL of the queries, executed by an MySQL server (on a Windows machine)? Update: I am asking about the free tool. ...
sql
staircase behavior with pivoted data
Hail to the fellow programmers and query writers, I have this beautiful query SELECT ID, [1] AS coL1, [15] AS coL2, [2] AS coL3, [16] AS coL4, [12] AS coL5 FROM MY_TABLE PIVOT (sum(INT_VALUE) FOR FUND_CODE IN ([1],[2],[15],[16],[12])) AS p --GROUP BY ID, [1] , [15] , [2] , [16] , [12] ORDER BY ID ASC That returns me data like this:...
PHP and SQL Social Networking - User Online
Hi! I want to set up online detection on my website. I have a row in my user table where the last login datetime is stored. Every time a user visits the site, his login date updates and user online row sets to 1 (1 - online, 0 - offline). How to change the online row to 0 (offline) if the last login was 10 or more minutes ago? The aim ...
SQL Joins Vs SQL Subqueries (Performance)?
Hi all, I wish to know if I have a join query something like this - Select E.Id,E.Name from Employee E join Dept D on E.DeptId=D.Id and a subquery something like this - Select E.Id,E.Name from Employee Where DeptId in (Select Id from Dept) When I consider performance which of the two queries would be faster and why ? Also is the...
Group and concatenate many rows to one
I want to "concatenate" all the "Text"-rows into one single row and get one row as a result. Is this even possible? I use MSSQL Server 2005. ...
How to convert all tables from MyISAM into InnoDB?
I know I can issue an alter table individually to change the table storage from MyISAM to InooDB. I am wondering if there is a way to quickly change all of them to InnoDB? ...
Speed problems with 3 like statements in 1 query (oracle)
This is a snippet of my query: and ( ADDR_FULLTEXT like upper(:streets1) ) Whenever i execute the query this is a part of, it executes in about 0.05 seconds. This is perfect! But, when I do the following: and ( ADDR_FULLTEXT like upper(:streets1) AND ADDR_FULLTEXT like upper(:streets2) ) it results in a ...
Django: Display many-to-many fields in the change list
Django doesn't support displaying of related objects from a many-to-many relation in the changelist for a good reason. It would result in a lot of database hits. But sometimes it is inevitable and necessary to e.g. display an object's categories, which have a many-to-many relation to the object, in the changelist. Given that case, does ...
oracle sql group count
SELECT a,b,count(*) FROM t GROUP BY rollup(a,b) result: a1, b1, 10 a1, b2, 90 a1, , 100 i need: a1, b1, 10, 100 a1, b2, 90, 100 how? ...
Script to find and replace function
Hi All, We have changed some base functions we use in an application and I am trying to figure out what the best way would be for me to find and replace the existing function with the new one? The input / outputs don't change, just the schema and some of the internal logic. Is there a good approach to finding the objects (views, proc...
Finding Foriegn Keys From Unknown Database SQL and Java
I have a java program that queries through any type of database with any amount of tables. The user can put in a string and it will return the table and rows containing that string. The problem is that some of these tables have foreign keys. How could I recursively go through all of tables finding foreign keys and rows without any knowle...
Multi Query PHP Question
I wanted to execute multiple queries in one. Is there a way to condense a bunch of queries into one big string and then execute it? Thanks in advance. ...
How can I use aggregate function SUM on an alias column?
Invoice ID, PO Number and dueDate are shown in duplicates. TotalPrice is an alias (It should be Unit Price, total price is a mistake, so assume it Unit Price not total price) TotalShippingPrice shows the shipping price that was associated with the InvoiceID/PONumber, for every invoiceID/PONumber there will be single shipping price. Sa...
SQL to select events that happened at pretty much the same time
I have a database table of events that happened: (timestamp, other data...) I want to group these by things that happened at 'pretty much the same time'. That is, when ordered by timestamp, such that all events in each group are within X seconds (e.g., X=3) of some other event in that group, and more than X seconds from all events in...
Using sqlite GROUP BY in a 'conversation-like' thread
We have a VERY old Sqlite database that can NOT be resigned/changed at this late stage. It has fields like User1, User2, and ConversationDate. How would I use "GROUP BY" so that instead of 2 records output... I would only get 1? Bad output: Bob contacted Fred on 01-Jan-2007. Fred replied to Bob on 11-Jan-2007. Desired outpu...
SELECT Statement - NOLOCK with SET TRANSACTION ISOLATION LEVEL READ COMMITTED
My understanding is that when NOLOCK is used in SELECT statement, it could read uncommitted / dirty rows as well. But I wanted to take advantage of NOLOCK hint on table so that my SELECT query will run fast. Now, does NOLOCK on table but along with "SET TRANSACTION ISOLATION LEVEL READ COMMITTED" give me NOLOCK advantage and faster SE...
SQL clause: newbie question
I have this table: culture street ------------------------ es Calle Roma 15 eu Via Roma 15 es Calle Paris 20 eu Via Paris 20 Is possible to create an SQL query that retrieve first the rows that the value "es" (in the field culture of course) and the rows that the value "es"? ...
(MySQL) Greatest Number By group Inner join , performance problems.
2 tables are still very small compared on how they will be in the future. Already on doing tests on some regular queries , performance problems arise. Could someone enlighten me on a better table structure or query ? ==== Table 1 : tv_show ( currently 117 rows) Table 2 : tv_episodes ( currently 43,000 rows ). tv_show contains an ...
Need Help in Calculating Working Hours
The way i designed my working sheet doesn't help me to calculate working hours easily. The output in snapshot has been gathered from multiple tables. Don't worry regarding setDate and timeEntered formatting. SetDate represents working day. tsTypeTitle represents type of shift, is it lunch time, etc. timeEntered represents the actual...
Upgrade/update SQL database to new schema version while keeping as much of the old data as possible
Im not used to work with SQL and I hardly know any DDL-scripting at all. Im using ADO.NET Entity Framework 4 in Visual Studio 2010 with EDMX-designer and a Self-Tracking Entity Generator. It does the hard work for me and I get a DDL-script to be used to create the database when it doesnt exist. So far so good... Then I make a change in m...