Tag: mysql

Automating Church Membership Directory Creation: A Case Study in Workflow Efficiency

Maintaining and publishing a church membership directory is a meticulous process that often requires managing sensitive data and adhering to strict timelines. Traditionally, this would involve significant manual effort, often taking days to complete. In this blog post, I will share how I streamlined this process by automating the workflow using open-source tools. This approach not only reduced the time from several hours to under 13 minutes but also ensured accuracy and repeatability, setting a benchmark for efficiency in handling similar projects. Specifically it should be noted that the complicated sorting they needed for the final output could have taken the same time if done manually in case of last minute changes like addition or removal of a member that too if a head of the family expired and has to be updated before taking final output the whole prayer group sorting can affect. Consider the head of the family name was starting with Z and when removed automatic upgrade of the next member to head of family and the name starts with A the whole prayer group layout has a chance to take drastic change and manual layout would be herculian in this case. But with this implementation of automation, that is another 15 minutes to the maximum just a flag change in the xls and the command line “make directory” will run through the full process.

Workflow Overview

The project involves converting an xls file containing membership data into a print-ready PDF. The data and member photographs are maintained by a volunteer team on Google Sheets and google drive, these are shared via Google Drive. Each family has a unique register number, and members are assigned serial numbers for photo organization. The workflow is orchestrated using GNU Make, with specific tasks divided into stages for better manageability.

Stage 1: Photo Processing

Tools Used:

  • Bash Shell Scripts for automation
  • ImageMagick for photo dimension checking and resizing

The photo directory is processed using identify (ImageMagick) to determine the dimensions of each image. This ensures that all photos meet the required quality (300 DPI for print). Images that are too large or too small are adjusted using convert, ensuring consistency across all member profiles.

Stage 2: Importing Data into MySQL

Tools Used:

  • MySQL for data management
  • Libre Office Calc to export xls to csv
  • Bash and PHP Scripts for CSV import

The exported CSV data is imported into a MySQL database. This allows for sorting, filtering, and advanced layout calculations, providing a structured approach to organizing the data.

Stage 3: Data Sorting and Layout Preparation

Tools Used:

  • MySQL Queries for layout calculations

The data is grouped and sorted based on location and family register numbers. For each member, a layout height and page number are calculated and updated in the database. This ensures a consistent and visually appealing directory design.

Stage 4: PDF Generation

Tools Used:

  • PHP and FPDF Library

Using PHP and FPDF, the data is read from MySQL, and PDFs are generated for each of the 12 location-based groups. During this stage, indexes are also created to list register numbers and member names alongside their corresponding page numbers.

Stage 5: Final Assembly and Indexing

Tools Used:

  • GNU Make for orchestration
  • PDF Merge Tools

The 12 individual PDFs generated in the previous stage are stitched together into a single document. The two indexes (by register number and by member name) are combined and appended to the final PDF. This single document is then ready for print.

Efficiency Achieved

Running the entire workflow on an ASUS A17 with XUbuntu, the process completes in less than 13 minutes. By comparison, a traditional approach using desktop publishing (DTP) software could take 20–30 hours, even with a skilled team working in parallel. The automated workflow eliminates manual errors, ensures uniformity, and significantly improves productivity.

Key Advantages of the Automated Workflow

  1. Time Efficiency: From 20–30 hours to 13 minutes.
  2. Accuracy: Eliminates manual errors through automation.
  3. Scalability: Easily accommodates future data updates or layout changes.
  4. Cost-Effective: Utilizes free and open-source tools.
  5. Repeatability: The process can be executed multiple times with minimal adjustments.

Tools and Technology Stack

  • Operating System: XUbuntu on ASUS A17
  • Photo Processing: ImageMagick (identify and convert)
  • Database Management: MySQL
  • Scripting and Automation: Bash Shell, GNU Make
  • PDF Generation: PHP, FPDF Library
  • File Management: Google Drive for data sharing

Conclusion

