montymoose
Posts: 85
|
| Posted: 03/13/2008, 4:37 AM |
|
Hello, I'm having difficulty with codecharge and one of my larger databases:
I have a table called ITEMS which contains information about many historical items. I then have 3 search tables SUBJECTS_LINK / PERIOD_LINK / TYPE_LINK - which map each item to any number of subjects periods and types.
EG
~TABLE_ITEMS_LINK~
ITEM_ID | ITEM_TITLE
2344 | Monet school photograph
~TABLE SUBJECT_LINK~
SUBJECT_LINK_ID | SUBJECT_LINK_ITEM | SUBJECT_LINK_SUBJECT
1 | 2344 | 23
2 | 2344 | 26
~TABLE SUBJECTS~
SUBJECT_ID | SUBJECT_TITLE
23 | Art
24 | History
25 | Music
26 | Education
This example shows how item 2344 (Monet school photograph) is linked to both subjects 'Art' and 'Education'.
There are about 45,000 items and each is linked to 2-4 subjects. Thus the SUBJECT_LINK table has almost 140,000 rows in it.
I am building a CodeCharge 3 page (PHP MYSQL LATEST VERSIONS) to display items with a search to allow a user to filter results by SUBJECT, TYPE and PERIOD.
Using the following query:
SELECT items_name
FROM ((items
INNER JOIN search_subject ON
search_subject.search_subject_linked = items.items_genid)
INNER JOIN search_period ON
search_period.search_period_linked = items.items_genid)
INNER JOIN search_type ON
search_type.search_type_linked = items.items_genid
WHERE search_subject.search_subject_data = {s_subject}
AND search_period.search_period_data = {s_period}
AND search_type.search_type_data = {s_type}
(I am aware that the table/field names don't match those in the example above, I was just simplifiying it for the example - the field names in this SQL block are correct)
It does work, but runs painfully slowly and often crashes out to a white page or timeout error.
Is there anything I can do to either speed up this query, or just speed up my entire MYSQL server/database. (I've already tried lowering the max_packet_size and optimizing all the tables.
Many thanks for reading what seems to have turned into a novel of a post! Any suggestions welcomed...
M00S3 
|
 |
|
datadoit
|
| Posted: 03/13/2008, 4:22 AM |
|
Perhaps try creating a db VIEW and use that for your grid. That should
speed things up a little.
|
|
|
|
montymoose
Posts: 85
|
| Posted: 03/13/2008, 6:12 AM |
|
Bit of a n00b sorry - how do you use DB views in codecharge?
|
|
|
|
ReneS
Posts: 225
|
| Posted: 03/13/2008, 1:55 PM |
|
Hi,
Create a view in your database, not in CCS. Then use that view like you would use a table in CCS.
Rene
|
|
|
|
GeorgeS
Posts: 206
|
| Posted: 03/13/2008, 3:45 PM |
|
I have one DB with the same table structure:
Categories - 5,000 records
Products - 650,000 records
and
CategoriesProducts - 1,700,000 records
Joining tables never worked for me with this amount of data, so I'm not sure about views.
Let us know if it worked for you.
You want to search one table (Products) and on BeforeShowRow event do custom SQL and get data form other table(s)
This way you will get data just for the current page from other tables.
Also, do not forget to create indexes for all columns that will be used for search!
This will speed things up many times.
_________________
GeorgeS |
|
|
|
wkempees
Posts: 1679
|
| Posted: 03/13/2008, 4:18 PM |
|
take your sql, with some sample subject/period/type values
(prefferably the larger set) and feed it to PhPmyAdmin, Heidi or Navicat.
Lik this:
In designer, Project Explorer->Common Files->db_mysql.php search for Debug = 0;
Switch that to 1 and your page will display the constructed SQL, copy the SQL from there feed it to
(in my case) Navicat and run it. Next put the word EXPLAIN in front of the SELECT.......
MySQL will show you exactly how the query is executed, which path and which indexes.
You might be suprised........., optimize from there.
Second hint:
If this is a Search-Grid combi (80% chance), and the search has like 3 listboxes that need to be choosen from before the Search is executed, I would:
1: make the listboxes required, so they need to be carrying a value to tighen the dataset
2:make the grid's SELECT have default values of -1 for each s_listbox, to make sure that initally no rows are selected.
Your inital page will be fast, as it has nothing to do, the user selects values from the listboxes and fires the query, which after being optimized, will need less and less time to execute.
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
RonB
Posts: 228
|
| Posted: 03/13/2008, 4:37 PM |
|
check your indexes on the tables. make indexes for the search tables.
Ron
|
|
|
|
montymoose
Posts: 85
|
| Posted: 03/14/2008, 5:10 AM |
|
Thanks for all your help, I am new to large datasets, I have been using mysql for years, but never with more than about 800 records per table. This is all new stuff for me.
Thanks.
|
|
|
|
datadoit
|
| Posted: 03/14/2008, 10:06 AM |
|
Don't sweat it. There are actually specific jobs in corporations whose
sole purpose is to optimize databases. If you agree with time=money,
then it's very understandable.
|
|
|
|
|
 |
|
|
|
