MySQL or MicrosoftSQL?

[pedra]

Which one is better? I know nothing about data base so is a hard decision!

[rtv]

MySQL is totally free whereas MSSQL "can" be faster in some scenarios if you've got lots of RAM

I am using both and can assure there's no specific problem with either of them. My HTPC runs nicely with MySQL

[frodo]

SQL Server 2005 express is also free

[Rhys.Goodwin]

Well free as in no money but not really free licence. Not that it makes any difference I guess. Open source is ideal though. I use SQL Express, it's nice but I also like the fact that MP supports a truly free db. (MySQL is still opensource right!?)

[Noelix]

I just tried out the tv server for the first time with MySQL (first time since testing the tv server since early January) and it runs like a dream. MySQL takes up so little memory and processing power its practically transparent. Thanks so much for working so hard on this guys (frodo, rtv)!

[pedra]

Thanks all!!

[htpsd]

Hello Everybody,

I was wondering what database I should use. So I set up the server once with MSSQL and once with MySQL. The content of both databases should be exactly the same. I checked it for the Table "program". In both databases I importet the same data from Clickfinder. 7777 rows are in both databases.

Then I wrote a simple PHP Script wich makes 10.000.000 queries on the table "program". Once for MySQL and once for MSSQL.

I ran the script for each database about 10 times to be shure that the result is right.

The MSSQL database needed 740 seconds to execute the query.
The MySQL database needed 600 seconds to execute the query.

Query was: "select * from program" .


Than I changed the script to insert data into the databases. Again I choosed the table "program". I simply added some trash data. The script added 10.000 entries in both databases.

The MSSQL database needed 240 seconds to execute the query.
The MySQL database needed 255 seconds to exevute the query.


--> So for reading MySQL is faster for TVengine3 - for writing MSSQL.
In my configuration with 4 clients the TVservice reads more data from database than it writes into it - so I decided to use MySQL.


Additional info:

-Both databases were set up like its written in the TVengine3 wiki.
-This performance test is only for the TVengine Database correct. Other databases could give other results.
-The tests run at the same machine. Only Apache and the specific database were running.
-The results are averages from about 10 results.
-The Server is running MySQL 5.1, MSSQL 2005 Express, PHP5.1 on Dual Core AMD 64 4600+ running on Windows 2003 Enterprise x86 and 4 GB Ram.

Queries for MySQL (MSSQL is the same code, but mssql_connect)

<?php

$host = 'localhost';
$db   = 'tvlibary';
$user = 'root';
$pw   = '***';

set_time_limit(3600);
$link = mysql_connect($host, $user, $pw);
if (!$link) {
   die('keine Verbindung möglich: ' . mysql_error());
}
echo 'Verbindung erfolgreich<br><br>';
$que = mysql_select_db($db);
$t1 = time();
//Queries
$i=1;
while ($i <= 10000000) 
{
  mysql_query("select * from program");
  $i++;
}
$t2 = time();
$t3 = $t2-$t1;

echo $t3;
?>

[funkstar]

the real question is: do those figures make any real wold difference to the performance of MediaPortal?

however the memory footprint and resources used for the DB while running the TVserver will probably have more of an effect (is any, depending on the machine specs) than the time taken to read 10,000,000 records or write 10,000

(i could be wrong in this of course, i'm not a DB engineer)

[htpsd]

This is not a statement that says wich database is the better one.
I just wanted to know wich database is faster for my uses. I posted it because this information could be useful to somebody...

Both databases are good databases. I work everyday with databases (MSSQL, MySQL and PostgreSQL). The performance on any database depends on the hardwarespecs and for what you use it.

I don't think that MP will run slower when I use MSSQL. It's just an info

[tourettes]

Originally Posted by htpsd

This is not a statement that says wich database is the better one.
I just wanted to know wich database is faster for my uses. I posted it because this information could be useful to somebody...

Both databases are good databases. I work everyday with databases (MSSQL, MySQL and PostgreSQL). The performance on any database depends on the hardwarespecs and for what you use it.

I don't think that MP will run slower when I use MSSQL. It's just an info

To get real database performance figures for MSSQL / MySQL you could maybe use SQL statements that tv engine is using. I would assume that SELECT * operations are pretty much going to run at similar speeds on different SQL servers.