Storing trees in a database is a subject which has many different solutions. It depends on if you want to retrieve a subhierarchy as well (so all children of item X) or if you just want to grab the entire set of hierarchies and build the tree in an O(n) way in memory using a … Read more
# mysqladmin status Uptime: 587 Threads: 121 Questions: 3456 Slow queries: 0 Opens: 152 Flush tables: 1 Open tables: 64 Queries per second avg: 5.888
In the SQLite Documentation it says: … “an index should be created on the child key columns of each foreign key constraint” ie. the index is not automatically created, but you should create one in every instance.
The reason that you’re seeing a difference is due to the execution plan that the planner is putting together, this is obviously different depending on the query (arguably, it should be optimising the 2 queries to be the same and this may be a bug). This means that the planner thinks it has to work … Read more
If you’re absolutely sure your numbers will always stay within the range of smallmoney, use that and you can save a few bytes. Otherwise, I would use money. But remember, storage is cheap these days. The extra 4 bytes over 100 million records is still less than half a GB. As @marc_s points out, however, … Read more
You can avoid this issue by adding an unneeded ORDER BY clause to the query. SELECT * FROM object_values WHERE (objectID = 53708) ORDER BY timestamp DESC, objectID limit 1;
When you have both the LIMIT and ORDER BY, the optimizer has decided it is faster to limp through the unfiltered records on foo by key descending until it gets five matches for the rest of the criteria. In the other cases, it simply runs the query as a nested loop and returns all the … Read more
No, the order does not matter. Almost all RDBMS’s (such MS Access, MySQL, SQL Server, ORACLE etc) use a cost based optimiser based upon column statistics. In most situations, the optimiser will choose a correct plan. In the example you gave, the order will not matter (provided statistics are up to date). To decide what … Read more
You don’t seem interested in the actual count so give this a try: SELECT 1 FROM table WHERE fk = 1 LIMIT 5000, 1 If a row is returned, you have 5000 and more records. I presume the fk column is indexed.
SUSPENDED: It means that the request currently is not active because it is waiting on a resource. The resource can be an I/O for reading a page, A WAITit can be communication on the network, or it is waiting for lock or a latch. It will become active once the task it is waiting for … Read more