MySQL Forums
Forum List  »  Stored Procedures

Re: Query with variables very slow
Posted by: Rick James
Date: January 31, 2012 03:20AM

It's difficult to follow -- is "key" == "field4"? That is, you are saying that
SELECT * FROM mytable WHERE field4=proc_parameter;
is slower than
SELECT * FROM mytable WHERE field4='MYVALUE';

Given
PRIMARY KEY (`field1`,`field2`,`field3`,`field4`),
and
KEY `txfield3` (`field4`),
?

The PRIMARY KEY is useless for that SELECT.
The secondary key I listed _should_ be used to good effect for either SELECT.

However, ... If 'MYVALUE' occurs in more than something like 20% of the rows of the table, it will ignore the index and do a table scan instead. Please provide SHOW INDEXES FROM mytable; It may provide some useful information. (Please make it clear which index has field4 in it.)
I'm thinking that if the cardinality is poor, the SP will always do a table scan.

As for cache sizes -- it depends on RAM size, as the document explains (I thought).

Options: ReplyQuote


Subject
Views
Written By
Posted
2412
January 27, 2012 10:38PM
1158
January 28, 2012 06:41PM
2160
January 28, 2012 10:16PM
1249
January 29, 2012 07:09PM
1268
January 30, 2012 02:18AM
1271
January 30, 2012 03:39AM
Re: Query with variables very slow
1118
January 31, 2012 03:20AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.