slow querying - what to do!

is querying a separate database quicker than tables in a larger db?

[Sebastiaan]

Hello all!
I've got a website that sells toner cartridges, and have programmed a script to allow users to select their printer, and it queries the db to return all the toners/ink carts compatible with that printer.

to make the dropdowns the db queries two tables, and there is a common key between the two (printer_id) One table lists all the cartridges against 'printer_id' and product 'sku', and the other table lists the printer name, type (laser, ink, etc), and Brand, also matched against the 'printer_id'

Essentially to get a result, takes 4 queries, as the user selects from a triple dropdown list, which is dynamically populated dependant on the previous dropdown.

query one: populates the select by brand, query two populates the type, and then query 3 populates the list of printers based on brand and type.

when a user selects a printer, a query using a few joins cross matches the product sku from the cartridge table to the product sku of the product table, and returns the products. Great! But all this can be quite slow (even the drop downs).

Should I just make the data needed for the drop downs into an array(s) straight in my php script to atleast make that part quicker, or what about moving the tables used to make the dropdowns into a separate database?

Am happy to post the script or queries if someone out there has the inclination to look at it :-)

cheers

seb

[edited by: jatar_k at 5:16 pm (utc) on Aug. 24, 2005]
[edit reason] no urls thanks [/edit]

[sun818]

> Should I just make the data needed for the drop
> downs into an array(s) straight in my php script

That sounds like a good idea. Look at how eBay does their Sell Your Item page with respect to their Category selector. Seems to me the person at the other end would benefit from all data being pre-loaded rather than having to wait for query results while they drill down.

[Sebastiaan]

OK,
So if I was going to do that, could you give me a brief example (i'm not strong on array stuff).
I.E. the fields printer_id, printer_name, type, brand has a row for each printer like:

HP-001, HP 4200 Laser Printer, Laser Printer, HP
HP-002, HP 4000 Laser Printer, Laser Printer, HP

etc.

Would I make each row into a single text string using a delimiter like a '¦', and then put each row in as one array element, and then use explode on the '¦' to separate them?

Or, would I make 4 separate arrays (one for each field)? and use the keys to match the data back up?

Any examples or direction appreciated :-)

Cheers

seb

[sun818]

Sorry, I'm not a programmer. Maybe someone else has an idea with the specifics.

[coopster]

Querying the database for an application such as this should be lightning fast. I would build my query statements and then use the database query analysis tools to examine the query to see where bottlenecks are found.