Opened 6 years ago

Closed 4 years ago

#1658 closed improve feature (wontfix)

updatestats.php - change some SQL (trivial!)

Reported by: tobixen Owned by:
Priority: major Milestone: unassigned
Component: BW General Keywords: easy
Cc:

Description (last modified by tobixen)

This query is slow:

SELECT COUNT(distinct(members.id)) as cnt from members right join BW_ARCH.logs on  members.id=BW_ARCH.logs.IdMember and BW_ARCH.logs.type='Login' and BW_ARCH.logs.created between '2012-05-15 00:00:00' and '2012-05-16 00:00:00' and BW_ARCH.logs.Str like 'Successful login%'  /* /home/bwrox/www.bewelcome.org/htdocs/bw/updatestats.php */;

I created an index on BW_ARCH.logs(created), but it didn't help. Looking at the query again, I notice that the join to the members table is a noop and can be dropped. I ran the old query and the query without the join - they return the same, but it's very much faster without the join:

mysql> SELECT COUNT(distinct(members.id)) as cnt from members right join BW_ARCH.logs on  members.id=BW_ARCH.logs.IdMember and BW_ARCH.logs.type='Login' and BW_ARCH.logs.created between '2012-05-15 00:00:00' and '2012-05-16 00:00:00' and BW_ARCH.logs.Str like 'Successful login%'  /* /home/bwrox/www.bewelcome.org/htdocs/bw/updatestats.php */;
+-----+
| cnt |
+-----+
| 136 | 
+-----+
1 row in set (7.65 sec)

mysql> SELECT COUNT(distinct logs.IdMember) as cnt from BW_ARCH.logs where  BW_ARCH.logs.type='Login' and BW_ARCH.logs.created between '2012-05-15 00:00:00' and '2012-05-16 00:00:00' and BW_ARCH.logs.Str like 'Successful login%';
+-----+
| cnt |
+-----+
| 136 | 
+-----+
1 row in set (0.01 sec)

Change History (7)

comment:1 Changed 6 years ago by tobixen

  • Description modified (diff)

comment:2 Changed 5 years ago by TimLoal

  • Component changed from unknown to BW General
  • Type changed from unknown to improve feature

comment:3 Changed 5 years ago by tobixen

I think this should be prioritied because it's low-hanging fruit. Just to grep for that query and change it. I'm not sure if I have commit rights ... or I would have done it myself ;-)

comment:4 Changed 5 years ago by tobixen

  • Description modified (diff)
  • Summary changed from updatestats.php - change some SQL to updatestats.php - change some SQL (trivial!)

comment:5 Changed 5 years ago by jsfan

  • Milestone Future deleted

Milestone Future deleted

comment:6 Changed 5 years ago by planetcruiser

  • Keywords easy added
  • Milestone set to unassigned

implementation might be easy, but testing might not be.. well, let's see who wants to look into this

comment:7 Changed 4 years ago by shevek

  • Resolution set to wontfix
  • Status changed from new to closed

This doesn't need an update as only a very few people have the beta rights and it is a bw page.

Closing.

Note: See TracTickets for help on using tickets.