christopher.l.hood@stripped wrote:
> Ok, I will be the first to say that I am learning about indexes, however
> it is my understanding that if I have a database with MANY rows and I
> wish my queries to be faster I should index my database. With that being
> said, I have 2 tables in my database that are being queried with a
> single query using a UNION these 2 tables combined are about 9 Million
> records (yes I said million).
>
> My query which is below takes about 1 minute to run, now some people
> would say that this isn't long, however when the 2 tables were sub 5
> million it only took a matter of about 20 seconds to run, so I figure I
> need an index. So I have created an index called "Main" within both
> tables and added 6 columns to that index, most of the columns that are
> used in my query.
>
> Sorry for the long background, but here is the problem, my query DID NOT
> speed up at all. It still takes right at 1 minute per query, so indexing
> didn't buy me anything as far as I can tell.
>
> Can someone tell me how the indexes are supposed to be done ( to ensure
> that I did it correctly) and tell me if they think that it should have
> sped up or if there is a more efficient way to do my query.
>
You should debug (read = investigate) your query using EXPLAIN SELECT
statement. Read http://dev.mysql.com/doc/mysql/en/EXPLAIN.html and perhaps
http://dev.mysql.com/doc/mysql/en/Query_Speed.html
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ Egor.Egorov@stripped
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
