substring_index string function

Substring_index function returns us a substring from the main string from a landmark which is given by delimiter. From the delimiter to left or right ( towards staring or ending ) of the string can be given by count. If count is positive then everything towards left from final delimiter ( from left ) is returned. If the count is negative then everything towards right from final delimiter is returned.

Here is the syntax

substring_index(string,"delimiter”,count )

We will apply this to our newsletter subscriber table where we stored email address of our subscribers. Here we will apply substring_index to separate domain part and userid part and maintain a list. Here is a sample of email address.

userid@domainname.com

Here is the query to get the

select email,substring_index(email,"@",-1) from newsletter_table

This will give output as

userid@aol.com aol.com
onemore@gmail.com gmail.com

Like this the full list can be displayed. We can use count and group by command to generate a query to which can tell us number of subscribers in each domain. Say how many have yahoo account, how many have gmail account etc….

SELECT count(substring_index(email,"@",-1) ) as no, email FROM `newsletter_table` group by substring_index(email,"@",-1) order by no desc

Fixing slow Apache on localhost under Windows 7

A couple days ago, I documented my recent experience with my wireless network and how I got hacked. I briefly mentioned that I'm installing Windows 7 this time around.

Yesterday, I ran into an issue with 32-bit Apache 2.2.15 running on Windows 7 Ultimate 64-bit. This issue appears to only affect 64-bit Windows 7 and 64-bit Windows Vista web developers attempting to run 32-bit Apache on the system.

NOTE: I didn't test 64-bit Apache because that is experimental and 64-bit PHP is even more experimental. However, I doubt the results would be too different.

The issue is slow response times (anywhere from 1 to 3 seconds per request) when connecting to 'http://localhost/'. Connecting to 'http://127.0.0.1/' and 'http://[NetBIOScomputernamegoeshere]/' have fast response times. I've seen various fixes around that seem to boil down to these three:

- Disable the Windows Firewall.
- Disable IPv6 support.
- Edit the 'hosts' file.

It looks like there is a bug in IPv6 DNS mapping for localhost under Windows that causes the slowdown when used in conjunction with the Windows Firewall. If Microsoft employees are reading this - THIS IS A BUG...FIX IT! Apache doesn't seem to be the issue but it could be that 'localhost' via the firewall first tries an IPv6 address first, fails after a second or two, and then falls back to IPv4 at which point a connection to 32-bit Apache is established. Apache isn't the issue here - the Windows Firewall combined with IPv6 is. Anything 'localhost' only listening on IPv4 will have slow responses.

What worked for me was to use the IPv4 version of 'localhost' in my hosts file.  The 'hosts' file is usually located in the 'C:\Windows\System32\drivers\etc' directory on most systems. Editing the 'hosts' file was a pain in the neck. You can't simply right click or double-click to open the file (BUG! ALSO NEEDS TO BE FIXED!). You first have to fire up Notepad or your favorite editor using "Run As Administrator" (right-click) and then hunt for the file using the "File -> Open" dialog. Windows prevents editing that file normally (a good thing) but then get in the way of actually editing it (a bad thing).

Anyway, under Windows 7, the following two lines are commented out:

# 127.0.0.1 localhost
# ::1 localhost

Uncomment the first line but leave the second commented:

127.0.0.1 localhost
# ::1 localhost

Save the file. Try loading pages in Apache again. Should be back to normal speed.

Why this works?

The 'hosts' file supersedes all DNS mappings. So this "fix" merely forces the IPv4 mapping to occur when 'localhost' is requested. Uncomment the second line and Apache will slow down again.

Disabling the Windows Firewall supposedly fixes the issue but I'm pretty sure no one wants to run without a firewall. Disabling IPv6 also apparently fixes the issue but IPv6 is supposedly the future - IPv4 is what the OS falls back on without IPv6. The 'hosts' file fix is really the most desirable until Microsoft can figure out why the Windows Firewall is broken.

Hopefully this helps someone else and helps provide insight into the actual problem.

Side note: If you want to simplify future installations of Apache and you also use MySQL and PHP, you might want to try my portable version of Apache + MySQL + PHP for Windows.