Solved

Very slow MySQL query

Posted on 2011-03-03
8
327 Views
Last Modified: 2013-12-13
I am writing a fairly simple web application in PHP which sits on a pre-existing MySQL database which belongs to my client.  It is to replace an existing application which will soon shut down.

One of the queries runs very slowly indeed - I haven't benchmarked it, but we're talking seconds, a really noticeable gap.  But the existing application, which works in a very similar way, does not have this delay.

I know nothing about optimising SQL queries (in MySQL or anywhere else).  Can anyone help me?

The query is:

SELECT `fid`, `enid`, `label`, `activityname`, `date`, `aid_FK`, `extension`
FROM first INNER JOIN second INNER JOIN third
ON first.fid = second.fid_FK AND second.aid_FK = third.aid
WHERE `mem_no_FK` = $var
ORDER BY `date`

Details of the tables are attached as a file.

It may be relevant that table 'first' holds user files - WP documents, spreadsheets, etc. - in its `data` field.

question-1.txt
0
Comment
Question by:hnmcc
  • 4
  • 4
8 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35025872
can you post the EXPLAIN plan for the query, please?
0
 

Author Comment

by:hnmcc
ID: 35025961
EXPLAIN plan attached (rows don't line up here). question-2.txt
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35026029
>WHERE `mem_no_FK` = 2353
do you have an index on that field (presuming it's from "first" table)?
if not: create one, and repost the explain plan then (if you are not happy with the new execution time)

also, I suggest you change the SQL a bit to increase readablity (and eventually query execution):
* add table aliases to your tables, and use them on ALL the columns (see the code below, I had to guess, though)
* put the JOIN conditions just below/beside the actual join
SELECT f.`fid`, `f.enid`, s.`label`, s.`activityname`, f.`date`, s.`aid_FK`, t.`extension`
FROM first f
INNER JOIN second s
  ON f.fid = s.fid_FK 
INNER JOIN third t
  ON s.aid_FK = t.aid
WHERE f.`mem_no_FK` = $var
ORDER BY f.`date`

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:hnmcc
ID: 35095867
OK (sorry for delay in responding - I've been away).  I've improved the naming for clarity's sake, and stripped away all the fake names (too boring...).  This is the query:

SELECT f.`fid`, f.`label`, f.`extension`, u.`enid`, a.`activityname`, a.`date` FROM files f JOIN user_entries u ON f.`fid` = u.`fid_FK` JOIN activities a ON u.`aid_FK` = a.`aid` WHERE f.`mem_no_FK` = $var ORDER BY a.`date`

Open in new window


There is a key in files on `mem_no_FK` (B-tree, not unique).

As the rows in files are each in a 1-to-1 relationship with a row in user_entries, would it be better to insert the contents of files into user_entries?  All rows in files have a corresponding row in user_entries, but most rows in user_entries have no corresponding row in files.

Unfortunately, after adding the necessary columns to a copy of user_entries, my attempt to copy the data across fails:
#1054 - Unknown column 'user_entries_x.fid_FK' in 'where clause'
INSERT INTO user_entries_x (`filename`, `extension`, `data`, `size`, `uploaded`, `edited`, `label`) SELECT `filename`, `extension`, `data`, `size`, `created`, `modified`, `label` FROM files WHERE files.`fid` = user_entries_x.`fid_FK`

Open in new window

0
 

Author Comment

by:hnmcc
ID: 35095908
EXPLAIN plan attached. SQL-result---phpMyAdmin-3-3-2deb.pdf
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 35100606
I would say you don't have a index on files.mem_no_fk field yet
and you don't have an index on user_entries.fid_fk field

or, the fields used in the join don't have the same data type/specs

in regards to the insert: you actually would want to "UPDATE", not to INSERT .
if you would like to end up to perform the update anyhow:
http://www.experts-exchange.com/A_1517.html
0
 

Author Comment

by:hnmcc
ID: 35107726
There is (and has been since I inherited the database) an index on files.mem_no_FK.  I've now added one to user_entries.fid_fk as you suggest, and checked the join fields for compatibility.  They were all the same data types, but in each case the original was unsigned and the matching FK wasn't.  Does that matter? I changed them anyway.

Altogether this improves the query's performance considerably: c.15s to 0.2s - which is good enough for the requirement.

Thanks for your help.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35107729
excellent :)
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question