This project highlights the power of automation in handling repetitive and labor-intensive tasks. By leveraging open-source tools and orchestrating the workflow with GNU Make, the entire process became not only faster but also more reliable. This method can serve as a template for similar projects, inspiring others to embrace automation for efficiency gains.

Feel free to share your thoughts or ask questions in the comments below. If you’d like to adopt a similar workflow for your organization, I’d be happy to provide guidance!

From Laggy Listings to Lightning Speed: A Database Optimization Case Study

Problem: Filtered listings on the web frontend were taking a long time (22-25 seconds) to load.

Analysis: Identified inefficient database queries as the culprit. Specifically, tables lacked proper indexing, causing the database to scan through a large amount of data unnecessarily when filtering was applied. Joins with filtering conditions were especially slow because the primary key alone wasn’t enough to optimize the search.

Solution:

  • Added required indexes: Created indexes on the columns used for filtering. Indexes act like an organized filing system for the database, allowing it to quickly locate relevant data based on the filter criteria. This significantly reduces the amount of data the database needs to scan.
  • Combined partitioning with the filter field and primary key: Partitioning is a technique that divides large tables into smaller, more manageable chunks. By combining partitioning with the filter field and the primary key, you further optimized the search process. When a filter is applied, the database can quickly identify the relevant partition(s) containing the filtered data, reducing the search scope even further.
  • Few code changes: When analyzed in detail found that in this particular scenario the concerned situation demanded only to fetch those records related to the currently logged in user. The original developers had used a join statement with the user_master and condition was user name. But the userid (int ) was already in the session, so just tweaked to remove the join statement and use the userid from the session to filter on the single table with userid = ‘xx’ condition.

Result: These optimizations led to a significant improvement in performance. The filtered pages now load and render in just 4-5 seconds, which is a massive improvement from the original 22-25 seconds.

Percentage decrease in loading time:

  • The average of the original loading time range: (22 seconds + 25 seconds) / 2 = 23.5 seconds
  • The difference between the original and optimized times: 23.5 seconds – 4.5 seconds = 19 seconds
  • Divide this difference by the original average time and multiply by 100% to arrive at a percentage: (19 seconds / 23.5 seconds) * 100% = 80.85% (approximately 81%) decrease in loading time.

Percentage increase in loading speed:

  • Calculate the improvement by dividing the original average time by the optimized time and multiply by 100%: (23.5 seconds / 4.5 seconds) * 100% = 522.22% (approximately 522%) increase in loading speed. This is absolutely insane and mind-blowing.

The anonymized captures of Firefox developer tools network performance analysis is added.

screenshots of analysis

SQL – Always use aggregate functions if you can

One would wonder what the title means. Well it was a thunder bolt for me when I was trying to optimize some headless scripts. Well we at Saturn do heavily use headless scripts written in php. Some use mysql some use xml and some other use memcache, in fact pretty much all would use memcache. But that is not the situation now.

In an attempt to multi thread a cron job part of optimizations done an year back, instead of sequential processing, we had switched it to single row processing. Which required to have a method getNextRow which was passed a parameter, the primary key of the table. The cron starts with a value 0 (zero) and after each is processed, the value is supplied as the last processed one. The getNextRow had


select ID from [table] WHERE ID > [LAST_ID] ORDER BY ID ASC LIMIT 1;

Continue reading “SQL – Always use aggregate functions if you can”

How I got MySQL 5.5 running on Ubuntu 10.04 Server

After referring to all sort of documents online, for getting the src deb and compiling, which always created one or other method of headaches for me, we approached the Oracle recommended method. Downloaded the binary from official oracle downloads. In the actual process I just deviated here and there to suit my preferences. Untarred the binary to /opt/mysql-5.5.15-linux2.6-x86_64, soft linked to this from /usr/local/mysql.

