MySQL slow queries

Ok, I spent the last two days reading bunch of articles about MySQL database optimization on the internet in order to address this issue more efficiently. Honestly, it was not easy at all, but the effort definitely paid off since the slowness is gone.

Thanks guys, I couldn't do it without you Here is what I came up with mostly because of your suggestions that were more than helpful and absolutely in place. yeah, I can see that now.

- VARCHAR sizes have been reduced by 70 percent
- date is, well DATE now
- I realized that the four columns can be completely removed, one has already  been  deleted, one, as Owain North suggested will be moved to a separate table while the remaining two will be deleted as soon as possible given the number of pages on which the code must be adjusted first. This should improve the overal performance a bit more.
- 3 indexes have been added. Adding indexes noticeably reduced the number of scanned rows.
- I will never again use a freaking star (*) on my keybord
- LIMITs have been added in queries where only 10 records should be displayed

anyway, check out the timings

Runtime: 172ms          Runtime: 188ms     Runtime: 322ms
Runtime: 0ms            Runtime: 31ms      Runtime: 78ms
Runtime: 0ms            Runtime: 0ms       Runtime: 16ms
Runtime: 16ms           Runtime: 18ms      Runtime: 0ms
Runtime: 16ms           Runtime: 0ms       Runtime: 0ms

after these modifications, fully equipted page with CSS, javascript etc. takes only 3 seconds to load which is a significant improvement over initial 8-9

What happens when you

1) create an index on category for each of the tables maintable, news and tuts

2) get data from existing result set by means of query of a query, as follows

<cfquery name="data" datasource="datasource">
SELECT *
FROM maintable
WHERE CATEGORY = "1"
</cfquery>

<cfquery name="dataByDate" dbtype="query">
SELECT *
FROM data
ORDER BY DATE DESC , id DESC
</cfquery>

<cfquery name="free" dbtype="query">
SELECT *
FROM data
WHERE buy = "Free"
ORDER BY HITS DESC
</cfquery>

<cfquery name="buy" dbtype="query">
SELECT *
FROM data
WHERE buy = "withprice"
ORDER BY HITS DESC
</cfquery>

I think you are hurting yourself by only supplying us with the bare minimum of information and forgetting important things like that there are cfqueryparams involved and that you only really need 10 records. Why don't you just enable debugging on your CF server and post a dump of the debug output here, together with the actual code?

Further, your design is hurting you. You say 11000 records is 20 MB, so 8000 records is 16 MB. Over a 100 Mbit/s network that takes 1.5 seconds just to transport from MySQL to CF. If you need to display 10 records, then download 10 records from the database to CF. An index on category is not going to help you since it is not selective enough. A composite index on category and buy may help, but only if you add hits to it as well so it helps to avoid the sort that has to happen for your free and buy queries.

In addition to Adam's answer, ask yourself the following questions:

Do you really need all those records every single time, or are there further constraints you can add to your where clause?

What are you doing with the data after you get it?

Why are you only selecting from one table at a time?

You should experiment with the indexes anyhow.  And also consider indexing the cols you're ordering on.

Also, and most importantly, don't use SELECT * unless you really really want every column to be returned.  If not, specify which columns you want.

Are categories always numeric values?  If so, making the column a numeric (like a LONG, probably) would improve things a bit.

How do these queries perform if you run them in a dedicated MySQL client tool?  Is it definitely the queries that are bogging you down?

Is there anything on MySQL which will do an explain path for you, to see if there's any elements of the query execution which is slow?

--

Adam