> Member::findByLetter() uses this condition:
> "lower(left(Member.last_name, 1)) = '${letter}'"
> I avoided using LIKE because I only want to look at the first letter, anyway.
This sounds like a bad idea for large tables. Assuming you have an
index on the `last_name` field, if you use this...
WHERE `Member`.`last_name` LIKE 'r%'
... it will use the index (it's a range query) and result in speedy
lookups
Using this...
WHERE LOWER(LEFT(`Member`.`last_name`, 1)) = 'r'
...will result in a full table scan, which is horribly inefficient.
(You can use EXPLAIN SELECT to see for yourself)
Case-sensitivity isn't really an issue because you can either set the
collation for the whole table as CI, or just that index. I can't
recall a single instance where the case insensitivity of a database
collation caused an issue, in any of my real-world projects. YMMV, of
course.
hth
grigri
On Apr 1, 4:38 pm, brian <bally.z...@gmail.com> wrote:
> I have something which does that. But it displays all names for a
> given letter, rather than using a paging limit. I can't figure out a
> clean way to do both.
>
> public function index($letter = null)
> {
> $letter = is_null($letter) ? 'a' : strtolower((string) $letter);
>
> $this->set('members', $this->Member->findByLetter($letter));
> $this->set('alpha_list', $this->Member->getAlphaList());
> $this->set('letter', $letter);
>
> /* render the element only so we don't end up with a nested mess
> */
> if ($this->RequestHandler->isAjax())
> {
> Configure::write('debug', 0);
> $this->viewPath = 'elements'.DS.'members';
> $this->render('alpha_list');
> }
>
> }
>
> Member::findByLetter() uses this condition:
>
> "lower(left(Member.last_name, 1)) = '${letter}'"
>
> I avoided using LIKE because I only want to look at the first letter, anyway.
>
> The rest is a bit more complicated, so I've posted it here:
>
> http://bin.cakephp.org/view/1433101916
>
> This is all based on some stuff I had with a non-Cake site. That one
> actually had a lot of the heavy lifting done in the DB using pl/python
> (Postgres).
>
> On Wed, Apr 1, 2009 at 3:52 AM, meximex <meixne...@gmail.com> wrote:
>
> > hi,
> > if i have an application with a list of 1000 items and want to jump to
> > the page of the items starting with M. how could y do that?
>
> > do i have to implement that by my self?
>
> > if you would do it by yourself, how would you do it?
>
> > create a loop for every letter and an sql that counts the items before
> > that letter?
>
> > isnt there something better in mysql?
>
> > thx for your help!
> > meximex
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---
