I'm not an expert on this, but a few thoughts.
First, if I'm reading your message right, it sounds like your problem
probably isn't with the query, but with how many times you're running
it. If you think about it, if it's taking you 12 seconds to run *500*
queries, that's actually pretty darn good -- only 24 ms per query,
which is quite fast.
So (again, if I understand you correctly) I would try focusing on how
you can execute that query fewer times. It should be possible to not
run a different query for every single item; you'll just need to look
at how you're using Django's ORM.
A few thoughts:
1) If you haven't looked at select_related, give it a shot. It might
fix your problem entirely, particularly if you can find the right
depth: http://docs.djangoproject.com/en/1.1/ref/models/querysets/#id4
2) If that doesn't work, try setting up django-debugtoolbar. In fact,
I'd do that anyway. It's a great help at narrowing down problems like
this, because it shows you every query a page uses, and then shows you
how long each individual query takes to execute. This should let you
narrow in on the problem more precisely:
http://github.com/robhudson/django-debug-toolbar
Good luck.
Adam
On Dec 30, 1:46 am, Sam Walters wrote:
> Hi
> I have reached the optimisation stage of my project.
> I am trying to work out how to reduce the turnaround time for my queries.
> I implemented 'db_index=True' for the fields where i determined there
> should be a performance boost by indexing that particular field.
> This has not really improved the speed of the queries.
>
> Worst case senario is that my website will have to display about 500
> out of 6000 entries in the db.
>
> Each entry bridges a many-to-many relationship with another table and
> also follows a reverse one-to-one which will return a set of related
> entries. Often the query takes 12-14 seconds from start to finish.
> (and 4-5 seconds to display around 100 results)
> The major performance penalty thus far has been measured to be when
> the MySQL statements for these queries are executed.
> The many-to-may and one-to-many components only return 1-3 related
> records at most for each entry and they always hit indexes so that
> part of the design is optimised.
>
> I have been using the following tools:
>
> print connection.queries
> import time
> and looking at the raw MySQL to see whatother optmisations could be made.
> use of MySQL EXPLAIN
>
> Anyway, here are the details:
>
> *in views.py the method that packages the results from the mysql
> query:http://pastebin.com/m3eef56e5
>
> *the models associated with this from two applications: 'directory' and
> 'common'http://pastebin.com/m3868a1fchttp://pastebin.com/m18ec3765
>
> *python manage.py sqlall directory && python manage.py sqlall common:
>
> http://pastebin.com/m63a50593http://pastebin.com/m6f958cda
>
> As far as I can tell indexes are working and the queries should be fast.
>
> Here is the MySQL per iteration (will execute 500 times for my worst
> case scenario which takes 12 seconds on average)
>
> SELECT `common_addresstype`.`id`, `common_addresstype`.`adrtype` FROM
> `common_addresstype` WHERE `common_addresstype`.`id` = 1;
> SELECT `common_addresstype`.`id`, `common_addresstype`.`adrtype` FROM
> `common_addresstype` WHERE `common_addresstype`.`id` = 2;
> SELECT `common_addradmin`.`id`,
> `common_addradmin`.`surfaceMail_works`,
> `common_addradmin`.`addr_enabled` FROM `common_addradmin` WHERE
> `common_addradmin`.`id` = 1;
> SELECT `common_address`.`id`, `common_address`.`airfield_id`,
> `common_address`.`country_id`, `common_address`.`addresstype_id`,
> `common_address`.`addradmin_id`, `common_address`.`location_id`,
> `common_address`.`street1`, `common_address`.`street2`,
> `common_address`.`user_lat_dec`, `common_address`.`user_long_dec`,
> `common_address`.`zoom` FROM `common_address` INNER JOIN
> `common_address_directory` ON (`common_address`.`id` =
> `common_address_directory`.`address_id`) WHERE
> `common_address_directory`.`directory_id` = 4267;
>
> Last but not least Explain for the above MySQL for 1 instance of 500 entries.
>
> mysql> EXPLAIN SELECT `common_addresstype`.`id`,
> `common_addresstype`.`adrtype` FROM `common_addresstype` WHERE
> `common_addresstype`.`id` = 1;
> ++-++---+---+-+
> -+---+--+---+
> | id | select_type | table | type | possible_keys | key
> | key_len | ref | rows | Extra |
> ++-++---+---+-+
> -+---+--+---+
> | 1 | SIMPLE | common_addresstype | const | PRIMARY |
> PRIMARY | 4 | const | 1 | |
> ++-++---+---+-+
> -+---+--+---+
>
> mysql> EXPLAIN SELECT `common_addresstype`.`id`,
> `common_addresstype`.`adrtype` FROM `common_addresstype` WHERE
> `common_addresstype`.`id` = 2;
> +
