Joomla!
http://forum.joomla.org/

Adding indexes to J1.5
http://forum.joomla.org/viewtopic.php?f=199&t=216565		 Page 1 of 1
Author:  dkarlovi [ Wed Sep 26, 2007 11:16 am ]
Post subject:  Adding indexes to J1.5
Hello.

I wonder if RC2 is a good time to track down non-indexed query issues or suboptimal solutions. For example, I'm running a test RC2 installation on my old J1.0 content and found some trouble:

Code:
mysql> explain  SELECT *   FROM jos_session   WHERE session_id = '014lu9418srsah9eij50ckdr40';

+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table       | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | jos_session | const | PRIMARY       | PRIMARY | 602     | const |    1 |       |
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+


This is fine, but looking at create table jos_session, it seems that session_id is declared varchar(200), but all the data that gets stored is equaly long (md5()?) so declaring char(32) would be a good idea, smaller index size, fixed size length. Also, time gets stored as a varchar?

Code:
mysql> explain  SELECT *   FROM jos_components   WHERE parent = 0;
+----+-------------+----------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table          | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+----------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | jos_components | ALL  | NULL          | NULL | NULL    | NULL |   33 | Using where |
+----+-------------+----------------+------+---------------+------+---------+------+------+-------------+


Adding index on the parent column seems a good idea.

Guess there's more, but I just wanted to know if finding and listing them is a good idea. ;)

All this is just by looking at query execution, I haven't gone through the code so NHF anybody (especially the devs). Also, I wasn't using the latest source so if these (and the like) issues are fixed, all the better).

Edit:
and of course, now I find the J! devel boards.  :D
Author:  Schlu [ Wed Sep 26, 2007 11:40 am ]
Post subject:  Re: Adding indexes to J1.5
Have a look at this thread. But not sure if something made it in the code.

schlu
Author:  dkarlovi [ Wed Sep 26, 2007 11:55 am ]
Post subject:  Re: Adding indexes to J1.5
Schlu wrote:
Have a look at this thread. But not sure if something made it in the code.

schlu


Yeah, I had something like that in mind. On a busy site you can feel those suboptimal queries (heck, even on a small site with shared hosting), I'm looking at new 1.5 stuff and loving it (esp. core output override, it's excellent), but things like this need to get squashed before final, IMHO.

You know what they say, tweak your DB server and get a 10% improvement, tweak your DB scheme / query / indexes and get a 10x improvement.  ;)
Author:  Jinx [ Thu Sep 27, 2007 1:20 am ]
Post subject:  Re: Adding indexes to J1.5
Please post any suggestions in the 1.5 Q&t forums and we will see about including them before final release.
Author:  dkarlovi [ Wed Oct 24, 2007 8:33 am ]
Post subject:  Re: Adding indexes to J1.5
I see this topic has been moved, hurray.  ;)

What Joomla really should look into is the way phpBB3 handles query debugging: in debug mode with each SELECT you get an EXPLAIN SELECT and it's really easy to spot these rough spots, I've gone trough their queries and it was clear this feature helped them with development, a lot, I can't remember  finding a single unindexed query (which couldn't be said for Joomla, I'm afraid). Also, MySQL has an option log-queries-not-using-indexes which could also help with this, but seeing it all at once is a great solution.
Author:  humvee [ Wed Oct 24, 2007 12:19 pm ]
Post subject:  Re: Adding indexes to J1.5
You are welcome ;) ;)
Author:  dkarlovi [ Sun Jan 20, 2008 4:42 pm ]
Post subject:  Re: Adding indexes to J1.5
This topic should get revived, IMHO. I've setup my local mysqld with log-queries-not-using-indexes and so far this is what I get:

Code:
mysql> explain SELECT m.*, c.`option` as component FROM jos_menu AS m LEFT JOIN jos_components AS c ON m.componentid = c.id WHERE m.published = 1 ORDER BY m.sublevel, m.parent, m.ordering;
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+-----------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref               | rows | Extra                       |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+-----------------------------+
|  1 | SIMPLE      | m     | ALL    | NULL          | NULL    | NULL    | NULL              |   30 | Using where; Using filesort |
|  1 | SIMPLE      | c     | eq_ref | PRIMARY       | PRIMARY | 4       | j15.m.componentid |    1 |                             |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+-----------------------------+


