Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New Coder
    Join Date
    Jan 2013
    Posts
    17
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Query Optimisation

    Hi Everyone

    I would be very grateful for a bit of assistance with optimising a query. The code is as follows:

    CREATE TABLE table_name (

    Code:
    SELECT T1.*
    FROM table1 AS T1,
    (SELECT column1, column2, COUNT(column1) AS cnt
    FROM table1
    GROUP BY column1
    HAVING cnt>1
    ) AS T2
    
    WHERE T1.column1 = T2.column1
    AND T1.column2 = T2.column2
    ORDER BY T1.column1
    The query runs but times out even though column1 and column2 are both index columns.

    Any suggestions would be most helpful.

    Thanks in advance.

  • #2
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,620
    Thanks
    0
    Thanked 645 Times in 635 Posts
    Run an EXPLAIN on the query to find out whether the indexes are actually being used.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •