Speed testing SUBSTRING_INDEX replacements

test_query.py

from pootle_store.models import Store
from pootle_app.models import Directory
from django.db import connection
import timeit
import os

project = "firefox"


def get_path_sortkey(path):
    """Returns the sortkey to use for a `path`."""
    if path == '' or path.endswith('/'):
        return path

    (head, tail) = os.path.split(path)
    return u'~'.join([head, path])


def resources_substring():
    resources_path = ''.join(['/%/', project, '/%'])

    sql_query = '''
    SELECT DISTINCT
        REPLACE(pootle_path,
                CONCAT(SUBSTRING_INDEX(pootle_path, '/', 3), '/'),
                '')
    FROM (
        SELECT pootle_path
        FROM pootle_store_store
        WHERE pootle_path LIKE %s
      UNION
        SELECT pootle_path FROM pootle_app_directory
        WHERE pootle_path LIKE %s
    ) AS t;
    '''
    cursor = connection.cursor()
    cursor.execute(sql_query, [resources_path, resources_path])

    results = cursor.fetchall()

    # Flatten tuple and sort in a list
    resources = list(reduce(lambda x,y: x+y, results))
    resources.sort(key=get_path_sortkey)

    return resources


def resources_pr3572():
    resources_path = ''.join(['/%/', project, '/%'])

    sql_query = '''
    SELECT pootle_path
    FROM (
        SELECT pootle_path
        FROM pootle_store_store
        WHERE pootle_path LIKE %s
      UNION
        SELECT pootle_path FROM pootle_app_directory
        WHERE pootle_path LIKE %s
    ) AS t;
    '''
    sql_query = '''
        SELECT pootle_path
        FROM pootle_store_store
        WHERE pootle_path LIKE %s;
        SELECT pootle_path FROM pootle_app_directory
        WHERE pootle_path LIKE %s;
    '''
    cursor = connection.cursor()
    cursor.execute(sql_query, [resources_path, resources_path])
    results = cursor.fetchall()

    # Flatten tuple and sort in a list
    resources = set()
    for result in results:
        s = result[0]
        s = s[s.find("/", s.find("/", 1) + 1) + 1:]
        resources.add(s)

    resources = list(resources)
    resources.sort(key=get_path_sortkey)

    return resources


def resources_taras():
    resources_path1 = '%/' + project + '/%'
    resources_path2 = '/%/' + project + '/%'

    # Flatten tuple and sort in a list

    resources = set()
    results = []
    def res(results):
        for result in results:
            s = result[0]
            s = s[s.find("/", s.find("/", 1) + 1) + 1:]
            resources.add(s)

    #res(Directory.objects.filter(pootle_path__contains=resources_path1).values_list('pootle_path'))
    #res(Store.objects.filter(pootle_path__contains=resources_path1).values_list('pootle_path'))
    res(Directory.objects.extra(where=["pootle_path LIKE %s"], params=[resources_path2]).values_list('pootle_path'))
    res(Store.objects.extra(where=["pootle_path LIKE %s"], params=[resources_path2]).values_list('pootle_path'))

    resources = list(resources)
    resources.sort(key=get_path_sortkey)

    return resources

#print resources_substring()
#print resources_pr3572()
#print resources_taras()
print(timeit.timeit(resources_substring, number=1))
print(timeit.timeit(resources_pr3572, number=1))
print(timeit.timeit(resources_taras, number=1))