473,372 Members | 1,067 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

Trying to optimize a MySQL query.

Okay, well what my query looks like is:

Expand|Select|Wrap|Line Numbers
  1. SELECT id, title FROM myTable LIMIT 0, 500 
This query is taking a long time and changes in time each time i run it, from 0-10 seconds.

And I don't see any reason behind it. id is set as my PRIMARY index.

The table has 132,000 rows, but I don't think it should be taking this long. Do you think the server is just slow? Or do you see a problem with my SQL query?

Thanks for any response. Also if you think it's a problem with my server, could you recommend a database host that would be faster?
Feb 18 '07 #1
5 1923
5,058 Expert 4TB

There is nothing wrong with that query.
It is possible that your server is slow, or that your connection is slow.

I'm using a server from bluehost.com that works pretty well and doesn't cost to much.
Feb 18 '07 #2
I don't think it's a connection problem, because when running it from just phpmyadmin I get...

Showing rows 0 - 499 (500 total, Query took 10.9797 sec)

If I use EXPLAIN I'm getting

id select_type table type possible_keys key key_len ref rows Extra

(Sorry that didn't come out well) but it says it's looking through all 132000 rows which seems weird?

What I don't understand is if I run some of my other queries, that are just a simple SELECT * FROM myTable WHERE id=5 they take 1/100 of a second. I mean I can count the first 500 rows and return them faster than my database is doing it, does it matter about the other data in the row? I think it may be because my host uses a shared mysql environment, but I think it has to do with my query, since other queries are performed fine. Any more help would be great.


This query takes less than a second

SELECT * FROM `myTable` WHERE id <= 500

I think there is a problem with the LIMIT?
Feb 18 '07 #3
Okay I'm just gonna use the < and <= things because LIMIT seems to suck horribly. If anyone can tell me a reason behind this please let me know.

And thanks atli for responding =].
Feb 18 '07 #4
5,058 Expert 4TB
I've check this out and it appearers that the LIMIT clause suffers from performance issues.

From what I've read, queries that have both the index and offset set will cause a full scan of the table.

Lets say we have a table with 100,000 entries.
This query will read the first 50 entries and then stop, finishing quickly.
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM mytable LIMIT 50
While this one will go through the entire table, and throw away the first 99,950 records before returning the final 50, which in large tables, will run slowly.
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM mytable LIMIT 99950 50

So I believe it is possible to rewrite your query and avoid the performance issue like so.
Expand|Select|Wrap|Line Numbers
  1. SELECT id, title FROM mytable LIMIT 500
Btw. If you use the WHILE statement like you posted, if you have or will ever delete a record with a id less than 500, that query wont return 500 records.
Feb 18 '07 #5
Well, the reason I used the LIMIT 0, 500 is because sometimes I start at 500, 500
1000, 500 etc. So the LIMIT was really causing a performance strain. It seems to be okay now, I'll just try not to delete any rows =] (hopefully).
Feb 19 '07 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

by: jy2003 | last post by:
I have read a book, which suggests we should change OR to UNION for better performance, but currently I have too many OR clauses(I have a query with 100 ORs) and it does not sound good to have 100...
by: Andreas Falck | last post by:
Hi, I ran the code below on two different versions, 4.0.14 and 4.0.16 respectively, both running RH 7.3 on intel. In version mysql server version 4.0.14 the SELECT privelege suffices for...
by: Denis St-Michel | last post by:
Hello All, Hope some Guru will be able to help me with this. Let's take this example table A ------------------------------------------------------------------------------- id | ...
by: Bruce D | last post by:
Could someone please help to explain why the following query isn't using the index... explain select id from kbm where state = 'MA' table type possible_keys key key_len ref rows Extra...
by: Huaer.XC | last post by:
>From the following MySQL command: EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id) JOIN t3 ON t3.name = t1.name WHERE t1.id IN(123, 124); which result is:...
by: kshirsagar007 | last post by:
friends, I would like to optimize the following query....as its taking 2 minutes to get the records. select a.CODE "Code", ud.Name "Name", a.SchemeId "SchemeID" from (MASTER sh,...
by: bravo | last post by:
hi i need to optimize the join query, which joins three tables say table1 ,table2 , table3 each having huge volume of records... the query is as select table1.id,table2.time,table3.Status from...
by: acornejo | last post by:
Hi All I've the following code I need to optimize. Currently tblOutgoing is about 250K registers and growing at a rate of about 20k records per day. This code takes me over 5 secs to run on each...
by: zabsmarty | last post by:
Can any one help me to make my query code optimize and load faster. Please help me or any example what steps should we use to optimize. Thank You
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.