We add the index for where and order (to avoid Using filesort):

Code:
mysql> alter table jos_menu add index ordering(published, sublevel, parent, ordering);
Query OK, 30 rows affected (0.02 sec)
Records: 30  Duplicates: 0  Warnings: 0

mysql> explain extended SELECT m.*, c.`option` as component FROM jos_menu AS m LEFT JOIN jos_components AS c ON m.componentid = c.id WHERE m.published = 1 ORDER BY m.sublevel, m.parent, m.ordering;
+----+-------------+-------+--------+---------------+----------+---------+-------------------+------+-------------+
| id | select_type | table | type   | possible_keys | key      | key_len | ref               | rows | Extra       |
+----+-------------+-------+--------+---------------+----------+---------+-------------------+------+-------------+
|  1 | SIMPLE      | m     | ref    | ordering      | ordering | 1       | const             |   27 | Using where |
|  1 | SIMPLE      | c     | eq_ref | PRIMARY       | PRIMARY  | 4       | j15.m.componentid |    1 |             |
+----+-------------+-------+--------+---------------+----------+---------+-------------------+------+-------------+
Author:  dkarlovi [ Sun Jan 20, 2008 5:34 pm ]
Post subject:  Re: Adding indexes to J1.5
These queries need to get rewritten:

Code:
mysql> explain SELECT a.id, a.title, a.title_alias, a.introtext, a.fulltext, a.sectionid, a.state, a.catid, a.created, a.created_by, a.created_by_alias, a.modified, a.modified_by, a.checked_out, a.checked_out_time, a.publish_up, a.publish_down, a.images, a.attribs, a.urls, a.metakey, a.metadesc, a.access, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(':', a.id, a.alias) ELSE a.id END as slug, CASE WHEN CHAR_LENGTH(cc.alias) THEN CONCAT_WS(":", cc.id, cc.alias) ELSE cc.id END as catslug, CHAR_LENGTH( a.`fulltext` ) AS readmore, u.name AS author, u.usertype, g.name AS groups, cc.title AS category, s.title AS section, s.ordering AS s_ordering, cc.ordering AS cc_ordering, a.ordering AS a_ordering, f.ordering AS f_ordering FROM jos_content AS a INNER JOIN jos_content_frontpage AS f ON f.content_id = a.id LEFT JOIN jos_categories AS cc ON cc.id = a.catid LEFT JOIN jos_sections AS s ON s.id = a.sectionid LEFT JOIN jos_users AS u ON u.id = a.created_by LEFT JOIN jos_groups AS g ON a.access = g.id WHERE 1 AND a.access <= 2 AND a.state >= 0 ORDER BY  f.ordering LIMIT 0, 21;
+----+-------------+-------+--------+------------------------------+---------+---------+------------------+------+----------------+
| id | select_type | table | type   | possible_keys                | key     | key_len | ref              | rows | Extra          |
+----+-------------+-------+--------+------------------------------+---------+---------+------------------+------+----------------+
|  1 | SIMPLE      | f     | ALL    | PRIMARY                      | NULL    | NULL    | NULL             | 1029 | Using filesort |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY,idx_access,idx_state | PRIMARY | 4       | j15.f.content_id |    1 | Using where    |
|  1 | SIMPLE      | cc    | eq_ref | PRIMARY                      | PRIMARY | 4       | j15.a.catid      |    1 |                |
|  1 | SIMPLE      | s     | eq_ref | PRIMARY                      | PRIMARY | 4       | j15.a.sectionid  |    1 |                |
|  1 | SIMPLE      | u     | eq_ref | PRIMARY                      | PRIMARY | 4       | j15.a.created_by |    1 |                |
|  1 | SIMPLE      | g     | eq_ref | PRIMARY                      | PRIMARY | 1       | j15.a.access     |    1 |                |
+----+-------------+-------+--------+------------------------------+---------+---------+------------------+------+----------------+


