473,320 Members | 1,831 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

How to optimise this query

Hi

I have this query:

SELECT id, name, YEAR(born) AS year
FROM people
WHERE DAYOFMONTH(born) = 7
AND MONTH(born) = 12
ORDER BY year DESC, name

operating on this table:

CREATE TABLE `people` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(100) NOT NULL default '',
`born` date NOT NULL default '0000-00-00',
`died` date NOT NULL default '0000-00-00',
`imdb` int(11) NOT NULL default '0',
`allmusic` varchar(25) NOT NULL default '',
`allmovies` varchar(25) NOT NULL default '',
`uri` varchar(100) NOT NULL default '',
`image` blob NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `imdb` (`imdb`),
KEY `born` (`born`),
KEY `died` (`died`),
FULLTEXT KEY `name_2` (`name`)
) TYPE=MyISAM AUTO_INCREMENT=314541 ;

which has 169,549 rows.

EXPLAIN SELECT shows that it has type ALL, and no possible keys. It
shows 'using where, using filesort'. The query is quite slow.

How could I optimise this query?
Thanks in advance

Jasper Bryant-Greene

Jul 20 '05 #1
1 1866
Jasper Bryant-Greene wrote:
SELECT id, name, YEAR(born) AS year
FROM people
WHERE DAYOFMONTH(born) = 7
AND MONTH(born) = 12
ORDER BY year DESC, name


I think this is a similar problem as trying to use an index for a query
like this:
SELECT ... FROM people WHERE allmusic LIKE '%foo';

The database cannot create an index for an arbitrary trailing substring.
Likewise, it cannot create an index on a calculation of a numeric or
date column. An index can be defined for simply a column name, not an
expression on that column (though the "expression index" is a feature of
some other RDBMS products, e.g. Firebird, so it is in fact possible).

To use an index for this query, you are going to have to store redundant
columns for DAYOFMONTH and MONTH. That is:

ALTER TABLE ADD COLUMN day_of_month_born integer;
ALTER TABLE ADD COLUMN month_born integer;
UPDATE TABLE SET
day_of_month_born = DAYOFMONTH(born),
month_born = MONTH(born);
CREATE INDEX people_domb ON people(day_of_month_born);
CREATE INDEX people_mb ON people(month_born);

Then do your query using the columns that have indexes on them:

SELECT id, name, YEAR(born) AS year
FROM people
WHERE day_of_month_born = 7
AND month_born = 12
ORDER BY year DESC, name

Regards,
Bill K.
Jul 20 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: dr zoidberg | last post by:
Hello, $a = mysql_query("Select a,b FROM t WHERE category=1") while($a) { $x = $a; //some echo //another query $b = mysql_query("Select * FROM t WHERE subcategory=$x") while ($b) {
1
by: ben | last post by:
I have been using a nasty combination of php and mysql to generate a narrow down by attribute bar as seen on the likes of shopping.com. For example a user could select 4X Zoom to narrow down a...
0
by: Wouter | last post by:
Hi, I have a database like this: CREATE TABLE weersgeschiedenis ( WMO enum('06235','06260','06280','06290','06310','06380') NOT NULL default '06235', YYYYMMDD date NOT NULL default...
0
by: Jasper Bryant-Greene | last post by:
Hi I have this query: SELECT id, name, YEAR(born) AS year FROM people WHERE DAYOFMONTH(born) = 7 AND MONTH(born) = 12 ORDER BY year DESC, name
5
by: Robin Tucker | last post by:
I have these 3 queries - they are the same except each fetches record counts for one of 3 different record types, nSubsets (type 0), nAssets (type 1) and nImages (type 2). Is there any way I could...
3
by: Harvey | last post by:
Hi, I try to write an asp query form that lets client search any text-string and display all pages in my web server that contain the text. I have IIS 6.0 on a server 2003. The MSDN site says...
6
by: Driesen via SQLMonster.com | last post by:
Hi guys Is there any way I can run this query faster? Should I take out the ORDER BY clause? This is supposed to return 17,000 rows and takes around 30 minutes or so. Is there no way at all to...
2
by: Hervé Piedvache | last post by:
Hi, I have may be a stupid question, but I'm a little surprised with some explains I have, using date fields ... I would like to understand exactly when index are used ... I'm using...
5
by: Aussie Rules | last post by:
Hi, I have a vb.net 2005 project that has just got slower and slower as I develop. Does anybody know of a code tool to use to to pin point performance problems, and clean up/optimise the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.