Hi Gleb, Thanks for the quick response. Unfortunately, ANALYZE TABLE does not appear to affect the Cardinality field of a temporary table (it is still NULL, even with rows added). Only by creating the index itself after the insert seems to make the query faster. I also tried FORCE INDEX and it did not make things any faster.
Do you have any idea why creating the index AFTER putting the data in the table affects the performance so dramatically? Allan "Gleb Paharenko" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hello. > > > Why is it that adding an index before or after the addition of data > >makes a difference on SELECT performance? > > > Check whether the problem still exists if you run ANALYZE table after > inserts in case you have already created indexes. FORCE_INDEX in the > SELECT clause should help as well. Have a look here: > http://dev.mysql.com/doc/refman/5.0/en/how-to-avoid-table-scan.html > > > > Allan Miller wrote: > > In MySQL 4.1.11, if you create two temporary tables with non-unique indices > > as part of the CREATE TEMPORARY TABLE statement, then > > insert several hundred (or thousand) rows, a subsequent SELECT using a JOIN > > between the two tables is substantially slower than if > > you create the indices after inserting the rows into the temporary tables. > > When I run a SHOW INDEX for these tables, the only > > difference is the Cardinality field, where it is NULL if the index was > > created prior to any inserts and is a number if the index was > > created after the inserts. > > > > Why is it that adding an index before or after the addition of data makes a > > difference on SELECT performance? > > > > Allan Miller > > > > > > > > > > > -- > For technical support contracts, goto https://order.mysql.com/?ref=ensita > This email is sponsored by Ensita.NET http://www.ensita.net/ > __ ___ ___ ____ __ > / |/ /_ __/ __/ __ \/ / Gleb Paharenko > / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] > /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET > <___/ www.mysql.com > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
