The 'seller' column is indexed and I have over 300,000 items.The first query runs through super quick (0.01 seconds).The second query takes 10 seconds.

I see that the Not Equals or "!=" does not work on an index. Is there a way to speed this up somehow?

ThanksKind regards

The reason for the performance difference is the quantity of result sets in the two queries - if there are 500 johndoes there, the first query will process those 500 records to get the first five, and the second will be working with 299,500 records.

I don't think your 2nd query will do exactly what you want - without any sort of sort order in it, it should return the same five records each time.

The actual queries are much larger and complex, which includes the randomization part. The only difference between the two queries is the "!=" part on the seller column, which is the difference between 0.01 seconds and 10 seconds.

As Rubble said, this won't find random items. Finding random items in SQL is very hard, because you first have to pull all the rows in, assign each row a unique number, then pick out randomly numbered rows, and check . It's much more than just a simple select statement because this is going way out of bounds of what SQL is really designed for. Even if you have a numeric ID column, it's still not going to be easy to do in SQL.

You're probably better off returning all results to a php array, then randomly pulling the array elements by number.

Unfortunately, I've tried all three methods and it's still taking just as long to perform the query. Even if I strip out any random generators and use LIMIT, it still takes just as long. You would think MySQL would have something for dealing with this.

I may be wrong, but I have a feeling that the problems you are having may be a result of less than optimal database design.

I'm far from a "normalization" expert, and because I had little experience crafting complex queries, I set up complex tables.They were OK as long as the amount of data was minimal, but they didn't scale well.

Short of breaking your table into smaller cross-referenced relational tables, you could try explicity specifying the fields you're interested in getting instead of using the * wildcard. That might help some.

You could run the search code say every night at midnight and generate an array just with the row ID - this would negate any time taken - the array will be written to a file on the server.When the user visits the page 5 random values are taken from the two arrays, the data is fetched from the database and displayed.

I may be wrong, but I have a feeling that the problems you are having may be a result of less than optimal database design.

Well, remember he's also doing some randomization in his SQL statement and isn't giving us his fully query. I think there is something else going on. Like I said earlier, doing the random selection in SQL is probably the least preferred method.

Well, remember he's also doing some randomization in his SQL statement and isn't giving us his fully query. I think there is something else going on. Like I said earlier, doing the random selection in SQL is probably the least preferred method.

I modified the question a bit to remove any randomization. All I want is to get this simple function to work in a fast manner:

You could run the search code say every night at midnight and generate an array just with the row ID - this would negate any time taken - the array will be written to a file on the server.When the user visits the page 5 random values are taken from the two arrays, the data is fetched from the database and displayed.

This could work, although our site has over 10,000 categories and subcategories with many items within them. I'm guessing this would end up being impractical.

There must be another way, I know I'm not the only one on the internet who shows a list of "related items from other sellers" on their sites.

I am certain you will get a massive speed increase, since now we first limit the query to any records in category 21 that is not made by seller johndoe, instead of limiting the query to any records not made by seller johndoe that is also in category 21.

However, the problem you will see when this works is that you will always pull the same 100 records (unless some is deleted) i.e. ASC order. In addition the RAND() function in SQL should be avoided like the plague since it quite honestly is a "DB killer" on larger tables.

However, please note that LIMIT X,Y is also very slow. The reason for this is due to if you have a table with a million records and write: LIMIT 997000, 100 the SQL engine actually read all 997000 records to get to the ones you want to pull. This means if you want to speed this up more than the suggested query above, you need to alter your database models with this in mind, allowing you to base queries on primary keys, and being certain that you can actually use it internally in the software after.