Code:
mysql> explain SELECT a.id, a.title, a.title_alias, a.introtext, a.fulltext, a.sectionid, a.state, a.catid, a.created, a.created_by, a.created_by_alias, a.modified, a.modified_by, a.checked_out, a.checked_out_time, a.publish_up, a.publish_down, a.images, a.attribs, a.urls, a.metakey, a.metadesc, a.access, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(':', a.id, a.alias) ELSE a.id END as slug, CASE WHEN CHAR_LENGTH(cc.alias) THEN CONCAT_WS(":", cc.id, cc.alias) ELSE cc.id END as catslug, CHAR_LENGTH( a.`fulltext` ) AS readmore, u.name AS author, u.usertype, g.name AS groups, cc.title AS category, s.title AS section, s.ordering AS s_ordering, cc.ordering AS cc_ordering, a.ordering AS a_ordering, f.ordering AS f_ordering FROM jos_content AS a INNER JOIN jos_content_frontpage AS f ON f.content_id = a.id LEFT JOIN jos_categories AS cc ON cc.id = a.catid LEFT JOIN jos_sections AS s ON s.id = a.sectionid LEFT JOIN jos_users AS u ON u.id = a.created_by LEFT JOIN jos_groups AS g ON a.access = g.id WHERE 1 AND a.access <= 2 AND a.state >= 0 ORDER BY  f.ordering;
+----+-------------+-------+--------+------------------------------+---------+---------+------------------+------+----------------+
| id | select_type | table | type   | possible_keys                | key     | key_len | ref              | rows | Extra          |
+----+-------------+-------+--------+------------------------------+---------+---------+------------------+------+----------------+
|  1 | SIMPLE      | f     | ALL    | PRIMARY                      | NULL    | NULL    | NULL             | 1029 | Using filesort |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY,idx_access,idx_state | PRIMARY | 4       | j15.f.content_id |    1 | Using where    |
|  1 | SIMPLE      | cc    | eq_ref | PRIMARY                      | PRIMARY | 4       | j15.a.catid      |    1 |                |
|  1 | SIMPLE      | s     | eq_ref | PRIMARY                      | PRIMARY | 4       | j15.a.sectionid  |    1 |                |
|  1 | SIMPLE      | u     | eq_ref | PRIMARY                      | PRIMARY | 4       | j15.a.created_by |    1 |                |
|  1 | SIMPLE      | g     | eq_ref | PRIMARY                      | PRIMARY | 1       | j15.a.access     |    1 |                |
+----+-------------+-------+--------+------------------------------+---------+---------+------------------+------+----------------+


The second one needs a limit because it says (with my data): Rows_sent: 982 and I don't think those rows are used, but I could be wrong on this one. Obviously, index on jos_content_frontpage.ordering is needed for order by.
Author:  dkarlovi [ Wed Feb 27, 2008 12:02 pm ]
Post subject:  Re: Adding indexes to J1.5
Please review the mod_mostread query, I've found out (by using ircmaxwell's "print time to execute query in debug") that it can take almost 3x as much as the two "big" queries in com_content combined. :'(

Code:
SELECT a.*, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":", a.id, a.alias) ELSE a.id END AS slug, CASE WHEN CHAR_LENGTH(cc.alias) THEN CONCAT_WS(":", cc.id, cc.alias) ELSE cc.id END AS catslug
  FROM jos_content AS a
  LEFT JOIN jos_content_frontpage AS f
  ON f.content_id = a.id
  INNER JOIN jos_categories AS cc
  ON cc.id = a.catid
  INNER JOIN jos_sections AS s
  ON s.id = a.sectionid
  WHERE ( a.state = 1
  AND s.id > 0 )
  AND ( a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2008-02-27 11:42:48' )
  AND ( a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2008-02-27 11:42:48' )
  AND a.access <= 0
  AND cc.access <= 0
  AND s.access <= 0
  AND s.published = 1
  AND cc.published = 1
  ORDER BY a.hits DESC
  LIMIT 0, 5
Page 1 of 1 All times are UTC
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/