The importance of creating indexes

In v1.4 there have been some refinements made to the SQL query optimizer. In particular there was an issue with the SQL optimizer when used with INNER JOINs which had been reported and has been fixed in v1.4.

Lianja SQL makes extensive use of indexes to optimize performance. Interestingly, in the queries that I ran below, if we have indexes created on the columns we want to JOIN on and also what we want to query on in the WHERE condition, the number of records in the tables has hardly any effect on performance as the indexes are used without having to scan through the whole table. This is particularly important when you have data on a remote server or shared network filesystem.

So if you are having any SQL performance issues be sure to run your SQL query using EXPLAIN.

Notice below the improved EXPLAIN output also (it now tells you how many I/O operations occurred) for performance optimization.


select orderid, customerid, employeeid , customers.contactname, order_details.productid from ;
orders inner join customers on orders.customerid = customers.customerid, ;
orders inner join order_details on orders.orderid = order_details.orderid ;
where orders.orderid = 10308


Optimized join to table 'customers' using index tag 'CUSTOMERID'
Optimized join to table 'order_details' using index tag 'ORDERID'
Optimized WHERE condition for table 'orders' using index tag 'ORDERID'
Table 'orders' has 834 records
Table 'customers' has 91 records
Table 'order_details' has 2155 records
Processing table 'order_details' using index tag 'ORDERID'
Total I/O read operations was 7
sqlcnt=2


ORDERID CUSTOMERID EMPLOYEEID CONTACTNAME PRODUCTID


10308 ANATR 7 Ana Trujillo 69
10308 ANATR 7 Ana Trujillo 70


2 records selected in <1ms


select orderid, customerid, employeeid , customers.contactname, order_details.productid from ;
orders inner join order_details on orders.orderid = order_details.orderid, ;
orders inner join customers on orders.customerid = customers.customerid ;
where orders.orderid = 10308


Optimized join to table 'order_details' using index tag 'ORDERID'
Optimized join to table 'customers' using index tag 'CUSTOMERID'
Optimized WHERE condition for table 'orders' using index tag 'ORDERID'
Table 'orders' has 834 records
Table 'order_details' has 2155 records
Table 'customers' has 91 records
Processing table 'customers' using index tag 'ORDERID'
Total I/O read operations was 8
sqlcnt=2


ORDERID CUSTOMERID EMPLOYEEID CONTACTNAME PRODUCTID


10308 ANATR 7 Ana Trujillo 69
10308 ANATR 7 Ana Trujillo 70

2 records selected in <1ms