MySQLdb is extremely slow with large result setsĪnd note that a LIMIT 20 query can never really be that large.
CURSORS TYPES HOW TO
Like Cursor but usesĬursorUseResultMixIn. So each row has to be retrieved individually with calls to mysql_fetch_row(), which of course can add up considerably when dealing with large tables. The MySQL docs mention that mysql_use_result() initiates a result set retrieval without actually reading the result set into the client, like mysql_store_result() does. So it's about mysql_store_result() vs mysql_use_result(). The MySQLdb docs mention that the standard Cursor class is using mysql_store_result() whereas SSCursor is using mysql_use_result() "with the latter you must ensure all the rows have been read before another query can be executed". Running it with SSCursor instead takes about 0.002 seconds. Running the following with the default cursor 40 times takes ~3 seconds: SELECT COUNT(*) Changing back to the default Cursor surprised me when it executed so fast. I was using SSCursor because it was proving to have vastly superior performance on a number of other queries and then suddenly became very slow for this one.
Removing the calculation of weight (Counter/LOG(Length)) makes this query execute fast again using an SSCursor however. Running the same query 40 times with the default Cursor takes 0.004 seconds. INNER JOIN Terms ON TermOccurrences.TermID = Terms.TermID
INNER JOIN TermOccurrences ON TermOccurrences.PageID = Pages.PageID I have noticed that choosing the right type of Cursor in the MySQLdb python module when executing a query has a really big effect on performance and was wondering if someone could explain or provide a reliable resource explaining which cursor to use when.Īs an example, executing this query 40 times with SSCursor takes 7 seconds: SELECT Pages.PageID, I am currently writing a python script which makes use of numerous MySQL tables and queries to obtain results from an inverted index stored in tables.