On Thu, 2004-11-11 at 15:45, Jigal van Hemert wrote: > `price` is still in the ORDER BY, so removing it only from the WHERE clause > will not help really.
No the query I ran didn't have an order by clause (sorry if the one I pasted did..) > - create an INDEX on the columns in the WHERE clause _and_ ORDER BY / GROUP > BY, etc. This is the only way to ensure that all data is retrieved using an > INDEX OK, ta. > - the DESC direction will be slower than ASC (but you'll probably need it > anyway) Ah right, I do need that but I will set the default to ASC > - the extra speed you could gain from the LIMIT will be removed by the > SQL_CALC_FOUND_ROWS option. This option makes sure that the query will be > executed as if the limit was not present to calculate the number of rows in > the entire result set. This is really for formating on the site and I suppose isn't really needed, I used to run 2 queries until I found that option. I use it to work out page numbers though, but for the beta search until I can prove I can make money out of this I will remove it. > - run OPTIMIZE TABLE regularly to help MySQL optimize execution paths; the > cardinality of the indexes are used to optimize the execution path. I have only done about 160-170 queries since I ran: myisamchk -rq --sort-index --analyze On the table, would this make any difference so soon? > All sites where huge result sets are possible will limit the set no matter > what. Sites like google _estimate_ the number of results. > You could also retrieve the id's of the desired records (with a maximum of > say 500 records) and store the id's in e.g. session data. Paging through the > results will only require you to retrieve the data of 10 or 20 records at a > time. You've lost me there, its late in the working day so that might be the problem ;-) Cheers John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
