Changes between Initial Version and Version 1 of Ticket #1658


Ignore:
Timestamp:
May 18, 2012, 9:02:42 AM (7 years ago)
Author:
tobixen
Comment:

Legend:

Unmodified
Added
Removed
Modified
  • Ticket #1658 – Description

    initial v1  
    11This query is slow:
    22
    3 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 */;
     3{{{
     4SELECT 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 */;
     5}}}
    46
    57I 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 probably a noop and can be dropped:
    68
     9{{{
    710mysql> 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 */;
    811+-----+
     
    2023+-----+
    21241 row in set (0.01 sec)
    22 
    23 
    24 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%';
     25}}}