CMS Made Simple Forums

This tables have not a primary key or an index,  the other have only a primary key but no index - why is it so ?

additional_htmlblobs_users_seq
additional_users_seq
adminlog
content
content_props
content_props_seq
content_seq
css
css_assoc
css_seq
group_perms_seq
groups_seq
htmlblobs
htmlblobs_seq
module_comments_seq
module_deps
module_linkblog_comment_seq
module_deps
module_linkblog_seq
module_linkblog_type_seq
module_linkblog_types
module_news_seq
modules
permissions_seq
siteprefs
templates_seq
user_groups
users_seq
version

The _seq tables are how sequences are emulated with ADODB, so they're really only tables with one column and one row.  There are several other tables that don't need primary keys, like modules, because they are always looked up by name anyway...

And yeah, I have thought about the lack of indexes.  They're not really necessary on tables with primary keys, especially when queries are usually based on searching for a primary key, but there are few places where it could improve performance of CMSMS.  The most notable one I can think of is selecting from content by content_alias.  Really, that's going to take some analysis of queries to see which are used a lot and wether they can be sped up by using carefully crafted indexes.

I add a primary key in the table cms_content (new installation) with the field content_id..
Another new installation in standard mode withiut a key.

First call  to the page SVS

Page generated without key  0,280533 seconds  , with key 0,224297 second.

Great result with only some pages in a new installation and only one new key.

Interesting.  The primary key (usually) adds a resulting index.  I REALLY need to go back and figure out all of the best indexes to use and get them into 0.10.  I guess I'll put it on my TODO list.  Thanks.

I'm playing with indexes this evening.  Trying to evaluate some popular select statments.  Try this one for a huge speed burst.
I'm looking for others that help that much...

Well , i made some powertests with primary key content_is (table content) and index  (table content) hierarchy:

Apache 2.0.53 Linux  Mysql 4.1.10  PHP 5.03
24 pages, testpage id = 24, all pages activ

All test with a primary key table content_pros with fields content_is plus prop_name.


cms_content with primary key field content_id
With mysqli

Mysql Standard


cms_content with primary key field content_id and index hierarchy  field hierarchy
With mysqli


Mysql Standard


Now PHP cached with eaccelerator (mmcache)  5. call of the page

cms_content with primary key field content_id
With mysqli

MySql Standard


cms_content with primary key field content_id and index hierarchy  field hierarchy
With mysqli

Mysql Standard


cms_content with primary key field content_id
Now PHP cached with eaccelerator (mmcache)  AND cached page via cms 5. call of the page
With mysqli

MySql Standard



cms_content with primary key field content_id and index hierarchy  field hierarchy
Now PHP cached with eaccelerator (mmcache)  AND cached page via cms 5. call of the page
With mysqli

MySql Standard


cms_content with primary key field content_id
Now cached page via cms 5. call of the page
With mysqli

MySql Standard



cms_content with primary key field content_id and index hierarchy  field hierarchy
Now cached page via cms 5. call of the page
With mysqli

MySql Standard


Results:
If you cannot use eaccelerator the best way to use cms is Mysql with mysqli-extension and the table cms_content with a primary key with field content_id  and  and index hierarchy  field hierarchy  WITHOUT caching the page  via cms !!!  The primary key and the index is not standard at this time, you must set it with phpmyadmin.

If you can use eaccelerator the best way to use cms is Mysql - Standard and the table cms_content with a primary key with field content_id WITH caching the page  via cms !!!  The primary key is not standard at this time, you must set it with phpmyadmin.

Index content_pro field content_id is slower as content_id + prop_name.

That doesn't make any sense...  The SQL query is: There is no place in that query where prop_name and content_id are used together.  I'll have to do some testing with the EXPLAIN function and see.

Another little test

table content without any keys

table content_props with primary key field content_id and content_props

No cache via cms no cache via mmcache  5. call of testpage

With mysqli

MySql Standard



With cache via cms no cache via mmcache  5. call of testpage

With mysqli

MySql Standard

The other thing is that I would seriously considering testing against 0.9.2.  The fact that there are 24 SQL queries on a cached page leads me to believe that this was before the lazy loading of content props which SEROIUSLY reduced the amount of queries on a page...

It is good stuff, though.  We can make this thing highly tuned, which is something I've been avoiding until I knew most of the infrastructure was in place.  I feel we're almost there, so nows the time.

SELECT * FROM cms_content_props WHERE content_id = '1';

That is right, the difference is, i use a primary key  and you create an index.

Show the cvs export (shorted) and you see why my primary key mus include 2 fields:

NULL;"16";"string";"url";;;;
NULL;"25";"string";"headtags";;;;
NULL;"19";"string";"content_en";;;;
NULL;"20";"string";"headtags";;;;
NULL;"19";"string";"headtags";;;;
NULL;"1";"string";"content_en";;;;
NULL;"25";"string";"content_en";;;;
NULL;"23";"string";"content_en";;;;
NULL;"20";"string";"content_en";;;;
NULL;"21";"string";"content_en";;;;
NULL;"24";"string";"headtags";;;;;
NULL;"21";"string";"headtags";;;;;
NULL;"1";"string";"headtags";;;;;
NULL;"24";"string";"content_en";;;;

The content_id is not unique , but with the prop_name it is.

Ah, now that makes sense.  Actually, yes, that's a great idea for a primary key.  I am slow sometimes...