Understanding MySQL Cursor Types

Question

MySQL Version: 5.5.37-0ubuntu0.14.04.1

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.

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.

As an example, executing this query 40 times with SSCursor takes 7 seconds:

SELECT Pages.PageID,
       Pages.PageName,
       Counter AS TermFreq,
       Pages.Length,
       (Counter / LOG(Length)) AS Weight
FROM Pages
INNER JOIN TermOccurrences ON TermOccurrences.PageID = Pages.PageID
INNER JOIN Terms ON TermOccurrences.TermID = Terms.TermID
WHERE TermName = %s
ORDER BY Weight DESC
LIMIT 20;

Running the same query 40 times with the default Cursor takes 0.004 seconds.

Removing the calculation of weight (Counter/LOG(Length)) makes this query execute fast again using an SSCursor however.

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. Changing back to the default Cursor surprised me when it executed so fast.

EDIT: Some more examples.

Running the following with the default cursor 40 times takes ~3 seconds:

SELECT COUNT(*)
FROM Pages
INNER JOIN TermOccurrences ON TermOccurrences.PageID = Pages.PageID
INNER JOIN Terms ON TermOccurrences.TermID = Terms.TermID
WHERE TermName = %s AND Counter > 2

Running it with SSCursor instead takes about 0.002 seconds.

Answer 1

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".

So it's about mysql_store_result() vs mysql_use_result().

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 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.

Also in the MySQLdb docs:

SSCursor: A "server-side" cursor. Like Cursor but uses CursorUseResultMixIn. Use only if you are dealing with potentially large result sets.

So SSCursor is mainly good if your result set is too large to move into your client all at once.

See also these questions:

• How to efficiently use MySQLDB SScursor?
• MySQLdb is extremely slow with large result sets

And note that a LIMIT 20 query can never really be that large. You might have to check your KEYs. To get a better idea about why that could take 7 seconds it's probably best to also include the db schema in a question, probably more something for DBA stack.