archive “2008/01”

temporary/memory tables in mysql

by Hamid Reza Fahimi Madjd @ Jan 21, 2008

As you know memory tables (temporary tables) are a kind of tables which store data in memory and respond to your query very fast.
I've experienced temporary tables in Oracle and SQL Server by myself but I had no practical experience about that in mysql till yesterday!
Yesterday, I had a query that was very slow and took 4-5 hours for running! So I started to search about temporary tables in mysql and found how to use them.
After I used memory table in my queries, it took only 9 minutes !!! It was unbelievable.
So I decied to post a new mini article here and share result of my research with you.

There is a very simple command for creating a memory table :

create table tbl1(fld varchar(64)) engine=memory;

This commands will create a table in memory for us but it's empty. Now, how we can fill it ? We can use insert command for filling this table or fill it on creation time:

create table my_table engine=memory
select ip,country from ip_location;

As you see it's like a piece of cake ;)
Now we have a table in memory which help to decrease our query run time. if you get

Error Code : 1114
The table 'my_table' is full

error message during creation memory table, you have some solutions to solve the problem:

1. increase max_heap_table_size system variables.

set @@max_heap_table_size=1048576 /* your favorite size in byte, it's 1 MB */

It's a system variables that force maximum size on memory tables

2. limit rows by max_rows table option. It's a table option in the create table statement to keep the table from becoming too large

3. decrease length of fields type, for example if you have a field as varchar(100) change it to varchar(32)

After creation, the table will be accessible for all sessions till server doesn't restart or shut down, after that you have your memory table without any rows.

You can read a complete document about memory tables here.

The solution solved my problems, I wish you enjoyed it :)

mysql 1 comment(s)

Set JFreeChart data from database

by Hamid Reza Fahimi Madjd @ Jan 15, 2008

A couple of days ago I needed to generate some charts using Java so I searched for a library which could do it. Soon I found JFreeChart library and downloaded it from here. But I was surprised when I saw the user guide wasn't free! Therefore I decided to write this post and describe how to obtain JFreeChart data from database (I'll use mysql database).

At first I copied jcommon-1.0.12.jar, jfreechart-1.0.9.jar and mysql-connector-java-5.0.7-bin.jar files to /WEB-INF/lib directory then I created a table in the database and filled it with below data:

type             count
----------------------
invoice_detail    3273
----------------------
object_status     2819
----------------------
service_sales_... 1540
----------------------
call_function     1183
----------------------
contact           904
----------------------
invoice           775
----------------------
payment           596

Now I create DatabaseChart servlet for connecting to database and generating my chart

import org.jfree.data.jdbc.JDBCPieDataset;
import org.jfree.chart.JFreeChart;
import org.jfree.chart.ChartFactory;
import org.jfree.chart.ChartUtilities;

import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.ServletException;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.SQLException;
import java.sql.DriverManager;
import java.sql.Connection;

public class DatabaseChart extends HttpServlet {

  public void doPost(HttpServletRequest request, response) throws ServletException, IOException {}

  public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    Connection connection = null;
    try {
      Class.forName("com.mysql.jdbc.Driver").newInstance();
      try {
        connection = DriverManager.getConnection("jdbc:mysql://localhost/my_db?user=my_un&password=my_pass&useUnicode=true&characterEncoding=utf-8");
      } catch (SQLException e) {
        e.printStackTrace();
      }
    } 
    catch (InstantiationException e) {
      e.printStackTrace();
    } 
    catch (IllegalAccessException e) {
      e.printStackTrace();
    } 
    catch (ClassNotFoundException e) {
      e.printStackTrace();
    }

    JDBCPieDataset dataset = new JDBCPieDataset(connection);
    try {
      dataset.executeQuery("Select `type`, `count` From my_table order by count desc");
      JFreeChart chart = ChartFactory.createPieChart("Pie Chart", dataset, true, true, false);
      if (chart != null) {
        response.setContentType("image/png");
        OutputStream out = response.getOutputStream();
        ChartUtilities.writeChartAsPNG(out, chart, 450, 400);
      }
    } 
    catch (SQLException e) {
      e.printStackTrace();
    }
    try {
      if(connection != null){connection.close();} 
    }
    catch (SQLException e) {e.printStackTrace();}
  }

}

at the end I add the servlet information to web.xml file

<servlet>
<servlet-name>generate_chart</servlet-name>
<servlet-class>DatabaseChart</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>generate_chart</servlet-name>
<url-pattern>/generate_chart</url-pattern>
</servlet-mapping>

now if you run the project you will get a chart like this

java, jfreechart 16 comment(s)

How to search and sort primitive arrays in Java ?

by Hamid Reza Fahimi Madjd @ Jan 8, 2008

Always I was keen on solution for sorting and searching primitive arrays. Today I found java.util.Arrays class that includes some useful static methods for sorting, searching, filling and ... For sorting you can use Arrays.sort(aPrimitiveArray) static method, look here :

String[] test = {"d", "z", "a"};
Arrays.sort(test);
System.out.println(Arrays.toString(test));

The result will be:

[a, d, z]

As you see we've used Arrays.toString(test) for printing sorted array.

And for searching you can use Arrays.binarySearch(aPrimitiveArray, key) static method:

Arrays.sort(test);
System.out.println(Arrays.toString(test));
System.out.println(Arrays.binarySearch(test, "a"));

The result will be:

[a, d, z]
1

1 is index of found key otherwise it will be a less than zero number.

Attention: that before using Arrays.binarySearch(aPrimitiveArray, key) method you have to sort array by Arrays.sort(aPrimitiveArray) method.

array, java 1 comment(s)

boring English teacher!

by Hamid Reza Fahimi Madjd @ Jan 3, 2008

Two days ago English teacher was absent. she went to holidays because she is Christian.
Instead another teacher came for teaching us. I think she was illiterate, she couldn't speak English fluent and when we asked her about new word she said "I'm not dictionary, check your dictionary please"!

The class was boring and all of us yawned frequently :D
In break time we went to institute office and talked with our supervisor for leaving class but he said "It's very impolite, please stay till end of class"! and we were forced to tolerate that stupid teacher!

no comment