Need reality check with my use of myxql

[nat]

Greetings, I am new to both MySQL and using the xql UDF. I am using
the xql UDF to produce a master xml document of concerts/music events
that is then used by XSLT to produce the various public XHTML pages.

I have a couple concerns about whether I am implementing xql API and
MySQL in the best way.

1. I am unable to make indexing changes in MySQL that seem to effect
the queries or "feedback" from MySQL EXPLAIN. Does the fact that I am
using xql have any impact on how to optimizer the quieres?

2. Here is the current code (entire page) I am using to make the xml,
is this sensible or am I being an idiot?

<?php
header('Content-Type: application/xml');
require($_SERVER['DOCUMENT_ROOT']."/database.php");
$db = new mysqli(DATABASE_HOST, DATABASE_USER, DATABASE_PASSWORD,
DATABASE_NAME);
$result = $db->query("SELECT xql_concat(xql_textdecl('1.0',
'UTF-8'),
xql_agg('calendar' AS `xql:root`,
xql_element('event' AS `xql:child`,
xql_forest(event_status AS `status`,
(SELECT content FROM `_cms_media` WHERE image_id = _cms_media.id)
AS `photo`,
header AS `header`,
headline AS `headline`,
support AS `support`,
xql_element('location' AS `xql:child`,xql_forest(
xql_element('name',(SELECT venue_name FROM `_cms_calendar_venue`
WHERE venue_id = _cms_calendar_venue.id) AS `venue`),
xql_element('adr' AS `xql:child`,
xql_forest((SELECT venue_address FROM `_cms_calendar_venue` WHERE
venue_id = _cms_calendar_venue.id) AS `street-address`,
(SELECT venue_city FROM `_cms_calendar_venue` WHERE venue_id =
_cms_calendar_venue.id) AS `locality`,
(SELECT venue_state FROM `_cms_calendar_venue` WHERE venue_id =
_cms_calendar_venue.id) AS `region`))),venue_id AS `id`),
xql_element('date', DATE_FORMAT(show_time,'%b %e') AS `date`,
show_time AS `title`),
CONCAT('Doors: ',DATE_FORMAT(door_time,'%H:%i'),' Show: ',
DATE_FORMAT(show_time,'%H:%i')) AS `time`,
event_ages AS `ages`,
event_tags AS `category`,
xql_element('onsale', IF(tickets_onsale >= NOW(),CONCAT('on sale
',DATE_FORMAT(tickets_onsale,'%W, %b %e at %l:%i%p')),'now') AS
`onsale`, DATE_FORMAT(tickets_onsale,'%Y-%m-%dT%H:%i:%s-05:00') AS
`title`),
ticket_link AS `ticket-link`,
price AS `price`), id, announce))
)
FROM `_cms_calendar_shows` WHERE show_time >= NOW() AND announce <
NOW()");
list($xml) = $result->fetch_row();
print_r($xml);
?>

If would greatly appreciate any guidance or advise if I am doing
anything that is an obvious "gotcha" or missing any crucial best
practices.

-n

[Nat Woodard]

Thanks Arnold, that helps. When I say idiot I am referring to the
fact that I am implementing sub-queries unnecessarily and don't even
know it. I have tried to do a normal join (by following examples on
the udf) but haven't been able to make it work, so I resorted to a
row by row hack, but I am experiencing the performance issues. By
doing a join in the FROM are you referring to final FROM part?

-n

On Mar 4, 2008, at 7:38 PM, Arnold Daniels wrote:

> Hi Nat,
>
> 1.) No it shouldn't.
>
> 2.) You're not an idiot. I've never tried using subqueries in the
> SELECT part, I use them in the FROM part. I think that putting the
> subqueries in the SELECT part, will not be good for the
> performance, since a query is executed for each row.
>
> In this example you don't seem to aggregate any of the data from
> the subqueries. If you don't need aggregated subdata, like multiple
> locations for an event, you don't need subqueries, just do a normal
> joint over all the tables. Otherwise you should use xql_agg() in
> the subquery.
>
> 3.) Some small notices:
> - For xql_element() the field name is only used with the
> attributes, so an alias (AS) for the first 2 arguments isn't
> necessary.
> - To output you should use echo, not print_r.
>
> Good luck,
> Arnold

