# A Performance Benchmark for the PostgreSQL and MySQL Databases
## 4 Experiments and Results
### Results
#### 4.3.1 Primary Experiments Results  
1. Select Query to Fetch All Records from the Table: A select query was executed 100 times to fetch all records from both the PostgreSQL and MySQL databases. Figure 5 shows that the execution time in MySQL varies between 9 ms and 12 ms, whereas in PostgreSQL (Figure 6), it ranges from 0.6 ms to 0.8 ms--significantly lower than MySQL. Figure 7 compares the execution times for retrieving 1 million records from PostgreSQL and MySQL, clearly showing PostgreSQL's superior performance. Table 6 shows the statistics for the execution times of both databases, including the median, maximum, minimum, and percentile values. These statistics assist in evaluating database performance. In this experiment, PostgreSQL outperforms MySQL in all select query stats by a factor of 13. \begin{table}
\begin{tabular}{l c c} \hline \hline
**Experimental Condition** & **Number of Times Query** & **Number of Records** \\  & **Executed (Runs)** & **in Table** \\ \hline Select query to fetch all records from table & & \\ evaluated while database performs insert & 100 & 1 million \\ operations simultaneously & & \\ \hline Select query with condition to fetch record for one & & \\ user only while database performs insert operation & 100 & 1 million \\ parallelly & & \\ \hline Insert new records in table while database & & \\ performs select operation simultaneously & 100 & 1 million \\ \hline \hline \end{tabular}
\end{table} 
Table 4: Complex experiment details. \begin{table}
\begin{tabular}{l l} \hline \hline Database Type & MySQL and PostgreSQL \\ \hline Database Kind & SQL \\ \hline Database Version MySQL & 8.0.20 \\ \hline Database Version PostgreSQL & PostgreSQL 14.6 \\ \hline Operating System & Windows-10-10.19041-SP0 \\ \hline System Memory & 11.650901794433594 \\ \hline CPU Type & Intel64 Family 6 Model 140 Stepping 1, Genuine Intel \\ \hline Total Cores & 8 \\ \hline Total Threads & 1 \\ \hline \hline \end{tabular}
\end{table} 
Table 5: Detailed System Configuration. Figure 5: Select query execution time of MySQL for primary experiment one. Figure 6: Select query execution time of PostgreSQL for primary experiment one. Figure 7: Select query comparison of MySQL and PostgreSQL for primary experiment one. Select Query with Condition for One User: The second experiment tested a select statement with a where clause necessary for fetching data for specific users based on criteria. Using a query like select * from data where unam = 'clair', around ten thousand records were fetched from 1 million total records for the username 'clair'. MySQL took between 0.9 ms and 1 ms, Figure 8, while PostgreSQL required about 0.09 ms to 0.13 ms, Figure 9--again outperforming MySQL. Figure 10 compares the results for select queries with where clauses between MySQL and PostgreSQL; PostgreSQL consistently outperforms MySQL. Table 7 shows that PostgreSQL beats MySQL at every stage, with a median execution time of 0.0726 ms versus MySQL's 0.8428 ms.  
3. Insert New Records: For continuous authentication scenarios where large amounts of data are generated every second, an experiment was conducted with an insert query executed 100 times on both databases. Figures 11 and 12 show similar performance between MySQL and PostgreSQL, with execution times ranging from 0.0010 ms to 0.0030 ms for MySQL and from 0.0007 ms to 0.0014 ms for PostgreSQL. Figure 13 illustrates execution time graphs for both databases, showing comparable performance, with PostgreSQL slightly better than MySQL. The statistics from Table 8 confirm overlapping performance between MySQL and PostgreSQL, with a small differences in their statistical values.