View Brian Mansell's profile
MySQL: EXPLAIN EXTENDED
2007-11-08
In this older article, Peter Zaitsev (a MySQL guru) explains a somewhat rarely discussed feature introduced in MySQL 4.1 known as the Extended Explain.
One nice feature added for EXPLAIN statement in MySQL 4.1 is EXTENDED keyword which provides you with some helpful additional information on query optimization. It should be used together with SHOW WARNINGS to get information about how query looks after transformation as well as what other notes optimizer may wish to tell us. It is best seen by example...
Labels: mysql
posted by Brian Mansell @ 8:39:00 AM,
, links to this post
It's all in the name
2007-10-05
When designing a schema for a database, always remember to be consistent with naming conventions, especially:
- Primary Keys
- Foreign Keys
- Boolean fields
- Date fields
Lastly, keep your names case consistent. Databases you may be working with are most likely case sensitive to table names and or column names.
This all has recently come to light given some assignments that have graced my plate in recent weeks.
posted by Brian Mansell @ 3:29:00 PM,
, links to this post
MySQL Tip: Using BETWEEN rather than LIMIT
2007-05-08
I've found that when working with a large data set in MySQL (InnoDB) that uses an integer primary key, it can be easier to query a batch of rows from the set using the BETWEEN comparison rather than the LIMIT clause.
The LIMIT clause is helpful in selecting the first X number of rows from within a table, but when selecting X number of rows after Y number rows (e.g. LIMIT 4000000,20000) it can potentially take a very long time to generate as the database is forced to iterate through the first 4000000 before it gets to the batch of 20000 it needs. On the other hand using a BETWEEN comparison (e.g. WHERE id BETWEEN 4000000 AND 4019999) quickly utilizes the integer primary key index to find the rows relevant to the batch.
Note: This method does not insure consistent batch sizes if the tables are fragmented by row deletes.
Test Results
mysql> select id FROM table_a WHERE id BETWEEN 4000000 AND 4019999;
20000 rows in set (10.51 sec)
mysql> select id FROM table_a LIMIT 4000000,20000;
20000 rows in set (1 min 5.19 sec)As you can see the query time of the BETWEEN example is 84% that of the LIMIT example.
Labels: databases, innodb, mysql, optimization, sql
posted by Brian Mansell @ 6:51:00 PM,
, links to this post