[Nat Woodard]

Thanks Arnold, that helps, I really appreciate your help. I have been
working on loosing the selects but still get errors when I introduce
the join. I have simplified but am unable to figure out what I am
missing. I wasn't sure how to convert the old selects I was using.
When I introduce "'content' AS `photo`" and the join I get an error.
The WHERE is an arbitrary single event call for testing purposes.

$db->query("SELECT xql_concat(xql_textdecl('1.0', 'UTF-8'),
xql_agg('calendar' AS `xql:root`, xql_element(

'event', xql_forest('content' AS `photo`, event_status AS
`status`,header,headline), id, announce)
)
)
FROM _cms_calendar_shows LEFT JOIN _cms_media ON
_cms_calendar_shows.image_id = _cms_media.id WHERE
_cms_calendar_shows.id = 400");

On Mar 4, 2008, at 8:26 PM, Arnold Daniels wrote:

> It should something like
>
> SELECT ... FROM _cms_calendar_shows LEFT JOIN _cms_media ON
> _cms_calendar_shows.image_id = _cms_media.id LEFT
> JOIN_cms_calendar_venue ON _cms_calendar_shows.venue_id =
> _cms_calendar_venue.id WHERE ...
>
> Try making a query without xql, but with all the data, first.
>
> +a :p

[Arnold Daniels]

It should something like

SELECT ... FROM _cms_calendar_shows LEFT JOIN _cms_media ON
_cms_calendar_shows.image_id = _cms_media.id LEFT
JOIN_cms_calendar_venue ON _cms_calendar_shows.venue_id =
_cms_calendar_venue.id WHERE ...

Try making a query without xql, but with all the data, first.

+a :p

[Arnold Daniels]

Hi Nat,

1.) No it shouldn't.

2.) You're not an idiot. I've never tried using subqueries in the SELECT
part, I use them in the FROM part. I think that putting the subqueries
in the SELECT part, will not be good for the performance, since a query
is executed for each row.

In this example you don't seem to aggregate any of the data from the
subqueries. If you don't need aggregated subdata, like multiple
locations for an event, you don't need subqueries, just do a normal
joint over all the tables. Otherwise you should use xql_agg() in the
subquery.

3.) Some small notices:
- For xql_element() the field name is only used with the attributes, so
an alias (AS) for the first 2 arguments isn't necessary.
- To output you should use echo, not print_r.

Good luck,
Arnold

[Arnold Daniels]

Hi Nat,

Again, try to make a query without xql functions first and get that
working. I don't think you want to quote 'content'. That means "content"
as literal string, not field. You probably want to use backquotes
`_cms_media`.`content` AS `photo`.

So
a.) Write a query with the xql functions.
b.) If you get an error, look closely at the query and error message.
MySQL error messages aren't that clear, but you should be able to figure
it out.
c.) Once the query works, see if the data looks correct.
d.) Add xql functions to make XML.

Please try to do a to c on your own. It might take you some effort, but
that is just a peril of learning MySQL. Remember, google is your friend.

Good luck,
Arnold

>>>>> NOW(),CONCAT('on sale

[varadha rajan]

Hello all,

         Hi, I am Varadharajan.A from India. I am doing Masters in Engineering in Computer Science. I am doing my final year project in MySQL. I would like to contribute some advanced functions to MySQL. I have implemented the following simple functions: InitCap(), Sinh(), Cosh(), Tanh(), Asinh(), Acosh(), Atanh() and Binom(). I am now implementing the concept – GiST (Generalized Search Tree). I would like to implement some more functions too. 
         I have included my functions in the MySQL - Open Source Code. I need to test whether my functions work properly. Is there any software available to test it?

 

                                                                                                  Thank You,

                                                                                                Varadharajan.A

[Arnold Daniels]

Hi,

There is a Test Suite for MySQL call mysql-test.
http://dev.mysql.com/doc/refman/5.1/en/mysql-test-suite.html

It works kind of like unit testing. You write your queries, than execute
them. Have a close look at if the results are correct and than use a
flag to store the results. Next time, after modifying your functions,
you can check if nothing broke.

Good luck,
Arnold