[SOLVED] Confusing Query..Doesn't work and has to be better way.

[drbigfresh]

So I was struggling with this one all last night and I could never get it to work, and it is a performance sucker even not working. The query should return a bunch of different numbers, depending on the date range, but for some reason it only returns the first count(*) for all the selects. I tried the concat to see if i could fake it out, but no luck.... Also what would people recommend as a better way of doing this? The table is +200,000 rows and growing, and the description field is a varchar(125) - indexed, and full text indexed.

 

select count(*) as popularity, (select count(*) from feed WHERE description LIKE concat('%','coke', '%') and created > DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 2 day)) as LastDay, (select count(*) from feed WHERE description LIKE concat('%','coke', '%') and created > DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 1 day)) as Today, (select count(*) from feed WHERE description LIKE concat('%','coke', '%') and created > DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 7 day)) as LastWeek, (select count(*) from feed WHERE description LIKE concat('%','coke', '%') and created > DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 1 month)) as LastMonth from feed WHERE description LIKE '%coke%' order by popularity desc 

[Wildbug]

This is a little different from what you might be trying to accomplish, but if you're trying to get all the values by the description you can try something like this.

 

SELECT
description,
COUNT(*) AS popularity,
SUM(created BETWEEN NOW() - INTERVAL 1 DAY AND NOW()) AS Today,
SUM(created BETWEEN NOW() - INTERVAL 2 DAY AND NOW() - INTERVAL 1 DAY) AS Yesterday,
SUM(created BETWEEN NOW() - INTERVAL 7 DAY AND NOW()) AS "This Week",
SUM(created BETWEEN NOW() - INTERVAL 1 MONTH AND NOW()) AS "This Month"
FROM feed
GROUP BY description;

 

It does without subselects and LIKE conditions and should be faster than with them.  Otherwise, adjust the intervals as you see fit.

[drbigfresh]

Hmmm... doesn't really work quite right. The original query only returned one row, which was all the various counts, and none of the data from the table. So if I ran it it would come back with:

 

'5,5,5,5,5'

 

or something like that...The real problem is that all the numbers were the same, even though I know they should be different.

[Wildbug]

It works for me as follows:

 

mysql> SELECT *,created BETWEEN NOW()-INTERVAL 1 MONTH AND NOW() AS "Last Month",created BETWEEN NOW()-INTERVAL 1 DAY AND NOW() AS "Today" FROM feed;
+---------------------+-------+------------+-------+
| created             | d     | Last Month | Today |
+---------------------+-------+------------+-------+
| 2007-04-30 20:52:25 | coke  |          1 |     1 |
| 2007-04-27 12:00:00 | coke  |          1 |     0 |
| 2007-01-01 12:00:00 | coke  |          0 |     0 |
| 2006-05-01 00:00:00 | coke  |          0 |     0 |
| 2007-01-02 13:00:13 | pepsi |          0 |     0 |
| 2007-03-31 09:00:00 | pepsi |          1 |     0 |
| 2007-03-30 01:00:00 | coke  |          0 |     0 |
+---------------------+-------+------------+-------+
7 rows in set (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2007-04-30 20:56:13 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT d,COUNT(*),SUM(created BETWEEN NOW()-INTERVAL 1 MONTH AND NOW()) AS "Past Month",SUM(created BETWEEN NOW()-INTERVAL 1 DAY AND NOW()) AS "Today" FROM feed GROUP BY d;
+-------+----------+------------+-------+
| d     | COUNT(*) | Past Month | Today |
+-------+----------+------------+-------+
| coke  |        5 |          2 |     1 |
| pepsi |        2 |          1 |     0 |
+-------+----------+------------+-------+
2 rows in set (0.00 sec)

 

Anyway, the point is that using a true/false condition in the SELECT clause ("created BETWEEN min AND max") returns 0 if false and 1 if true, and you can use the SUM() function to count all of those cases in a GROUP condition.  By doing so you avoid all those subselects and searching the description field multiple times.  This should be a better method than your original attempt.  Maybe you need to adjust your ranges to get what you expect; just remember that the smaller number/earlier date-time needs to come first.

[drbigfresh]

The problem seems to be that as soon as I throw in the requisite [from feed WHERE description LIKE '%coke%'] which gets added on to each nested select and the main select, then it all seems to break... Maybe it's one of those things where I just need to do multiple selects... Thanks for your help though.

[Wildbug]

What "nested select?"  What's your query?

[drbigfresh]

This is the exact query:

 

select count(*) as popularity, (select count(*) from feed WHERE description LIKE '%coke%' and created > DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 2 day)) as LastDay, (select count(*) from feed WHERE description LIKE  '%coke%'  and created > DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 1 day)) as Today, (select count(*) from feed WHERE description LIKE  '%coke%'  and created > DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 7 day)) as LastWeek, (select count(*) from feed WHERE description LIKE  '%coke%'  and created > DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 1 month)) as LastMonth from feed WHERE description LIKE '%coke%' order by popularity desc

[Wildbug]

Oh.  You're still using the original one.  I think the one I posted will be more efficient.  If you need to use a pattern, just replace the GROUP BY with WHERE description LIKE '%coke%' or whatever and take the description out of the column list to avoid the "mixing GROUP columns" error.

 

mysql> SELECT COUNT(*),SUM(created BETWEEN NOW()-INTERVAL 1 MONTH AND NOW()) AS "Past Month",SUM(created BETWEEN NOW()-INTERVAL 1 DAY AND NOW()) AS "Today" FROM feed WHERE d LIKE '%coke%';
+----------+------------+-------+
| COUNT(*) | Past Month | Today |
+----------+------------+-------+
|        5 |          2 |     1 |
+----------+------------+-------+

 

Why doesn't that work?  If it doesn't, can I see some of the data you have and the results that you expect?

[drbigfresh]

That totally worked. You are a life saver. About a gazillion times quicker as well. Thanks!