spacemonkey wrote:
@dkarlovi: Indexes slow down all CRUD operations (create, replace, update, delete) so the more indexes you have, the slower your CRUD ops are. On fast machines that are tuned, that is not so significant, but on the abovementioned overloaded shared machines, that really hurts, and hurts more than you'd normally expect.
CRUD stands for Create,
Read, Update, Delete so I don't think indexes slow down
all of it. ;D It's obvious that changing the dataset is going to need index rebuild but that shouldn't affect performance to the effect you describe, it's a corner case at best and should be treated as such.
Quote:
Add mysql's silly MyISAM as default storage, and you got table locks going on everywhere because the CRUD ops are blocking all SELECT statements, thanks to MyISAM's inability to do locks at the row level. Yeowch.
No argument there, MySQL is basically a toy, but it's the toy Joomla chose so we all play with it. Here's a quote from
MySQL's Explain doc page:
Join type: ALL
A full table scan is done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked const, and usually very bad in all other cases. Normally, you can avoid ALL by adding indexes that allow row retrieval from the table based on constant values or column values from earlier tables.Now take this Joomla query, I've taken it from my slow.log:
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 <= 0 AND a.state = 1 AND (( cc.published = 1 AND s.published = 1 ) OR ( a.catid = 0 AND a.sectionid = 0 ) ) AND ( a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2008-02-01 15:19:28' ) AND ( a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2008-02-01 15:19:28' ) 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 | 1774 | Using filesort |
| 1 | SIMPLE | a | eq_ref | PRIMARY,idx_section,idx_access,idx_state,idx_catid | PRIMARY | 4 | area51.f.content_id | 1 | Using where |
| 1 | SIMPLE | cc | eq_ref | PRIMARY | PRIMARY | 4 | area51.a.catid | 1 | Using where |
| 1 | SIMPLE | s | eq_ref | PRIMARY | PRIMARY | 4 | area51.a.sectionid | 1 | Using where |
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | area51.a.created_by | 1 | |
| 1 | SIMPLE | g | eq_ref | PRIMARY | PRIMARY | 1 | area51.a.access | 1 | |
+----+-------------+-------+--------+----------------------------------------------------+---------+---------+---------------------+------+----------------+
in my slow.log, there's also this:
Code:
# Query_time: 6 Lock_time: 0 Rows_sent: 1735 Rows_examined: 12251
So, this query took six seconds to examine 12251-row dataset and return 1735 rows which I doubt it uses. If you take a look at the first row of the explanation, you'll see that it in fact lacks an index. This query is from com_content executed from the frontpage. So, what I'm saying is, in general, indexes are better then no indexes.
Try it yourself, turn off MySQL query cache and do a select on a non-indexed field with ten rows, add an index and do the select again, it shouldn't make a real big difference, it could be couple msec either way. Now, do the same thing with 100k rows, you should really see the difference. Anyway, if you're interested, I would suggest
MySQL performance blog, a really great read for things like this
Somewhat related, this:
Code:
for ($x = 0; $x < count($array); $x++){
}
What people don't understand is that in this example count($array) is
executed every time, not just the first one as one would imagine, and count() is costly.
Login
Register
Rules
FAQ
The team
