...
Note: The hashes in the query above are not necessarily the correct hashes for the actual predicates / objects, but the overall statement is correct.
SQL Execution time: As noted at above, this query was taking - for a particular large profile - 20-ish seconds, and accounting for almost all of the time taken to generate the co-author visualisation.
Tweaking MySQL
Knowing the exact query that is being executed against the database allows us to actually focus on the database, see how the query is being executed and make configuration changes that help it to support the actual data structures and workload being demanded of it.
The first thing to note is that (executing the SQL directly) the performance of the query is very consistent. Knowing that it returns quite a large amount of data (approx 200,000 rows), and has a significant amount of work to do, this suggests that the caches aren't necessarily being used effectively.
So, lets increase the join, read and innodb buffers. If you've installed MySQL on a Mac as described above, you'll need to create a my.cnf in /etc, otherwise edit the my.cnf that is being used:
| Code Block | ||
|---|---|---|
| ||
join_buffer_size = 32M
read_rd_buffer_size = 32M
innodb_buffer_pool_size = 512M
innodb_file_per_table = 1
innodb_file_format = barracuda |
NB: There are also some tweaks to the InnoDB file layout - these are good practice things to have.
Whilst this does not affect the initial execution of the SQL query (20 seconds), it does make subsequent executions for the same profile faster - approximately 14 seconds.
It's not known what size buffers should really be used for any given database size, or how well this translates into overall performance, e.g. how much can be cached to the benefit of the entire application. Of course, each individual site would need to look at the resources available on their own systems, and what they can dedicate to a given purpose.
Even Deeper Explanation
So far, we've just scratched the surface of MySQL, and put in a basic tweak to the configuration, but we haven't really looked in depth at what the query is actually doing.
Like other database engines, MySQL provides execution plans for queries, so lets go ahead and ask MySQL to give us the execution plan - by adding EXPLAIN to the start of the query:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | <derived2> | ALL | 852 | |||||
| 1 | PRIMARY | R_1 | eq_ref | PRIMARY | PRIMARY | 8 | S_1.X_4 | 1 | |
| 1 | PRIMARY | R_2 | eq_ref | PRIMARY | PRIMARY | 8 | S_1.X_3 | 1 | |
| 1 | PRIMARY | R_3 | eq_ref | PRIMARY | PRIMARY | 8 | S_1.X_2 | 1 | |
| 1 | PRIMARY | R_4 | eq_ref | PRIMARY | PRIMARY | 8 | S_1.X_6 | 1 | |
| 1 | PRIMARY | R_5 | eq_ref | PRIMARY | PRIMARY | 8 | S_1.X_1 | 1 | |
| 1 | PRIMARY | R_6 | eq_ref | PRIMARY | PRIMARY | 8 | S_1.X_5 | 1 | |
| 2 | DERIVED | Q_1 | ref | SubjPredObj,PredObjSubj,ObjSubjPred | SubjPredObj | 24 | const,const,const | 1 | Using where; Using index; Using temporary |
| 2 | DERIVED | Q_2 | ref | SubjPredObj,PredObjSubj | SubjPredObj | 16 | const,const | 1 | Using where; Using index |
| 2 | DERIVED | Q_3 | ref | SubjPredObj,PredObjSubj,ObjSubjPred | SubjPredObj | 16 | const,const | 852 | Using where; Using index |
| 2 | DERIVED | Q_4 | ref | SubjPredObj,PredObjSubj,ObjSubjPred | SubjPredObj | 24 | Q_3.o,const,const | 1 | Using where; Using index |
| 2 | DERIVED | Q_5 | ref | SubjPredObj,PredObjSubj,ObjSubjPred | SubjPredObj | 16 | Q_3.o,const | 1 | Using where; Using index |
| 2 | DERIVED | Q_6 | ref | SubjPredObj,PredObjSubj,ObjSubjPred | SubjPredObj | 24 | Q_5.o,const,const | 1 | Using where; Using index |
| 2 | DERIVED | Q_7 | ref | SubjPredObj,PredObjSubj,ObjSubjPred | SubjPredObj | 16 | Q_5.o,const | 1 | Using index |
| 2 | DERIVED | Q_8 | ref | SubjPredObj,PredObjSubj,ObjSubjPred | SubjPredObj | 24 | Q_7.o,const,const | 1 | Using where; Using index; Distinct |
| 2 | DERIVED | Q_9 | ref | SubjPredObj,PredObjSubj,ObjSubjPred | SubjPredObj | 16 | Q_7.o,const | 1 | Using where; Using index; Distinct |
| 2 | DERIVED | Q_10 | ref | SubjPredObj,PredObjSubj,ObjSubjPred | SubjPredObj | 24 | Q_9.o,const,const | 1 | Using where; Using index; Distinct |
| 2 | DERIVED | Q_11 | ref | SubjPredObj,PredObjSubj | SubjPredObj | 16 | Q_9.o,const | 1 | Using index; Distinct |
| Info | ||
|---|---|---|
| ||
Reading the documentation on MySQL, there is in 5.6 a feature called "batched key access", which on the surface should help with large joins. However, when tested on this query, it appeared to make no difference |