… USER_STATISTICS. The first two can interest anyone …the database. Having many different users running their queries…strategic decisions. Sample outputs: mysql> SHOW USER_STATISTICS LIKE ‘hosting2′\G …SELECT query transformations, joins and better filtering for the statistics Also combining…
Comment: ORDER BY ... LIMIT Performance Optimization
September 15, 2007 By 100 Comments
… the fastest. http://jan.kneschke.de/projects/mysql/order-by-rand a.id This brings … is generated inside the query resulting with two different random numbers. This can be overcome by …my choice was to combine the ORDER BY RAND() and the other one. SELECT * FROM (SELECT * FROM comments WHERE count…
Post: MySQL Error Message Nonsenses
March 16, 2008 By 7 Comments
…by but little details: mysql> select * from user oder by pwd…MySQL errors and in some cases error code may have two…MySQL error code 124: Wrong index given to function The same code instead of explanation is often seen in other combinations…. When we tested different transactional storage engines …
Post: Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 8 Comments
… add all combinations of restrictions and order by to be fully indexed. An extra problem comes from the fact MySQL prefers when… really selective it is likely it will need to scan less than 100 rows to generate result. The speed difference between these two example queries is about 100 times so it may be quite serious. To fix this issue MySQL would…
Comment: How to find wrong indexing with glance view
August 23, 2008 By 27 Comments
…’s always more selective than any of its columns alone. If you don’t have such compound index then mysql most probably… one is better for all combinations of (male, female, both) and different ranges on Age with high selectivity and low. At the end…, LookingFor) and (City, Gender, Age, Available). So mysql can choose between two which is more selective. 3) You can even use (City, Gender…
Post: Efficient Boolean value storage for Innodb Tables
April 23, 2008 By 6 Comments
…could use BIT(1) fields which get combined together for efficient storage: CREATE TABLE `bbool` ( `…one of two values – NULL or Empty String. Lets see how these 3 different table …different query based on parameter (IS ” would not work) or you can use Null-Aware comparison operator: mysql> select…
Post: Is it query which needs to be optimized ?
March 6, 2007 By 9 Comments
…ve simply got to use different solutions, such as summary table. With MySQL 5.0 it is …sort of profile browsing at social networking site – SELECT COUNT(*) FROM profiles where STATE_ID=12 …two queries, because it will not be able to use covering index to compute the rows. If the number of combinations…
