Rewrite lookup via MySQL table

agentzh agentzh at
Tue Aug 24 07:32:21 MSD 2010

On Tue, Aug 24, 2010 at 8:22 AM, btafoya <nginx-forum at> wrote:
> Any chance I can use a MySQL database table to do a lookup to return the
> url to rewrite to?
> Let me explain the situation first...
> Magento utilizes rewrite rules heavily but does the majority of the
> rewrite within the Front controller. The problem is that it is VERY
> in-efficient and a store with more than 10,000 products is severely
> delay. But, all information is referenced in a table 'core_url_rewrite'
> so my though it so do the lookup first in Nginx and hand off to Magento
> with the non-seo url.

Here's a sample configuration that has been tested on my laptop.

Consider you seo uri is /baz, the true uri is /foo/bar. And I have the
following table in my local mysql "test" database:

   create table my_url_map(id serial, url text, seo_url);
   insert into my_url_map(url, seo_url)values('/foo/bar', '/baz');

And I build my nginx 0.8.41 this way:

    ./configure \
                --add-module=/path/to/ngx_devel_kit \
                --add-module=/path/to/set-misc-nginx-module \
                --add-module=/path/to/ngx_http_auth_request_module-0.2 \
                --add-module=/path/to/echo-nginx-module \
                --add-module=/path/to/lua-nginx-module \
                --add-module=/path/to/drizzle-nginx-module \

Also, I have lua 5.1.4 and the lua-yajl library installed to my system.

And here's the central part in my nginx.conf:

    upstream backend {
        drizzle_server dbname=test
             password=some_pass user=monty protocol=mysql;
        drizzle_keepalive max=300 mode=single overflow=ignore;

    lua_package_cpath '/path/to/your/lua/yajl/library/?.so';

    server {

        location /conv-mysql {
            set_quote_sql_str $seo_uri $query_string; # to prevent sql injection
            drizzle_query "select url from my_url_map where seo_url=$seo_uri";
            drizzle_pass backend;
            rds_json on;

        location /conv-uid {
            content_by_lua_file 'html/foo.lua';

        location /jump {
            rewrite ^ $query_string? redirect;

        # your SEO uri
        location /baz {
            set $my_uri $uri;
            auth_request /conv-uid;

            echo_exec /jump $my_uri;

Then let's access /baz from the client side:

    $ curl -i localhost:1984/baz
    HTTP/1.1 302 Moved Temporarily
    Server: nginx/0.8.41 (without pool)
    Date: Tue, 24 Aug 2010 03:28:42 GMT
    Content-Type: text/html
    Content-Length: 176
    Location: http://localhost:1984/foo/bar
    Connection: keep-alive

    <head><title>302 Found</title></head>
    <body bgcolor="white">
    <center><h1>302 Found</h1></center>
    <hr><center>nginx/0.8.41 (without pool)</center>

We can optimize this further by introducing ngx_srcache, ngx_memc and
memcached clusters with connection pooling to cache our mysql result
sets. But I'd keep this sample config minimal for now :) If anybody is
interested, just follow up and ask here :)

Note that 0.8.42+ won't work for this config, and the request will
hang for reasons that I don't know yet. We do not have the resource to
track all the aggressive changes in recent nginx core and that's no
fun at all.


More information about the nginx mailing list