Category: SQL

Let’s explore PostgreSQL DBMS

Why PostgreSQL?

The purpose of this article is to give you an idea about PostgreSQL. Most probably this will be an introduction for you. I will be providing a lot of links through out the article. Make sure to check those out .

Let me start like this. I have been a MySQL fan for a long time & I got a chance to work with PostgreSQL. Then I realised  the value of PostgreSQL. Let me tell you little story. This might hurt but let’s face the truth.

Do you know that MySQL is owned by Oracle. As you know MySQL is licensed under FOSS(Free and open-source software) License. Do you really think that Oracle will let their FOSS product(MySQL) beat their commercial product(Oracle Database). I think I made my point here. Don’t get me wrong, I’m not saying that MySQL is bad. It’s an awesome DBMS. That’s for sure. Also PostgreSQL is called the FOSS version of Oracle because it’s that much feature-rich.

– Read More –

#1093 – You can’t specify target table ‘table_name’ for update in FROM clause

Have you ever got this MySQL error? Oh! You are searching for a solution right? You have come to the right place. Let me give you the simplest answer and it works charm.

For the time being assume that this is your query.

What you can do is change the query to something like this below.

Okay let me explain how the magic happens here.

– Read More –

How to do MySQL Indexing

MySQL Indexes! Whooh! Let me tell you what my experience about indexing a table. I had a SQL query which took 8-9 secs to fetch the data I want. After indexing now it only takes 1 sec. Interesting right. Let’s go through what’s indexing and how to and when to and stuff. You could find technical explanation of indexes in the internet. So let me elaborate it in a more appealing way to you.

Index is a pointer to the table and it allows you to retrieve the data faster. It’s the same as the index which we find in the back of a book. Index will represent the book in the keyword or subject order where book is printed in the page order. It’s true that index will faster your select query but it will slow down your other queries such as updates and inserts. You might be wondering why is that. It’s because once the new data is added to the table, Index should be updated too. Otherwise how can an index points to the new data in the table.

There are three types of indexes

  • Single Column(EG :- Indexed by mobile No)
  • Composite(EG :- Indexed by first name and last name)
  • Unique (EG :- indexed by e-mail)

Look at table below.

– Read More –

How To Reset My WordPress Admin Password?

Connect to the WordPress database via the phpMyAdmin tool at your web hosting account or open up a MySQL terminal to execute the below MySQL command.

Once in phpMyAdmin select the WordPress database & Open the SQL tab (look at the top navigation bar).

write the following SQL query:

 

“new_password_here” – replace this with the new password you wish to use.
“admin_username” – replace this with the username the password should be updated for.

Click on the GO button to submit the query. If everything goes fine without errors, you can login to the site using the new password.

Importing A Large mySQL Database Using BigDump

So you have a larger mySQL database of more than 30 MB size, you want to move it to a new server, and you’re unable to import your database to the new server via phpMyAdmin, due to its time and size limitations.
But don’t worry, its not a big problem. It can be done using a tool BigDump

Steps

Here are the steps involved in moving your large database to a new server easily.

1. First you will have to download your old mySQL database to your local computer. This can be done via phpMyAdmin using the Export function.
2. Download the dumper from BigDump website, and unzip it to your local computer, using Winzip or any other unzip tool.
3. Create a new folder named e.g. “dump” on your new web server, and change its permissions to chmod 755. (Do change it back to default when you’re don’t with importing database.)
4. Open the unzipped file bigdump.php in a text editor like notepad, and adjust the database setting, i.e. database name, username, password.
5. Upload bigdump.php along with the dump file (downloaded from old server) to the new server under the directory we just created named “dump”
6. Now open the bigdump.php file by using a browser, i.e. Firefox, Internet Explorer, using address something like http://www.yourwebsite.com/dump/bigdump.php
7. Select the appropriate options and start the process, then wait for the process to finish, do not close your browser, if you do so, then you will have incomplete database on your new server. As the dump file is present on the server it will take less than a minute to complete the process, if the dump file is, consider 40 MB.
8. You must remove your dump (MySQL) file and the bigdump.php file from your server, also delete the dump folder, or change its permissions back to default.

Great tool for you to try. Make sure you leave your opinions in the comments below. Your feedback is highly appreciated. (happy-face)