Tekker Blog

So, because the office was closed today, I had the option to work at home so I didn’t lose 8 hours pay.  Great idea! So, I make sure to copy the code and database backup of my project before I left work the other night.  Get up today to start working.

Sweet! Oh ya, I got to install Visual Studio 2008. No problem, I’ll grab my copy of my file server and install it.  LOLWUT? Can’t copy file 67 of 71? Fine, I go find another copy.  2 hours later, it’s installed.

Time to install Sql 2008 Express.  Run setup. Wut? .Net framework 3.5 needs to be installed.  I thought that came with Visual Studio 2008? Anyway, download it and install it.  Rerun Sql Express setup.  Now I gotta have Windows Installer 4.5.  WTF? Just install! So, download that and install it.  Run Sql Express setup AGAIN. OMFG! I need Visual Studio 2008 Service Pack 1 installed! FINE. Download it, run it.  Wow, it took 50 minutes to install the damn service pack.  Funny, installing THE ENTIRE DAMN VISUAL STUDIO APPLICATION BUNDLE TOOK 20 MINUTES, but a service pack takes 50?

Nice going Microsoft.  What should have taken no longer than an hour has now taken me damn near 5.

Again, I was working on a client project (the same project as this post) on my Windows workstation running AppServ. I ran across some unicode that I needed to insert into MySQL. Did it work? You guessed it, it sure didn’t! Turns out things like © and ® are not things php+mysql care for.  I found a few suggestions while googling to “SET NAMES utf8;” and “SET CHARACTER SET utf8;” after connecting to the database.  It didn’t work.

Example:

CREATE TABLE `blah` (
`id` int(11) NOT NULL auto_increment,
`blah` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

php code:

mysql_connect('localhost', 'blah', 'blah') || die (mysql_error());
mysql_query('SET NAMES utf8;');
mysql_query('SET CHARACTER SET utf8');
mysql_select_db('blah') || die (mysql_error());
mysql_query('insert into blah (blah) values (\'this © and this ® suck\')') || die (mysql_error());

The result of the insert was was ‘this ‘.  It cuts the string off at ©.

I ended up just replacing the extra tags with super coding skills like str_replace(‘©', '').  I am aware of the different stuff that php has to break that down to © and ®. However I was dealing with urls that had these characters in it and running those functions against the whole string would do fun things like turn & into & and what happens if you already have an & in the url because a value is already url encoded?

Fun times.  Fun times indeed.

I was working on a project for a client today and ran into a strange issue.  I have 3 main tables in myisam storage format that work is being performed on.

TableA: ID (int), Title (varchar(255)). This table has under 100 records.

TableB: ID (int), TableAID (int), Title (varchar(255)). This table has about 35,000 records.

TableC: ID (int), TableBID (int), Data1 (varchar(255)), TextField1 (text). This table has about 1,000,000 records.

I had added some new rows to TableA and the work performed on that row would generate rows in TableB.  The work preformed on TableB would generate rows in TableC.  I wanted to see all the data in TableC that had been added as a result of adding rows to TableA so I used a query similar to the following:

select * from TableC where TableBID in (
     select ID from TableB where TableAID in (
          select ID from TableA where ID > 30
     )
)

This query took over 20 minutes to complete.  So, I took the subqueries and dumped the results, 81 records, into a temp table.  Then ran:

select * from TableC where TableBID in (select id from temptable)

And the query finished in under a second.  What gives?  It’s the same damn query. Does mysql rerun the subqueries against each record in TableC?  If so, this is terrible.  It should, at minimum, cache the results of each subquery and to further enhance performance, it should actually process the innermost query, cache the results, run the next subquery out and cache those results and then on the final query (the results I want to see) and just say “where in ‘cache results’”.  Maybe I am just naive in the ways of MySQL, but plenty of other database platforms don’t act like this.

Anyone have similar experiences? If so, please leave a comment.