SQLite and the importance of indexes

I didn’t spend too much time on this issue, but as someone who knows the basics to intermediate in SQL it still caught me off-guard on how easy it is to have insanely slow query performances.

Take an innocent query like this. Once would think it has some sort of meta information stored already, but it doesn’t

select mytable.*, 
   (select count(*) 
    from mytable2 
    where mytable2.id=mytable.id ) as count
 from some_table;

What it does is that it will get all the columns in mytable and additionally it performs a COUNT based on id in a subquery thus the result is the total count per ID.

It took 7662ms to perform the query on two tables that have 387, and 4,800+ rows respectively. That’s not acceptable in any way, putting that sort of query in production will make your customers leave eventually due to the slowness of the system.

I googled a bit on the issue and found that sqlite doesn’t store any meta or index. I went ahead and create an index based on the second table, as the first one already has an index (primary key).

I managed to trim those seconds to 240ms, which imho is pretty acceptable, yet makes me feel uneasy.

The result?

select mytable.*, 
   (select count(ROWID)
    from mytable2 
    indexed by my_awesome_index
    where mytable2.id=mytable.id ) as count
 from some_table;

That’s all it took.

I’m still not completely satisfied with results like this, I will continue finding a way to trim those 200ms away to 20 or 50. I have an idea on how to do it but for now this will do.

Repositories

If by any chance you click whatever github link related to my account I have to say that I’ve removed myself from Github and now use BitBucket as my go-to needs for “backing up” and collaborating. There’s also GitLab but I have found that their interface and site is so… so slow, compared to BitBucket.

Support

Yesterday, one of the most beloved wrestlers in the WWE, Daniel Bryan, announced his retirement followed by one of the most passionate, honest goodbyes in the WWE. Daniel retired due to medical reasons he was set to the final slot yesterday in Raw followed by the WWE Network for an extended time.

8v4kQ14

It was heartbreaking.

I love wrestling, really do. Seeing one of the wrestlers you cheered for go down for whatever reason hurts to some extent. Today though, I was reflecting on some of his words, his interactions with his family, and so many other things that made question, “what am I doing?”.

My interactions with people hasn’t been the best; I feel like most of the time I have spent has been wasted. I’ve been ungrateful. I seldom smile honestly, it’s something that I have taught myself to do over the years. I want to learn how to “act” normal, while missing the point of being yourself.

I wish I could say I tried, but this isn’t the case. I have given up so many times without trying, yet you see this guy, Daniel? He’s a fighter, he kept pushing on even with all the injuries. I didn’t. I always come around, really late, pick myself up and push once again.

Which is why it was never a matter of “trying harder” but having the desire to want it. There’s a lot of issues I want to resolve… I guess time heals old wounds. I did learn a lot yesterday, I just don’t think I can put it into words.

Aftermath of memory management / designing for mobile / the need of something better

The aftermath… of all my chasing is that I got nothing. You heard me right. I spent days figuring out what was wrong just to receive one hell of a slap to the face that the OS doesn’t necessarily claim the memory back. But, I also noticed that memory started to be freed after changing the parent of QNetworkReply to 0, meaning I manage it manually.

Regardless, after some testing I’m happy to say that while I messed up big time the application is working as expected. I think the lowest I saw was the app using 18mb and that for me is more than enough.


Now onto designing mobile applications. See, I’m not a designer I know that, you know that. I don’t think I will get this right… but it’s time to let go honestly; I have spent too much time trying to have the “perfect” design and such thing doesn’t exists. Now I will sit one more time to work on a new mock up.


Perhaps I’m losing my mind. The more I think about it the more I notice that I’ve been preventing myself from experiencing something good. Not so long ago I pondered on getting a Macbook Pro for various reasons. The first being development, the second being Unix, the third being a polished desktop, the fourth being an stable desktop.

I get it, you love linux and I do too. It’s not enough. I want better printer support, better wireless support, better battery life because Linux can’t even do battery life management correctly and for a battery that last 2-3 hours using Windows I get only 1 hour. I just want a better desktop experience. See, windows provide that without me having to do much. Linux… provides that to a certain degree, once things start breaking apart due to upgrades then good luck because you will need it depending on the issue. The whole “well, you can just reinstall it back’ isn’t even a solution but a living proof that linux is still behind desktop experience.

It feels awful though, being a supporter and all, but once you start luring people in and seeing them running into problems it just feels all kinds of wrong.

Fun with Python3 and PyQT

I guess I should start by saying that I’m using PyQt4 mostly because… well, I don’t have an excuse, just pure laziness when I started writing the code. This is mostly an exercise where I toy with Python and Qt in general; I find it “relaxing” to work on other stuff than eating C++ every day, and Python being as delightful as it is I know I don’t have to worry about memory management at all.

Screenshot from 2016-01-28 23:25:06

So, in this little app I just wanted to play with slots/signals to see how it was done in PyQt. The documentation in PyQ is messy and that is an understatement, I had to poke a lot of pages in the documentation just to see how it was done. I’m not sure if PySide is any better …

