Table Design and MySQL Index Details - The Diigo Meta page

• 25 Feb 14
  

  sarfraznawaz2005
  • Seven seconds is bad for a query this simple, as is examining 45,000 rows to return 2. Furthermore, this  query was logged over 4,000 times in one day, meaning MySQL spent about 28,000 seconds a day on this query,  or 7 hours.
  • the date column should not be type TEXT. What  complicates the issue more is that the data in the date column is not normalized. That is, there are  different representations of data (dates) in the column: Some dates are typical YYYY-MM-DD format  and others are Unix timestamps. The first issue this creates is increased code complexity: Having to account  for data that may be in different formats. Secondly, it's space inefficient. A date as TEXT will require 10 + 2  bytes or, times 45,000 rows, 540k.
  • As a  DATE column type, 3 bytes or 135k.
  • How to index a date column as TEXT. Indexes on TEXT columns require  a prefix length; that is, since TEXT columns are truly variable you have to tell MySQL how much of it you want  to index. In this case, since the data is not normalized and everything counts down to the last second for  dates and times, we would have to index 10 bytes, another 450k for the index (minus compression).
  • full table scan (type: ALL) plus a filesort (caused by ORDER BY).
  • no indexes MySQL can use (possible_keys: NULL).
  • No table scan and only one row examined. However, Extra doesn't say "Using index" even though our  multi-column key includes both game_id and date.
  • MySQL won't retrieve the values from the  index because "SELECT * " requires more values: It requires all 6 columns for every matching row but the index  only has 2 columns.
  • In Case 1 only columns that were part of the key were included in the SELECT criteria,  which is why things worked out better there than here.
  • The filesort is caused by date being TEXT.
  • And it goes to show that using efficient column types is important, unless you like  filesorts.
  • but simply adding an index like we  usually do isn't so easy anymore because there's multiple conditions.
  • We would like MySQL to use an index to find matching rows (avoid a table scan) but this won't be possible  given the OR conditions.
  • For it to be possible, the conditions have to form a leftmost prefix  of an index.
  • In other words, an index can't start with game_id OR other_id.  Therefore, MySQL 4.x cannot use an index with this query. We're left with two options: Reduce the query even  further or use MySQL 5.x.

  13 more annotations...

• 23 Dec 11
  

  張 旭

  mysql database

• 27 Sep 10
  

  zeroeth

  mysql indexing optimization performance sql

• 28 Jul 10
  

  Jamie Adams

  programming sql mysql database

• 09 Dec 09
  

  Alexey Gusev

  webdev mysql optimization indexing

• 02 Sep 09
  

  Michael Simborg

  mysql optimization database performance design indexing sql tips DEV FED IT UX UXD IA

• 26 Aug 09
  

  Eran Toch

  index mysql database

• 29 Jun 09
  

  Joel Liu

  mysql index design

• 02 Apr 09
  

  sudhirp

  mysql optimization performance design

• 16 Feb 09
  

  database mysql optimization sql

• 12 Nov 08
  

  Alan

  programming design tips dev learning database table SQL index MySQL optimization performance indexing optimizing

• 13 Jun 08
  

  vasillis vontikakis

  Table Design and MySQL Index Details

  mysql database

• 12 Jun 08
  

  Xerophyte Penta

  mysql

• 16 Oct 07
  

  Jan Nordgreen

  imported Bookmarks Bookmark_Bar programming mysql

Would you like to comment?

Join Diigo for a free account, or sign in if you are already a member.