what is the actual query that is giving you trouble?

This is an example:

SELECT ad_id, title, pic, datetime, items.item AS items
FROM announce
LEFT JOIN cities ON cities.id = announce.city
LEFT JOIN items ON items.id = announce.category
WHERE TYPE IN (
's', 'r', 'e'
)
AND cities.city = 'uk'
ORDER BY datetime DESC
LIMIT 9600 , 50

joins slow it down as well

you're joining three tables together, ordering and using a set to match against for TYPE

my gut says this query will be slow anyway, though you may be able to speed it up with indexes etc

maybe take a look at the option of splitting this single query into multiple queries and benchmarking

Would it be better to not normalize the table and put the actual value for the "city" and the "item" in the announce table so that i just grab them without having to join?

Please answer my question and observe this too:

I did some testing and found out that same query would execute in about 0.15 Sec instead of over 9 Sec just by removing:

ORDER BY datetime DESC

So have you any fast alternative so the table get sorted by datetime without having to use the "order by"?

Thanks in advance

I tweaked in mysql configurations little and it became much faster but i am sure it could be faster so could somebody help me with configuring it better?

My current configurations:

# The MySQL server
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 16M
join_buffer = 1M
max_allowed_packet = 16M
max_connections=300
wait_timeout=100
connect_timeout=10
table_cache = 1024
sort_buffer_size = 10M
net_buffer_length = 16K
read_buffer_size = 10M
read_rnd_buffer_size = 2M
myisam_sort_buffer_size = 64M
query-cache-type = 1
query-cache-size = 32M
query_cache_limit = 1M

I want it to be very fast in sorting but without destroying other options...

Thanks in advance

the not joining may be an option so you are doing selects on a single table

as much as normalizing data is great, some data redundancy can often speed up the system by leaps and bounds

as far as an alternative to sort, well i don't know one off the top of my head

you could look at the speed of sorting using your scripting language, might work, I have a sneaking suspicion though that reading all those rows into an array and sorting, then chopping out what you don't want will be slower, or at least equally slow.

Unfortunately, I fear you are expecting too much from 256MB of RAM.
There's only so much tweaking you can do to your configuration file, and yours looks ok - but bear in mind that the sort buffers and read buffers are PER THREAD, so if you have more than about 10 threads, mysql is going to take all the ram.

Queries involving 300k records really demand a fairly hefty processor and at least 1GB ram for sub-second execution, or you are likely to get disk swapping which will kill performance.

[edited by: FalseDawn at 8:29 pm (utc) on Jan. 7, 2007]

Ok thanks a lot,

Just a technical question, if there is slow query that takes for example 10 seconds on 256 RAM, it could be optimized and everything to go down for example to 1 second, but lets say it wasnt optimized and the RAM was upgraded to 4 Giga how many seconds would that query run through?

Thanks

I've been saying this a lot lately, yet most of the time it seems to be an issue. So I will say it again: check that your tables are properly indexed and you might even try rebuilding the indexes if it's been some time since they were originally indexed.

They are indexed and going faster now!

But i still want to know about the effect of higher amount of RAMs on slow queries..

Thanks

Increasing memory will only help with query speed if you are swapping to disk due to MySQL having insufficient RAM allocated to perform its duties entirely in memory.

If you are not swapping, you will need a faster processor to speed your queries up, all other things being equal.

Looking at your MySQL "runtime information" (phpMyAdmin) should help locate any issues.