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 <mr.sam...@gmail.com> 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; > +----+-------------+--------------------+-------+---------------+---------+ > ---------+-------+------+-------+ > | id | select_type | table | type | possible_keys | key > | key_len | ref | rows | Extra | > +----+-------------+--------------------+-------+---------------+---------+ > ---------+-------+------+-------+ > | 1 | SIMPLE | common_addresstype | const | PRIMARY | > PRIMARY | 4 | const | 1 | | > +----+-------------+--------------------+-------+---------------+---------+ > ---------+-------+------+-------+ > 1 row in set (0.00 sec) > > mysql> EXPLAIN SELECT `common_addradmin`.`id`, > `common_addradmin`.`surfaceMail_works`, > `common_addradmin`.`addr_enabled` FROM `common_addradmin` WHERE > `common_addradmin`.`id` = 1; > +----+-------------+------------------+-------+---------------+---------+-- > -------+-------+------+-------+ > | id | select_type | table | type | possible_keys | key > | key_len | ref | rows | Extra | > +----+-------------+------------------+-------+---------------+---------+-- > -------+-------+------+-------+ > | 1 | SIMPLE | common_addradmin | const | PRIMARY | > PRIMARY | 4 | const | 1 | | > +----+-------------+------------------+-------+---------------+---------+-- > -------+-------+------+-------+ > 1 row in set (0.00 sec) > > mysql> EXPLAIN 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; > +----+-------------+--------------------------+--------+------------------- > -------------------------------+---------------------------------------+--- > ------+----------------------------------------------------+------+-------+ > | id | select_type | table | type | possible_keys > | key > | key_len | ref | > rows | Extra | > +----+-------------+--------------------------+--------+------------------- > -------------------------------+---------------------------------------+--- > ------+----------------------------------------------------+------+-------+ > | 1 | SIMPLE | common_address_directory | ref | > address_id,directory_id_refs_id_4b74d39abef4d575 | > directory_id_refs_id_4b74d39abef4d575 | 4 | const > | 1 | | > | 1 | SIMPLE | common_address | eq_ref | PRIMARY > | PRIMARY > | 4 | aeroclub_devel.common_address_directory.address_id | > 1 | | > +----+-------------+--------------------------+--------+------------------- > -------------------------------+---------------------------------------+--- > ------+----------------------------------------------------+------+-------+ > 2 rows in set (0.00 sec) > > What I dont want to do is use caching id rather solve this first. I > also dont want to refactor the db into less tables, it is designed > near perfect so far as db Object Oriented design principles and > requirements for the project. > > cheers > -Sam -- You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-us...@googlegroups.com. To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/django-users?hl=en.
