Tuesday 11 January 2011

MSSQL VS MySQL Test for reports

Hi,

This might be helper for for all of us, just imagined toady that some times we need different analytical data to dispose.
Like counting page hits or search results, so it might be a challenge run all through database table with 1000K+ records to find data.

So, today working and expecting to created simple report took me some time to discover simple SQL queries for thsi problem, so here is how you can make it:

// case in mssql a bit complicated but any way

SELECT COUNT(CASE WHEN Column_Name1='something' THEN 1 ELSE 0 END), COUNT(CASE WHEN Column_Name1='Other Thing' THEN 1 ELSE 0 END) FROM tableName Table1

// Query searching though 1000K+ records took 187 seconds, slow, but thats report any way.

// Case in mysql
SELECT SUM(Column_Name1='something'), SUM(Column_Name1='Other Thing') FROM Table1;
//Well tested on 63k records worst scenario 0.063 secs
// try even
SELECT SUM(Column_Name1 like '%something%'), SUM(Column_Name1='%Other Thing%') FROM Table1;
//result for me 0.280 secs
// a bits of creative PHP and your report is ready

You should agree MySQL beats it, well of corse it depends on other obstacles, like in mssql case records are up from year 2000 so it means more reads, of course MSSQL case has no optimizations.

Happy Queries

No comments:

Post a Comment