Extremely Inefficient SQL structure

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Setup Phurl as per instructions
2. Get a TON of shortened URLs
3. Wait

What version of the product are you using? On what operating system?

Using Phurl 2 beta2 on a Linux server.

Please provide any additional information below.

I'm using Phurl on http://b2l.me and everything was working splendidly
until I started using it as the primary URL shortener for my SexyBookmarks
WordPress plugin... That created a HUGE flux in the amount of URLs being
shortened. I now have nearly 2,000,000 shortened URLs, and recently
received an email from my hosting company saying that they nearly disabled
the database due to how inefficient it was running.

Here's a snippet of the email they sent me explaining what they did to fix it:

I'm writing you about your database "b2l_shrinker".  I almost had to
disable this database due to it causing high mysql server load, using
100% CPU on 8 cores of the server, and being very inefficient.  I was
able to add the following index to drop the rows examined from 1.85
million records examined to 4.  Please make sure to properly index your
queries to avoid having databases disabled.  If you have further
questions let us know.

mysql> desc SELECT id, code, alias FROM phurl_urls WHERE url LIKE
'http://canadianfreestuff.com/best-buy-canada-coupons';
+----+-------------+------------+------+---------------+------+---------+------+
---------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len |
ref  | rows    | Extra       |
+----+-------------+------------+------+---------------+------+---------+------+
---------+-------------+
|  1 | SIMPLE      | phurl_urls | ALL  | NULL          | NULL | NULL    |
NULL | 1853739 | Using where | 
+----+-------------+------------+------+---------------+------+---------+------+
---------+-------------+
1 row in set (0.00 sec)

mysql> 
mysql> alter table phurl_urls add index url (`url` (40));
Query OK, 1853741 rows affected (1 min 50.45 sec)
Records: 1853741  Duplicates: 0  Warnings: 0

mysql> desc SELECT id, code, alias FROM phurl_urls WHERE url LIKE
'http://canadianfreestuff.com/best-buy-canada-coupons';
+----+-------------+------------+-------+---------------+------+---------+------
+------+-------------+
| id | select_type | table  | type  | possible_keys | key  | key_len
| ref  | rows | Extra       |
+----+-------------+------------+-------+---------------+------+---------+------
+------+-------------+
|  1 | SIMPLE      | phurl_urls | range | url       | url  | 122    
| NULL |    4 | Using where | 
+----+-------------+------------+-------+---------------+------+---------+------
+------+-------------+
1 row in set (0.01 sec)

Thanks!
Justin K

Original issue reported on code.google.com by josh.jon...@gmail.com on 8 Dec 2009 at 8:27

[GoogleCodeExporter]

Original comment by hdo...@gmail.com on 19 Mar 2010 at 4:26

• Changed state: Accepted
• Added labels: Priority-High
• Removed labels: Priority-Medium

[GoogleCodeExporter]

Original comment by hcblahb...@gmail.com on 3 Jul 2010 at 8:43

• Changed state: WontFix