Last few days I have done some major improvements to Special pages for SMW.

The old way of generating SpecialProperties involved unnecessary joins and limited result (some result were omitted), the new method is much simpler, I only query the required information in multiple simple queries which should be much much faster than the old way.

After this I also did some analysis on using Indexes in the db tables to see how MySQL worked with the new method and got amazing results.

The following query is done with no indexes (as is now), it runs very slow scanning about 10,724 rows to return only 250 of them. Slow Indeed 😉

mysql> explain select * from store3.smw_ids where smw_namespace=102 order by smw _sortkey limit 200,50;

+—-+————-+———+——+—————+——+———+——+—
—-+—————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | ro
ws | Extra |
+—-+————-+———+——+—————+——+———+——+—
—-+—————————–+
| 1 | SIMPLE | smw_ids | ALL | NULL | NULL | NULL | NULL | 10
724 | Using where; Using filesort |
+—-+————-+———+——+—————+——+———+——+—
—-+—————————–+
1 row in set (0.00 sec)

Next is my new way using index  (smw_namespace, smw_sortkey) runs faster by scanning only 1010 rows to return the same result of 250.

mysql> explain select * from store3.smw_ids where smw_namespace=102 order by smw
_sortkey limit 200,50;
+—-+————-+———+——+—————+——-+———+——-+-
—–+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref |
rows | Extra |
+—-+————-+———+——+—————+——-+———+——-+-
—–+————-+
| 1 | SIMPLE | smw_ids | ref | NS_SK | NS_SK | 4 | const |
1010 | Using where |
+—-+————-+———+——+—————+——-+———+——-+-
—–+————-+
1 row in set (0.00 sec)

Still more

While the new method is really fast we still need to cache stuff as this is still expensive for really large wikis

Advertisements