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/m3868a1fc http://pastebin.com/m18ec3765 *python manage.py sqlall directory && python manage.py sqlall common: http://pastebin.com/m63a50593 http://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.
