HSQLDB very slow Select query for in-memory embedded database
I am using hsqldb full in memory for a project and I need fast select queries for the project. I made some tests to measure the performance as below;
I am using HSQLDB as embedded and full in-memory. I have a table with 92 columns and insert one million rows of that table to the database. Inserting performance is relatively good in order of miliseconds. However, Select or Select Like query as given below of a row from database takes 3-4 seconds which is very high for the project needs.
-SELECT column1,column2,column3 FROM table WHERE column1 LIKE '%xxx';
-SELECT column1,column2,column3 FROM table WHERE column1= 'xxxxxx';
In the project, I will have huge database and it should perform fast and high amount of select queries(~in miliseconds) while the new rows being inserted continously to the database.
Can you help me about this issue? What am i doing wrong and How can i achieve this performance?