Re: Query optimization help
First, you might want to move the WHERE...t3.int_a = some integer condition into the join condition for t3. Your not using anything from t4, so I'm not sure why you have that table in your query. You can suggest or force mysql to use an index if it's using the wrong one: http://dev.mysql.com/doc/refman/5.0/en/index-hints.html On very rare occasions I've had to do this. What's happening is that mysql is analyzing the information it has about the data and indexes and coming to the wrong conclusion, perhaps even opting for an entire table scan. You can run ANALYZE TABLE to force mysql to update the information it has about the data. This may actually solve your problem. Try SHOW INDEX FROM t1 to see what data mysql has about the indexes. Sometimes the CARDINALITY (uniqueness) column will be null which can indicate a problem. Posting the result of your EXPLAIN will actually be helpful. Hope that helps. Brent Baisley On Aug 11, 2008, at 8:26 AM, Jonathan Terhorst wrote: I have this query: SELECT DISTINCT t1.string FROM t1 LEFT JOIN t2 ON t1.string=t2.string LEFT JOIN t3 ON t1.int_a=t3.int_a LEFT JOIN t4 ON t1.int_b=t4.int_b WHERE t1.string != '' AND t2.string IS NULL AND t3.int_a = some integer ORDER BY t1.string ASC This query is executing slower than it should. EXPLAIN has it using temporary and using filesort. I have indexes on every column in the query, but I think the problem is the one-index-per-table limitation. According to EXPLAIN, there are two possible indices in use for t1 (int_a and string), but only int_a is being used. So I tried constructing a compound index on int_a and string. Although this new index appears in possible_keys, EXPLAIN still shows the key actually being used as int_a. I tried building the compound key in both orders and had the same results. How do get mysql to all possible keys on t1 when running the query? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query optimization help
I have this query: SELECT DISTINCT t1.string FROM t1 LEFT JOIN t2 ON t1.string=t2.string LEFT JOIN t3 ON t1.int_a=t3.int_a LEFT JOIN t4 ON t1.int_b=t4.int_b WHERE t1.string != '' AND t2.string IS NULL AND t3.int_a = some integer ORDER BY t1.string ASC This query is executing slower than it should. EXPLAIN has it using temporary and using filesort. I have indexes on every column in the query, but I think the problem is the one-index-per-table limitation. According to EXPLAIN, there are two possible indices in use for t1 (int_a and string), but only int_a is being used. So I tried constructing a compound index on int_a and string. Although this new index appears in possible_keys, EXPLAIN still shows the key actually being used as int_a. I tried building the compound key in both orders and had the same results. How do get mysql to all possible keys on t1 when running the query? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query optimization help needed
I asked for help with a version of this query a few months
ago, and subsequently made some improvements to it, and also
fooled around with other parts of my app that were in need of
tuning. I've since done some more extensive benchmarking and
realized that this query really is somewhat slow. Even though
the data set is rather small and everything is (I think)
properly indexed and the joins are sensible, I can't seem to
get rid of the using temporary and using filesort in my
EXPLAIN. I'd be grateful for any suggestions for improving
this.
Here's the query (obviously I run it with different values for
subject.name and different LIMIT values, but this is
representative):
SELECT citation.*, DATE_FORMAT(citation.last_modified, '%e %M, %Y') AS
last_modified
FROM citation, subject, citation_subject
WHERE subject.name = 'History'
AND citation_subject.subject_id = subject.id
AND citation_subject.citation_id = citation.id
AND citation.deleted = 0
ORDER BY citation.stripped_word, FIELD(citation.part_of_speech, 'NOUN',
'ADJECTIVE', 'ADVERB', 'VERB'), citation.id
LIMIT 150, 50
and EXPLAIN gives me this:
*** 1. row ***
table: subject
type: ref
possible_keys: PRIMARY,name
key: name
key_len: 50
ref: const
rows: 1
Extra: Using where; Using temporary; Using filesort
*** 2. row ***
table: citation_subject
type: ref
possible_keys: citation_id,subject_id
key: subject_id
key_len: 4
ref: subject.id
rows: 169
Extra: Using index
*** 3. row ***
table: citation
type: eq_ref
possible_keys: PRIMARY,deleted
key: PRIMARY
key_len: 4
ref: citation_subject.citation_id
rows: 1
Extra: Using where
Finally, here are the three tables involved. I've trimmed out the
irrelevant columns:
CREATE TABLE `citation` (
`id` int(11) NOT NULL auto_increment,
`word` varchar(50) NOT NULL default '',
`stripped_word` varchar(50) default NULL,
`part_of_speech` enum('NOUN','VERB','ADJECTIVE','ADVERB') NOT NULL default
'NOUN',
`last_modified` timestamp(14) NOT NULL,
`deleted` datetime default '-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `deleted` (`deleted`),
KEY `word` (`word`),
KEY `stripped_word` (`stripped_word`)
) TYPE=MyISAM
CREATE TABLE `citation_subject` (
`id` int(11) NOT NULL auto_increment,
`citation_id` int(11) NOT NULL default '0',
`subject_id` int(11) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `citation_id` (`citation_id`,`subject_id`),
KEY `subject_id` (`subject_id`,`citation_id`)
) TYPE=MyISAM
CREATE TABLE `subject` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(50) NOT NULL default '',
`deleted` datetime default NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`)
) TYPE=MyISAM
Thank you for any suggestions.
Jesse Sheidlower
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query optimization help
Chuck Gadd wrote: I've got a query that I can't seem to get optimized, so I'm hoping someone here can spot something I've missing! Table has three columns: CoordID int unsigned, Zip_Lo char(9), Zip_Hi char(9) Table has 3 million records indexes: acg_lo (Zip_Lo) acg_hi (Zip_Hi) acg_combined (Zip_Lo, Zip_Hi) Here's the query: select * from acg where zip4_lo_pot = '80128' and zip4_hi_pot = '80128' Explain shows: type: ALL possible keys: acg_lo,acg_hi,acg_combined rows: 3022309 extra: Using where This kind of query cannot be efficiently optimized on a pre-4.1 version. With 4.1, if you are using MyISAM tables you could make (zip4_lo_pot,zip4_hi_pot) a spatial column with a spatial index. See http://www.mysql.com/doc/en/Spatial_extensions_in_MySQL.html -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query optimization help
Without seeing the data I am assuming that you are going over the 30% threshold with your less/greater equal to where clauses. What sort of criteria are you asking the database engine to search for? Original Message On 2/25/04, 9:44:02 PM, [EMAIL PROTECTED] wrote regarding Re: Query optimization help: Maybe i'm wrong here, someone correct me, if its just int's you are gonna use set the field types to bigint it may search faster you are doing a character search, to get there quicker in a text search scenerio i'd suggest mysql4 and full text searching MATCH AGAINST I've got a query that I can't seem to get optimized, so I'm hoping someone here can spot something I've missing! Table has three columns: CoordID int unsigned, Zip_Lo char(9), Zip_Hi char(9) Table has 3 million records indexes: acg_lo (Zip_Lo) acg_hi (Zip_Hi) acg_combined (Zip_Lo, Zip_Hi) Here's the query: select * from acg where zip4_lo_pot = '80128' and zip4_hi_pot = '80128' Explain shows: type: ALL possible keys: acg_lo,acg_hi,acg_combined rows: 3022309 extra: Using where So, how can I optimize this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query optimization help
I've got a query that I can't seem to get optimized, so I'm hoping someone here can spot something I've missing! Table has three columns: CoordID int unsigned, Zip_Lo char(9), Zip_Hi char(9) Table has 3 million records indexes: acg_lo (Zip_Lo) acg_hi (Zip_Hi) acg_combined (Zip_Lo, Zip_Hi) Here's the query: select * from acg where zip4_lo_pot = '80128' and zip4_hi_pot = '80128' Explain shows: type: ALL possible keys: acg_lo,acg_hi,acg_combined rows: 3022309 extra: Using where So, how can I optimize this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query optimization help
Maybe i'm wrong here, someone correct me, if its just int's you are gonna use set the field types to bigint it may search faster you are doing a character search, to get there quicker in a text search scenerio i'd suggest mysql4 and full text searching MATCH AGAINST I've got a query that I can't seem to get optimized, so I'm hoping someone here can spot something I've missing! Table has three columns: CoordID int unsigned, Zip_Lo char(9), Zip_Hi char(9) Table has 3 million records indexes: acg_lo (Zip_Lo) acg_hi (Zip_Hi) acg_combined (Zip_Lo, Zip_Hi) Here's the query: select * from acg where zip4_lo_pot = '80128' and zip4_hi_pot = '80128' Explain shows: type: ALL possible keys: acg_lo,acg_hi,acg_combined rows: 3022309 extra: Using where So, how can I optimize this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
