Tag Info

New answers tagged

1

First step: This is SQL. You don't need to make separated queries for every status type. SELECT sh_table.`status`, SUM( CASE WHEN ( date(temp.max_created_at) BETWEEN DATE_SUB(DATE(NOW()), INTERVAL 1 DAY) AND DATE_SUB(DATE(NOW()), INTERVAL 0 DAY)) THEN 1 ELSE 0 END ) AS INTERVAL1, SUM( CASE WHEN ( date(temp.max_created_at) BETWEEN ...


1

Create a set of staging tables in the target database. Write rows to these as they are generated, which seems to be one or two at a time. This can be inside a transaction. Once the whole batch (200 rows?) is in these staging tables use a stored procedure in the target database to move them en masse from the staging tables to the real ones.


1

I've seen similar problems addressed by decoupling the app from the central server: Remote sites install a local SQL Server Express along with the app Apps talk to the local Express instance. Low latency, good availability. SQL Server Express uses Service Broker to deliver the updates to the central server Service Broker handles the network availability, ...


1

I don't have your dataset, so can't test if this is better, but it feels better. After building the chains, we reverse them to find the 'childest' item in each chain, then join back to the original chain. -- The same as your first CTE ;WITH RelationshipChain AS ( SELECT ID, ParentID, ChildID, 0 AS Seq, ID AS RootID FROM Relationships WHERE ParentID = '' ...


0

About CMS versioning; for drupal it makes a special table for every field of the entity that stores the old value ; such a concept alllows you a fine manipulation of your data but i think it's expensive, my own solution is to convert my object to xml format and store it as string with the other fields( changetime, id...)


0

Try this: with me as ( select * from users where id = 2 ) select u.id, u.popularity from users u, me where u.gender = (select gender from me) order by u.latest_location::geometry <-> (select latest_location from me)::geometry ASC limit 50; When I look at the fast plan here's what jumps out at me (bolded): Limit ...


2

Your plans show that the only option for looking up a scalar id in the table is a scan. This clearly indicates a missing index. Your code is incorrect under concurrency. Query plans have to work for any parameter value. To give an analogy, consider this example in a non-SQL language: int x; void f(int i) { if (i != 1) x++; } You are asking the ...


0

In this particular case, I think the INFORMATION_SCHEMA is a red herring. From my own tests of SHOW COLUMNS performance, the innodb_stats_on_metadata variable doesn't seem to make any difference on either MyISAM or InnoDB tables. However, from the MySQL 5.0 manual... Some conditions prevent the use of an in-memory temporary table, in which case the ...


2

Simplified query to make it readable: SELECT * FROM "Follows" f JOIN "Users" u ON f."followeeId" = u."id" WHERE f."followerId" = 169368 ORDER BY f."createdAt" DESC LIMIT 1000; Your index follows_followinglist_followerid_createdat_idx looks good for the job. In Postgres 9.2+ it might get a bit faster if you append "followeeId" to the index - if ...



Top 50 recent answers are included