Calling all MySQL experts out there!

Recently we had some reports about Core and some modules generating too many or not totally optimized queries, as reported by Tatane and Yasir

We would like to announce a competition for best/most MySQL database optimizations in XOOPS. This can happen in following areas:

a) existing Core queries
b) existing popular modules
c) new module with the best optimized way to access and manage MySQL database, incl. a tutorial on how it was done

Please submit your entries by July 15

This competition is sponsored by Webyog, which offers SQLyog, one of the best MySQL tools on the market. See the features here:

http://webyog.com/en/sqlyog_feature_matrix.php

They offered to give us up to 5 copies of the SQLyog Ultimate version (worth $179 each) to winners of a competition among XOOPS developers. Of course, it will depend on the number and quality of the entries, if we will use all five copies.

There are many very good tutorials and presentations about optimizing MySQL, but we're sure, you all know those tricks:

http://www.percona.com/files/presentations/percona-live/dc-2012/PLDC2012-mysql-query-optimization.pdf
http://www.slideshare.net/guest9912e5/15-ways-to-kill-your-mysql-application-performance
http://www.slideshare.net/posullivan/capturing-analyzing-and-optimizing-your-sql

Let's see how could we use them to improve our Core and our Modules.

Please post your solutions in this thread.

Crap, I wrote a reply to this and closed the window before submitting.. Oh well..

Anyhow, I wonder if it is not time to convert from the current 10+ year old MySQL library calls to the much better and current PDO system.

This was brought up recently by Wishcraft in this thread about promoting to switching to ADODB from the antiquated MySQL library:
https://xoops.org/modules/news/article.php?storyid=6098

There was also a discussion about this compared to PDO. There is also a newer MySQLi library but from what other articles I have read say PDO is the way to go. The older MySQL library that xoops is based off of is currently depreciated and may not work with future versions of MySQL. Not sure what all MySQLi brings to the table but the articles I have mentioned that PDO makes using MySQL or MySQLi PHP libraries obsolete.

Switching to PDO should increase the PHP performance as well as reduce the number of Queries required.

I compared ADODB and PDO in this thread:
https://xoops.org/modules/newbb/viewtopic.php?viewmode=flat&type=&topic_id=74894&forum=8

A couple of articles that discuss using PDO instead of MySQL or MySQLi are here:
http://webdevrefinery.com/forums/topic/1272-your-mysql-code-sucks
http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers

While PDO is an abstract layer on top of MySQL which should make it slower in actuality it can drastically speed up a site due to some features as mentioned in the above articles.

I will look through the core this weekend and try to build a "core drop in" replacement to use PDO. I will document my progress in this thread:
https://xoops.org/modules/newbb/viewtopic.php?viewmode=flat&type=&topic_id=75217&forum=74

As mentioned in that thread I suspect by using this it will break any module that uses the MySQL library calls instead of the Xoops object calls. However these calls should be relatively easy to convert over to the normal Xoops calls and thus to PDO.

Please check out that thread for any updates. I will also be looking for people to help test this with some of the more used modules as well. Only a couple to determine if this is a route we can use by its self to help with the performance and number of queries.

Two things.
One thing is to optimize the database (schemas, table structures, indexes, etc.).
Another, how core and modules use of these tables in the database.
So the subject 'DB Optimization' is ambiguous.

From what I remember xoopsObject not support joining more than two tables (getByLink method). So many modules increase the number of queries. Maybe later I will put some examples.

Certainly the use PDO facilitate this task. I think that the introduction of PDO should be transparent to the modules. Just some modules will use the old way and others will use the extra features with PDO.

Quote:
Bumciach, good point.

The first goal is to improve the queries itself, i.e. optimize them, and possibly, minimize the number of queries.

The second step, based on the work on the query optimization, could be recommendation on how to improve the database (schemas, table structures, indexes, etc.). This could go into XOOPS 2.6.x improvements.

The XOOPS database design is probably done on MySQL 4.x. And I am not sure if it was updated to take advantage of MySQL 5.x features that were not available when the XOOPS database was designed. But since MySQL 5.x is the recommended version for current XOOPS releases, we should be looking into it.

Bottom line: we have a need to improve, we have some cool prizes thanks to Webyog, so let's share some ideas what could be improved.

