Best way for large mysql queries to load on page?

N

nick-harper

New member
Sep 12, 2008
157
0
0
Coventry, UK
www.smarterwebdesign.co.uk
I have an issue where one of my sites uses mysql queries that sometimes take about 5 seconds and I will sometimes have 2 or 3 of them on a page.

The queries is basically just searching a huge DB but I don't want it to slow the page down as at the minute it waits for the queries before the page loads.

Is the only option ajax? The only bad side to this is that search engines might not see the text being loaded but it would let the rest of the page load and then load the rest afterwards.

Is there a better way to do it other than using ajax?
 


Make sure the DB is normalized and indexed properly. After that is done you could possible make a view to select from. You should also take a look at how your queries are wrote.
 
All of that is pretty much done and it is using full text searching for speed but there are about 11 million records and it is searching with full text using one of the indexes but still sometimes takes a while :( I also wanted a fancy preloader to say "we are getting the items now" so I guess ajax is the only way?
 
When you say sometimes - can you reproduce the behaviour consistently with one specific search but different searches behave differently, or one specific search will behavour differently each time?

Cache on the mySQL server? Does disabling it change the behaviour? How is the db table structure for Indexes etc?
 
It really depends, if it is for something broad like "red" then it takes longer than something specific as there are a lot less results and less work to do.

The table has about 15 columns with just 1 index (the column I am searching / product name)
 
It runs in its own process and you update it when you update your database. If you're running on shared hosting, you're shit outta luck unless you're willing to upgrade to a VPS, an EC2 instance, a dedicated server or something else like that. If, you do in fact have some control over your hosting environment (i.e. can install stuff), then elastic search rocks. Sorting, stemming and all that stuff is included in its vast feature set.
 
I have a dedicated server so that is no issue.

So I would still use my sql database that I have now but link elastic search into it to do the indexing? At the minute all my scripts dump into mySQL on a cron.

Would this suck the items out of mySQL into its own instance?
 
If your queries are taking that long, chances are you aren't doing something right.

Your tables should be optimized for the queries you intend to do.

I suggest making a search table that links keywords to object id's, similar to how a tag web would work. When the object is created or edited, it would index the information then. Now you don't have to run large queries searching by fulltext.

Example:

Products:
productId | name
1 | blue suede shoes
2 | red suede shoes

SearchWords:
wordId | name | count
1 | blue | 1
2 | suede | 2
3 | shoes | 2
4 | red | 1

SearchLinks:
linkId | productId | wordId
1 | 1 | 1
2 | 1 | 2
3 | 1 | 3
4 | 2 | 4
5 | 2 | 2
6 | 2 | 3

SearchQueries:
id | queryId | word
1 | 1 | red
2 | 1 | shoes

SELECT * FROM `products` WHERE `productId` IN (SELECT `productId` FROM `searchlinks` WHERE `wordId` IN (SELECT `wordId` FROM `searchwords` WHERE `name` IN (SELECT `word` FROM `searchqueries` WHERE `queryId`='1')))

This could probably be improved to rank results by relativity.
 
Basically I am just searching through product names. So if somebody searches for red shoes it gets everything with that in the product name.

Would that still work with millions in the products table?

Isn't that just working as a cache for searches that have already been done?
 
nick-harper said:
Basically I am just searching through product names. So if somebody searches for red shoes it gets everything with that in the product name.

Would that still work with millions in the products table?

Isn't that just working as a cache for searches that have already been done?
Click to expand...

That's just a basic example, there are ways to improve the relativity. This should still work well with millions of items.

It is just a cache but the cache happens whenever a product is added, edited, or deleted it will add the necessary SearchWords (or increase/decrease the counts), and then add the necessary SearchLinks to link them to products. When someone types a query it will add it into the SearchQueries table before the query is run, and later pruned from the database or kept for records on what is searched for the most.
 
Sphinx is another option that will help you a lot. I had issues with fulltext searches and solved it with that.
 
Get an SSD for your MYSQL data.

Make sure your my.cnf key_buffer size is large enough to hold your index.
 
If you are doing multiple expensive queries per page load, a few slaves would make it possible to use parallelism.
 
If there's nothing you could do in the DB design to optimize it, use 'offset' and 'limit' in the queries so you can get the results one page at a time.
 
You must log in or register to reply here.
Top Bottom
Back