MySQL vs SQLite + UNIQUE INDICES
For reasons that are not relevant to this question, I will need to run multiple SQLite databases instead of the more common MySQL for some of my projects, I would like to know how SQLite compares to MySQL in terms of speed and performance in terms of disk I / O (the database will be hosted on a USB 2.0 stick ).
I read the database speed comparison page http://www.sqlite.org/speed.html and I have to say that I was surprised by the performance of SQLite, but these benchmarks have become a bit outdated since then. I was looking for a more updated benchmark (SQLite 3 and MySQL 5), again my main problem is disk performance, not CPU / RAM .
Also, since I don't have that much experience with SQLite, I'm also wondering if it has something similar to TRIGGER events (on update, delete) in the MySQL InnoDB engine. I also couldn't find a way to declare the field as UNIQUE like MySQL, only PRIMARY KEY - is there something I am missing?
As a final question, I would like to know if there is a good SQLite database manager (preferably free or open source).
a source to share
A few questions:
- In terms of disk I / O limits, I don't think the database engine matters much. Maybe there are a few small things, but I think it mostly depends on whether the database can read and write data as quickly as your application requires. Since you will be using the same amount of data with MySQL or SQLite, I think this will not change.
- SQLite supports triggers: CREATE TRIGGER Syntax
- SQLite supports UNIQUE constraints: the syntax for defining column constraints .
- To manage my SQLite databases, I use Firefox SQLite Manager add-on . It's not bad, does whatever I want.
a source to share
In terms of disk I / O limits, I don't think the database engine makes much of a difference.
In Mysql / myISAM, data is stored UNORDERED, so RANGE reads ON PRIMARY KEY, in theory, it will be necessary to release several operations from the HDD SEEK.
In Mysql / InnoDB, data is sorted by PRIMARY KEY, so RANGE reads ON PRIMARY KEY will be done using one DISK SEEK operation (in theory).
To summarize: myISAM - data is written to the hard drive in an unordered manner. The moderated range PRI-KEY is read if the pri key is not a unique AUTO INCREMENT field.
InnoDB is ordered data, bad for flash drives (since data needs to be reordered after insertion = extra records). The PRI KEY range is very fast to read, slow to write.
InnoDB is not suitable for flash memory. Because the queries are very fast (so you won't get too many benefits from reordering the data) and the extra writes needed to maintain order corrupts the flash memory.
myISAM / innoDB makes a huge difference for regular and flash drives (I don't know what about SQLite), but I'd rather use mysql / myisam.
a source to share
I really prefer using SQLiteSpy http://www.portablefreeware.com/?id=1165 as my SQLite frontend. It supports things like REGEXP which might come in handy.
a source to share