If you know any MySQL gurus, feel free to invite them here.

As Bumciach mentioned there are likely things that can be done to optimize queries and such.

I believe from looking at the code Xoops is based on the 10+ year old MySQL connector code and is based off early MySQL 4.x.

MySQL 5.x has brought MANY new things to the table. You can switch to the newer MySQLi Connector stuff to take advantage but there really is no point at this stage. If you are going to switch to a new system then it is better to move to a universal system such as PDO because it will take the same or maybe even less work. With few exceptions other than the connection string , by using PDO we can also support any of the PDO supported databases out of the box. Both PDO and MySQLi support the latest MySQL.

From my experience a number of developers have directly accessed MySQL from their modules for a variety of reasons(Speed, convenience, security, lack of knowledge of API functions). Going to PDO or even MySQLi may break some of those modules. PDO will help to facilitate other Optimizations and should have an immediate impact. A big reason a lot of sites now use PDO is also because of the fact it is much more secure than MySQL connect commands. By its design of using Prepared statements and some other features is MUCH more secure than MySQL. It is much harder for someone to "forget" to sanitize data since some of this PDO does automatically.

Another thing to look at is the Database Table Type that we use.
We use MyISAM with XOOPS and vast majority of Modules. We should consider looking at basing the Databases Types off InnoDB instead. I believe MyISAM is an older type and the InnoDB was created at a later time to support many of the newer functions.

Taken from:
http://www.mysqltutorial.org/understand-mysql-table-types-innodb-myisam.aspx

Quote:

Only listing the relevant table types here for Brevity. If you want to know more go to the listed site.

Quote:

I have read elsewhere that MyISAM generally is faster for large databases with few linked tables. I have read that the InnoDB datatable type was created to support many of the new features of MySQL 5.x.

From what I have read it looks like we would benefit greatly by moving to InnoDB as the database type.

My impression by using InnoDB and PDO it will allow us to implement quite a number of optimizations we are limited from doing now. I find it curious though that PDO talks about working with transactions but doesn't suggest that it requires using InnoDB when using MySQL.

Here is another reason to move to PDO... Or whatever system...

The MySQL extention/connection that Xoops uses to access its databases has been depreciated since July 10, 2011.

This article suggests that people move to PDO or MySQLi since the MySQL extention is officially depreciated and support can be removed for it at any time. The below mentioned article mentions this is likely to be removed as early as either 5.5 or 5.6 versions of MySQL. Since the current version is 5.5.25 and it still exists then the next earliest version it could be removed from is 5.6 but realistically it could be removed at any time. SO we should look into moving to PDO or at a minimum MySQLi for 2.6. Since the same basic work is required I again suggest PDO since it appears to be the "defacto" standard for the vast majority of PHP stuff.

Mentioned Article...
http://news.php.net/php.internals/53799

BTW, to qualify for contest one should tweet why they like SQLyog.

Or use simply this - http://bitly.com/M5TNtV

Please make sure to check out this thread and help me test this MySQLi
class to see if it works:

Developing MySQLi then PDO

Mamba and redheadedrod:
Quote:
Do we have any progress in this?

can we choose PDO with InnoDB as MySQL engine and proceed?

At this time no. I had hoped to have something ready for this weekend but it seems this week has been against me.

I swapped rooms with my daughter then found out she apparently had been raped recently so been dealing with that stuff and then I have worked 30 hours in past 3 days on my normal weekend so I have not had time to finish this.

Because Wishcraft said he had a working PDO library I stopped working on this project but the only thing he has brougth forth is that the ADODB library he designed is ready but it is not something we want to use.

Mamba forwarded a really cool PDO class to me he had found and the comments are written in Portugues so I am translating this and will look this over.

I want to go through this because they have tackled many of the things I wanted to do and done it in ways that are much better than I had considered. I should be releasing a PDO library compatible with the current code soon with no additional functionality. (Hopefully week to 10 days. If no more suprises)
I hope to have a full feature class available shortly after that which will take advantage of the new features with the intent to move it to 2.6 and maintain compatibility as much as possible for database stuff between 2.5.5 and 2.6 while supporting all the new features that PDO brings. Code refactoring will be required for the core to take advantage of the new features which I understand is planned for Alpha 3.


Rodney