[Kyle]

Hi,

I've this code and it has 168 rows of data to collect.

"SELECT * FROM userdrugs WHERE Username='%s' LIMIT 0,1"

Here is half of the structure:



It goes upto gram179. For it to select and obtain the data it takes around 5 seconds.

What can I do to optimise and make it faster?

Thanks.

[AndreT]

Why on earth do you need 180 gramX fields, anyways? What does each zero stand for?

[Kyle]

Quote:

Originally Posted by AndreT Why on earth do you need 180 gramX fields, anyways? What does each zero stand for?

Knew you would ask this!

Anyway - I need it for each drug gram on the server so it can be logged.

The first zero stands for - Drug Gram Availibility: Whether it is allowed to be used slot or if it is taken.

The second zero stands for - Drug Gram Type - 0 means set it to 1 in the script. 1 means Stale drugs and 2 means freshly grown.

The third zero holds a time of upto 9999 and it counts down in the script. When it reaches 0 it means convert the drug type from 2 to 1.

[AndreT]

Okay, well, I think some experimenting would be allowed in this case and never having written a script related to drug handling myself, I can point out that maybe the amount of data stored in each field can be reduced, because what the first zero stands for can easily be implemented in the second zero, or the other way around.

So the zero would stand for its availability and type at the same time. If the availability is 0, then it cannot be used or w/e. If it is 1, then the type is stale, and 2 is freshly grown.

But I suppose this would not actually cut down much on the time it takes to retrieve the data, so I'm thinking that you maybe should have one huge text field (VARCHAR if you know what the limit will be). Not only does this make your amount of drug grams on the server virtually unlimited (well, I don't know if that's a good thing for your economy or not), but also might make the data loading faster, because 181 fields is an awful lot to take care of at once depending on your SQL server & tables set up.

Test it out with one huge field containing all the drug information for the player - perhaps you could even integrate it into your main players table at a later point to get rid of one query if this method proves to be worthy. What you have to do with the current setup is parse the field and that takes some time as well depending on your method. With the updated method, parsing is of course involved, as well, even at a more difficult level but I suppose you can handle it.