~ overflow ~

Problem:

A supposed plain query like:

select
	pacote.*,
	users.id as uid,users.usr,
	group_concat(content.nm separator '|') as fnm,
	group_concat(historico.mid separator '|') as fmid
from
	pacote
	join historico on pacote.id=historico.pid
	join content on historico.mid=content.id
	join users on users.id=historico.uid
group by historico.pid,pacote.id
order by pacote.id desc

takes 15s to run, only because of the order by, assuming that each of the involved tables have at least 100,000 entries and pacote.id is the primary key.

Solution:

I’ve searched a lot but there’s no way to optimize this, limits will not help due the `order by`, having this query un-merged in many others will get worst results, having it without join will get about the same processing time (a bit slower in long run).

So, the rest of options were quite bad:
1. Remove crap rows out the tables
2. Get new hardware?!

Although pacote.added will not match pacote.id 100%, it will match 99.9%, so why not use the bare sorting (without order by mysql will output the rows in primary key order) ?

This would require a little coding to have it right:

So we have the same query as above WITHOUT order by:

select
	pacote.*,
	users.id as uid,users.usr,
	group_concat(content.nm separator '|') as fnm,
	group_concat(historico.mid separator '|') as fmid
from
	pacote
	join historico on pacote.id=historico.pid
	join content on historico.mid=content.id
	join users on users.id=historico.uid
group by historico.pid,pacote.id

(0.1s avg)

now on php:

// some vars
define("_ipp",20); // items per page
$x['page']=0; // current page

// do the query (assuming that $sql is the query above)
$res=mysql_db_query($db,$sql);

// build an array with ALL results
for ($rez=array();$r=mysql_fetch_array($res,MYSQL_ASSOC);$rez[]=$r) { }

// cut the array from the end (note minus signal)
$rez=array_slice($rez,-(intval($x['page']+1)*_ipp),_ipp);
krsort($rez); // invert array, simulating order by's DESC

foreach ($rez as $val) {
    // do things
}

(4.25s avg)

So the php solution is far faster than the mysql order by, although it’s not accurate as, it really solved the issue.

Problem:

How to copy a table into another, like a fast backup of a single table.

Solution:

MySQL:

create table <destination> select * from <source>

MSSQL:

select * into <destination> From <source>

Notes:

Although primary keys are preserved, the primary key column will not be automatically set on the destination table, so watch out before start inserting content on the destination.

Problem:

When importing a .sql with entries with accents, like not regular english, it may lead to issues, like:

‘São Paulo‘ instead of ‘São Paulo‘

Solution:

Even mysqld default charset being latin1, sometimes it don’t work with accents, depending on the imports you’re doing.

So you may need to force it to fallback to utf8, on my case i just added this to the beggining of the .sql file i was importing:

charset utf8 \c

and it worked just fine.

Note: If you are using asian chars (japanese/chinese specific), then utf8 might not be enough to cover all chars.

Problem:

How to reset a auto_increment / identity value on a table?

Solution:

MySQL: alter table tablename auto_increment=1234;

MSSQL: dbcc checkident(‘tablename‘,RESEED,1234);

Problem:

Migrate a SQL 2000 DB to a 2005 Server and IIS 6.0 to IIS 7.0.

Extra Issues:

- SQL2000 is full of procedures, views and identity tables (+200 each)
- Coder who built tables/procedures was lame, so there might be NULL values where it shouldn’t among with other things, like STRINGs being converted to DATE, INT, etc.
- Also includes lame asp coding.
- CRYPT MICROSOFT ERRORS (CRITICAL)

More Problems:

I don’t know why people still using microsoft, everything is against it. Not only the OS, but everything (ASP, SQL, VB, IE, Office, etc) related to microsoft is bad.

When you start to get those `error: XXXXXXXXX` like you know you’re screwed, why:

- Error codes aren’t clear and used by many different instances.. guess what, none of them will fit your issue;
- Microsoft KB isen’t accurate;
- Sometimes, Microsoft KB might present you the right answer, but it will be the last one on a list of 10 possible solutions, since they don’t have much control on the crypt errors themselfs, no wonder;
- Microsoft KB likes to do inaccurate automatic translations of the content (if your not english native), meaning that, if you eventually forgot that, you will be lead to another issue caused by the automatic translation.
- MSSQL don’t have `limit x` on delete/update queries, unless you do a huge workaround that will lead to more issues.
- IIS complies with MSSQL, also showing crypt errors, making the debugging impossible for non-computer beings (eg.: you).
- IIS crypt errors can be more funny when it simple shows `Bad Request`  or `Application Error` and NOTHING ELSE.
- IIS likes to get stuck, if you try to restart/stop/start/recicle a service that takes too much time to happen, IIS manager simply shows a message `Service not responding` and abort the operation, sometimes it abort the operation and a few minutes later it happens, other times it just don’t happen and/or get stuck then you need to reboot;
- Copying database straight from the 2000 server to a local 2005 server gives the following error: `Error, please check your log`; Checking `your log` gives lots, lots of crypt non-sense errors in my native language, guess what? 0 results on google.
- The error above takes 30 minutes to happen, nothing is copied, but there’s a 300mb .mdb file.
- If you do a `SCRIPT DATABASE` you will not get the whole data or/and tables will not have identity columns or/and some data may be corrupted (even deatched);
- If you do a `IMPORT/EXPORT` procedure, you will fall on the VERY SAME problems above, but now you may also get timeout crypt message;
- You can only edit top 300 lines on MSSMS, sorted by MSSMS’ will;
- [db_name].[dbo].[tbl_name] will not appear on the automatic generated scripts, but if you forget to add it your script will miserabily fail with a non-crypt error message (first time) — that only happens if you use MSSMS;
- Can’t stand that green circle looping;
- MSSMS 2005 and 2008 are about the same, meaning you will have the same errors, so don’t even try to update;
- SQL2005 SP3 must be installed before anything, unless you want to fall on unsolved crypt errors, leading to nothingness;
- You can also amazingly fall into crypt errors with ALSO typing errors like `erro XXXXXXXXX`;
- Screaming to the skies and throwing things around will not help;
- Reinstalling SQL Server will not help;
- Reinstalling SQL Server with latest SP will not help;
- Reinstalling OS will not help;
- Reinstalling OS with latest SP will not help;
- Reinstalling <Microsoft Software> with <Any Condition> will not help;
- IIS will not forgive and forget any `;` you put on a asp page, too bad for mainly php coders (eg.: me);
- IIS is for Apache as IE is for Firefox (is this understandable in english? don’t think so);

Another Complaint:

The triumph of a bad software can’t be explained for me, I can’t also understand why they made so much money on something that simply dosen’t work. Maybe users like
things that don’t work? Maybe there’s something magic on crypt errors and blue screens? Maybe Microsoft is paying developers to use their languages and/or software?

Solution:

After several days, tries and lots of time lost I was able to do the migration using MSSQL Enterprise 2005 SP3, the Developer, Express, SQL 2008, SP1 and SP2 gave me crypt errors when copying the database, nothing related to installing the latest SP was on the KB.
Note that i installed ALL versions to try. Thanks microsoft for such great support and programs, looking forward to support the selling of pirated microsoft dvds on streets.

Solution 2:

Move to LAMP, please.