Anyway here’s the code:

from PyQt4.QtGui import QSpacerItem, QApplication, QTextEdit, QPushButton, QWidget, QLabel, QVBoxLayout, QMainWindow
from PyQt4.QtCore import Qt, SIGNAL, pyqtSignal, pyqtSlot, QObject
import sys


app = QApplication(sys.argv)

window = QMainWindow()
window.setWindowTitle("Qt Rocks!")
window.setMinimumWidth(500)
window.setMinimumHeight(500)

widget = QWidget()
textEdit = QTextEdit(widget)
textEdit.setFixedHeight(50)
pushy = QPushButton("Say my name", widget)
spacer = QWidget()
label = QLabel("Type your name",widget)

@pyqtSlot()
def receiver():
    label.setText("Hello, %s" % textEdit.toPlainText())
    return

pushy.clicked.connect(receiver)
# _clicked_ is a signal from QPushButton. This form of connection doesn't 
# get detected by YouCompleteMe in the autocomplete... so gotta read up documentation

layout = QVBoxLayout()
layout.setAlignment(Qt.AlignHCenter)
layout.addWidget(label)
layout.addWidget(textEdit)
layout.addWidget(pushy)
layout.addWidget(spacer)
widget.setLayout(layout)
window.setCentralWidget(widget)
window.show()

sys.exit(app.exec_())

I find that if I’m going to invest a lot of my time with Qt. I think Qt/Python makes a devastating (awesome) team in the sense that whilst you don’t have to worry about memory management you can fully focus on writing and gushing out that vital code the business needs . I’d say that depending on the nature of the application Python/Qt will do on most cases. These days memory is cheap, hardware is more powerful than ever… nevertheless always know the nature of the app before choosing a language.

I concluded that hopefully in my next project I’ll be using that combination.

Back to business

So I’m finally back from my one week break. I’m somewhat disappointed with my lack of exercising these days, and much of it boils down to my sciatica interfering when I start working out. That aside, it’s been a roller coaster of emotions.

screenshot
2016-01-26-232237_1600x900_scrot

For starters I moved my laptop to use elementaryOS, and so far I have no regrets. It’s been a delightful experience. I think the next iteration of it is around the corner (maybe?) with the next Ubuntu LTS and it’s going to be a hell of an upgrade. What I worry at the moment is the lack of updates in Spotify I hope that when Ubuntu LTS kicks in they finally update their client to use a recent version of libgcrypt.

As for my application. I’ve been pondering for a while what to do with the user interface as it’s been a very stressful task of bringing something good to the table. My fault lies within the need “to be original”… sadly original these days is incredibly hard so I’m going to focus on bringing a sane and solid experience even if the design in the end reeks of “boring”. It really stresses me as while part of me want to provide something really solid I can’t come up with a good interface without sacrificing something. Well, tomorrow I’ll be sitting once again sketching a new and predictable design.

I’m no designer though… so all I can do is just try.

Looking back: OpenSUSE 42.1 with latest KDE packages

I’ve been killing some time with other Linux distributions. As far as I can tell one of my favorite Desktop Environments is still struggling out there. plasmashell crashes everywhere, right now the right side of the panel is a mess with icons overlapping on each other. It makes me a sad penguin!

I think… in 2015 I just stopped caring about the current progress of KDE… as much as it hurts to say. Amusingly–for some–I’m looking forward to see the next iteration of elementary OS or maybe Linux Mint Cinnamon. The next iteration of Ubuntu LTS is around the corner needless to say, so I’m guessing that both elementary OS and Linux Mint will be ready when the time comes.

I am looking forward to that at least. I am glad that I chose to test over in a virtual box before installing this on my laptop, else I would have been pretty upset.

As for OpenSUSE 42.1 (Leap) I got no complains whatsoever. It seems relatively stable, excluding the KDE problem as that’s a problem that lies with upstream, not OpenSUSE team. I got Spotify working in less than 10 minutes, sufficed to say I’m impressed on that since last time I had to hop through a lot of obstacles just to get it working.

Well, I hope 2016 is a fruitful one for Linux users. I also hope that KDE finally stabilizes this year.

Chasing the butterflies in GDB

You know when you are chasing bugs it can take a lot of time and energy. While I’m still fatigued I ran into a small design problem, a decision I wrote without really thinking how QNetworkReply would react thus I ran into a lot of crashes. See, crashes you at least have info to work with as the GDB would stop and deliver the frames that you will later check.

When it comes to CPU hog though, everything becomes a chase. Sometimes you either require gut feeling, or a lot of poking through the threads to see if a thread is stuck in a loop. Sadly, due to my limited experience with GDB I just can’t say “hey, alert me if you are busting a nut over there”.

Before I go on, the reason I ran into this specific problem was because I’m taking advantage of QObject parent/child relation. At the same time I’ve been moving away from pointers or anything heap related unless I really require them, which so far I haven’t had the need. This also means less memory management for me. So, if a QObject that is a parent dies, it takes the children along with it, which is incredibly ideal for me.