After creating the /etc/my.cnf to my desired settings, I proceeded with the mysql_install_db, which promptly failed complaining about a libaio. Again a bit of googling, found the answer ‘apt-get install libaio1’. Okay the install db went straight. Now the startup script. Copied the script [mysql-base-dir]/support-files/mysql.server to /etc/init.d/mysql. Issued update-rc.d mysql defaults. All was well, the server started smoothly. But Ubuntu did not have path spec for finding the binaries, so did the easy way ln -s /usr/local/mysql/bin/* /usr/bin/. Since we were using the Ubuntu stock mysql-server and client for a long time, most of our shell scripts also expect the same path.
Continue reading “How I got MySQL 5.5 running on Ubuntu 10.04 Server”

MySQL: Optimize all tables; Run using cron

I was looking for a optimize table script and finally stumbled upon some code which could be salvaged into a shell script. The full code is reproduced here for future reference.


#!/bin/bash
 
#-- I usually add the root username and password to the ~/.my.cnf
MYSQL_CMD='/usr/bin/mysql '
 
$MYSQL_CMD -e "SELECT concat('OPTIMIZE TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,';') as cmd FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') AND Data_free > 10 AND NOT ENGINE='MEMORY' INTO OUTFILE '/tmp/cmd.sql'"
 
$MYSQL_CMD < /tmp/cmd.sql
rm -f /tmp/cmd.sql

Using Bash to take incremental backups on MySQL database

The backup plan was to have a full backup of data and structure every Sunday and then difference of the database from last sunday to every other day, rotate the backups every fourth week.

At Saturn in the development labs, we restructured the development mysql by logically splitting out or combining projects and spread it across four instances of mysql running on a dedicated server. Later we felt that the weekly backups of mysql data folder was not sufficient for some of our projects. And for these we decided on having a rotational backup. The backup plan was to have a full backup of data and structure every Sunday and then difference of the database from last sunday to every other day, rotate the backups every fourth week. The structure backup was decided to be taken with mysqldump and the options –routines –triggers –no-data –compact. Whereas the data backup was to be taken as tab-seperated-values, using select into outfile. We had specific reasons to decide these methods as well as to take data and structure seperately.

Continue reading “Using Bash to take incremental backups on MySQL database”

Getting datetime type into JavaScript as Date object

When selecting datetime to be displayed in a JavaScript ui library, select the unix_timestamp * 1000 from the sql

This is not any new thing and may be discussed at different other places. But just as I came across like any other things, I just wanted to make a record of this.

When selecting datetime to be displayed in a JavaScript ui library, select the unix_timestamp * 1000 from the sql.
Continue reading “Getting datetime type into JavaScript as Date object”

Session TimeZones and DateTime fields

I was pondering over how to coax mysql (and mssql) to automagically select local time if a session variable was set with the required timezone

Today I was pondering over how to coax mysql (and mssql) to automagically select local time if a session variable was set with the required timezone. Well we arrived at a conclusion that we could not do that on DateTime fields in MySQL. Alas we were having varchar(19) in the equivalent field in MsSQL, which was the outcome of an import from MySQL using a connection string and a procedure. We were at a dismay, and admitted defeat. Later while having lunch, I got enlightend about using the built in functions to do the methodical conversions.
Continue reading “Session TimeZones and DateTime fields”

Migration from MySQL to MSSQL – Quick Tip

With the help of some colleagues, I could export all the indexes and primary keys, which we were using from a MySQL table as MSSQL compatiable TSQL using a union query and on the MySQL information_schema.

With the help of some colleagues, I could export all the indexes and primary keys, which we were using from a MySQL table as MSSQL compatiable TSQL using a union query and on the MySQL information_schema.

I did the migration to MSSQL using some tips found online, and calling a sp after connecting directly with the MySQL server. Actually forgot how it was done. Anyway it does not matter in this post. Will add it as a comment later on. The important matter was that, the method did not import any of the indexes, or primary keys. We manually created those were absolutely necessary to roll out the project. And now that we were about to release a service patch, and this includes more incredible search methods, the indexes are absolutely necessary.
Continue reading “Migration from MySQL to MSSQL – Quick Tip”