Waspman
Posts: 948
|
| Posted: 05/26/2011, 12:49 PM |
|
After all the apps I've developed I'm suddenly getting messages from the host telling me my queries are too slow?
I've done nothing any different, this is the query...
SELECT cusrecID, customers.cusID AS customers_cusID, cus_name, updated_date, managed_by, Fname, lname, locID
FROM (customers LEFT JOIN users ON
customers.managed_by = users.userID) LEFT JOIN customer_locations ON
customers.cusID = customer_locations.cusID
WHERE customer_locations.postcode LIKE '{s_keyword}%'
OR customers.cus_name LIKE '%{s_keyword}%'
Is it slow, I used the query builder to generate this.
Any ideas?
_________________
http://www.waspmedia.co.uk |
 |
 |
jjrjr2
Posts: 131
|
| Posted: 05/27/2011, 12:14 AM |
|
Hi
On your server or a new client's server?
Have U ever installed an app on this server before???
Is the server this is happening on running other apps hitting the DB with no probs???
John
_________________
John Real - More CodeCharge Studio Support at - http://CCSElite.com
Real Web Development At: http://RealWebDevelopment.us |
|
|
|
Waspman
Posts: 948
|
| Posted: 05/27/2011, 12:45 AM |
|
It's a shared server,
Yeah, used this server for loads of apps.
Never on a db with 7K records though.
I'm not running any apps, but it's shared?
Thanks, John
_________________
http://www.waspmedia.co.uk |
|
|
|
ReneS
Posts: 225
|
| Posted: 05/27/2011, 5:54 AM |
|
Hi,
7K records? to be sure, that is 7000? Then it most probably is a server problem and not your query.
7000 should never be a problem with the query you are running, to any database...in my opinion.
But you can always check your index etc. to try and speed things up.
Rene
|
|
|
|
Waspman
|
| Posted: 05/27/2011, 7:08 AM |
|
_________________
http://www.waspmedia.co.uk
---------------------------------------
Sent from YesSoftware forum
http://forums.codecharge.com/
|
|
|
|
Waspman
Posts: 948
|
| Posted: 05/27/2011, 7:09 AM |
|
It was me 
I create a unique id for each customer record and join the location and people tables to it.
It looks like this...TLT00000923060
Obviously not a good idea 
Ya live'n' learn
_________________
http://www.waspmedia.co.uk |
|
|
|
mamboBROWN
Posts: 1713
|
| Posted: 05/27/2011, 12:49 PM |
|
@Waspman
You might want to also consider doing an analysis and stress test (with a high number of records) of the database. I ran into a situation with an application that I built (for a client) that initially ran very fast. After a couple of years usage the client notified me that the application was reacting very slow (it had over 100K records). I did a database analysis and discovered that some of the tables needed an extra index (I added one extra index in 2 tables). Once I made this change to the tables - it had a immediate effect on the queries that were being run by the web application. It even seemed faster then when I first finished the application for the client. From that point forward I try to do an analysis and stress test of the database. Just my two cents....
|
|
|
|
Waspman
Posts: 948
|
| Posted: 05/27/2011, 1:13 PM |
|
Thanks MB, on it already. It's really fast now thanks man:)
_________________
http://www.waspmedia.co.uk |
|
|
|
beevet
Posts: 47
|
| Posted: 05/28/2011, 3:17 AM |
|
MB,
for those of us not in the know on database stress testing, could you tell me what you do to complete such a stress test? Particular software? Particular things to test?
Thanks,
Chris
|
|
|
|
mamboBROWN
Posts: 1713
|
| Posted: 05/29/2011, 8:09 PM |
|
@ beevet
I will post the method that I used to resolve the issue that I mentioned above. It's pretty straight forward. I'll try to post it in the next day or two.
@ Waspman
Don't forget to add resolved in the thread header.
|
|
|
|
mamboBROWN
Posts: 1713
|
| Posted: 06/01/2011, 9:22 PM |
|
Here's What I did....
1. I talked with the customer to see what the problem areas were in the web application.
2. I got a copy of the database and put it in my test environment (I scrambled the data too).
3. I was able to recreate the slow issue and determined that it was not the code that was the problem. The code was executing as expected without any errors but when it made the SQL calls to the database that is where I identified the bottleneck.
4. In the web application, I was capturing the SQL statements that were being used to Create, Update and Delete data in the database (this was a way for the client to see who was making what data changes to the database). I used this information to determine the most commonly used sql statements in the identified problem areas of the web application.
5. I executed the SQL statements using a MySQL gui tool and discovered that most of the select queries were running very slow. I ran the queries again using the EXPLAIN statement to show me how MySQL would execute the SQL statement and to determine if I needed more indexes (in the tables). Sure enough, I did need more indexes. I only had one index per table which more or less happen to be the primary key index, which was not enough. My queries where actually passing through all 100k records every time in order to find a specific record. In simple terms, if a query needed to pass through a table two times to find a record then it read a total of 200K records-that means it read the whole table twice!!!! And that was just to find one record!!!!! Once I added the needed indexes the pass through dropped to the hundreds of records read. This meant that my queries were now blazing fast.
6. After determining which tables needed indexes, I added the indexes and retested the web application (especially in the problem areas) in my test environment. It was like night and day. You would have thought that I had made serious code changes when all I did was add more indexes.
7. I notified the client that I had resolved the issue and set a date to make the necessary changes to the database.
8. After a full backup was completed I made the modifications (to the database) and had the customer to run through the web application.
9. The change was so dramatic that the customer actually thought that the web application was faster now then when I first gave it to them.
10. From this point forward I decided to make sure that I stress test any web application (that I make) with at least 100K records. I am looking forward to getting to the millions of records real soon...
NOTE: When doing a database analysis in most cases it is not this cut and dry (more steps are involved). It just so happend that I had a gut feeling (based on my experience as a database administrator) and decided to try it out first before I do a full on database analysis (which I determined during step 3). The whole process took me a total of 3 hours (to find and fix the problem).
|
|
|
|