See, these butterflies I’m talking about aren’t real. I made the mistake of doing a test with data that is 10 years old, with address that I have to poll with QNAM. There’s a lot of redirection/error handling that needs to be in place–which I have taken care of–so I noticed that a thread (apparently a HTTP Thread by NAM) was hogging a core for itself, then after 30 seconds to a minute it would stop/terminate. My first thought was to poke the parsers, since last time I ran into a loop problem as they weren’t able to handle a instruction so it went into a loop. I think, maybe some wouldn’t call it CPU hog as to be honest said CPU hog stopped.

I got fed up; it was a dead end. If there’s a problem with my source code, I can’t detect it. I’m flushing QNetworkReplies as safely as I can, aborting them as safely as I can. So… time to go with a fresh test case. I’m using recent URLs of updated sites and have noticed that there’s no CPU hog anymore. Granted, I still have to keep testing.

I feel like I wasted a day though. A dead end with no clue. It’s a bit frustrating as all these speculations of a memory/cpu hog problem wasn’t real, or maybe it is and I haven’t really scratched the surface of it. On the bright side I cleaned up an class and its behavior is more predictable than before.

On a side note, I feel like I’m doing premature optimization and I should really stop. There has to be a legitimate issue before I jump into these types of searches.

Easy steps to move away from GMail

2016-01-12 (1)

So if you are like me, paranoid and all then by now you have been wondering “how to move away from gmail”. There are e-mail services like Fastmail (check my sidebar) that offers to import from gmail, but it can only be done once else you might fall into duplication hell. Today I’m going to go over what I did:

Starting fresh

Wait, what? You deleted all your e-mails? No, of course not. I have a decade worth of e-mail and I can’t be bothered to go through all that data. First step is to go to Google Takeout and back up your data. Back up your mail data and download the archive when it’s done.

I did want to start fresh. There’s a catch on why though, I don’t need to keep track of my e-mails. There are people that do have to keep track of them maybe due to work.

Use a encryption tools like Veracrypt, Bitlocker, etc.

2016-01-12 (1)

While Bitlocker gets a bad rap for not being open source thus not being able to audit [insert childish “b-but it’s Micro$soft” comment]. What I’ve read from it personally I consider it safe as long as you don’t back up your recovery keys to Microsoft. If you have invested yourself in Windows please do yourself a favor and research more about Bitlocker, else you could also completely encrypt your physical volume with Veracrypt.

The point of using these tools is to create a container, size may vary due to your mbox file. I will continue explaining later in this post.

Choose your e-mail client

Personally there are many ways to access your mbox file. Heck if you wanted you could create a virtual pc (virtual box) with linux, all encrypted with your mbox file in it and read it through Thunderbird. So it would go like this (virtual image fixed size gets to a Veracrypt container, load the image whenever you want with Virtual Box).

That’s too complex, seriously. What I did was simple, I chose/downloaded Thunderbird from Portable-Apps and installed it in my C:/ to later move it to my Veracrypt container.

Install ImportExport Tools for Thunderbird

Installing portable Thunderbird was the first step. Now install the ImportExport tools because with that tool you can import your mbox file to Thunderbird. Note that this process while take minutes to complete if the mbox is too big.

Let it index

This also may take minutes or hours. Once you have finished importing your mbox, let Thunderbird finish indexing.

Calculate the size of your Thunderbird portable folder

2016-01-12 (2)

Once it has finished indexing, go to the installation folder of Thunderbird and calculate how big it is. Let’s assume that it’s 2GB, so go open up Veracrypt and create a container around 2.5GB or 3GB.

Move your thunderbird installation

Since this is a portable edition of Thunderbird you can just move the folder to the container. The point of this is that you can put your container in a USB and use it whenever you want.

Time to delete

2016-01-12 (4)

Please ensure that your container has all the data before deleting.

Delete all the data from your gmail account and the initial installation folder of thunderbird. We don’t want that data lying in your personal pc unencrypted do we?

Upload it to the cloud

You can upload it to Sync or Dropbox if you feel like. I prefer Sync because you can upload it and move it to the Vault that way it won’t be deleted by anyone.

Enjoy your privacy, and peace of mind. Congratulations your data is no longer on Gmail. Now with your data safeguarded on the cloud + encrypted, you can start using a fresh e-mail address. Do keep in mind that instead of using your real address, you can create aliases like if you sign up to Facebook or Pinterest you could say [alias]+[folder]@fastmail.com where as alias is linked to your real account, and +folder is where you want the e-mail to go.

So consider the possibility of a site getting hacked, and said site uses a weak hashing algorithm (heck, it could even be in plaintext). Using an alias will buy you time to change your e-mail password if it turns out that you used the same password in both accounts. The hackers will have to go through lengths to actually find your real account. Plus, they also can’t log in to your Paypal as the e-mail they got is an alias, taking in account that you also use different passwords. So yea, you get to buy time on all accounts. If you liked this, do consider Fastmail services check my sidebar.