Category Archives: SQL

select from multiple tables and sum vs join and sum

I am doing internship in an advertising company, i already implemented a tool to gather all the necessary data form facebook & import them on a database.

Now i am trying to manipulate that data, first by making some test cases & getting some results. The tables grow by 35k rows per day so after a month of using the tool i noticed that the query i use to obtain the sum of certain adcreatives clicks is starting to slow down.

i am asking if the query i use can speed up if i use it with a join & how.

here is the query i have for the sum of clicks per adcreative (with adgroup_id,campaign_id as connect to the other tables):

currently the query takes 3 secs to complete on a dedicated server, i guess after 6 months it will be at more than 60 secs or so as the tables grow.

edit: here is the explain of the query ( although this is the first time i actually use it & not so sure what it means)

That looks like a full table scan, & with that rapid growth small performance changes won’t make a huge difference on the long run. You need a different approach.

I would calculate aggregates for the previous months (days, etc) with a cron job, & when you need stats then merge that with the fresh results (using the query you already wrote). That why you only have to scan the fresh record, which means the queries is going to be fast.

Alternatively, you can keep up-to-date counters in the adgroups table, & update them on each click. Not sure if mysql is the right tool for this, I can recommend MongoDB, it can do very swift atomic increments on fields, & though it doesn’t donate you as strict guarantees (ACID) as a relational database, in this case it’s not a problem, ad clicks aren’t mission critical data, nobody is going to complain, if you lose < 0.01% percent of click information.

SQL and number combination search

I have table with 10 number fields (let’s say F1, F2... F10).

Now I have 4 numbers (N1, N2, N3, N4).

I have to find if those 4 numbers appear anywhere in the above table. For example, if F2=N4 & F1=N2 & Fx=N3 & Fy=N1 (any order, any combination).

I was wondering is there quick way to do it via SQL or is it only way to write looooong combination of selects (I am not sure I will be able even complete that in this life time).

Here is SQLFiddel Demo

Below is the sample Query

how to add multiple columns to a group by case?

I have the following group by clause on my query which uses a case statement yet I want to add another column to the group by too:

how do I add another column to this group by?

self join on one table

I have a table STARTSTOP

Action 0 -> START ACTION
Action 1 -> STOP ACTION
DATA is a timestamp of action

I would like to run a select statement that would return records something like:

OR summary for all actions duration in one row.

Is it feasible with a single database query? (without creating additional tables)

DATEDIFF function

SQLFiddle Example for MSSQL yet it has to work under Firebird too

SQL Server date formatting from string

We’ve recently migrated our database to a different server & since this I think the date format querying has changed somehow.

Previously we could use the following..

However now we have to use..

Can someone tell me what I need to alter to obtain back to the previous version?

Try this one –

Query:

Read current settings:

Output:

Use Aggregate Function in UNION ALL result set

How can I use aggregate Functions in UNION ALL Resultset

FOR EXAMPLE

Result Set Would Be

When I tried to obtain MAX(A) it returns 3. I want 6.

When I tried to obtain MAX(B) it returns 4. I want 7.

Other than Max(), Can I obtain another aggregate function which user defined?

For example:

(SELECT TOP 1 A WHERE B=5)

Try this way:


If the column A is varchar (You said that in the comment below) try this way:

With TOP 1