1

We are running MySQL 5.6.15. We have a website that gets data from the database to render every page. We have an issue where once a large query is started in one of the pages, (eg; some pages have calculation tools that start large queries that populate temp tables etc...), all the other pages can not respond until the query completes.

Is it possible in MySQL to have a large query start, and while it is running execute smaller queries and have them complete before the large one does?

Other details:

  • The website uses one account to connect the db for all requests.
  • The connection to the db is cached for the life-cycle of the page, and a new connection is made for every page instance.
  • There is only a small number of people ever going to be using the site as it is not on a public site.

Regards,

Scott

user2109254
  • 1,709
  • 2
  • 30
  • 49
  • What language/API are you using to connect to MySQL? – Tim Biegeleisen May 29 '15 at 04:31
  • 1
    In short, yes. If you are making the queries on a single connection they would of course be sequential, but that is an unlikely scenario. – Bohemian May 29 '15 at 04:32
  • If you have multiple cores, it is likely that your fast-running query will zip past the slow-running query by using an available core - assuming that both queries are sent through separate connections (like Bohemian mentions) – zedfoxus May 29 '15 at 04:35

1 Answers1

1

It sounds like your longer-running query may be locking the tables, making it so that other queries cannot read from those tables until the lock is released. There is a way around this. See https://stackoverflow.com/a/918092/259457:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM TABLE_NAME;
COMMIT;

You would do this on your shorter-running queries. This will ignore any locks that have been placed on the table.

However, you must be aware that this will case a "dirty read," meaning you could be selecting information from the table that is not absolutely current (it should be as current as when the lock was created on the table). Usually this isn't a problem, but it's something to consider.

Community
  • 1
  • 1
Travesty3
  • 14,351
  • 6
  • 61
  • 98